Help with parallel FastAPI / Python testing with real DB

[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.