flaky tests from shared database state: how to isolate

TL;DR

Each test needs a pristine database. Use transactions rolled back per test as the default, truncation for multi-connection tests, and separate schemas for parallel workers.

Error

(Not an error; an isolation pattern. Symptom: tests fail from leftover rows created by other tests.)

Steps

1. Wrap each test in a transaction rolled back afterward (framework-specific: transactional fixtures, DatabaseCleaner, TestTransaction). Expected: no rows persist.
2. For browser-driven tests on a separate connection, use truncation or deletion strategy for those tests. Expected: the app server sees the data.
3. Seed only the minimum per test, in the test itself, not in global fixtures. Expected: tests declare their data needs.
4. For parallel runs, give each worker its own database or schema. Expected: no cross-worker collisions.
5. Run the suite twice back-to-back. Expected: second run green proves isolation.

When to use

• DB leakage between tests.
• Setting up test DB strategy.

When not to use

• Non-database shared state.
• Read-only tests with a static fixture DB.

Tool compatibility

• Any ORM and test framework; per-framework transaction helpers.

Variant phrasings

Test database isolation strategy

The general topic; transactions first.

Database state leaking between tests

The symptom; rollback is the fix.

Why it happens

Tests share one database by default. Inserts persist unless rolled back, and later tests see them.

Edge cases

• Sequences and auto-increments are not rolled back by all strategies; avoid asserting exact IDs.
• Truncation is slow; reserve it for the tests that need it.
• Migrations run once per suite, not per test.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_uVR6lb9kERouRcpNNwBcJw