阅读中文版本

Build a Schema Migration CLI

Schema Migration CLI — a complete walkthrough of database schema migration, covering migration loader, version tracker, up applier, and testing.

schema_migration_cli/
├── src/index.ts
├── src/migration-loader.ts
├── src/version-tracker.ts
├── tests/schema_migration_cli.test.ts
└── package.json

// Migration loader: database schema migration core
export function migrationloader(input: MigrationloaderInput): MigrationloaderResult {
  // Build the migration loader that anchors database schema migration and defines the core data shape.
  return processMigrationloader(input);
}

// Version tracker — handles database schema migration Add the version tracker so
export class Versiontracker {
  constructor(private opts: VersiontrackerOptions) {}
  async run(ctx: Context): Promise<VersiontrackerOutput> {
    return this.execute(ctx);
  }
}

// Up applier: database schema migration stage
export const upapplier = (next: Next) => async (req: Request) => {
  const out = await transform(req);
  return next(out);
};

// Down reverter for database schema migration
export async function downreverter(stream: AsyncIterable<Chunk>): Promise<Aggregate> {
  let acc = init();
  for await (const c of stream) acc = merge(acc, c);
  return acc;
}

// Status reporter: database schema migration finalization
export function statusreporter(state: State): Result {
  validate(state);
  return finalize(state);
}

Project overview

This project builds a database schema migration system whose reason for existing is applying ordered, reversible schema changes so a database evolves alongside code safely. The temptation in database schema migration is to bolt the interesting part onto a working prototype and discover the hard cases in production; this build inverts that order and treats a migration that alters a large table and holds an exclusive lock long enough to block deploys as the first thing designed, not the last.

You will implement Migration loader, Version tracker, Up applier, Down reverter, and Status reporter, each as a module with one responsibility and a test that proves it in isolation. The database schema migration pipeline is then the wiring that composes them, and the integration test is where the composition is shown to survive a migration that alters a large table and holds an exclusive lock long enough to block deploys.

The walkthrough is written for a developer who has used a database schema migration system but has not had to make one honest. It favors explicit data shapes over clever abstractions, and it names the failure each design choice prevents rather than presenting the choice as a style preference.

Each phase ends with something observable — a migration loader that returns the right shape, a version tracker that rejects bad input, an integration test that goes green — so you can stop at any point and still have a database schema migration system that works for the cases covered so far.

What you will build

The finished database schema migration system exposes five capabilities, each with a focused test:

• Migration loader
• Version tracker
• Up applier
• Down reverter
• Status reporter

Migration loader is the foundation that every later stage assumes; Version tracker is where database schema migration correctness lives; Up applier handles a migration that alters a large table and holds an exclusive lock long enough to block deploys; Down reverter makes the system observable; and Status reporter is the graceful-degradation layer that keeps overload from becoming corruption.

The build keeps the database schema migration logic in ordinary modules you can read in one sitting, and pulls in dependencies only for the parts that are genuinely tedious to hand-roll. That keeps the diff between the tutorial and a real codebase small, and keeps the failure modes visible in code rather than hidden behind a framework.

Architecture and data model

The system is organized around Migration loader, Version tracker, and Up applier, with Down reverter tying them together. Data flows as a directory of numbered migration files each with up and down steps, tracked by a versions table in the target database.

Each component is a module with a pure interface where practical: the same input produces the same output, and side effects are pushed to the edges. That discipline is what makes the phases independently testable, because a test for Up applier does not need to stand up Migration loader and Version tracker to exercise the interesting branch.

The data model is deliberately explicit. A directory of numbered migration files each with up and down steps, tracked by a versions table in the target database Keeping the shape written down — in a schema, a type, or a fixture — means a change to the shape is a visible diff rather than a silent runtime shift, and it means the integration test can assert against a concrete shape instead of guessing.

Configuration and code are kept apart: the configuration says what database schema migration should do, the modules say how. A reviewer can change a threshold or a policy without reading the implementation, which keeps the risky changes small and lets the diff stand as the review.

The failure path through Up applier is designed before the happy path is finished, because a migration that alters a large table and holds an exclusive lock long enough to block deploys is the case that distinguishes a database schema migration system from a demo. Every component declares what it does when its inputs are wrong, and the integration test asserts each of those declarations.

Prerequisites and dependencies

Have these ready before you install anything, so the database schema migration setup is not blocked by a missing tool:

• Node.js 20 LTS or later
• Familiarity with database schema migration fundamentals
• A local environment to run the test suite

The runtime dependencies are chosen to keep the database schema migration datapath legible:

• pg (^8.13.0) — Postgres connection
• commander (^12.1.0) — CLI framework

# setup for schema-migration-cli
pnpm init
pnpm add pg commander
pnpm add -D typescript tsx vitest

Versions are pinned to a major range so the database schema migration install is reproducible; commit the lockfile. The migration loader reads its operational parameters from the environment and validates them at startup, so a missing value fails the boot rather than the first database schema migration request.

Directory structure

Keep each database schema migration component in its own file so it can be tested without starting the others:

schema_migration_cli/
├── src/index.ts
├── src/migration-loader.ts
├── src/version-tracker.ts
├── tests/schema_migration_cli.test.ts
└── package.json

Phase 1: Migration loader

Build the migration loader that anchors database schema migration and defines the core data shape.

// Migration loader: database schema migration core
export function migrationloader(input: MigrationloaderInput): MigrationloaderResult {
  // Build the migration loader that anchors database schema migration and defines the core data shape.
  return processMigrationloader(input);
}

After this phase the migration loader works in isolation and is covered by a unit test. The migration loader defines the data shape that every later stage assumes, so getting it right first means the later phases compile against a stable contract.

Treat the migration loader as the single source of truth for the database schema migration data shape. Validate inputs at the boundary and reject early, so a malformed value never reaches Version tracker or Up applier where its effect would be harder to localize.

Phase 2: Version tracker

Add the version tracker so database schema migration can applying ordered, reversible schema correctly.

// Version tracker — handles database schema migration Add the version tracker so
export class Versiontracker {
  constructor(private opts: VersiontrackerOptions) {}
  async run(ctx: Context): Promise<VersiontrackerOutput> {
    return this.execute(ctx);
  }
}

After this phase the version tracker works in isolation and is covered by a unit test. This is where database schema migration correctness lives: the version tracker is what makes applying ordered, reversible schema changes so a database evolves alongside code safely true, and its test is the proof.

Keep the version tracker pure where you can. A function that takes an input and returns an output is trivial to test and impossible to mock wrong; a class with hidden state is the opposite. Push side effects to the caller and keep the version tracker about the transformation.

Phase 3: Up applier

Wire the up applier to handle a migration that alters without losing data.

// Up applier: database schema migration stage
export const upapplier = (next: Next) => async (req: Request) => {
  const out = await transform(req);
  return next(out);
};

After this phase the up applier works in isolation and is covered by a unit test. The up applier is the difference between a database schema migration system that works on the happy path and one that survives a migration that alters a large table and holds an exclusive lock long enough to block deploys.

Design the failure before the success. Decide what up applier does when a migration that alters a large table and holds an exclusive lock long enough to block deploys: reject, retry, shed, or degrade — and write that test first. A database schema migration system whose failure behavior is unspecified will pick one at random under pressure, and it is usually the wrong one.

Phase 4: Down reverter

Integrate the down reverter to make database schema migration observable and testable end to end.

// Down reverter for database schema migration
export async function downreverter(stream: AsyncIterable<Chunk>): Promise<Aggregate> {
  let acc = init();
  for await (const c of stream) acc = merge(acc, c);
  return acc;
}

After this phase the down reverter works in isolation and is covered by a unit test. Observability is not a finishing touch; it is how you know the database schema migration system is doing what it claims in production.

Emit one structured event per meaningful action, with enough context to localize a failure without a second round-trip. The down reverter is what turns “something is slow” into “the version tracker is slow for inputs matching this shape,” which is the difference between an alert and an investigation.

Phase 5: Status reporter

Finalize the status reporter so the database schema migration system degrades gracefully under overload.

// Status reporter: database schema migration finalization
export function statusreporter(state: State): Result {
  validate(state);
  return finalize(state);
}

After this phase the status reporter works in isolation and is covered by a unit test. The status reporter is what keeps overload from becoming corruption: when the database schema migration system is asked for more than it can do, it degrades in a defined way instead of failing in an undefined one.

Pick a degradation strategy for status reporter — shed, queue, or backpressure — and make it explicit in code and in a test. Leaving the behavior to chance is how a database schema migration traffic spike becomes a data loss incident.

Testing

Each phase has a focused test: migration loader, version tracker, and up applier are exercised in isolation, then an integration test drives the full database schema migration pipeline and asserts the a migration that alters case is handled.

Start with unit tests that pin each component’s contract: the migration loader returns the documented shape, the version tracker transforms correctly, and the up applier fails in the documented way. These tests are fast and they localize regressions to the component that broke.

Then add an integration test that composes the components and drives a realistic database schema migration scenario end to end. Assert the happy path first, then deliberately trigger a migration that alters a large table and holds an exclusive lock long enough to block deploys and assert the system degrades rather than corrupts. Because the components are pure where practical, the integration test stays deterministic rather than flaky.

A database schema migration suite must also prove the negative: bad input is rejected at the boundary, overload is shed rather than queued indefinitely, and a partial failure does not leave the system in an inconsistent state. These are the tests that catch the regressions a happy-path suite never would.

The entire database schema migration suite runs with no external service; every dependency is either in-process or stubbed, so every case is reproducible in CI. That is what lets the up applier failure-mode assertions live as tests rather than as a manual checklist that drifts out of date.

# run the database schema migration test suite
pnpm vitest run

Deployment and operations

Run at least two database schema migration instances behind a load balancer and share state in Redis. Set resource limits so database schema migration overload surfaces as backpressure rather than failure.

Run at least two stateless instances behind a load balancer, and move any shared state into a shared store so the database schema migration behavior is global rather than per instance. Size limits so the system sheds load before it runs out of memory, because a database schema migration system that OOMs under load fails in a way that is hard to reason about.

Drain on deploy: stop accepting new work, let in-flight database schema migration operations finish, then exit. Because the system holds no state that cannot be reconstructed, a rollout is a matter of cycling instances one at a time while watching the error rate.

When a dependency is unavailable, decide explicitly whether the database schema migration system fails closed or degrades. Leaving that choice to chance is how a dependency outage becomes a silent data loss, so make the choice visible in code and in a test.

Security considerations

Validate all database schema migration inputs at the boundary, reject oversized payloads before buffering them, and keep secrets in the environment rather than the repository.

The database schema migration system is a choke point, so it is the right place to reject malformed input before it reaches version tracker or up applier. Validate the shape and the size at the edge, and let the inner components assume what they receive is well-formed.

Never log secrets or full payloads; log enough to localize a failure without recording data that should not be on disk. Treat any external call as untrusted and validate its response before using it, because a database schema migration system that trusts its inputs blindly is one malformed response away from corruption.

Keep dependencies pinned and run an audit in CI. A transitive dependency with a known vulnerability is still a vulnerability in your database schema migration system, and the lockfile is what makes the audit meaningful.

Observability

Emit one structured event per database schema migration operation with the inputs’ shape, the outcome, and a correlation id. Track error rate and latency per component and alert on a step change rather than a single spike.

The correlation id is the thread that ties a user-visible symptom to the database schema migration decision and to the component log entry for the same operation. Without it, an investigation starts from a timestamp and a guess.

Watch the up applier failure rate separately from the overall error rate. A jump in up applier failures usually means a migration that alters a large table and holds an exclusive lock long enough to block deploys, not a bug in version tracker, and the two have different owners and different fixes.

Chart the share of database schema migration operations that shed, queue, or succeed. A change in the mix points at the cause faster than an aggregate latency can, because each class has a different root.

Troubleshooting

A migration that alters a large table and holds an exclusive lock long enough to block deploys. Reproduce with a minimal fixture, then add a guard test so the database schema migration system rejects the condition loudly.

Performance regresses under database schema migration load. Profile with the sampling analyzer, locate the hot path in version tracker, and add a targeted benchmark to lock the regression.

Keep the database schema migration dashboards separate from dependency dashboards. When an operation fails, the correlation id lets you decide in seconds whether the fault is migration loader, version tracker, or up applier, instead of guessing from a timeout.

Limitations and next steps

• Single-region; multi-region replication is out of scope.
• The migration loader is in-process and not horizontally shared by default.
• No auth layer; assume deployment behind an edge that enforces identity.

The database schema migration system is single-region by design; extending to multi-region means making migration loader shareable across regions, which is a real change to the consistency model rather than a config flag. The up applier is in-process, which is correct for one instance but must move to a shared store before scaling out.

There is no authentication or authorization layer; the system assumes it is deployed behind an edge that enforces identity. Adding one is straightforward but out of scope for this build, and doing it here would distract from the database schema migration correctness that is the point.

The natural next step, once the database schema migration pipeline is trusted in production, is to add a short-TTL cache for idempotent reads and a richer down reverter that exports histograms rather than counters. Both are additive and neither changes the contracts this build establishes.