Test Data Invariants at the Database Layer
Your app validation is a suggestion. Your database constraint is a law. Application-level checks only run when writes go through your application — and your application is not the only writer. Migrations, backfill scripts, admin consoles, cron jobs, and other services all touch the same tables. If an invariant matters — "emails are unique", "every order has a customer", "quantity is never negative" — it needs to be enforced where every writer must pass: the database. And then it needs to be tested there, not just declared.
Why app-layer checks leak
An ORM unique: true validation typically does a SELECT-then-INSERT. Two concurrent requests can both pass the SELECT and both insert — the only thing that actually stops the duplicate is a database unique constraint. The same pattern repeats everywhere: presence checks skipped by a raw-SQL backfill, "not negative" rules a data-fix script never heard of, orphaned rows left behind by a service that doesn't load your models. App checks are useful UX; they are not integrity.
Test on a real, disposable database
- Spin up a throwaway instance of the same engine and major version as production. Testcontainers does exactly this — throwaway, code-defined database containers for tests, with libraries for Java, Go, .NET, Node.js, Python, Rust, and more.
- Run your real migrations against it, from zero. The schema under test is the schema you ship — not a hand-maintained fixture.
- Seed rows that attack each constraint: insert a duplicate key, delete a parent row with children, insert a value the check constraint forbids — and assert the database rejects each one.
- Tear it down. Next run starts clean.
A test that expects INSERT to fail is the proof the law exists. If someone drops the constraint in a migration, this test — not a production incident — catches it.
Engine gotchas worth a test each
| Engine | Gotcha |
|---|---|
| PostgreSQL | UNIQUE treats two NULLs as distinct by default — duplicate null rows are allowed unless you use NULLS NOT DISTINCT. A CHECK is also satisfied when it evaluates to NULL, so it won't block nulls; pair it with NOT NULL. |
| MySQL | CHECK constraints are only enforced from 8.0.16 — on older versions they parse and do nothing. Expressions can't use other tables, subqueries, or stored functions. |
| SQLite | Foreign key enforcement is off by default and per-connection: without PRAGMA foreign_keys = ON on every connection, your FKs are decoration. |
Every row in that table is a reason a mocked or in-memory "compatible" database gives you false confidence. Test against the engine you deploy.
Power-user moves
- Test the schema itself, in SQL. pgTAP lets you write TAP-emitting tests inside PostgreSQL —
has_table(),has_column(),col_is_pk()— so the constraint's existence is asserted, not just its behavior. - Assert the error, not just the failure. Check the constraint name in the rejection (e.g.
users_email_key) so the test fails loudly if a different error masks a dropped constraint. - Exercise concurrency where it matters. For uniqueness, fire two competing inserts in parallel transactions; only a real database reproduces the race your app-layer check loses.
- Keep one seed script for "hostile" data. A small, versioned set of rows designed to violate each invariant doubles as living documentation of what the schema guarantees.
Resources
- PostgreSQL — Constraints (CHECK, UNIQUE, NOT NULL, FK)
- Testcontainers — throwaway database containers for tests
- SQLite — foreign key support and
PRAGMA foreign_keys - MySQL 8.4 — CHECK constraints
- pgTAP — unit tests inside PostgreSQL
Building an AI feature? Yeda AI designs, audits, and ships production LLM systems.