阅读中文版本

Build a Database Migration Runner

A complete migration runner: ordered versioned files, a checksum ledger, transactional apply, rollback scripts, and drift-detection tests.

migration-runner/
├── bin/migrate.mjs
├── src/plan.mjs
├── src/ledger.mjs
├── src/apply.mjs
├── migrations/0001_init.sql
├── tests/migrate.test.ts
└── package.json

const plan = files.sort((a, b) => num(a) - num(b));
if (hasDuplicateIds(plan)) throw new Error('duplicate migration id');

CREATE TABLE IF NOT EXISTS schema_migrations (
  id text PRIMARY KEY, checksum text NOT NULL, applied_at timestamptz DEFAULT now());

if (applied.get(id) && applied.get(id) !== checksum(file)) throw new Error('checksum drift: ' + id);

await client.query('BEGIN');
await client.query(sql);
await client.query('INSERT INTO schema_migrations(id, checksum) VALUES ($1,$2)', [id, hash]);
await client.query('COMMIT');

await client.query('BEGIN'); await client.query(downSql); await client.query('DELETE FROM schema_migrations WHERE id = $1', [id]); await client.query('COMMIT');

Project overview

A migration runner is simple until the day it is not: a file was edited after it ran, two deploys raced, or a rollback left the ledger disagreeing with reality. This build makes the ledger the source of truth and drift a detected event rather than a silent one.

You will order migrations by a numeric prefix, record a checksum per applied file, apply pending files inside a transaction, refuse to run when an already-applied file has changed, and provide an explicit rollback for paired down scripts.

The checksum is the feature that turns a discipline into a guarantee. Without it, an edited migration is a coin flip between environments; with it, the runner refuses and tells you exactly which file moved.

The runner is deliberately boring: plain SQL files, a ledger table, and a lock. There is no DSL to learn and no hidden state beyond what is recorded in the database.

Each phase is independently useful: ordering, then the ledger, then drift detection, then transactional apply, then rollback, so the runner grows without a rewrite.

The runner is the one component that touches every environment, so its failures are the most expensive kind. Keeping it small, deterministic, and loud about drift is the whole design philosophy.

What you will build

The runner provides five capabilities, each verified against a real database:

• Ordered plan
• Ledger table
• Drift detection
• Transactional apply
• Rollback

Ordered discovery gives a deterministic plan; the ledger records what ran; drift detection compares checksums; transactional apply keeps a failed migration from leaving partial state; and rollback pairs a down script with its up script.

The design assumes humans write SQL and machines enforce order. It does not generate migrations or guess schema changes, because a tool that edits your database for you is harder to trust than one that applies what you wrote.

Architecture and data model

Migration files follow a numeric prefix. A ledger table stores the id, checksum, and applied timestamp. The runner applies pending files in a transaction, refuses to run if an applied file changed, and supports a rollback command for paired down scripts.

Migration files are discovered, sorted by numeric prefix, and planned against the ledger. The ledger stores the id, the checksum, and the applied timestamp for each file, so the runner knows exactly what has run and what has not.

Drift detection recomputes the checksum of every applied file and compares it to the ledger, refusing to proceed on a mismatch. That single comparison prevents the classic failure where one environment ran a different version of the same migration.

Each pending migration runs inside a transaction where the database supports it, and the ledger row is written in the same transaction. A migration that fails therefore leaves neither a partial schema change nor a misleading ledger entry.

An advisory lock serializes runners, so two deploys cannot both decide to apply the same migration. Without the lock, the ledger check and the apply are a race that only loses under exactly the concurrency real deploys create.

The plan is a pure function of the files and the ledger, which makes it printable and testable without touching the database. That separation is what lets you answer what would run before committing to run it.

The ledger is a table the runner owns exclusively; application code must not read or write it, or the two notions of what has run will diverge.

Prerequisites and dependencies

You need a database and a directory of ordered SQL files; the runner does not generate them.

• Node.js 20 LTS or later
• PostgreSQL 15+
• A database user allowed to create tables

The dependencies are minimal so the runner can be dropped into any deployment:

• pg (^8.12.0) — Apply SQL and read the ledger

pnpm init
pnpm add pg
pnpm add -D typescript tsx vitest

Keep the connection string in the environment and the lock name in configuration. Treat the migrations directory as code: reviewed, immutable once applied, and never edited after the ledger records it.

Directory structure

Separate discovery, the ledger, drift detection, apply, and rollback so each can be tested against a disposable database:

migration-runner/
├── bin/migrate.mjs
├── src/plan.mjs
├── src/ledger.mjs
├── src/apply.mjs
├── migrations/0001_init.sql
├── tests/migrate.test.ts
└── package.json

Phase 1: Ordered plan

Sort migration files by numeric prefix and detect gaps or duplicates.

const plan = files.sort((a, b) => num(a) - num(b));
if (hasDuplicateIds(plan)) throw new Error('duplicate migration id');

A deterministic order makes environments reproducible.

Discover files, sort by the numeric prefix, and reject duplicate or non-monotonic prefixes, because ordering is the whole contract. A plan is just a list, and it should be printable before anything runs.

Phase 2: Ledger table

Create the ledger if missing and read applied checksums.

CREATE TABLE IF NOT EXISTS schema_migrations (
  id text PRIMARY KEY, checksum text NOT NULL, applied_at timestamptz DEFAULT now());

The ledger is the single source of truth for what ran.

Create the ledger with an id, a checksum, and an applied timestamp, and read it to compute what is pending. The ledger is the only state the runner trusts, so it must be queried rather than inferred from the filesystem.

Phase 3: Drift detection

Compare the file checksum to the ledger and fail on mismatch.

if (applied.get(id) && applied.get(id) !== checksum(file)) throw new Error('checksum drift: ' + id);

Editing an applied migration silently diverges environments.

Recompute the checksum of every applied file and compare. Detect drift before applying anything, so a mismatch aborts the run instead of compounding it with a new migration.

Phase 4: Transactional apply

Run each migration inside a transaction and record it on success.

await client.query('BEGIN');
await client.query(sql);
await client.query('INSERT INTO schema_migrations(id, checksum) VALUES ($1,$2)', [id, hash]);
await client.query('COMMIT');

A failed migration must not leave a half-applied schema.

Apply each pending file in a transaction that also writes the ledger row. On failure, roll back both, so the database and the ledger never disagree about what happened.

Phase 5: Rollback

Run the paired down script and delete the ledger row.

await client.query('BEGIN'); await client.query(downSql); await client.query('DELETE FROM schema_migrations WHERE id = $1', [id]); await client.query('COMMIT');

Rollback must undo the exact applied version.

Pair each up migration with an optional down script and apply it in reverse order, writing a ledger entry that records the rollback. An unrecorded rollback is indistinguishable from a migration that never ran.

Testing

Apply a set against a fresh database, assert the ledger matches, rerun and assert nothing re-applies, then edit a file and assert drift is detected.

Apply a set against a fresh database and assert the ledger matches the files, then rerun and assert that nothing re-applies, which proves idempotence.

Edit an applied file and assert drift is detected and the run aborts. This is the test that captures the runner’s reason for existing.

Simulate a failing migration and assert that neither the schema change nor the ledger row is left behind, which is the transactional guarantee made concrete.

Run two runners concurrently and assert that only one applies, proving the advisory lock actually serializes them rather than merely being configured.

Test rollback explicitly, including the case where a down script is missing, and assert that the command refuses instead of applying a partial reversal.

pnpm vitest run

Deployment and operations

Run migrations from a job before deploying app code and never from every app instance at once. Use an advisory lock so concurrent deployers cannot race on the ledger.

Run migrations from a job before deploying application code, and never from every application instance at once. The advisory lock is a safety net, not a licence to let all instances race.

Never edit a migration that has run in any environment; add a new one instead. The checksum exists to make this rule enforced rather than merely documented.

Back up before a large migration and note the point ahead of time, because transactional DDL is not available for every change and some operations cannot be rolled back by a down script.

Record who ran a migration and when, so an unexpected change in a shared environment can be traced to a deploy rather than investigated as a mystery.

Keep the migrations directory in the image alongside the code, so the schema version and the application version ship together and a deploy can be reasoned about as one artifact.

Security considerations

Parameterize where possible and avoid embedding credentials in migration files. Restrict the migration role so application traffic cannot alter the schema.

Treat the migrations directory as privileged code; it runs with the schema owner’s rights and can drop data. Restrict who can merge a migration as strictly as who can deploy the service.

Do not embed credentials or environment-specific values in a migration file; use parameters or a separate seeding step, so the same file applies identically everywhere.

Restrict the runner’s database role to the minimum it needs, and keep the connection string out of logs. A migration job that can read application secrets is a bigger risk than the schema change it performs.

Assert the expected database and schema at the start of a run, so a mis-pointed connection string fails immediately instead of applying migrations to the wrong database.

Require an explicit confirmation for a rollback that drops a column or table, because an automated rollback on the wrong environment is the fastest way to lose data.

Do not run migrations with a role that can create other roles or change ownership, since a migration file can otherwise escalate its own privileges on a shared database.

Observability

Log each applied id and duration and the final schema version. Alert if the ledger has no row for a file present in the repository, indicating a skipped deploy.

Log each migration id and its outcome with duration, so a slow migration is visible before it becomes a timeout. Duration is the metric that catches a missing index in a backfill.

Alert on drift detection, because a checksum mismatch is a correctness event rather than a routine warning, and it should stop a deploy rather than be reviewed later.

Track the number of pending migrations at deploy time; a sudden jump means a batch was merged without being applied anywhere, which is worth a look before production.

Expose the ledger’s last applied id as a health signal so the deployed code and the schema can be compared at a glance rather than reasoned about.

Emit the plan as a log line at the start of every run, so the applied set is recorded even when the run then fails before writing the ledger.

Track the wall time of the whole run as well as per migration, because total duration is what determines whether a deploy window is long enough.

Troubleshooting

Two deploys apply the same migration. Without an advisory lock both runners passed the pending check; take a lock for the whole run.

A migration hangs. A DDL statement waited on a lock held by app traffic; set a lock_timeout and run heavy migrations in a maintenance window.

If a migration reports as pending though it clearly ran, compare its checksum; the file was edited after it applied, and the runner is right to refuse.

If two deploys interleave, check whether the advisory lock is acquired on the same database connection the migration runs on; a lock taken on a different session releases too early.

If the ledger contains an id with no matching file, treat it as an applied migration that was deleted rather than one that never ran, and fail the plan instead of assuming the file is pending.

Limitations and next steps

• Only forward SQL and paired down scripts are supported.
• No automatic down-script generation.
• Data migrations that are not transactional need manual review.

The runner applies plain SQL and does not generate or diff schemas, so authoring migrations remains a developer responsibility.

Rollback depends on a hand-written down script; where one is missing the rollback command refuses rather than guessing, which means some migrations are effectively forward-only.

Transactional DDL depends on the database engine, so a migration that alters objects in a non-transactional engine can still leave partial state, which the runner documents rather than hides.

There is no dry-run that predicts the exact statements a migration will emit; the plan shows which files will run, not what they will do to the schema.

The runner does not detect schema drift caused by someone editing the database by hand; it only checks the files it knows about, and manual changes remain a documented risk.

The runner assumes a single primary; applying to a read replica or a sharded cluster is out of scope and would need a ledger per shard.