Loading
Create a CLI migration tool with up/down scripts, version tracking, rollback support, and schema validation.
Database migration tools solve a critical problem: how do you evolve a database schema safely, reproducibly, and reversibly across multiple environments? In this tutorial, you'll build a migration CLI from scratch that manages SQL migration files, tracks which migrations have been applied, and supports rollback.
What you'll build:
migrate up, migrate down, migrate status, migrate create)up and down SQLWhat you'll learn:
Create the project structure:
Create the directory layout:
Create a connection helper. For this tutorial we'll use a simple connection string pattern that works on macOS, Windows, and Linux:
The DATABASE_URL environment variable works identically on macOS, Windows (PowerShell: $env:DATABASE_URL = "..."), and Linux.
Before running any migrations, ensure the tracking table exists:
This table records which migrations have been applied and when. The name column stores the migration filename.
The create command generates a timestamped migration file:
The timestamp prefix ensures migrations are ordered chronologically. The file contains -- Up and -- Down sections that the migrator parses.
Split each migration file into its up and down SQL:
The up command applies all pending migrations in order, each wrapped in a transaction:
Each migration runs in its own transaction. If the SQL fails, the transaction rolls back and the migration is not recorded as applied.
The down command rolls back the most recently applied migration:
Show which migrations are applied and which are pending:
Wire everything together with a simple argument parser:
This works on macOS, Windows, and Linux — process.argv is cross-platform.
Create your first migration:
Edit the generated file:
Run the migration:
You now have a working migration tool. Extend it with features like: target version rollback (down --to 20240101), migration locking to prevent concurrent runs, and validation that ensures every up has a corresponding down.
mkdir db-migrate && cd db-migrate
npm init -ydb-migrate/
src/
cli.js # CLI entry point
migrator.js # Core migration logic
connection.js # Database connection
migrations/ # Migration files go here// src/connection.js
const { Client } = require("pg");
function createClient() {
const url = process.env.DATABASE_URL || "postgresql://localhost:5432/migrate_demo";
return new Client({ connectionString: url });
}
module.exports = { createClient };const MIGRATIONS_TABLE = `
CREATE TABLE IF NOT EXISTS _migrations (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
`;function createMigration(name) {
const timestamp = new Date().toISOString().replace(/[-:T]/g, "").slice(0, 14);
const filename = `${timestamp}_${name}.sql`;
const template = `-- Up\n\n-- Down\n`;
fs.writeFileSync(path.join("migrations", filename), template);
console.log(`Created: migrations/${filename}`);
}function parseMigration(content) {
const upMatch = content.indexOf("-- Down");
if (upMatch === -1) throw new Error('Missing "-- Down" marker');
const up = content.slice(content.indexOf("-- Up") + 5, upMatch).trim();
const down = content.slice(upMatch + 7).trim();
return { up, down };
}async function migrateUp(client, options = {}) {
const applied = await getApplied(client);
const pending = getAllMigrations().filter((m) => !applied.includes(m.name));
for (const migration of pending) {
const { up } = parseMigration(migration.content);
if (options.dryRun) {
console.log(`[DRY RUN] Would apply: ${migration.name}`);
console.log(up);
continue;
}
await client.query("BEGIN");
try {
await client.query(up);
await client.query("INSERT INTO _migrations (name) VALUES ($1)", [migration.name]);
await client.query("COMMIT");
console.log(`Applied: ${migration.name}`);
} catch (err) {
await client.query("ROLLBACK");
throw new Error(`Migration ${migration.name} failed: ${err.message}`);
}
}
}async function migrateDown(client, options = {}) {
const applied = await getApplied(client);
if (applied.length === 0) {
console.log("Nothing to roll back");
return;
}
const latest = applied[applied.length - 1];
const file = findMigration(latest);
const { down } = parseMigration(file.content);
if (options.dryRun) {
console.log(`[DRY RUN] Would rollback: ${latest}`);
console.log(down);
return;
}
await client.query("BEGIN");
try {
await client.query(down);
await client.query("DELETE FROM _migrations WHERE name = $1", [latest]);
await client.query("COMMIT");
console.log(`Rolled back: ${latest}`);
} catch (err) {
await client.query("ROLLBACK");
throw new Error(`Rollback of ${latest} failed: ${err.message}`);
}
}async function status(client) {
const applied = await getApplied(client);
const all = getAllMigrations();
for (const m of all) {
const isApplied = applied.includes(m.name);
const marker = isApplied ? "✓" : "○";
console.log(`${marker} ${m.name}`);
}
}const [, , command, ...args] = process.argv;
switch (command) {
case "create":
createMigration(args[0] || "unnamed");
break;
case "up":
run(migrateUp, args.includes("--dry-run"));
break;
case "down":
run(migrateDown, args.includes("--dry-run"));
break;
case "status":
run(status);
break;
default:
console.log("Usage: migrate <create|up|down|status>");
}node src/cli.js create add_users_table-- Up
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Down
DROP TABLE IF EXISTS users;node src/cli.js up
node src/cli.js status
node src/cli.js down
node src/cli.js status