Database mocks are not worth it

Original Article ↗ Hacker News Discussion ↗

Overall stance on DB mocks

  • Many commenters agree DB mocks tend to be brittle, verbose, and easy to get out of sync with reality, especially for constraints, defaults, indexes, and query semantics.
  • They argue that this gives a false sense of safety: tests “pass” while real queries or schemas would fail.
  • A common view: mocks are fine for some external APIs, but databases are central enough that you should usually test against the real thing.

Real databases in tests

  • Strong advocacy for using the same engine as production (often Postgres) in tests: “test what you fly, fly what you test.”
  • Modern tooling (Docker, docker-compose, Testcontainers, ephemeral Postgres tools, frameworks like Ecto/Rails) makes spinning up isolated DBs straightforward.
  • Techniques for speed and isolation:
    • One shared DB with each test wrapped in a transaction and rolled back.
    • Multiple DBs per test process, created from a template DB with schema/migrations pre-applied.
    • Running on RAM disks, disabling fsync, using UNLOGGED tables in CI.
    • Parallelizing tests across multiple DBs.

SQLite, fakes, and in-memory approaches

  • Some report success using in-memory SQLite as a “sweet spot”: very fast, catches many schema/constraint issues, easy with ORMs (e.g., Django, EF Core).
  • Others found SQLite–Postgres/MSSQL differences (types, constraints, JSONB, timestamps, indexes, geospatial, dialect nuances) caused too many false positives, forcing lowest-common-denominator designs.
  • Several prefer “fakes” (in-memory implementations over hash maps, etc.) over mocks, to decouple tests from call-sequence details while remaining fast.

Unit vs integration testing and mocks

  • Ongoing tension:
    • One side: databases in tests turn unit tests into integration tests, which are slower and potentially flaky; mocks isolate logic and keep TDD loops tight.
    • Other side: modern DB-backed tests can be “unit-test fast,” so better to hit the real DB and avoid maintaining mocks/fakes that can diverge.
  • Some promote “functional core, imperative shell”: put logic in pure functions (unit-tested without I/O), keep DB and other I/O as thin, simple layers tested via integration tests, often without mocks.
  • There is disagreement on how often mocks should be used at all; some call them a “code smell,” others see them as essential for testing error paths and external failures.

Database features, invariants, and design

  • Several insist on leveraging DB features (constraints, cascades, recursive CTEs, JSONB, row-level security, etc.) and testing them directly, since they enforce critical invariants.
  • A minority argue that if RDBMS “rakes” are painful, consider non-relational storage or event-based models instead, though this view is contested.