Test Against the Real Thing

Using a different database for testing than the one running in production feels like a reasonable trade-off. An in-memory database like SQLite is fast, requires zero setup, and keeps the test suite portable. The logic is sound: swap in something lightweight for tests, run the real thing in production.

The problem is that databases aren't interchangeable. They handle transactions differently. They enforce constraints differently. They use different SQL dialects — ON CONFLICT DO NOTHING works in Postgres but not in SQLite, and string comparisons that are case-insensitive in one are case-sensitive in another by default. Code that passes every test can crash the moment it hits production, not because the logic is wrong, but because the database underneath it behaves differently than the one you tested against.

This mismatch usually gets introduced early in a project when speed of setup matters most, and then never revisited. By the time the divergence causes a production incident, it's been in the test suite long enough that nobody questions it.

The fix is straightforward. Tools like Docker make it easy to spin up the same database you're running in production as part of your test suite — real constraints, real syntax, real transaction logic. The tests run a little slower. In exchange, an entire category of production surprise disappears.

Test against the thing you're actually going to run.