Managing clean test state across API integration tests

WHAT THIS TESTS: Whether you can design integration tests that are deterministic and independent, and whether you grasp the tension between realism, speed, and isolation when tests share a stateful database.

A GOOD ANSWER COVERS: Strategy one is seed-and-clean: before each test insert known fixtures, after each test truncate or delete affected tables. Pros: simple, explicit, works with any commit pattern. Cons: slow at scale, easy to forget a table, ordering bugs if cleanup is incomplete. Strategy two is per-test transaction rollback: open a transaction in beforeEach, run the test, roll back in afterEach so nothing persists. Pros: very fast, perfectly isolated. Cons: breaks when the code under test commits its own transaction or opens a second connection, and cannot test commit-level behavior like triggers. Strategy three is real database in a container per suite (Testcontainers): highest fidelity to production dialect, at the cost of startup time. In-memory engines like sqlite or mongodb-memory-server are fast and disposable but risk dialect drift from the production engine, so a query that passes locally may fail in production.

COMMON WRONG ANSWERS: Sharing one mutable dataset across all tests with no reset, creating order dependence; using a different database engine in tests than production and assuming parity; running tests serially to hide isolation bugs.

LIKELY FOLLOW-UPS: How do you parallelize tests that share a database (schema-per-worker, separate databases)? How do you reset auto-increment sequences? Why can transaction rollback fail with connection pools?

ONE CONCRETE EXAMPLE: A Postgres API suite uses Testcontainers to boot a real Postgres image once, runs migrations, then wraps each test in BEGIN/ROLLBACK for speed; tests that exercise committed triggers instead truncate explicitly. This balances fidelity and speed.