How to test database migration

Save the schema, apply the migration, roll it back, then compare: sqlite3 app.db .schema > before.sql, migrate up 3, migrate down 2, diff before.sql after.sql. The rollback prints a name and exits 0 while an index it should have recreated is gone, so the diff decides the result, not the exit code.

Why check this

Run this on the release branch, before the migration reaches staging, and again whenever the down leg changes. It takes a copy of the database, moves it one version forward, moves it back, and asks whether the two ends match.

The failure it prevents arrives during a rollback at night. A release is reverted, the runner reports every down step as done, and the service comes back with a table that lost an index in the round trip. Reads that used the index now scan the table. No migration error appears, because a missing index is valid schema and the runner has nothing to complain about.

Everything below ran on SQLite 3.50.6, the only engine on the machine that produced this output. The sequence transfers to other engines, the commands do not.

Prerequisites

• SQLite 3.50.6 on the command line, and Node 22.23.2, whose bundled node:sqlite carries SQLite 3.51.3. Two SQLite builds on one machine, which matters when a version-dependent feature is in play. See the ALTER TABLE documentation.
• node:sqlite is experimental in Node 22 and prints a warning on stderr. --no-warnings keeps that line out of the comparison.
• The runner. It applies or reverts one file inside a transaction, records the version, and stores a checksum of the file it ran.

// migrate.mjs: a migration runner small enough to read. node:sqlite, Node 22.
import { DatabaseSync } from 'node:sqlite';
import { readdirSync, readFileSync } from 'node:fs';
import { createHash } from 'node:crypto';

const [, , cmd = 'status', arg] = process.argv;
const db = new DatabaseSync(process.env.DB ?? 'app.db');
db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (version INTEGER PRIMARY KEY,
  name TEXT NOT NULL, applied_at TEXT NOT NULL, checksum TEXT NOT NULL)`);

const sum = (p) => createHash('sha256').update(readFileSync(p)).digest('hex').slice(0, 12);
const all = readdirSync('migrations').filter((f) => f.endsWith('.up.sql')).sort()
  .map((f) => ({ version: Number(f.slice(0, 3)), name: f.replace('.up.sql', ''),
    up: `migrations/${f}`, down: `migrations/${f.replace('.up.sql', '.down.sql')}` }));
const applied = () => db.prepare('SELECT * FROM schema_migrations ORDER BY version').all();

function run(m, dir) {
  db.exec('BEGIN');
  try {
    db.exec(readFileSync(dir === 'up' ? m.up : m.down, 'utf8'));
    if (dir === 'up') db.prepare('INSERT INTO schema_migrations VALUES (?,?,?,?)')
      .run(m.version, m.name, new Date().toISOString().slice(0, 19) + 'Z', sum(m.up));
    else db.prepare('DELETE FROM schema_migrations WHERE version = ?').run(m.version);
    db.exec('COMMIT');
  } catch (e) {
    db.exec('ROLLBACK');
    console.error(`FAIL ${dir} ${m.name}: ${e.message}`);
    process.exit(1);
  }
  const top = applied().at(-1)?.version ?? 0;
  db.exec(`PRAGMA user_version = ${top}`);
  console.log(`${dir}   ${m.name}`);
}

if (cmd === 'up') {
  const done = new Set(applied().map((r) => r.version));
  const target = arg ? Number(arg) : Infinity;
  for (const m of all) if (!done.has(m.version) && m.version <= target) run(m, 'up');
} else if (cmd === 'down') {
  const target = Number(arg ?? 0);
  for (const r of applied().reverse()) {
    if (r.version > target) run(all.find((m) => m.version === r.version), 'down');
  }
} else {
  const done = new Map(applied().map((r) => [r.version, r]));
  for (const m of all) {
    const r = done.get(m.version);
    const state = !r ? 'pending' : r.checksum === sum(m.up) ? 'applied' : 'CHANGED';
    console.log(`${String(m.version).padStart(3, '0')}  ${state.padEnd(8)} ${m.name}`);
  }
  console.log(`user_version = ${db.prepare('PRAGMA user_version').get().user_version}`);
}

• The migrations. Version 3 is the one under test. It rebuilds users to make email required, because SQLite cannot add NOT NULL to an existing column any other way, and its down leg recreates one index and forgets the other.

-- migrations/001_users.up.sql
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01'
);
CREATE INDEX idx_users_name ON users(name);

-- migrations/001_users.down.sql
DROP INDEX idx_users_name;
DROP TABLE users;

-- migrations/002_email.up.sql
ALTER TABLE users ADD COLUMN email TEXT;
CREATE INDEX idx_users_email ON users(email);

-- migrations/002_email.down.sql
DROP INDEX idx_users_email;
ALTER TABLE users DROP COLUMN email;

-- migrations/003_email_required.up.sql
CREATE TABLE users_new (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01',
  email      TEXT NOT NULL DEFAULT ''
);
INSERT INTO users_new (id, name, created_at, email)
  SELECT id, name, created_at, COALESCE(email, '') FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
CREATE INDEX idx_users_name ON users(name);
CREATE INDEX idx_users_email ON users(email);

-- migrations/003_email_required.down.sql
CREATE TABLE users_old (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT '1970-01-01',
  email      TEXT
);
INSERT INTO users_old (id, name, created_at, email)
  SELECT id, name, created_at, email FROM users;
DROP TABLE users;
ALTER TABLE users_old RENAME TO users;
CREATE INDEX idx_users_name ON users(name);

-- migrations/004_orders.up.sql
CREATE TABLE orders (
  id      INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total   NUMERIC NOT NULL DEFAULT 0
);
CREATE INDEX idx_orders_user ON orders(user_id);

-- migrations/004_orders.down.sql
DROP TABLE orders;

• Three more databases, built once before the steps: a reference that never leaves version 2, a copy carrying a row for step 7, and one for step 8.

DB=fresh.db node --no-warnings migrate.mjs up 2
cp fresh.db drop.db && sqlite3 drop.db "INSERT INTO users (id,name,email) VALUES (1,'ada','ada@example.com');"
DB=order.db node --no-warnings migrate.mjs up 2

Steps

1. 1

   Step 1.

   Read the version the database is on now.

   node --no-warnings migrate.mjs status
   

   001  applied  001_users
   002  applied  002_email
   003  pending  003_email_required
   004  pending  004_orders
   user_version = 2

2. 2

   Step 2.

   Save the schema you expect to come back to.

   sqlite3 app.db .schema > before.sql && wc -l before.sql
   

   9 before.sql

3. 3

   Step 3.

   Apply the migration under test, and nothing after it.

   node --no-warnings migrate.mjs up 3
   

   up   003_email_required

4. 4

   Step 4.

   Roll it back and keep the exit code.

   node --no-warnings migrate.mjs down 2; echo "exit=$?"
   

   down   003_email_required
   exit=0

   The runner is satisfied. A rollback test that stops here passes.

5. 5

   Step 5.

   Compare the schema with the copy from step 2.

   sqlite3 app.db .schema > after.sql && diff -u --label before.sql --label after.sql before.sql after.sql
   

   --- before.sql
   +++ after.sql
   @@ -1,9 +1,9 @@
   CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY,
    name TEXT NOT NULL, applied_at TEXT NOT NULL, checksum TEXT NOT NULL);
   -CREATE TABLE users (
   +CREATE TABLE IF NOT EXISTS "users" (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
   -  created_at TEXT NOT NULL DEFAULT '1970-01-01'
   -, email TEXT);
   +  created_at TEXT NOT NULL DEFAULT '1970-01-01',
   +  email      TEXT
   +);
   CREATE INDEX idx_users_name ON users(name);
   -CREATE INDEX idx_users_email ON users(email);

   Two findings sit in that diff and they are not equal. The last line is the defect: idx_users_email existed before the round trip and is absent after it. The first hunk is noise, the same four columns written differently, because ALTER TABLE ADD COLUMN appends to the stored text while a rebuild reprints the definition and quotes the renamed table.

6. 6

   Step 6.

   Ask the planner what the missing index costs, against a database that never left version 2.

   for db in app.db fresh.db; do echo "$db"; sqlite3 $db "EXPLAIN QUERY PLAN SELECT id FROM users WHERE email = 'ada@example.com';"; done
   

   app.db
   QUERY PLAN
   `--SCAN users
   fresh.db
   QUERY PLAN
   `--SEARCH users USING COVERING INDEX idx_users_email (email=?)

7. 7

   Step 7.

   Take a second database down one version and back up, then read the column.

   DB=drop.db node --no-warnings migrate.mjs down 1 && DB=drop.db node --no-warnings migrate.mjs up 2 && sqlite3 drop.db "SELECT id, name, quote(email) FROM users;"
   

   down   002_email
   up   002_email
   1|ada|NULL

   The schema round trip is exact here, because dropping the column and adding it back produces the same definition. The address is gone anyway, and no schema comparison can see that.

8. 8

   Step 8.

   Run the down leg of version 2 in the wrong order, to see what SQLite refuses.

   sqlite3 order.db "ALTER TABLE users DROP COLUMN email;"; echo "exit=$?"
   

   Error: stepping, error in index idx_users_email after drop column: no such column: email
   exit=1

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | diff prints nothing | The schema came back to where it started | Check the rows next. A matching schema is not matching data. | | A CREATE INDEX line only on the before side | The down leg dropped an index and never put it back | Add the CREATE INDEX to the down file. Dropping a table drops its indexes. | | CREATE TABLE "users" against CREATE TABLE users | The table was rebuilt and SQLite quoted the name after the rename | Compare columns with PRAGMA table_info before calling it a difference. | | down prints a name and exits 0 | The SQL ran without raising an error | Nothing else. Run the comparison. | | error in index ... after drop column | The down leg drops a column an index still uses | Drop the index first, in the same file, above the ALTER TABLE. |

Common mistakes

What to check next

• How to check database schema version: the round trip changes what the migrations table claims, and the claim is worth reading directly.
• How to compare database schemas: the same comparison across two environments rather than across two points in time.
• How to verify row counts after migration: the data half of the check that step 7 opens.
• Database migration checklist: the rest of the pass before a release that carries DDL.

FAQ

How to test database migrations without touching production?

Restore a copy of the production database and run the round trip on the copy. The comparison needs real row counts and real data shapes, which a seeded development database does not have.

How to test migration rollback?

Apply one version, roll back one version, then compare the schema and the rows against what you captured first. The exit code of the down command answers a smaller question: whether the SQL parsed and ran.

How to check a migration is reversible?

Reversible means the down leg restores the schema, and the diff in step 5 settles it. It does not mean the data survives: step 7 ends with an identical schema and loses every address. Report which of the two you tested.

How to test data migration?

Add row counts and checksums on both sides of the round trip. Count each affected table before the up leg, after it, and after the down leg. A schema comparison cannot detect a backfill that wrote the wrong value.

Does this work on PostgreSQL and MySQL?

The sequence does. These commands ran on SQLite 3.50.6 and were not verified on another engine. The equivalent capture is pg_dump --schema-only or mysqldump --no-data, and neither engine restores a dropped column any better.

Verified

Every command on this page ran against SQLite 3.50.6 on Windows 11, with the runner and the migrations printed above. That machine has no PostgreSQL and no MySQL server, so nothing here has been checked against one.