Why do test suites run against an in-memory database stand-in, and what does that hide?

## What an engine substitute is An engine substitute is a lightweight data store that a test suite writes to **instead of** the engine production writes to. The usual form runs inside the test process, keeps its data in memory, and speaks a query dialect close enough to the production one that the same application code compiles and runs against it. Many such engines advertise a *compatibility mode* that reshapes syntax and a handful of built-in functions to look like a named production engine. The same shape appears outside data stores: an in-process message broker instead of a real one, an embedded search index instead of a cluster, a local file-backed object store instead of a remote one. The reasoning and the failure mode are identical, so the vocabulary here transfers. ## Why teams choose one Four reasons, roughly in order of how often they are the real reason: 1. **Start-up cost.** An in-process engine is ready in milliseconds. A real engine, whether shared or started per run, costs seconds to tens of seconds before the first assertion. 2. **No external prerequisite.** Nothing to install, no daemon, no network, no credentials. A new joiner clones and runs the suite; a locked-down build agent with no privilege to run external services still works. 3. **Disposability.** A fresh instance per case, or per worker, is free. Isolation between cases stops being a design problem. 4. **Parallelism.** Each worker gets its own instance, so cases do not contend on one schema. These are real engineering benefits and choosing a substitute is a legitimate trade, not an automatic mistake. What makes it dangerous is leaving the trade implicit — a suite that is green tells a reader "it works", and nothing in the report says which engine it worked on. ## What the substitute cannot reproduce The substitute is a different implementation, so the suite is only as trustworthy as the overlap between what the code uses and what the substitute reimplements. Six families account for most escapes: - **Dialect and feature gaps.** Window functions, recursive queries, insert-or-update statements, array and document-typed columns, full-text search, vendor-specific functions, hints, partial and expression indexes, stored procedures. A missing feature usually fails loudly at test time, which is the *harmless* half. The dangerous half is a feature that exists on both but means something slightly different. - **Types and coercion.** Numeric precision and scale, rounding on overflow versus an error, fractional-second precision on timestamps, unsigned ranges, implicit coercion of a text value in a numeric comparison, whether an over-long text value is silently truncated or rejected. - **Collation and ordering.** Whether comparison and uniqueness are case-sensitive, how accents and punctuation sort, where null values land in an ordering, and what an unordered read returns — many substitutes return insertion order, which quietly makes an unordered query look deterministic. - **Constraint semantics.** When a constraint is checked, whether checking can be deferred to commit, cascade behaviour on delete, and whether repeated null values violate a unique key. - **Concurrency.** The default isolation level, whether readers block writers, row versus page versus table locking, lock-wait timeouts, and whether the engine detects a deadlock and aborts one participant. Substitutes commonly serialise everything, so a lost update or a deadlock is structurally unreachable in the suite. - **Schema provenance.** If the substitute's schema is generated from the code model while production's is applied by versioned migrations, the two schemas are different artefacts and can drift without anything failing. ## A worked case A subscription renewal job selects everything whose expiry falls at or before the run cutoff and renews it. The suite, on the substitute, stores the expiry with microsecond precision, so a record written at `10:00:00.000412` and a cutoff of `10:00:00` compare as expected and the record is skipped. Production declares the column at whole-second precision, truncates on write, and the same record compares as *equal* to the cutoff — so it is picked up on this run and again on the next one after the renewal window rolls forward. That is an off-by-one at a boundary, produced entirely by a storage-precision difference, and the suite is structurally incapable of seeing it: the assertion is correct, the logic is correct, and the engine is not the engine. ## How to hold the trade honestly Decide, per case, what the case is evidence *for*. A case about branching, validation or a calculation happens to need somewhere to put a row and is well served by a substitute. A case about a query, a constraint, a migration or concurrent access is evidence about the engine and must run on the engine. Where both exist, replaying the same cases against both engines is the only check that actually demonstrates the substitute is still a fair stand-in — and any divergence it finds is a defect in one of the two, not in the check. The worst outcome is the reverse pressure: production code bent to stay inside the substitute's supported subset — an index dropped, a set-based statement rewritten as a loop, a feature avoided — so the tests keep passing. At that point the substitute is designing the system.