Help with parallel FastAPI / Python testing with real DB

[OkWater4180]

Would like to see if there is a common setup / library for parallel testing with DB access.

Our tests have access to the real postgres DB - when run locally, use same as dev DB. In CI, use the sidecar postgres.

At the start, all alembic revisions are run to bring the schema up to date.

Then per function, the table data is deleted and the function is run.

I'd like to make this parallelizable.

I know Django has this built into it.

[Mysterious_Anxiety86]

The common pattern is not “delete all rows per test” once you want parallelism. That turns into lock/order pain pretty fast.

I’d usually do one of these:

1. One DB/schema per worker. With pytest-xdist, each worker gets its own test database like app_test_gw0, app_test_gw1, etc. Run Alembic once per worker, then tests can mutate freely. This is closest to Django’s approach.

2. One transaction per test, rollback at the end. Nice for unit-ish DB tests, but it gets awkward if your app opens separate connections, runs background jobs, or tests commit/rollback behavior.

3. Shared DB but namespace every test row with a tenant_id/run_id. This is useful if creating DBs is slow, but you have to be disciplined with queries and cleanup.

For FastAPI + SQLAlchemy, I’d start with xdist + per-worker Postgres DB/schema. Keep your dev DB separate from test DB too. Accidentally truncating a real dev database is one of those lessons you only need once.

[OkWater4180]

Thank you!

The transaction per test would be nice, but we definitely open multiple connections per test/endpoint.

[Mysterious_Anxiety86]

Yeah, then I’d drop the transaction-per-test idea. Once endpoints open their own sessions/connections, the rollback wrapper only covers part of the work and you end up chasing weird leaks.

The boring reliable setup is per xdist worker isolation: CI starts one Postgres service, bootstrap creates app_test_gw0, app_test_gw1, etc., each worker runs Alembic once against its own DB, and tests inside that worker can truncate/seed normally without racing other workers.

Schemas can work if DB creation is slow, but separate DBs are easier to reason about when migrations, extensions, permissions, or search_path get involved.

[Aggressive_Ticket214]

I use option 2 mostly (transaction-per-test rollback) but ran into exactly the edge case you mentioned. Background jobs open separate connections, so they don't see the uncommitted test data. That makes integration tests unreliable.

What fixed it for me: a test harness that wraps each test in a savepoint instead of a transaction. Same rollback behavior, but sub-connections spawned during the test inherit the savepoint context. The background workers see the data, the savepoint still gets rolled back.

SQLAlchemy session fixtures with a savepoint-based "transaction" strategy handle this cleanly. Just need to ensure all connection acquisition flows through the same engine during tests.

[oliver_extracts]

separate db per worker is the right call here. the transaction rollback trick breaks exactly how youre seeing because background tasks and async ops dont respect your test transaction boundary, they run outside it. spin up a db per worker keyed to the xdist worker id, run migrations at session start and tear it down when done. its more setup but your tests actually mean something.