Testing Patterns

Two main strategies for testing code that hits the DB:

1. In-memory SQLite — fast, no setup, but different from production.
2. Real Postgres with transactional rollback — slower setup, accurate, isolated tests.

Choose based on what your code does. Anything DB-specific (JSONB queries, Postgres-only functions, isolation behavior) needs real Postgres.

The transactional rollback pattern — the standard

Each test runs inside a transaction that’s rolled back at teardown. Fast and isolated; tests don’t see each other’s data.

# conftest.py
import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session

@pytest.fixture(scope="session")
def engine():
    return create_engine("postgresql+psycopg://user:pw@localhost/test_db")

@pytest.fixture(scope="session")
def setup_db(engine):
    Base.metadata.create_all(engine)
    yield
    Base.metadata.drop_all(engine)

@pytest.fixture
def db_session(engine, setup_db) -> Iterator[Session]:
    connection = engine.connect()
    trans = connection.begin()
    session = Session(bind=connection)

    yield session

    session.close()
    trans.rollback()
    connection.close()

Pattern:

1. Open a connection + outer transaction.
2. Bind a session to that connection.
3. Run the test — anything it commits is committed within the outer transaction.
4. Roll back the outer transaction in teardown.

Every test starts with an empty DB. No data leaks between tests. Hundreds of tests run in seconds.

The savepoint-restart pattern

The simple “outer transaction, rollback at end” breaks if the code under test calls session.commit(). The commit ends the inner transaction; the rollback at teardown has nothing to undo.

The fix: rollback to a savepoint instead, and use a “join_transaction_mode” pattern:

@pytest.fixture
def db_session(engine, setup_db):
    connection = engine.connect()
    trans = connection.begin()

    session = Session(
        bind=connection,
        join_transaction_mode="create_savepoint",
    )

    yield session

    session.close()
    trans.rollback()
    connection.close()

join_transaction_mode="create_savepoint" means: every session.commit() actually issues a RELEASE SAVEPOINT (not a real COMMIT). The outer transaction stays open. At teardown, trans.rollback() discards everything.

Now the code under test can session.commit() freely and tests still roll back cleanly.

In-memory SQLite for fast unit tests

@pytest.fixture
def engine():
    return create_engine("sqlite:///:memory:", connect_args={"check_same_thread": False}, poolclass=StaticPool)

@pytest.fixture
def db_session(engine):
    Base.metadata.create_all(engine)
    with Session(engine) as session:
        yield session
    Base.metadata.drop_all(engine)

StaticPool and check_same_thread=False allow the in-memory DB to be shared across threads (the FastAPI test client uses multiple threads).

Trade-offs:

• Fast: ~1ms per test setup.
• No setup: no Postgres needed.
• Different SQL dialect: SQLite doesn’t have JSONB, isolation levels behave differently, no ARRAY type, no real EXCLUDE constraints. If you use Postgres-specific features, tests pass on SQLite but break in production.

For pure model + business logic tests (no DB-specific SQL), SQLite is fine. For integration tests against real query plans, use Postgres.

Test database setup

# conftest.py — Postgres with create/drop per session
import os
from sqlalchemy import create_engine
from sqlalchemy.engine.url import make_url

@pytest.fixture(scope="session")
def engine():
    url = make_url(os.environ["DATABASE_URL"])
    test_db = f"test_{url.database}_{os.getpid()}"

    # Connect to "postgres" admin DB, create the test DB
    admin_url = url.set(database="postgres")
    admin_engine = create_engine(str(admin_url), isolation_level="AUTOCOMMIT")
    with admin_engine.connect() as conn:
        conn.execute(text(f'DROP DATABASE IF EXISTS "{test_db}"'))
        conn.execute(text(f'CREATE DATABASE "{test_db}"'))

    test_url = url.set(database=test_db)
    test_engine = create_engine(str(test_url))
    Base.metadata.create_all(test_engine)
    yield test_engine
    test_engine.dispose()

    with admin_engine.connect() as conn:
        conn.execute(text(f'DROP DATABASE "{test_db}"'))

Or simpler with pytest-postgresql (spins up a dedicated Postgres process per test session).

For CI: a docker-compose with Postgres, GitHub Actions services: postgres:, or a managed test DB.

Migrations in tests — should they run?

Two approaches:

For most tests, create_all is fine. Add a separate “migration test” that:

1. Sets up empty DB.
2. Runs alembic upgrade head.
3. Compares schema to Base.metadata (e.g. via alembic check or assert_schemas_match).

Catches “model added, forgot the migration.”

Factories — test data setup

import factory

class UserFactory(factory.alchemy.SQLAlchemyModelFactory):
    class Meta:
        model = User
        sqlalchemy_session_persistence = "commit"   # or "flush"

    name = factory.Faker("name")
    email = factory.Faker("email")

@pytest.fixture
def user_factory(db_session):
    UserFactory._meta.sqlalchemy_session = db_session
    return UserFactory

Usage:

def test_user_has_email(user_factory):
    alice = user_factory(name="alice")
    assert "@" in alice.email

factory_boy (factory.alchemy.SQLAlchemyModelFactory) handles SQLAlchemy-specific quirks like passing the session.

For simpler setups, just write helpers:

def make_user(session, **overrides):
    user = User(name="alice", email="a@b.com", **overrides)
    session.add(user)
    session.flush()
    return user

Testing async code

import pytest_asyncio
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine

@pytest_asyncio.fixture
async def engine():
    return create_async_engine("sqlite+aiosqlite:///:memory:")

@pytest_asyncio.fixture
async def session(engine):
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
    async with AsyncSession(engine) as session:
        yield session

@pytest.mark.asyncio
async def test_create_user(session: AsyncSession):
    session.add(User(name="alice"))
    await session.commit()

Plus pytest-asyncio (pip install pytest-asyncio, set asyncio_mode = "auto" in pytest config).

The same transactional-rollback pattern works for async — just await everywhere.

Testing with FastAPI’s TestClient

from fastapi.testclient import TestClient

@pytest.fixture
def client(db_session, app):
    def override_get_session():
        yield db_session

    app.dependency_overrides[get_session] = override_get_session
    yield TestClient(app)
    app.dependency_overrides.clear()

def test_create_user(client):
    response = client.post("/users", json={"name": "alice"})
    assert response.status_code == 201

dependency_overrides swaps the production get_session for one that yields the test session — every endpoint in the test uses the same session, transactional rollback works end-to-end.

What to test — and what not to

Focus on behavior unique to your code, not the library’s job.

Snapshot / golden testing for complex queries

For queries with intricate SQL output (window functions, complex joins), snapshot the generated SQL:

def test_complex_query_sql():
    stmt = build_complex_query(...)
    sql = str(stmt.compile(compile_kwargs={"literal_binds": True}))
    assert sql == EXPECTED_SQL

Catches accidental regressions in query plan. Combine with EXPLAIN ANALYZE perf tests for the hot queries.

Common pitfalls

• Test using a shared DB without isolation — tests pass alone, fail when parallel.
• Code under test commits, breaking outer-transaction rollback — use join_transaction_mode="create_savepoint".
• Test DB drift from production — use Alembic for both; run alembic check in CI.
• Testing ORM by querying a giant fixture dataset — slow. Build small focused fixtures per test.
• Sharing one session across tests — leaks state. Each test gets a fresh session.

Common interview confusions

• “In-memory SQLite is a drop-in replacement for Postgres.” — close enough for many tests; breaks on JSONB, arrays, isolation, advanced features. Use Postgres if your code uses Postgres-specific features.
• “Transactional rollback prevents commits.” — only if you set join_transaction_mode="create_savepoint". The naive setup breaks if the code under test commits.
• “Test factories are overkill.” — for small tests, plain helpers are fine. Factories pay off when you have many models with many required fields.

Interview angle

• “How do you isolate tests that hit a real database?” — open a connection and a transaction in the fixture, bind a session to that connection, run the test, rollback the transaction at teardown. Each test sees an empty DB; tests are fast.
• “What if the code under test calls commit()? Your rollback won’t undo it.” — use join_transaction_mode="create_savepoint". Inner commits become RELEASE SAVEPOINT; the outer transaction stays open and the teardown rollback works.
• “SQLite in-memory vs real Postgres for tests — which?” — SQLite for pure-Python / model logic (fast, no setup). Postgres for anything DB-specific (JSONB, isolation, indexes, query plans). Real-DB tests catch real bugs.
• “How do you test migrations?” — set up empty DB → alembic upgrade head → compare schema to current Base.metadata. Or do a roundtrip: upgrade then downgrade and verify clean state.
• “How do you keep test data setup readable?” — factory-boy (or hand-written helpers) for objects, fixtures for shared setup. Avoid one giant setUp() building 50 unrelated rows.
• “How does FastAPI’s dependency_overrides help with testing?” — replace get_session (or any dependency) with a test-fixture version. Endpoints under test use your test session; rollback at teardown isolates them.