Verdict
Yes
Yes, tests of code that runs SQL should use a real database of the same engine and major version as production, started in a container and built by the real migrations; unit tests of rules that take and return plain values need no database.
Why
- Blast radiususers
- Change frequencyregularly
- Detectabilityeventually
- Reversibilitywith-effort
- Test costmoderate
Yes, tests of code that runs SQL should use the database engine production runs, and tests of plain logic should use no database. The typical case is a PostgreSQL backend whose query tests could run on the real engine, on a mock, or on H2 or SQLite in memory. Blast radius is users and Change frequency is regularly, because customers see the stored rows and most features touch a query. Detectability is eventually, because a missing constraint or a wrong filter stores or returns plausible rows without an error. Reversibility is with-effort, because wrong rows need a repair script, and Test cost is moderate, because a test against a local database takes about an hour, so rule R11 gives Test.
| When | Decision | Why |
|---|---|---|
| The app uses only the methods a framework base class provides, such as save and findById, entities map columns by naming convention, and CI starts it against a real database with schema validation | Do not write separate database tests; test the code that calls those methods | Detectability moves to immediately and Reversibility to trivial, because a missing column fails startup in CI and a convention leaves no hand-written column name to swap |
| Several companies share one database, and every query must add the tenant ID | Test mandatory: rows of two tenants in each real-database test, asserting that each query returns one tenant's rows | Blast radius rises to safety-or-legal and Detectability to never, because a missing tenant filter leaks records without an error |
| A query selects the subscriptions that the nightly billing job charges | Test mandatory on the production engine, including the date boundaries | Blast radius rises to money and Reversibility to costly, because a double charge needs refunds |
| A read-only query feeds an internal staff dashboard, and no code acts on its rows | Test minimally: one real-database test of the main filter | Blast radius falls to internal and Reversibility to trivial |
| The database code is a one-time migration that rewrites existing rows | Test it differently: rehearse the migration on a restored copy of production data and compare row counts | Change frequency falls to once while Blast radius stays users |
| The queries run on a managed data warehouse with no local edition, so each test needs a shared cloud dataset | Test it differently: a scheduled job compares the query's results with a reference query and alerts on a difference | Test cost rises to heavy while Detectability stays eventually and Reversibility with-effort |
What breaks if you don't test
A mocked repository returns whatever the test handed it, so the SQL, the column mapping and the unique indexes never run. A substitute engine runs the SQL under its own rules: a regular SQLite table stores the text 'abc' in an INTEGER column that PostgreSQL rejects, and H2's compatibility modes cover only a small subset of the differences between databases. The tests stay green, and a customer finds duplicate or missing records weeks later.
What you lose if you over-test
The over-test puts the database into tests of rules that take values and return values, such as a status transition. Each test then inserts rows first and fails on schema changes unrelated to the rule.
How to test
Write integration tests on the production engine and major version, started in a container for each run. Build the schema with the migrations production runs, not from the ORM models, so an index missing from a migration is missing in the test too. The minimum set:
- One test per hand-written query, with rows it must return and rows it must leave out.
- One test per constraint the code relies on, such as an upsert run twice.
- A transaction rolled back after each test, which Rails does by default, or a fresh schema per test class.
Django builds its test database on the configured engine; for .NET, see the EF Core testing guide.
When the answer changes
- The code runs no SQL: unit test it with no database.
- A query decides which tenant's rows come back or what a customer is charged.
- SQLite is the production database, as in a mobile app: in-memory SQLite is then the real engine.
Real incident + Code example
The import that copied remote shifts every night
On a staff-scheduling product I worked on, a nightly job imported shifts with INSERT ... ON CONFLICT (employee_id, location_id, shift_date) DO UPDATE. Remote shifts had a NULL location_id, and PostgreSQL treats nulls in a unique index as distinct by default, so every run inserted another copy of each remote shift. The import tests mocked the connection and asserted the SQL string, which was correct. Three weeks later a customer's manager saw one Monday shift listed 19 times. We deleted the copies with a script, recreated the index with NULLS NOT DISTINCT (PostgreSQL 15 and later), and added this test:
from datetime import time
import psycopg
import pytest
from shifts.importer import import_shifts
@pytest.fixture
def db():
# PostgreSQL 16 from the CI service, schema built by the real migrations
with psycopg.connect("postgresql://test@localhost/shifts_test") as conn:
yield conn
conn.rollback()
def test_second_import_updates_a_remote_shift_instead_of_copying_it(db):
remote = {"employee_id": 42, "location_id": None, "shift_date": "2026-03-02", "starts": "09:00"}
import_shifts(db, [remote])
import_shifts(db, [remote | {"starts": "10:00"}])
rows = db.execute(
"SELECT starts FROM shifts WHERE employee_id = 42 AND shift_date = '2026-03-02'"
).fetchall()
assert rows == [(time(10, 0),)]
Related questions
FAQ
- Should unit tests hit the database?
No, unit tests of rules that take and return plain values should not touch a database; pass values in, or use an in-memory fake repository. Code whose behaviour lives in SQL gets integration tests against the real engine instead.
- Should E2E tests persist data in real databases?
Yes, end-to-end tests should write to a real database of the production engine, seeded for each test run. Each test creates its own records with unique names, so no test depends on another test's data.
- Should integration tests use a database?
Yes, an integration test of code that reads or writes tables should run against the production database engine in a container. A mocked database returns what the test hands it, so the SQL and the constraints never run.