Last week my tests were green and I almost shipped a database guarantee that wasn’t real.
I’m building a human-in-the-loop document extraction tool. Documents go in, structured fields come out, and low-confidence fields get routed to a person for review. The data model leans on a few database guarantees: unique constraints, foreign keys, and transaction behavior that keeps the review queue consistent.
The problem
My test suite runs on SQLite because it’s fast and needs no setup. Production is PostgreSQL. The two disagree in ways that matter: how they enforce constraints, how they handle concurrent writes, and what counts as a type mismatch.
What I changed
- Kept SQLite for fast unit tests.
- Added a separate test pass against a real PostgreSQL instance for anything the schema is supposed to guarantee.
- Made “passes on PostgreSQL” part of the definition of done for each build phase.
The takeaway
A passing test only proves the code works on the thing you tested against. If production is a different system, verify the guarantees there. It costs an afternoon now and saves a bad migration later.
I’m sharing the build as it happens in the community. If you’ve hit the same SQLite-versus-PostgreSQL gap, I’d like to hear how you handled it.