Test your database guarantees against the real migration files

I wrote constraints into my database: an immutable log, unique indexes, references between tables. Then I wanted to test them.

The common mistake

The test creates its own tables:

db.exec("CREATE TABLE posts (id INTEGER, slug TEXT)");

That tests what you wrote in the test, not what runs in production. The real schema carries constraints, triggers and indexes that those lines know nothing about, so the test keeps passing while production breaks.

What I do

I run the migration files themselves against an in-memory database:

for (const f of readdirSync(migrations).filter(f => f.endsWith('.sql')).sort()) {
  db.exec(readFileSync(path.join(migrations, f), 'utf8'));
}

Every test gets a fresh database, built exactly the way production is built.

What this actually caught for me

The schema turned out to be fine. My test fixtures were the problem: I was inserting rows with missing columns, and the real schema required mandatory fields I never passed. The test failed, and it was right to.

It also caught a column that did not exist. I had assumed the payments table had it, and it was never there. Without the real migrations that assumption would have survived.

What becomes easy afterwards

Testing the constraints directly:

assert.throws(() => db.exec("DELETE FROM audit_log"), /append-only/);
assert.throws(() => insertSame(key),                  /UNIQUE/);

Lines like these prove the guarantee exists, not that you intended it.

The trap

Order matters. Sort the migration files by name before executing. Alphabetical sorting works as long as your names are zero-padded: 0001, 0002, 0010. Without the padding, 10 comes before 2.

What I take from this

The schema under test and the schema in production have to be the same artifact. Any difference between them will surface one day, in production, with no test to catch it.

Related reading