Database tests, containers, and transaction rollback

Database tests validate SQL, migrations, constraints, and ORM mappings against a real engine—not an in-memory substitute that behaves differently. Testcontainers spin up ephemeral Postgres/MySQL instances in CI; alternatives include shared staging schemas with careful cleanup.

Wrap each test in a transaction rolled back after assertions, or truncate tables in deterministic order. Choose between speed (in-memory or shared) and fidelity (containerized production-like DB).

On interviews: explain when you would mock the repository versus run Testcontainers and how you prevent parallel test interference.

Common pitfalls: SQLite standing in for Postgres-specific features, leaked connections, and tests that depend on auto-increment IDs from prior runs.

The trade-off is CI time and complexity versus confidence in query and migration behavior.

Checklist:

• Match production engine features in integration tests.
• Reset state per test reliably.
• Keep migration tests in the pipeline.