How to test FastAPI with a real database using pytest fixtures

Database Testing with Fixtures

The safest pattern for database tests is to roll back each test's changes, leaving the database clean for the next test without needing to drop and recreate tables.

Transaction rollback fixture

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from fastapi.testclient import TestClient
from app.main import app
from app.database import Base
from app.dependencies import get_db

@pytest.fixture(scope="session")
def db_engine():
    engine = create_engine("sqlite:///:memory:")
    Base.metadata.create_all(engine)
    yield engine
    engine.dispose()

@pytest.fixture
def db_session(db_engine):
    connection = db_engine.connect()
    transaction = connection.begin()
    Session = sessionmaker(bind=connection)
    session = Session()
    yield session
    session.close()
    transaction.rollback()
    connection.close()

@pytest.fixture
def client(db_session):
    app.dependency_overrides[get_db] = lambda: db_session
    yield TestClient(app)
    app.dependency_overrides.clear()

The db_engine is session-scoped (created once). The db_session fixture is function-scoped — a new transaction is opened and rolled back for every test. This is the fastest and cleanest isolation strategy.