Integration Tests with a Real Database

Test against the same database engine as production (PostgreSQL via Docker, Testcontainers or a CI service). Apply Alembic migrations once per session, then isolate each test: run it inside a transaction that's rolled back (binding the session to a connection with an outer transaction), or truncate tables. Override the get_db dependency to use the test session.

import pytest
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from app.db import get_db
from app.main import app

engine = create_async_engine(TEST_DATABASE_URL)


@pytest.fixture
async def db():
    async with engine.connect() as conn:
        trans = await conn.begin()
        session = AsyncSession(bind=conn, join_transaction_mode="create_savepoint", expire_on_commit=False)
        app.dependency_overrides[get_db] = lambda: session
        try:
            yield session
        finally:
            await session.close()
            await trans.rollback()          # undo everything the test did
            app.dependency_overrides.pop(get_db, None)

Common pitfalls

• Using SQLite to test PostgreSQL-specific SQL.
• Tests that commit for real and pollute later tests.
• Creating schemas with create_all instead of running migrations (migrations go untested).

Key points

• Same engine as production
• Rollback-per-test isolation
• Run real migrations

Further reading: SQLAlchemy: joining an external transaction