Database testing

Database testing

Data code fails on the specifics an ORM hides: the dialect a query compiles to, the constraint that fires on a duplicate, the migration that locks a table under load. A test against SQLite when production runs Postgres proves your mock of the database, not the database. Run against the real engine and the surprises show up in CI instead of at 3 a.m.

Method

1. Run against the production engine and version. Start Postgres 16 (or whatever prod runs) with Testcontainers or a docker-compose fixture, not SQLite. Dialect differences in JSON operators, upserts, and case sensitivity are exactly where the bugs are.
2. Test migrations both directions. Apply the up migration to an empty schema, assert the tables and indexes exist, then apply the down and assert it returns clean. A down migration nobody runs is a rollback that fails during an incident.
3. Isolate each test in a transaction and roll back. Open a transaction in setup, run the test, roll back in teardown. This keeps tests order-independent and fast, and no test sees rows another one wrote.
4. Prove the constraints fire. Insert a duplicate against a unique index, an orphan against a foreign key, a null into a NOT NULL column, and assert the IntegrityError. A constraint no test triggers is a comment, not a guarantee.
5. Verify rollback on partial failure. Drive a multi-statement unit of work that errors midway and assert nothing persisted. An application-level transaction that leaves half its writes behind is the classic data-corruption bug.
6. Test concurrency where the code relies on it. For SELECT FOR UPDATE, optimistic version columns, or a chosen isolation level, run two concurrent transactions and assert the serialization failure or the correct winner, not just the single-threaded path.

Checks

• Do the tests run on the same engine and major version as production?
• Does a broken migration or a violated constraint fail a test rather than pass silently?
• After a mid-transaction error, does an assertion confirm zero rows landed?

Boundaries

This is the persistence layer under your code. The HTTP surface above it belongs to api-testing, cross-service seams to integration-testing, and the shape of the schema itself to schema-design. Reuse the project's fixture and container setup instead of standing up a parallel one.