NodeJSDatabase Migrations

Database Migrations

Your schema changes over time — new columns, new tables, renamed fields, new indexes. A migration is a versioned, scripted change to the database schema, checked into source control alongside your code. Migrations make schema evolution reproducible: the same ordered set of changes applied to dev, staging, and production yields identical structure. This page explains why migrations matter, the up/down model, and the rules for changing a live database without downtime or data loss.

Why migrations, not manual changes

  • Reproducible — every environment gets the exact same schema in the same order.

  • Version-controlled — schema changes are reviewed in PRs, like code.

  • Collaborative — teammates run pending migrations to sync their local DB.

  • Reversible — a down step can undo a change when a deploy goes wrong.

  • Auditable — the migration history is a timeline of how the schema got here.

Never hand-edit a production schema — and never use `sync`/`synchronize` there
Manually running `ALTER TABLE` in production (or letting an ORM's [`sequelize.sync`](/nodejs/sequelize) / [`typeorm synchronize`](/nodejs/typeorm) reshape tables) is how data gets silently lost and environments drift out of sync. Those auto-sync features are dev conveniences only. Production schema changes go through **migrations**: scripted, reviewed, and applied by a deliberate command.
The up / down model

migrations/20260620_add_user_role.js

JS
export async function up(db) {
  // Apply the change:
  await db.query(`
    ALTER TABLE users
    ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'user'
  `)
  await db.query('CREATE INDEX idx_users_role ON users(role)')
}

export async function down(db) {
  // Reverse it (for rollback):
  await db.query('DROP INDEX idx_users_role')
  await db.query('ALTER TABLE users DROP COLUMN role')
}
`up` applies, `down` reverses — both are part of the contract
Each migration has an `up` (apply) and ideally a `down` (undo). The migration tool records which have run in a tracking table (e.g. `_migrations`), so it knows what's pending and applies them in **timestamp order**. `down` lets you roll back a bad deploy. Some changes (a dropped column's data) can't truly be reversed — write `down` where you can, and know its limits.
Tooling per stack

Stack

Migration tool / command

Prisma

prisma migrate dev / migrate deploy

Sequelize

sequelize-cli db:migrate

TypeORM

typeorm migration:generate / migration:run

Knex / raw SQL

knex migrate:latest

Mongoose

migrate-mongo (data migrations, not schema)

Bash
# Typical workflow (Prisma shown):
npx prisma migrate dev --name add_user_role   # create + apply in dev
npx prisma migrate deploy                      # apply pending in production
Use the production-apply command in prod, never the dev one
Dev commands (`prisma migrate dev`, `sequelize db:migrate` against a scratch DB) may reset or interactively prompt. Production deploys must run the **non-interactive apply** command (`prisma migrate deploy`) as a controlled step in your release pipeline — typically before the new app code starts. Commit migration files to git; never generate them on the production box.
Expand–contract: changing live schemas safely

Text
Renaming a column WITHOUT downtime (e.g. 'username' → 'handle'):

  1. EXPAND   add 'handle'; deploy code that writes BOTH columns
  2. BACKFILL copy existing 'username' values into 'handle'
  3. MIGRATE  deploy code that reads/writes only 'handle'
  4. CONTRACT once nothing uses 'username', drop it in a later migration
A breaking schema change deployed in one step takes the app down
If old app code is still running when you drop or rename a column, those instances crash on every query. The safe technique is **expand–contract** (a.k.a. parallel change): first *add* the new structure and make code tolerate both, backfill data, switch reads to the new column, and only *then* remove the old one in a separate, later migration. Each step is independently deployable and backward-compatible. Big-bang schema changes plus a rolling deploy are a classic outage.
Rules for migration files

  • Forward-only in spirit — once a migration is applied in production, never edit it; write a new one to fix mistakes.

  • Small and focused — one logical change per migration; easier to review and roll back.

  • Backfill big tables in batches — a single UPDATE on millions of rows locks the table; chunk it.

  • Separate schema changes from data migrations when possible.

  • Test the migration on a production-like copy before deploying.

Never edit a migration that has already run in production
Migration tools track applied migrations by name/checksum. Editing one that already ran means dev/staging may re-run or flag a checksum mismatch while production keeps the old version — instant drift. Treat applied migrations as **immutable history**: to correct a mistake, add a *new* migration that fixes it forward. The only safe edits are to migrations that haven't been applied anywhere yet.
Migrations + deployment order
Run migrations before (compatible) code — and keep them backward-compatible
The reliable sequence: deploy a migration that's compatible with *both* the old and new code, run it, then deploy the new code. Because of rolling deploys and rollbacks, at any moment two code versions may run against one schema — so each migration must not break the currently-running version. This is precisely why [expand–contract](#) matters and why "drop the column in the same deploy that stops using it" causes outages. Plan migrations as part of the release, not an afterthought.
Next
Guarantee multi-step operations all-or-nothing: [Transactions](/nodejs/transactions).