Testing a DB endpoint via dependency override

WHAT THIS TESTS Whether you can leverage FastAPI's dependency injection to substitute a safe database in tests, and whether you balance realism against isolation.

A GOOD ANSWER COVERS The endpoint should receive its session through a dependency such as def get_db. FastAPI exposes app.dependency_overrides, a dict mapping the original dependency to a replacement. In your test setup you register app.dependency_overrides[get_db] = override_get_db, where override_get_db yields a session connected to a throwaway database, commonly an in-memory or file SQLite, or a dedicated test Postgres for parity. A robust pattern wraps each test in a transaction that is rolled back afterward, or recreates the schema per test, so tests stay independent. You then use Starlette's TestClient (or httpx AsyncClient) to call client.post('/users', json=payload), assert the status code and response body, and additionally query the test session to confirm the user row was actually written. Finally clear app.dependency_overrides so tests do not leak state. This exercises real serialization, validation, routing, and ORM persistence while never touching production.

COMMON WRONG ANSWERS Configuring tests to use the real DATABASE_URL, risking data corruption. Mocking the session or repository so completely that nothing real is tested. Forgetting to reset overrides, leaking state between tests. Sharing one database state across tests so order matters.

LIKELY FOLLOW-UPS SQLite versus a real Postgres test database, what fidelity do you lose? How do you isolate tests with transaction rollback? How do you test async endpoints with httpx?

ONE CONCRETE EXAMPLE A fixture creates an SQLite engine, builds the schema, and yields a session; override_get_db yields it. The test does response = client.post('/users', json={'email': 'a@b.com'}); assert response.status_code == 201; then asserts session.query(User).count() == 1. After the test, app.dependency_overrides.clear() and the schema is dropped, leaving production untouched.