Database Migrations: Why You Need Them and How They Work

Quick Answer

A database migration is a small script that changes your schema — adding a table, a column, an index — recorded as a versioned file alongside your application code. A migration runner tracks which ones have already been applied in a table inside the database itself, so re-running the full set is always safe and every environment ends up with an identical schema. The two things that actually matter are ordering, since migrations must run in a fixed sequence, and idempotency, since re-running an already-applied migration should change nothing.

Why you can't just run ALTER TABLE by hand

The tempting shortcut is to connect to the production database and run ALTER TABLE directly whenever you need a schema change. It works, once. The problem shows up the second time: your local database, your staging database, and production have now all diverged, and nothing records what changed, when, or why. Six months later nobody can say with confidence what production's schema actually looks like without connecting and checking table by table.

It gets worse with a team. If schema changes live only as commands someone typed into a terminal, every other developer has to be told to run the same command on their own machine, and inevitably someone doesn't, or runs it slightly differently. A fresh developer laptop, or a new staging environment, has no way to reconstruct the current schema from scratch.

Migrations fix this by making schema changes a normal part of your codebase: a file, committed to version control, reviewed the same way as any other code change. "What does the schema look like" becomes "run the migrations," the same answer for every environment, every time.

Anatomy of a migration file

A migration file is usually named with a timestamp prefix, so files sort into the order they need to run in without anyone having to manage a number by hand:

migrations/
  20260101_create_users.sql
  20260115_add_users_created_at.sql
  20260201_create_orders.sql

Each one pairs an up step (apply the change) with a down step (reverse it), whether that's expressed as two SQL files, two functions, or two blocks in one file depends on the tool. In code form, a migration is just an object with an id and a function that runs some schema-changing SQL:

const migrations = [
  {
    id: '20260101_create_users',
    up: (db) => db.exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL);`),
  },
  {
    id: '20260115_add_users_created_at',
    up: (db) => db.exec(`ALTER TABLE users ADD COLUMN created_at TEXT;`),
  },
  {
    id: '20260201_create_orders',
    up: (db) => db.exec(`CREATE TABLE orders (
      id INTEGER PRIMARY KEY, user_id INTEGER, total INTEGER,
      FOREIGN KEY (user_id) REFERENCES users(id));`),
  },
];

The timestamp prefix matters more than it looks. If two developers each add a migration on the same day named 001_x and 002_y by hand, merging their branches creates a numbering collision. A timestamp like 20260115143022 is effectively unique per developer per moment, so two people's migrations merge cleanly and still sort into a sensible order.

How a migration runner tracks what's already applied

The core trick of any migration tool is a table, usually called something like schema_migrations, that lives inside the database itself and records which migration ids have already run. Before applying anything, the runner checks this table and skips whatever it already sees:

function runMigrations() {
  db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
    id TEXT PRIMARY KEY, applied_at TEXT NOT NULL
  );`);

  const applied = new Set(
    db.prepare('SELECT id FROM schema_migrations').all().map((r) => r.id)
  );

  let ranCount = 0;
  for (const migration of migrations) {
    if (applied.has(migration.id)) continue;
    migration.up(db);
    db.prepare('INSERT INTO schema_migrations (id, applied_at) VALUES (?, ?)')
      .run(migration.id, new Date().toISOString());
    ranCount++;
  }
  console.log(`${ranCount} migration(s) applied this run.`);
}

runMigrations(); // first run
runMigrations(); // second run, right after

Running this twice in a row against a fresh in-memory database produces:

3 migration(s) applied this run.
0 migration(s) applied this run.

The first run applies all three migrations and records their ids. The second run finds all three ids already present and applies nothing — which is exactly the property you want. Deploying your app re-runs the migration step every time, and that has to be safe whether zero migrations are pending or five are. This is what people mean when they say migrations should be idempotent: running the same set twice produces the same end state as running it once.

The gotcha: "down" doesn't mean "undo"

A down migration reverses the schema change. It does not restore data that the up migration destroyed, and dropping a column is the clearest example of the gap between the two:

db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT, phone TEXT)');
db.prepare('INSERT INTO t (id, name, phone) VALUES (1, ?, ?)').run('Asha', '9876543210');
console.log(db.prepare('SELECT * FROM t').all());
// [ { id: 1, name: 'Asha', phone: '9876543210' } ]

db.exec('ALTER TABLE t DROP COLUMN phone'); // the "down" migration
console.log(db.prepare('SELECT * FROM t').all());
// [ { id: 1, name: 'Asha' } ]  -- phone value is gone, not just the column

db.exec('ALTER TABLE t ADD COLUMN phone TEXT'); // running "up" again
console.log(db.prepare('SELECT * FROM t').all());
// [ { id: 1, name: 'Asha', phone: null } ]  -- the column is back, the DATA is not

The column comes back exactly as it was defined. Asha's phone number does not — it's simply gone, replaced by null, because dropping a column deletes its stored values along with the column definition, and nothing recorded what those values used to be. A down migration for DROP COLUMN phone can only ever be ADD COLUMN phone; it has no way to know what the data was.

The practical takeaway: treat any migration involving DROP COLUMN, DROP TABLE, or a destructive data transformation as one-way in practice, whatever the down script claims to do. Take a database backup before running one against real data, and for anything valuable, migrate in two steps — stop writing to the column first, verify nothing reads it, and only drop it in a later, separate migration once you're certain.

Migrations in a team, and how to not break production

Once a migration has run against production, treat the file as frozen. Editing it and expecting the change to apply again does nothing on environments where it already ran — the runner sees the same id in schema_migrations and skips it, silently leaving those environments on the old version while anyone running migrations fresh gets the edited one. If a migration needs correcting, write a new migration that fixes it forward; never edit history.

Locking is the other production-specific risk that never shows up locally. An ALTER TABLE on a small local dev database finishes instantly. The same statement against a production table with tens of millions of rows can hold a lock for minutes, blocking every read and write to that table for the whole time — effectively an outage triggered by a routine deploy. PostgreSQL's CREATE INDEX CONCURRENTLY exists specifically to build an index without taking that lock, at the cost of taking longer to complete.

For a rename or type change on a live table, the safe pattern is several small migrations instead of one big one: add the new column, backfill it in batches, write to both old and new columns during a transition period, switch reads over, and only then drop the old column in its own later migration. Slower, but nothing ever has to fully stop for it.

Frequently Asked Questions