Test migrations and constraints

Application code and schema migrations ship together, but they fail separately. One typo in a migration and every handler that touches that table is broken, while every unit test stays green.

So we test the migrations themselves. Two tests, answering two different questions.

The clean-install test

Start from an empty database, apply every migration, and check the result. This proves a brand new environment can reach the current schema from nothing:

test('migrates a clean database to the current schema', async t => {
  const db = await freshDatabase(t)
  await migrate(db)

  const columns = db.prepare('PRAGMA table_info(books)').all().map(c => c.name)
  assert.deepEqual(columns, ['id', 'title', 'author', 'isbn', 'created_at'])

  await new SqliteBooks(db).insert({ title: 'Dune', author: 'Frank Herbert', isbn: '9780441172719' })
})

The write at the end matters. A schema can look right and still reject every insert.

The upgrade test

Production already has data. The upgrade test proves an existing environment can move from the previous schema to the current one without losing or corrupting rows.

Create the old schema from a frozen snapshot of the earlier migrations. Insert a few representative old rows. Then apply only the newer migrations, and read those rows through the current repository. Assert the preserved values and the new defaults.

Don’t build the “old” database with today’s schema helpers. That reproduces the destination, not the starting point, and the test proves nothing.

Constraints are tests you can run

A uniqueness constraint or a foreign key is a guarantee the database enforces. Test it the same way as a boundary: one write that succeeds at the edge, one write that fails past it.

For our ISBN rule, insert 9780441172719 once, then try again:

await assert.rejects(
  () => books.insert({ title: 'Dune (reprint)', author: 'Frank Herbert', isbn: '9780441172719' }),
  DuplicateIsbnError
)
assert.equal(db.prepare('SELECT count(*) AS n FROM books').get().n, 1)

The count proves the rejected write left the table unchanged. This is the test the stub from the test doubles lesson was standing in for.

An application validation test can’t prove the constraint exists in the database. A constraint test can’t prove the API reports a useful error. You need both.

Fail forward

Never edit a migration that already ran in production. Add a new one, and test the upgrade path from the schema people actually have. That’s the only path production will ever take.

Try this: add the uniqueness rule for ISBN, then write the clean-install test, the duplicate write test, and an upgrade test with existing rows. After the rejected duplicate, query the table and prove exactly one original row remains.