Database Testing

In the previous lesson the schema was a fixed assumption: tables ready, columns in place, constraints defined. The schema is itself a product piece and it changes through ordered steps. Each of these steps has an up direction and a down direction, and both can break just as code can.

This lesson does not teach the database; it puts the migration and the schema under test. The questions are: does the up migration produce the expected shape, is the step numbering sound, does the down migration preserve data, and what does the migration do to existing data. Every measurement runs on a real node:sqlite database.

The Migration Set

There are four steps. The third step’s down direction is given in two versions; that is what gets tested.

// migrations.mjs — ordered migration steps; the third step's down direction is given in two versions
export const DOWN_VERSION = process.env.DOWN_VERSION ?? 'buggy';

export const MIGRATIONS = [
  {
    no: 1,
    name: 'initial schema',
    up: [
      `CREATE TABLE member (member_no TEXT PRIMARY KEY, name TEXT NOT NULL, branch TEXT NOT NULL)`,
      `CREATE TABLE loan (book_no TEXT PRIMARY KEY, member_no TEXT NOT NULL,
         borrowed_day INTEGER NOT NULL, due_day INTEGER NOT NULL)`,
    ],
    down: [`DROP TABLE loan`, `DROP TABLE member`],
  },
  {
    no: 2,
    name: 'branch column on loan',
    up: [
      `ALTER TABLE loan ADD COLUMN branch TEXT`,
      `UPDATE loan SET branch = 'central'`,
    ],
    down: [`ALTER TABLE loan DROP COLUMN branch`],
  },
  {
    no: 3,
    name: 'splitting the member name',
    up: [
      `ALTER TABLE member ADD COLUMN surname TEXT`,
      `UPDATE member SET surname = substr(name, instr(name, ' ') + 1),
         name = substr(name, 1, instr(name, ' ') - 1) WHERE instr(name, ' ') > 0`,
    ],
    down: DOWN_VERSION === 'fixed'
      ? [`UPDATE member SET name = name || ' ' || surname WHERE surname IS NOT NULL AND surname <> ''`,
         `ALTER TABLE member DROP COLUMN surname`]
      : [`ALTER TABLE member DROP COLUMN surname`],
  },
  {
    no: 4,
    name: 'name and surname uniqueness constraint',
    up: [`CREATE UNIQUE INDEX member_name_surname ON member(name, surname)`],
    down: [`DROP INDEX member_name_surname`],
  },
];

The runner does three jobs: it checks the number sequence, keeps the applied steps in a record table, and counts the queries it runs. The counter is the cost’s measure.

// runner.mjs — migration runner: checks the number sequence, records what was applied, counts queries
import { DatabaseSync } from 'node:sqlite';
import { MIGRATIONS } from './migrations.mjs';

export function checkOrder(migrations) {
  const numbers = migrations.map((m) => m.no);
  const unique = new Set(numbers);
  if (unique.size !== numbers.length) {
    const repeated = numbers.find((n, i) => numbers.indexOf(n) !== i);
    throw new Error(`migration number repeated: ${repeated}`);
  }
  const ordered = [...unique].sort((a, b) => a - b);
  ordered.forEach((n, i) => {
    if (n !== i + 1) throw new Error(`gap in migration numbering: ${i + 1} missing`);
  });
  return ordered.length;
}

export function openDatabase(path = ':memory:') {
  const db = new DatabaseSync(path);
  db.exec('CREATE TABLE IF NOT EXISTS migration_record (no INTEGER PRIMARY KEY)');
  db.queries = 0;
  return db;
}

export const applied = (db) =>
  db.prepare('SELECT no FROM migration_record ORDER BY no').all().map((s) => s.no);

export function up(db, target) {
  checkOrder(MIGRATIONS);
  for (const migration of MIGRATIONS.filter((m) => m.no <= target)) {
    const done = applied(db);
    if (done.includes(migration.no)) continue;
    if (migration.no > 1 && !done.includes(migration.no - 1)) {
      throw new Error(`migration order broken: ${migration.no - 1} not applied before ${migration.no}`);
    }
    for (const command of migration.up) { db.exec(command); db.queries += 1; }
    db.prepare('INSERT INTO migration_record VALUES (?)').run(migration.no);
    db.queries += 1;
  }
  return db;
}

export function down(db, target) {
  for (const migration of [...MIGRATIONS].reverse().filter((m) => m.no > target)) {
    if (!applied(db).includes(migration.no)) continue;
    for (const command of migration.down) { db.exec(command); db.queries += 1; }
    db.prepare('DELETE FROM migration_record WHERE no = ?').run(migration.no);
    db.queries += 1;
  }
  return db;
}

export const columns = (db, table) =>
  db.prepare(`PRAGMA table_info(${table})`).all().map((s) => `${s.name}:${s.notnull}`);

Four Tests

The first test verifies the schema’s shape: which columns, in which order, and which cannot be left blank. The expected list is written by hand; this list diverges when a migration adds or forgets a column. The second test asks whether migration numbers are unique and gap-free; the class it catches is two migrations opened with the same number on two separate branches. The third test does a round trip. The fourth test runs the migration against existing data.

// migration.test.mjs — up migration, migration numbering, round trip, and constraint violation tests
import test from 'node:test';
import assert from 'node:assert/strict';
import { MIGRATIONS } from './migrations.mjs';
import { openDatabase, up, down, applied, columns, checkOrder } from './runner.mjs';

const insertMember = (db, no, name, branch) =>
  db.prepare('INSERT INTO member (member_no, name, branch) VALUES (?, ?, ?)').run(no, name, branch);

test('the up migration brings the schema to the expected shape', () => {
  const db = up(openDatabase(), 4);
  assert.deepEqual(applied(db), [1, 2, 3, 4]);
  assert.deepEqual(columns(db, 'member'), ['member_no:0', 'name:1', 'branch:1', 'surname:0']);
  assert.deepEqual(columns(db, 'loan'),
    ['book_no:0', 'member_no:1', 'borrowed_day:1', 'due_day:1', 'branch:0']);
});

test('migration numbers must be unique and gap-free', () => {
  assert.equal(checkOrder(MIGRATIONS), 4);
  const conflicting = [...MIGRATIONS, { no: 4, name: 'migration opened on another branch', up: [], down: [] }];
  assert.throws(() => checkOrder(conflicting), /migration number repeated: 4/);
});

test('the round trip preserves the member name', () => {
  const db = up(openDatabase(), 2);
  insertMember(db, 'U-0001', 'Alice Kane', 'central');
  up(db, 3);
  down(db, 2);
  assert.equal(db.prepare('SELECT name FROM member WHERE member_no = ?').get('U-0001').name, 'Alice Kane');
});

test('the fourth migration fails with a constraint violation on a duplicate name', () => {
  const db = up(openDatabase(), 2);
  insertMember(db, 'U-0001', 'Alice Kane', 'central');
  insertMember(db, 'U-0002', 'Alice Kane', 'campus');
  up(db, 3);
  assert.throws(() => up(db, 4), /UNIQUE constraint failed/);
});

DOWN_VERSION=buggy node --test --test-reporter=tap migration.test.mjs | grep -E '^ *(ok|not ok|(expected|actual):|# (tests|pass|fail))'

ok 1 - the up migration brings the schema to the expected shape
ok 2 - migration numbers must be unique and gap-free
not ok 3 - the round trip preserves the member name
  expected: 'Alice Kane'
  actual: 'Alice'
ok 4 - the fourth migration fails with a constraint violation on a duplicate name
# tests 4
# pass 3
# fail 1

The name of the caught defect class is data loss in the down migration. The third migration’s up direction splits the name in two, and its down direction drops the column carrying the second part directly; since that column is the only place holding the surname, the down direction leaves the name half-finished. The first test staying green also says why this class gets past it: the column list looks correct after the down migration, the value it carries is wrong.

The fix is merging the column’s content back before dropping it. This version is already written in the migration set.

DOWN_VERSION=fixed node --test --test-reporter=tap migration.test.mjs | grep -E '^ *(ok|not ok|# (tests|pass|fail))'

ok 1 - the up migration brings the schema to the expected shape
ok 2 - migration numbers must be unique and gap-free
ok 3 - the round trip preserves the member name
ok 4 - the fourth migration fails with a constraint violation on a duplicate name
# tests 4
# pass 4
# fail 0

The fourth test passing in both runs is separate information. The migration adding the uniqueness constraint fails when it finds a duplicate pair in existing data; the test fixes this not as a defect but as documented behavior. From this it follows that applying such a migration in production requires a cleanup step ahead of it.

What the Schema Test Does Not See

The first test verifies the schema’s shape: the branch column was added to the loan table, its name and position are correct. What the second migration writes into that column is not asked in any test.

// backfill.mjs — while the schema test is green, what the second migration actually writes into that column
import { openDatabase, up, columns } from './runner.mjs';

const db = up(openDatabase(), 1);
const insertMember = db.prepare('INSERT INTO member (member_no, name, branch) VALUES (?, ?, ?)');
const insertLoan = db.prepare('INSERT INTO loan (book_no, member_no, borrowed_day, due_day) VALUES (?, ?, 900, 914)');
for (const [no, name, branch] of [['U-1', 'Alice Kane', 'central'], ['U-2', 'Marcus Reyes', 'campus'], ['U-3', 'Elena Ashford', 'arts']]) {
  insertMember.run(no, name, branch);
  insertLoan.run(`K-${no}`, no);
}
up(db, 2);

const distribution = (table) => db.prepare(`SELECT branch, COUNT(*) AS n FROM ${table} GROUP BY branch ORDER BY branch`)
  .all().map((s) => `${s.branch}=${s.n}`).join(' ');

console.log(`what the schema test sees      : loan columns ${columns(db, 'loan').join(' ')}`);
console.log(`branch in the member table     : ${distribution('member')}`);
console.log(`loan branch after migration    : ${distribution('loan')}`);

what the schema test sees      : loan columns book_no:0 member_no:1 borrowed_day:1 due_day:1 branch:0
branch in the member table     : arts=1 campus=1 central=1
loan branch after migration    : central=3

Members are in three separate branches, every loan is in the central branch. The second migration filled the column with a fixed value; it did not read from the member’s branch. The name of the missed defect class is backfill value mismatch, and the schema test cannot see it, because the schema test’s oracle is the column list, not row contents. Catching this class requires building invariants over the data before and after the migration — something like “every loan’s branch equals its member’s branch” — and that claim is tested with the previous lesson’s data sets.

Cost

// cost.mjs — the migration test's cost: steps, queries, file, and relative time
import { mkdtempSync, rmSync, statSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { MIGRATIONS } from './migrations.mjs';
import { openDatabase, up, down } from './runner.mjs';

const root = mkdtempSync(join(tmpdir(), 'migration-'));
const steps = MIGRATIONS.reduce((t, m) => t + m.up.length + m.down.length, 0);

const fromScratch = performance.now();
const db = up(openDatabase(join(root, 'catalog.db')), 4);
const fromScratchTime = performance.now() - fromScratch;
const upQueries = db.queries;

const roundTrip = performance.now();
down(db, 2);
up(db, 4);
const roundTripTime = performance.now() - roundTrip;

const size = statSync(join(root, 'catalog.db')).size;
rmSync(root, { recursive: true, force: true });

console.log(`migration steps (up + down commands)                        : ${steps}`);
console.log(`queries from scratch to four                                : ${upQueries}`);
console.log(`total after round trip                                      : ${db.queries}`);
console.log(`file created                                                : 1, ${size} bytes`);
console.log(`round trip did not take longer than migrating from scratch  : ${roundTripTime < fromScratchTime * 3}`);

DOWN_VERSION=fixed node cost.mjs

migration steps (up + down commands)                        : 13
queries from scratch to four                                : 11
total after round trip                                      : 21
file created                                                : 1, 28672 bytes
round trip did not take longer than migrating from scratch  : true

Thirteen migration commands, eleven queries to go from scratch to four, twenty-one queries including the round trip, and one file. These numbers grow linearly as the migration set grows: every new step brings both an up and a down command, and one line’s change in the first test’s expected column list. The maintenance cost is here — adding one column requires touching three files: the migration set, the expected schema list, and the queries that read that column.

Summary

• The schema test’s oracle is a hand-written column list; this list diverges when a migration forgets to add or drop a column.
• Migration-number uniqueness is a separate test and it catches migrations opened with the same number on two branches.
• The round-trip test caught data loss in the down migration: a down migration that did not merge the column’s content before dropping it left the name half-finished.
• The migration adding the uniqueness constraint fails when it finds a duplicate pair in existing data; the test fixes this as documented behavior.
• The schema test cannot see the backfill value: the column was added, a fixed value was written to every row, and the test stayed green.

Next Step

In this lesson the dependency that ran for real sat on the same machine: its schema was on hand, its data could be generated, its migration could be repeated. Not every dependency is like this. The library’s loan flow depends on an external service that authenticates and returns a book’s bibliographic record: that service belongs to another team, it does not come up with the test’s command, it can give a different answer on every run, and on some runs it does not answer at all. The next lesson takes up this dependency with record and replay: real responses are recorded once, later runs are fed from the recording, and which defect class this catches and which it misses is measured.