Database Migrations

Migrations are versioned database changes. You commit them to git. Each environment (dev, staging, prod) runs them in order, tracking which have already been applied.

A Migration

-- 0003_add_users_admin.sql
ALTER TABLE users ADD COLUMN admin BOOLEAN NOT NULL DEFAULT false;
CREATE INDEX idx_users_admin ON users(admin);

Each migration:

• Has a unique sequence number (0003)
• Lives in your repo
• Runs exactly once per environment
• Is recorded in a migrations table

With Drizzle Kit

# generate from your schema.ts
npx drizzle-kit generate

# apply
npx drizzle-kit migrate

Diff-based — you edit your TS schema, Drizzle generates the SQL.

With Prisma Migrate

# create + apply a dev migration
npx prisma migrate dev --name add_users_admin

# in production
npx prisma migrate deploy

Manual SQL Files

For Drizzle-less or Prisma-less setups, node-pg-migrate or dbmate run plain SQL files.

Rollbacks

Each migration has a “down” file/section that reverses it:

-- 0003_add_users_admin.down.sql
DROP INDEX idx_users_admin;
ALTER TABLE users DROP COLUMN admin;

Real production rollbacks are rare — you usually forward-fix instead. But the down migration is your insurance.

The Cardinal Rules

1. Never edit a migration after it’s run in any environment. Add a new migration to fix it.
2. Migrations are part of the deploy. Commit them alongside the code that depends on them.
3. Expand-then-contract: never break clients.
   • Adding a column with NOT NULL? Two steps: add nullable column → backfill → tighten to NOT NULL.
   • Renaming a column? Add new → write to both → migrate readers → stop writing old → drop old.

Zero-Downtime Patterns

When you deploy continuously, the DB schema must accept both old and new code for some overlap window. That’s the expand-contract dance. Bad migrations are how outages happen.

End of Chapter

Data is persisted. Next chapter: making Node ready for production — tests, debugging, logging, deploys, monitoring.