skip to content

How do you override a FastAPI session dependency so tests use an in-memory SQLite database, and why does that need StaticPool?

level: middleimportance: should knowfreq 48%

answer

  1. a fixture owns the test engine
  2. override returns the fixture's session
  3. each memory connection is a new database
  4. requests run on other threads

basics

~10 s

A fixture builds a sqlite:// engine with StaticPool and check_same_thread=False, creates the tables and yields a Session; a second fixture maps get_session to a function returning it in app.dependency_overrides and removes the entry afterwards.

solid answer

~40 s

I keep the app's `get_session` yield dependency unchanged and swap it in tests. A `session` fixture calls `create_engine("sqlite://", connect_args={"check_same_thread": False}, poolclass=StaticPool)`, runs `create_all`, and yields a `Session`. A `client` fixture sets `app.dependency_overrides[get_session] = lambda: session`, yields a `TestClient`, and removes the entry in teardown. `StaticPool` matters because every new connection to `sqlite://` is a separate, empty database, and the request is served on other threads than the fixture: the default pool for an in-memory SQLite engine keeps one connection per thread, so the endpoint sees no tables. `StaticPool` hands every thread the same single connection, and `check_same_thread=False` lets SQLite accept it from another thread.

code

python · 61 lines
python
from typing import Annotated

import pytest
from fastapi import Depends, FastAPI
from fastapi.testclient import TestClient
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import StaticPool


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    item: Mapped[str] = mapped_column(String(50))


engine = create_engine("sqlite:///./app.db")  # the real engine; tests never connect to it


def get_session():
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]
app = FastAPI()


@app.post("/orders")
def create_order(item: str, session: SessionDep):
    order = Order(item=item)
    session.add(order)
    session.commit()
    return {"id": order.id}


@pytest.fixture(name="session")
def session_fixture():
    test_engine = create_engine(
        "sqlite://", connect_args={"check_same_thread": False}, poolclass=StaticPool
    )
    Base.metadata.create_all(test_engine)
    with Session(test_engine) as session:
        yield session


@pytest.fixture(name="client")
def client_fixture(session: Session):
    app.dependency_overrides[get_session] = lambda: session
    yield TestClient(app)
    app.dependency_overrides.pop(get_session, None)


def test_create_order(client: TestClient, session: Session):
    response = client.post("/orders", params={"item": "book"})
    assert response.status_code == 200
    assert session.scalars(select(Order.item)).all() == ["book"]

go deeper

for a junior

Recall the two fixtures: one builds the in-memory engine and session, the other registers the override for get_session and removes it.

for a middle

Explain why sqlite:// needs StaticPool and check_same_thread=False when FastAPI serves the request on other threads than the fixture.

for a senior

Show how to diagnose 'no such table' and same-thread errors in a test run, and why the override belongs on the session dependency, not module-level engine code.

for a principal

Weigh in-memory SQLite speed against fidelity to the production database, and decide which suites run where.

## The shape of the application A FastAPI app usually opens one database session per request with a `yield` dependency: ```python def get_session(): with Session(engine) as session: yield session SessionDep = Annotated[Session, Depends(get_session)] ``` Endpoints and other dependencies take `SessionDep`. For tests, nothing in the app changes: the test suite registers an override for `get_session`, the original callable inside the alias, so every consumer receives a session bound to a test database instead. ## The two fixtures 1. **`session` fixture.** Create a test engine with `create_engine("sqlite://", connect_args={"check_same_thread": False}, poolclass=StaticPool)`, create the schema with `Base.metadata.create_all(test_engine)`, then `yield` a `Session(test_engine)`. 2. **`client` fixture.** Depend on `session`, set `app.dependency_overrides[get_session] = lambda: session`, `yield TestClient(app)`, and in teardown remove the entry with `app.dependency_overrides.pop(get_session, None)`. The test then requests both `client` and `session`: it drives the endpoint through the client and checks the database through the same session object the endpoint used. The real `get_session` is never called, so the production engine never opens a connection. ## Why `sqlite://` alone fails `sqlite://` with no file name means an **in-memory database**. Its peculiarity: - every new SQLite connection to `:memory:` creates its own, empty database; - the tables created by `create_all` exist only on the connection that ran it. A FastAPI request in a test is not served on the thread that ran the fixture. `TestClient` drives the application through an event loop in a separate thread, and FastAPI runs `def` endpoints and `def` dependencies in a thread pool. SQLAlchemy's default pool for an in-memory SQLite engine keeps **one connection per thread**, so when the session is first used on a worker thread it gets a brand-new connection, therefore a brand-new empty database, and the insert fails with `no such table`. ## What each setting fixes | Setting | Problem it solves | Error without it | |---|---|---| | `poolclass=StaticPool` | all threads share exactly one connection, so they see one database | `sqlite3.OperationalError: no such table: orders` | | `connect_args={"check_same_thread": False}` | the `sqlite3` driver allows that connection to be used from a thread other than its creator | `sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that same thread` | Both are needed together: `StaticPool` creates the sharing, and `check_same_thread=False` permits it. ## Returning the session versus yielding a new one There are two reasonable override shapes: - **`lambda: session`** returns the fixture's session. The test and the endpoint share one object, so assertions see uncommitted state and identity-mapped objects exactly as the endpoint left them. The fixture closes it. - **A `yield` override** such as `def override(): with Session(test_engine) as s: yield s` gives each request its own session on the shared connection, closer to production. FastAPI runs its teardown after the request, as it would for the original. Either way the engine and schema belong to a fixture, so each test starts from a fresh in-memory database when the fixture is function-scoped. Isolating tests by transaction rollback, or running them against the same database product as production, is a database-testing topic rather than a FastAPI one. ## Pitfalls - Keying the override by `SessionDep` instead of `get_session`: the alias is never looked up, and the real database is used. - Creating the engine at module import with the production URL and expecting the override to change it: the override replaces the dependency, not module-level code. - Pointing the test engine at a file such as `sqlite:///./test.db`: the file survives between runs, so a test can pass because of rows an earlier run left behind. - Making the session fixture session-scoped to save time: every test then shares one in-memory database, and tests start depending on each other's rows. With in-memory SQLite, a function-scoped fixture that runs `create_all` per test is usually cheap enough. - Clearing the overrides inline after the request instead of in teardown: an assertion failure skips the line, and a later test that sets no override of its own still talks to this test's database. ## Summary Override the session dependency, not the engine: a fixture owns a `sqlite://` engine with `StaticPool` and `check_same_thread=False`, and `app.dependency_overrides[get_session]` hands its session to every consumer for the duration of one test.

  • What error do you see if StaticPool is left out but check_same_thread=False is kept?
    Typically `sqlite3.OperationalError: no such table`. The default pool for an in-memory SQLite engine gives each thread its own connection, so the thread serving the request opens a fresh, empty in-memory database that never ran `create_all`.
  • Does an async def endpoint avoid the threading problem?
    Not with `TestClient`: it runs the app's event loop on its own thread, separate from the test thread that created the session in the fixture. Synchronous dependencies such as a `def` yield override also run in FastAPI's thread pool. The `StaticPool` plus `check_same_thread=False` pair is still needed for a shared in-memory database.

  • Pointing the engine at sqlite:// is enough; every connection sees the same memory database.
  • check_same_thread=False alone makes all threads share the in-memory tables.
  • The override should replace the engine object rather than the session dependency.
  • Overriding the SessionDep alias swaps the session for every endpoint.
  • FastAPI runs the whole request on the test function's thread, so threading cannot matter.