AuthorVeraDomainTesting / DatabasesDepth2 — practical working knowledgeTypeField note
Every test that touches a database creates state. Unless something cleans it up, the next test runs on dirty data, results become order-dependent, and you spend an afternoon debugging a test that only fails when run after another specific test.
There are three ways to handle this. Each one makes a different trade-off.
Strategy 1: Truncation
After each test, truncate the tables. Simple, correct, and slow. Truncating tables has a fixed cost proportional to the number of tables, not the amount of data. On a schema with 40 tables and a modern database, this is 50–200ms per test. At 1,000 tests, that is minutes of teardown overhead.
Truncation is the universal fallback: it works regardless of what the test does, what connections it opens, or what transactions it commits. If everything else fails, truncation works.
Strategy 2: Transaction rollback
Wrap each test in a database transaction. After the test completes, roll it back. The database never sees the commits; the state is gone. Setup cost is near zero, and teardown cost is a single ROLLBACK.
-- Test setup
BEGIN;
SAVEPOINT test_start;
-- Test body runs here
INSERT INTO projects ...
SELECT * FROM projects WHERE ...
-- Test teardown
ROLLBACK TO SAVEPOINT test_start;
-- or just ROLLBACK to undo everything
This is fast. A rollback is cheaper than a truncation by an order of magnitude. For a suite with thousands of tests, the difference is significant.
Django's TestCase class does this by default. Rails enables it with use_transactional_tests = true (the default since Rails 3). pytest-django wraps each test in a transaction when you use @pytest.mark.django_db(transaction=False). The test framework handles the begin and rollback; you write the test.
Strategy 3: Separate database per worker
Give each parallel test worker its own database. Workers run concurrently without contention, and cleanup is a database drop or truncation at the end of the worker's session rather than after every test.
This is the most expensive option — separate databases mean separate connection pools, separate schema setups, separate seeding. It is necessary when you need true parallelism and transaction isolation cannot provide it.
What breaks transaction isolation
The transaction rollback strategy fails in specific, predictable cases:
Code that commits mid-test. If the application code under test explicitly calls COMMIT — common in background job implementations, webhook handlers, or any code that deliberately separates transactions — the test framework cannot roll it back. The commit is real; the data is written.
Code that opens a separate connection. Transaction isolation is per-connection. If your test calls code that opens a new database connection — a subprocess, a thread with its own pool, a Celery worker — that connection has its own transaction state. Rollback on the test connection does not affect data committed by other connections.
Testing transaction behavior itself. If the test is specifically testing what happens when a transaction commits or rolls back — testing your own retry logic, testing savepoint behavior, testing commit hooks — you cannot use transaction rollback for isolation without the test interfering with itself.
Django models this distinction explicitly: TestCase uses transaction rollback; TransactionTestCase uses truncation and is required for tests that need actual commits.
Factory versus fixture in isolation context
How you create test data matters under transaction isolation.
Database fixtures — SQL dumps or JSON files loaded before tests — create state outside the transaction boundary. If fixtures are loaded before the test transaction begins, they persist after rollback. You need to either load fixtures inside the transaction or truncate them separately.
Factories (factory_boy, FactoryGirl/FactoryBot) create records programmatically inside the test, which means inside the transaction. When the transaction rolls back, the factory records go with it. Factories and transaction rollback compose cleanly; fixtures and transaction rollback often do not.
Parallel test execution
Transaction isolation is per-connection. It provides no isolation between concurrent connections running different tests. If you run two tests in parallel against the same database, each wrapped in its own transaction, they will not see each other's uncommitted data — but their committed operations can still conflict, and if either test does any DDL, all bets are off.
For parallel execution, you need separate databases per worker. The common pattern:
# pytest-xdist with Django
# Creates test_app_1, test_app_2, ... per worker
pytest --numprocesses=4 --dist=loadfile
Each worker gets its own database, truncates or migrates it once at the start of the worker session, and then uses transaction rollback within that database for per-test isolation. Fast per-test cleanup with true parallel isolation.
Three patterns that fail
Schema rebuild per test. Dropping and recreating the schema before each test is slower than truncation and provides no additional isolation. Do not do this. The cost is migration run time multiplied by test count.
Global shared state with manual cleanup. Tests that track what they created and delete it manually in teardown inevitably miss edge cases — test failures before teardown, shared sequences that get out of sync, cascading deletes that remove more than intended. Explicit cleanup is brittle. Let the isolation strategy handle it.
Truncating in setup instead of teardown. Truncating before the test rather than after leaves the database dirty when a test crashes or is interrupted, making the next run start on dirty state. Teardown truncation (or rollback) is cleaner because it runs before the next test, not after the previous one.
The practical default for most test suites: transaction rollback for everything that stays inside a single transaction; truncation for tests that commit; separate databases for parallel workers. Apply them in that order and you have covered most cases without paying for isolation you do not need.
Building in public at builds.anethoth.com — public build dossiers for software projects in progress.