Database Fixtures with Pytest

We need a database for our tests.

The fixture in tests/conftest.py gives every test its own clean SQLite instance, an isolated Session, a TestClient wired to override the production session, and a few of pre-populated rows for tests that need a starting state.

---

The Session Fixture

The session fixture builds a fresh in-memory database, creates every table from SQLModel.metadata, and yields a Session bound to it.

tests/conftest.py

@pytest.fixture(name="session")
def session_fixture() -> Generator[Session]:
    engine = create_engine(
        "sqlite://",
        connect_args={"check_same_thread": False},
        poolclass=StaticPool,
    )
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        yield session

---

The Client Fixture

TestClient is a synchronous HTTP client from FastAPI's test utilities; under the hood, it wraps the ASGI app and lets tests issue client.get(...), client.post(...), and so on. The client fixture wires it up so every request inside a test uses the per-test session, not the real database.

tests/conftest.py

@pytest.fixture(name="client")
def client_fixture(session: Session) -> Generator[TestClient]:
    def get_session_override():
        yield session

    app.dependency_overrides[get_session] = get_session_override
    client = TestClient(app)
    yield client
    app.dependency_overrides.clear()
    get_engine.cache_clear()

---

Sample Data Fixtures

Most tests need at least one project to act on, and most task tests need at least one task. Two helper fixtures keep that boilerplate out of the tests themselves:

tests/conftest.py

@pytest.fixture()
def sample_project_id(client: TestClient) -> int:
    response = client.post(
        "/projects/",
        json={
            "name": "Release Platform",
            "description": "Coordinates planning for the production release.",
        },
    )
    assert response.status_code == 201
    return response.json()["id"]


@pytest.fixture()
def sample_task_id(client: TestClient, sample_project_id: int) -> int:
    response = client.post(
        f"/projects/{sample_project_id}/tasks",
        json={
            "title": "Wire up the dashboard",
            "details": "Connect the API to the static frontend.",
            "status": "planned",
            "priority": "medium",
        },
    )
    assert response.status_code == 201
    return response.json()["id"]

---

Reading the Tests

A test written with these fixtures stays focused on the behavior we're testing:

tests/test_tasks.py

def test_get_task_not_found(client: TestClient):
    response = client.get("/tasks/9999")
    assert response.status_code == 404
    assert response.json() == {"detail": "Task not found"}


def test_list_tasks_filter_by_status(
    client: TestClient, sample_project_id: int
):
    client.post(
        f"/projects/{sample_project_id}/tasks",
        json={"title": "Planned task", "status": "planned"},
    )
    client.post(
        f"/projects/{sample_project_id}/tasks",
        json={"title": "Done task", "status": "done"},
    )

    response = client.get("/tasks", params={"status": "done"})
    assert response.status_code == 200
    titles = [task["title"] for task in response.json()]
    assert titles == ["Done task"]

---

Fixture scope

Every fixture in this chapter uses the default function scope, which means a fresh database per test. That's the safest default.

Faster scopes (module, session) reuse state between tests, which is a useful optimization when the test suite gets large but introduces ordering dependencies that can cause confusing failures.