Test Against the Real Database
A substitute engine with different SQL semantics gives you a green suite and a broken production — the failure the substitute exists to prevent.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
When does it matter that a test runs against the same database engine as production?
The suite must catch a missing index, a violated constraint, a transaction that never commits and a query that only works on one engine — before the deploy, not after.
Run tests against SQLite in memory. It starts instantly, needs no container, resets between tests, and it is SQL — the queries are the same.
The SQL dialects diverge in exactly the places application code relies on: INSERT ... ON CONFLICT, RETURNING, window functions, JSONB operators, array types, SELECT ... FOR UPDATE. Code using any of them either fails to run or, worse, runs differently.
- The SQL dialects diverge in exactly the places application code relies on:
INSERT ... ON CONFLICT,RETURNING, window functions,JSONBoperators, array types,SELECT ... FOR UPDATE. Code using any of them either fails to run or, worse, runs differently. - Type systems differ. SQLite's dynamic typing accepts values a stricter engine rejects, so a test passes with data that production refuses to store.
- Concurrency semantics differ most of all. SQLite's default locking behaves nothing like Postgres MVCC or MySQL InnoDB row locks, so every test involving concurrent writes, deadlocks or isolation proves nothing about production (Isolation Levels).
- Constraint enforcement differs — SQLite does not enforce foreign keys unless explicitly enabled, so a test suite can pass with data that violates referential integrity.
- The subtlest failure: tests pass, deploy succeeds, and the divergent behaviour surfaces days later under production concurrency, where it looks like an application bug rather than a testing gap.
What is actually happening
- A database is not an interface with one implementation. It is a set of semantics — types, isolation, constraint timing, locking, query planning, error codes — and application code depends on those semantics whether or not the author noticed.
- The substitution only holds if you use the intersection of both engines' behaviour. That intersection is much smaller than it appears, and nothing tells you when you have stepped outside it.
- Containers removed the historical justification. Starting a real engine per test run is a routine part of CI now, and the fixed startup cost is amortised across the whole suite (Containers in Operation).
- Isolation between tests is the real engineering problem, and it has three standard answers: transaction rollback per test (fastest, but the code under test cannot manage its own transactions), truncate between tests (slower, allows real transactions), and a schema or database per parallel worker (best isolation, most setup).
- Migrations should run against the test database using the same tooling as production. That makes the migration path itself tested, which is otherwise verified for the first time in production (Schema Migrations from the Application Side).
- Fixtures should be minimal and built by factories rather than by a large shared seed file. A big shared fixture makes every test depend on data it does not care about, and one edit breaks fifty tests.
- Some things still get substituted: third-party HTTP, email, payment providers, clocks. The rule is consistent — substitute what is slow, costly or non-deterministic; keep what defines the semantics you depend on (A Test Strategy Chosen by What Each Layer Can Prove).
Where the substitute diverges
The argument for a substitute engine is that the differences are marginal. The differences are in fact concentrated in exactly the features that carry production correctness: uniqueness under concurrency, referential integrity, upserts, isolation and types.
Each row below is a case where the test is green and production is wrong. That combination — a passing test asserting a false thing — is worse than having no test, because it stops investigation.
| Behaviour the code relies on | Real engine (Postgres) | In-memory substitute (SQLite) | Consequence of testing on the substitute |
|---|---|---|---|
| Upsert | INSERT ... ON CONFLICT (a,b) DO UPDATE | Different syntax and conflict-target rules | The query is never exercised as written; fails on first deploy |
| Returning inserted rows | INSERT ... RETURNING * | Limited or absent depending on version | Code that reads the returned id is untested |
| Foreign keys | Enforced always | Off unless PRAGMA foreign_keys=ON | Orphan rows pass the suite and violate integrity in production |
| Isolation | MVCC; READ COMMITTED default; serialization failures surface as 40001 | Database-level locking; no comparable conflict errors | Every concurrency test proves nothing (Isolation Levels) |
| Row locking | SELECT ... FOR UPDATE blocks concurrent writers | No equivalent row-level semantics | Pessimistic locking is untested (Pessimistic Locking) |
| Types | Strict: a bad timestamp or overflowing integer is rejected | Dynamic affinity: values are coerced | Data that production refuses passes the suite |
| Errors | SQLSTATE 23505, 40001, 23503 — mappable to categories | Different codes and messages | Error translation is verified against the wrong codes (An Error Taxonomy That Maps Cause to Response) |
Isolation is the real engineering problem
Once the engine is real, the interesting question is how each test gets a clean world without paying to rebuild one. The three standard answers trade speed against how much the code under test is allowed to do.
Transaction rollback is the fastest and has one hard constraint: the code under test cannot manage its own transaction boundaries, because its commit would escape the wrapping transaction. If your service layer owns transactions — and it usually should — truncation or per-worker databases are the honest choice (Where the Transaction Boundary Goes).
What does the code under test do with transactions, and how much parallelism do you need?
when The code under test does not commit on its own — repository-level tests, query tests.
cost Cannot test real commit/rollback behaviour, and nested transactions need savepoints to behave.
when The service layer owns its transactions and really commits.
cost Slower per test; needs a reliable list of tables and correct ordering for foreign keys.
when You need real parallelism and full freedom in the code under test.
cost Setup complexity and more memory; migrations must run per worker or be templated.
when Almost never — only for tests that alter global server state.
cost Startup dominates the suite; the discipline collapses under it.
when Never for a real suite.
cost Order-dependent flakes that vanish on re-run and consume days of debugging.
What a real engine lets you actually assert
The payoff is a class of test that is impossible otherwise: two genuinely concurrent transactions, an engine deciding the conflict, and an assertion about which one survives. This is the behaviour your production correctness rests on, and it is only observable against the real thing.
Note that the test asserts the *outcome* and the *error category*, not a driver message. That keeps it stable across driver and engine-patch upgrades while still proving the semantics.
1// Same engine, same major version as production, migrated with the real tool.2const db = await startPostgresContainer({ image: 'postgres:16' })3await runMigrations(db.url) // the migration path is now tested too4 5describe('inventory reservation', () => {6 it('lets exactly one of two concurrent reservations win', async () => {7 await seed(db, { sku: 'A1', available: 1 })8 9 // Two REAL concurrent transactions. The engine resolves this, not our code.10 const [a, b] = await Promise.allSettled([11 reserve(db, { sku: 'A1', qty: 1 }),12 reserve(db, { sku: 'A1', qty: 1 }),13 ])14 15 const ok = [a, b].filter((r) => r.status === 'fulfilled')16 expect(ok).toHaveLength(1)17 18 const failed = [a, b].find((r) => r.status === 'rejected')19 // Category, not driver text — survives driver upgrades.20 expect((failed as PromiseRejectedResult).reason).toMatchObject({ category: 'conflict' })21 22 // And the invariant the whole feature exists to protect:23 expect(await availableFor(db, 'A1')).toBe(0)24 })25 26 it('rolls back the reservation when the payment step throws', async () => {27 await seed(db, { sku: 'B2', available: 5 })28 await expect(reserveAndCharge(db, { sku: 'B2', qty: 2, failPayment: true }))29 .rejects.toMatchObject({ category: 'dependency' })30 // Proves the transaction boundary is where we think it is.31 expect(await availableFor(db, 'B2')).toBe(5) // [[transaction-boundary]]32 })33})Neither test can be written against a substitute engine. The first depends on real MVCC conflict detection; the second depends on real rollback of a real transaction. Both are exactly the behaviours that break in production.
How to build it
Most important first.
- Run the same engine and the same major version as production. Version differences matter for planner behaviour, syntax and defaults — pin it in CI and locally.
- Start it as a container in CI and for local development, seeded by your real migrations rather than a hand-maintained schema dump.
- Isolate per test. A transaction rolled back after each test is the fastest option; use truncation when the code under test needs its own transactions (Where the Transaction Boundary Goes).
- Parallelise with one schema or one database per worker. Shared state across parallel workers produces order-dependent flakes that are miserable to diagnose.
- Test the things only a real engine can prove: unique constraints under concurrency, foreign-key cascades, isolation-level behaviour,
ON CONFLICTsemantics, and that migrations apply cleanly to a populated database. - Build data with factories that create only what the test needs, so a test reads as a statement about behaviour rather than about the fixture.
- Keep the fast unit suite genuinely separate so pure logic does not pay database startup cost. Two commands, two speeds.
- Assert on database error *categories* rather than driver messages, so the tests survive a driver upgrade (An Error Taxonomy That Maps Cause to Response).
What can go wrong
- Tests sharing one database in parallel, producing failures that depend on execution order and disappear on re-run — the worst kind of flake because re-running "fixes" it.
- A test database schema created by a dump that has drifted from the migrations, so the suite tests a schema that no environment actually has.
- Transaction-rollback isolation used with code that commits internally, so the rollback does not undo everything and state leaks into the next test.
- A shared seed file that grows until every test depends on it, and a single row change breaks dozens of unrelated tests.
- Container startup added to every test file rather than once per suite, making the suite slow enough that people stop running it.
- The mitigation failing: a real engine in CI and SQLite locally, so developers get green runs on a different engine and CI becomes the first honest signal.
- Tests that depend on row ordering without an
ORDER BY— they pass consistently until a plan change reorders results (Reading EXPLAIN ANALYZE).
- Concurrent-write behaviour is the strongest argument for a real engine: unique-constraint races, deadlock detection,
SELECT ... FOR UPDATEand serialization failures are engine-specific and untestable against a substitute (Deadlocks in Application Code). - Parallel tests race on shared schema objects. Per-worker isolation is the fix; retry-on-flake is the anti-fix.
- A test asserting optimistic-concurrency behaviour needs two real concurrent transactions on a real engine, because the behaviour under test *is* the engine's conflict detection (Optimistic Concurrency).
- Never seed a test database from a production dump without genuine anonymisation. Test environments have weaker access controls, wider access and longer retention (Sensitive Data Classification).
- Test authorization and tenant isolation against the real database. A missing tenant predicate is invisible with single-tenant fixtures and catastrophic with real data (Tenant Isolation).
- Verify that the application's database user has only the privileges it needs. A test suite running as a superuser will not catch a missing grant that breaks production (Database Privileges and Blast Radius).
- Test injection defences against the real parser. Engines differ in escaping, comment syntax and multi-statement handling, so a substitute engine can make an injection test meaningless (SQL Injection).
- Keep test database credentials out of the repository, exactly like any other credential (Secrets Are Not Configuration).
- "SQL is SQL." Dialects diverge exactly where application code lives — upserts, returning clauses, JSON, arrays, locking, isolation.
- "An in-memory database is the same, just faster." It is a different engine with different semantics. Speed is not the property in question.
- "Integration tests are slow, so use fewer." Use per-test isolation instead of restarting, and run them in parallel. Most slowness is a strategy problem, not an inherent one.
- "If it passes against a real database locally, it will work in production." Semantics yes; performance, data volume and concurrent load no.
- "Mocking the repository tests the same thing at a higher level." It tests that your code calls your mock. The constraint, the transaction and the concurrency are precisely what the mock removed (When the Repository Is Just Indirection).
- "We use an ORM, so the engine is abstracted." ORMs generate engine-specific SQL and expose engine-specific behaviour through migrations, types and error codes (What an ORM Buys and What It Costs).
Operating it
- Track suite duration and the database-startup share of it. When the fixed cost dominates, the fix is usually per-worker reuse rather than abandoning the real engine.
- Track flaky-test rate specifically for database tests — it is almost always an isolation problem, and it is fixable rather than inherent.
- When a production bug involves SQL or transactions, check whether an integration test could have caught it. That answer, over time, tells you exactly which semantics your suite is not exercising.
- Run migrations against a copy of realistic data volume in CI. A migration that is instant on an empty table and locks a large one is a production incident waiting for a deploy (Expand and Contract Migrations).
- At 10x tests, per-test isolation cost dominates. Transaction rollback scales far better than truncation, and per-worker databases scale better than either for parallelism.
- At larger suites, the database container should be started once per CI job and reused across test files, with isolation handled inside rather than by restarting.
- With realistic data volumes, integration tests start catching a different class of problem — missing indexes, plan changes, lock contention — that small fixtures never reveal (Should I Add an Index?).
- Nothing about the argument changes with scale: the substitute engine is wrong at every size. What changes is how much engineering the isolation strategy deserves.
- Real-database tests are slower than mocked ones and need infrastructure in CI and on every developer machine.
- Container startup is a fixed cost paid on every run, including for a one-line change.
- Isolation strategy is genuine engineering: transaction rollback constrains what the code under test may do; truncation is slower; per-worker databases need orchestration.
- They are still not production. A local container has different data volumes, different hardware and no concurrent load, so they prove semantics and not performance (Performance Testing a Backend).
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- DATABASE-SPECIFICThe whole lesson is about engine differences. Postgres and SQLite differ in typing, foreign-key enforcement, locking and upsert syntax; Postgres and MySQL differ in default isolation level (READ COMMITTED versus REPEATABLE READ), in error codes, and in whether DDL is transactional. Any two engines differ somewhere your code depends on.
- SCALE-SPECIFICFor a service whose data access is a handful of simple queries through an ORM, a substitute engine may genuinely be adequate — the risk grows with how much engine-specific behaviour the code relies on. Any use of upserts, advisory locks, JSON operators, explicit isolation levels or
FOR UPDATEputs you well outside the safe intersection. - FRAMEWORK-SPECIFICRails and Django ship transactional test cases against a real configured database, so this is the default path and the lesson is nearly free. A bare Node or Go service must assemble container startup, migrations and isolation itself, which is why substitute engines remain common there — the friction differs, the correctness argument does not.
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.
- — Testing & Reliability Engineering — test-double taxonomy, deterministic concurrency testing, and how to measure whether a suite actually detects the defects it claims to.