What your database tests are not telling you

Gap 1: the tests use a different database

Running tests against SQLite in memory is fast and needs no setup, so it is common. It is also a different database. We ran the same statements in SQLite 3.51 and PostgreSQL 18.3:

CREATE TABLE products (id integer PRIMARY KEY, name varchar(10) NOT NULL,
                       price numeric(10,2) NOT NULL CHECK (price > 0), stock integer);
INSERT INTO products VALUES (1, 'a name much longer than ten characters', 19.999, 'lots');
CREATE TABLE orders (id integer PRIMARY KEY, product_id integer REFERENCES products (id));
INSERT INTO orders VALUES (1, 999);
SELECT 'Abc' LIKE 'abc';

Every row is a bug that passes in tests and fails, or behaves differently, in production. SQLite is not wrong; it is documented to work this way. Column types are recommendations, length limits are ignored, and foreign keys are off unless each connection turns them on. It is simply not the database you run.

The fix is cheap now: run tests against the same major version of PostgreSQL you deploy, in a container or a local install. Starting a fresh database per test run takes seconds.

Gap 2: the test never commits

A popular way to keep tests independent is to open a transaction at the start of each test and roll it back at the end. It is fast and leaves nothing behind. But a test that never commits cannot see anything that happens at commit, or anything that depends on time passing inside a transaction.

BEGIN;
INSERT INTO invoices (id, customer_id) VALUES (1, 42);
SELECT count(*) FROM invoices;     -- 1: test passes
ROLLBACK;

COMMIT;
ERROR:  insert or update on table "invoices" violates foreign key constraint "invoices_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

Time behaves the same way. now() returns the time the transaction started, all the way through it. In one test transaction we created an invoice, waited one second and marked it paid with now():

 time_to_pay | real_elapsed
-------------+-----------------
 00:00:00    | 00:00:01.002202

A test of “invoices paid within N seconds” or “sort by most recently updated” can pass or fail for reasons that never happen in production.

Rolled-back tests are still worth having for most code. For anything involving deferred constraints, commit-time triggers, time, or work done over a second connection, write tests that really commit, and clean up by truncating tables or creating a fresh database.

Gap 3: the test runs one thing at a time

A race condition needs two operations to overlap. A test suite that runs one request at a time cannot produce one, however many cases it covers. You will see this in the lab: a “read the balance, then write balance minus one” withdrawal was correct for 4,000 withdrawals run one at a time, and lost about 85% of them when 8 clients ran it at once.

Testing for races means generating concurrency on purpose: several connections running the same operation on the same rows at once, then checking an invariant afterwards. pgbench with a custom script does this in a few lines.

Make the database check the rules

Tests check the code paths you thought of. Constraints check every write, from every path, forever:

• “Each email is used once” is a UNIQUE index, not an if in the signup code.
• “A price is positive” is a CHECK.
• “An order belongs to a real customer” is a foreign key.

Some rules cannot be constraints, usually because they span many rows: “the stored total_spent equals the sum of the customer’s orders”, “every paid order has at least one item”. Write each one as a query that returns the rows breaking it, and run those queries on a schedule against production, alerting when any return rows:

-- paid or shipped orders with no items: should always return nothing
SELECT o.id FROM orders o
WHERE o.status IN ('paid', 'shipped')
  AND NOT EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id);

Check yourself

1. Name three things SQLite accepted that PostgreSQL rejected or changed.

   Show answer

   Any three of: a string longer than varchar(10); the text 'lots' in an integer column; 19.999 in numeric(10,2) (PostgreSQL rounds it to 20.00); an order referencing a product that does not exist; and 'Abc' LIKE 'abc', which is true in SQLite and false in PostgreSQL.

   I could explain this without looking

2. Why did the rolled-back test pass while the committed insert failed?

   Show answer

   The foreign key was deferred, so it is only checked at COMMIT. The test rolled back instead of committing, so the check never ran.

   I could explain this without looking

3. A team’s tests cover the withdrawal code thoroughly and all pass. Why might money still go missing in production?

   Show answer

   The tests run one withdrawal at a time. A read-then-write race only appears when two withdrawals overlap, which the tests never cause.

   I could explain this without looking