🗂️ Database Migrations Concepts

What Is a Migration?

A database migration is a versioned script that describes one specific change to your database — creating a table, adding a column, building an index — together with instructions for undoing it. Run your migrations in order, and any empty database transforms into your current, correct schema. Run them on a teammate's laptop, on the CI server, and on production, and all three end up byte-for-byte identical.

Put simply: migrations are version control for your schema. Just as Git records every code change so you can replay history or roll back, a migration system records every schema change in ordered, immutable files that live right beside your application code.

Each arrow is a migration applied in sequence. The database's state at any point is simply "every migration up to here has run." That single idea — an ordered, replayable log of schema changes — is the whole concept.

Why Migrations Matter

Early in a project it is tempting to just open a database GUI and click "add column." It works — once. The problems arrive the moment a second person, a second environment, or a second month gets involved.

Life without migrations

What migrations give you

Anatomy of a Migration

Whatever tool you use, every migration shares the same three parts.

1. Up and down

The up function applies the change. The down function reverses it. They are mirror images: whatever up builds, down tears down.

// db/migrations/20260731143227_create_users_table.js
// Knex migration — 'up' applies, 'down' reverses.

/** @param {import('knex').Knex} knex */
exports.up = function (knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();              // auto-incrementing PK
    table.string('username').notNullable().unique();
    table.string('email').notNullable().unique();
    table.string('password_hash').notNullable();
    table.boolean('is_active').defaultTo(true);
    table.timestamps(true, true);                  // created_at + updated_at
  });
};

/** @param {import('knex').Knex} knex */
exports.down = function (knex) {
  return knex.schema.dropTable('users');           // the exact inverse
};

2. A version / timestamp

The filename starts with a timestamp (20260731143227) or a sequence number (001). This is what makes migrations ordered — the tool always runs them oldest-first so dependencies (a table before the foreign key that references it) resolve correctly.

3. The migrations tracking table

Here is the piece beginners overlook. Your migration tool creates a small bookkeeping table in your own database — Knex calls it knex_migrations, Prisma uses _prisma_migrations. It records which migrations have already run:

id | name                                  | batch | migration_time
---+---------------------------------------+-------+---------------------
 1 | 20260731143227_create_users_table.js |     1 | 2026-07-31 14:33:01
 2 | 20260731150000_create_posts_table.js |     1 | 2026-07-31 14:33:01
 3 | 20260801090000_add_user_bio.js        |     2 | 2026-08-01 09:15:22

When you run migrate:latest, the tool compares this table against the files on disk and runs only the ones not yet recorded. That is why you can run the command a hundred times and nothing happens after the first — the tracking table already knows those migrations are applied. This is the mechanism behind everything migrations do.

The Golden Rule: Never Edit an Applied Migration

This is the single most important rule in this lesson. Once a migration has been committed and applied anywhere — a teammate's machine, staging, production — treat it as frozen. Never change it.

Why? Because the tracking table records a migration as "done" by its name. If you edit the file's contents after it ran, the tool still sees the name in the table and won't run it again. Your machine now silently has a different schema from everyone who ran the original version. Some tools also store a checksum and will hard-error the moment a file's contents drift from what was applied.

Safe vs. Unsafe Changes & Zero-Downtime

On an empty dev database every change is instant and harmless. On a live production table with millions of rows and active traffic, some changes are safe and some can lock the table or break the running app. Knowing the difference is what separates a junior from a senior.

The zero-downtime pattern

The trick for the risky changes is to break one dangerous change into several safe ones, deployed as a sequence so old and new code both keep working at every step. Renaming last_name to family_name without downtime looks like this:

At no single moment does the schema and the deployed code disagree. This "expand, migrate, contract" sequence is how companies apply schema changes to systems serving millions of users without a maintenance window.

Schema Migrations vs. Data Migrations

There are two things a migration can change, and it's worth naming them clearly:

• Schema migration — changes the structure: create a table, add a column, build an index.
• Data migration — changes the contents: transform, move, or backfill the rows themselves.

A classic data migration: someone stored a full name in one name column and now needs separate first_name and last_name. That takes both kinds of change in one careful sequence.

// db/migrations/20260801090000_split_user_name.js

exports.up = async function (knex) {
  // 1) SCHEMA change: add the two new columns (nullable for now)
  await knex.schema.table('users', (table) => {
    table.string('first_name');
    table.string('last_name');
  });

  // 2) DATA change: reshape every existing row
  const users = await knex('users').select('id', 'name');
  for (const user of users) {
    const parts = (user.name || '').trim().split(/\s+/);
    await knex('users').where('id', user.id).update({
      first_name: parts[0] || '',
      last_name: parts.slice(1).join(' ') || '', // everything after the first word
    });
  }
};

exports.down = async function (knex) {
  // Rebuild the original 'name' from the split parts, then drop the columns.
  const users = await knex('users').select('id', 'first_name', 'last_name');
  for (const user of users) {
    const fullName = `${user.first_name || ''} ${user.last_name || ''}`.trim();
    await knex('users').where('id', user.id).update({ name: fullName });
  }
  await knex.schema.table('users', (table) => {
    table.dropColumn('first_name');
    table.dropColumn('last_name');
  });
};

Notice the down function carefully reverses both the data and the structure, in the opposite order. A reversible data migration is a hallmark of professional work.

The Tool Landscape

You rarely hand-write the tracking table or the run-in-order logic — a migration tool does it. In the JavaScript/Node world, four names dominate, and you'll see each up close in the next lesson.

Beyond Node there's Flyway and Liquibase (Java), Alembic (Python), and Rails migrations (Ruby) — all built on the same concepts. Even schema-less MongoDB benefits from migrations (via migrate-mongo) whenever the shape of documents needs to change across an existing collection. The tool changes; the mental model you learned here does not.

Practice & Quiz

// Step 1 — add nullable column
exports.up = (knex) =>
  knex.schema.table('users', (t) => t.string('phone_number'));

// (Step 2 happens in application code, deployed between migrations.)

// Step 3 — backfill existing rows
exports.up = (knex) =>
  knex('users').whereNull('phone_number').update({ phone_number: 'unknown' });

// Step 4 — enforce NOT NULL
exports.up = (knex) =>
  knex.schema.alterTable('users', (t) =>
    t.string('phone_number').notNullable().alter()
  );

exports.up = async function (knex) {
  await knex.schema.createTable('categories', (t) => {
    t.increments('id').primary();
    t.string('name').notNullable().unique();
  });
  await knex.schema.table('posts', (t) => {
    t.integer('category_id').unsigned().references('categories.id');
  });
};

exports.down = async function (knex) {
  // Undo in reverse: drop the FK column first, then the table it referenced.
  await knex.schema.table('posts', (t) => {
    t.dropColumn('category_id');
  });
  await knex.schema.dropTable('categories');
};

Best Practices & Pitfalls

✅ Do

• Keep migrations in version control, right next to the code that depends on them
• Make one logical change per migration and give it a descriptive name
• Always write a working down so every change is reversible
• Test migrations against a copy of production data before running them for real
• Break risky production changes into safe, sequenced steps (expand → migrate → contract)
• Back up the database before running migrations in production

❌ Don't

• Never edit or delete a migration that has already been applied or shared
• Don't make schema changes by hand in a GUI outside the migration system
• Don't drop a column or table before its data has been safely migrated
• Don't add a NOT NULL column with no default to a populated table in one step
• Don't let two deploys run migrations against the same database at the same time

Summary

📚 Additional Resources

• Knex.js — Migrations guide
• Prisma Migrate — official documentation
• node-pg-migrate — PostgreSQL migrations for Node
• Martin Fowler — Evolutionary Database Design

🚀 What's Next?

Now that you understand the concepts, the next lesson gets hands-on: installing and driving real migration tools — Knex, Sequelize, and Prisma Migrate — from the command line and inside your Node app: Using Migration Tools.