Testing FastAPI Without DB Mocks

Don't mock your database in tests.

The reference FastAPI project I keep has 71 tests at 100% coverage running in under one second — hitting a real SQLAlchemy session, without spawning a server, without docker-compose, without faking SQL.

The setup that makes this work:

1. SQLite in-memory + `StaticPool`. A single shared in-memory database for the whole test session. `connect_args={"check_same_thread": False}` lets multiple async sessions touch it.
2. `pytest-asyncio` in mode `auto`. Every `async def test_...` runs as a coroutine without the `@pytest.mark.asyncio` decorator on each one.
3. `AsyncClient` over `ASGITransport`. No socket, no TCP, no byte serialisation. `transport = ASGITransport(app=app)` and you call your handlers directly in memory. Each request is one coroutine.
4. `app.dependency_overrides[get_session] = override_session`. Swap the prod session factory for the SQLite one at the FastAPI DI layer. Restore it with `app.dependency_overrides.clear()` in teardown.
5. Per-test schema reset. A fixture truncates `books` between tests so each one starts on an empty table. Schema is created once at session start, dropped at session end.
6. Parametrize the boring branches. `@pytest.mark.parametrize("field", ["title", "author", "isbn"])` covers three identical validation paths with one test definition.

Why mocking the DB is the wrong answer:

— Mocks teach your test about your code, not about your database. Prod will raise `IntegrityError` on duplicate key; your mock won't, because no one wrote that branch.
— Mocks rot. The mocked behaviour stops matching SQLAlchemy as the library evolves. Real tests fail when reality changes; mocked tests pass wrongly.
— SQLite is fast enough. 71 hermetic tests against a real schema run in under a second. The cost argument is dead.
— The one limitation: dialect-specific SQL. If you use `JSONB` or Postgres-only functions, add an integration tier on top of the hermetic one. Most apps don't need it.

What good test coverage actually looks like:
a test for each branch of the route (422, 415, 409, 404, 500), each repository operation including error edges, a handful of schema tests that pin the contract, and `--cov-fail-under=100` on CI. If you can't reach 100 on a small project, you have dead code.

What's the slowest reasonable test suite you'd accept on a CRUD API — and why?

P.S. Code: https://github.com/bilouro/FastAPIProject

P.S. New tech post every Wednesday.

#FastAPI #Pytest #SoftwareTesting