Database Migrations Done Right
Schema changes are the most dangerous deploys — here are the patterns for zero-downtime migrations, rollback safety, and avoiding data loss.
Database migrations are the most anxiety-inducing part of any deployment. A botched migration can lock tables for minutes, corrupt data, or take down production. Unlike code changes, schema changes are hard to rollback — you can't just revert a commit when data has already been transformed.
Migration Files as Source of Truth
Every schema change lives in a numbered, timestamped migration file. The database's current state is the sum of all applied migrations.
// migrations/20200501_001_create_orders_table.ts
import { Kysely, sql } from "kysely";
export async function up(db: Kysely<any>) {
await db.schema
.createTable("orders")
.addColumn("id", "uuid", (col) =>
col.primaryKey().defaultTo(sql`gen_random_uuid()`),
)
.addColumn("customer_id", "uuid", (col) =>
col.notNull().references("customers.id"),
)
.addColumn("status", "varchar(50)", (col) =>
col.notNull().defaultTo("pending"),
)
.addColumn("total", "decimal(10,2)", (col) => col.notNull())
.addColumn("created_at", "timestamptz", (col) =>
col.notNull().defaultTo(sql`now()`),
)
.execute();
await db.schema
.createIndex("idx_orders_customer_id")
.on("orders")
.column("customer_id")
.execute();
}
export async function down(db: Kysely<any>) {
await db.schema.dropTable("orders").execute();
}Every migration has an up (apply) and down (rollback). In practice, down migrations for destructive changes (dropping columns, changing types) often can't restore data. They're still worth writing for structural rollbacks.
The Expand-Contract Pattern
Zero-downtime migrations follow a three-phase approach: expand the schema, migrate code, then contract the schema.
-- Phase 1: EXPAND — add the new column, keep the old one
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
-- Phase 2: MIGRATE — backfill data, deploy code that writes to both
UPDATE users SET full_name = first_name || ' ' || last_name
WHERE full_name IS NULL;
-- Phase 3: CONTRACT — remove the old columns (after all code uses full_name)
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;// ❌ Single migration — requires downtime, breaks running code
// Deploy migration: rename column from "name" to "full_name"
// Old code writes to "name" → fails
// New code writes to "full_name" → needs migration first
// You MUST take downtime to coordinate
// ✅ Expand-contract — zero downtime
// Deploy 1: Add full_name column, code writes to BOTH name and full_name
// Deploy 2: Backfill full_name from name where null
// Deploy 3: Code reads from full_name only
// Deploy 4: Drop name columnEach deploy is independently safe. If any step fails, the previous state still works.
Dangerous Operations
Some schema changes lock the table and block all reads and writes. On a table with millions of rows, this means downtime.
-- ❌ DANGEROUS: locks the entire table while rewriting all rows
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ NOT NULL DEFAULT now();
-- On a 10M row table, this takes minutes with an exclusive lock
-- ✅ SAFE: add column as nullable (no rewrite), then backfill in batches
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
-- Instant — no table rewrite
-- Backfill in batches to avoid long-running transactions
UPDATE orders SET shipped_at = created_at
WHERE id IN (SELECT id FROM orders WHERE shipped_at IS NULL LIMIT 10000);| Operation | Lock type | Safe at scale? |
|---|---|---|
ADD COLUMN (nullable) | Brief metadata lock | ✅ Yes |
ADD COLUMN (with default) | Table rewrite (pre-PG 11) | ⚠️ Depends on version |
DROP COLUMN | Brief metadata lock | ✅ Yes |
ALTER COLUMN TYPE | Table rewrite | ❌ Dangerous |
CREATE INDEX | Share lock (blocks writes) | ❌ Use CONCURRENTLY |
CREATE INDEX CONCURRENTLY | No lock | ✅ Yes |
Index Creation Without Downtime
-- ❌ Blocks all writes until the index is built
CREATE INDEX idx_orders_status ON orders (status);
-- ✅ Builds the index without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
-- Takes longer but doesn't lock the tableCONCURRENTLY can't run inside a transaction, so it needs to be its own migration step.
Backfilling Data Safely
Large data backfills should run in batches to avoid long-running transactions and lock contention.
async function backfillInBatches(
db: Kysely<any>,
batchSize = 5000,
) {
let updated = batchSize;
while (updated === batchSize) {
const result = await db
.updateTable("orders")
.set({ shipped_at: sql`created_at` })
.where("shipped_at", "is", null)
.where(
"id",
"in",
db
.selectFrom("orders")
.select("id")
.where("shipped_at", "is", null)
.limit(batchSize),
)
.executeTakeFirst();
updated = Number(result.numUpdatedRows);
// Yield to other queries between batches
await new Promise((resolve) => setTimeout(resolve, 100));
}
}Rollback Strategy
Always have a rollback plan before applying a migration.
// Migration with explicit rollback verification
export async function up(db: Kysely<any>) {
// Step 1: Add new column
await db.schema
.alterTable("products")
.addColumn("sku", "varchar(50)")
.execute();
// Step 2: Create unique index
await sql`CREATE UNIQUE INDEX CONCURRENTLY idx_products_sku ON products (sku)`.execute(db);
}
export async function down(db: Kysely<any>) {
await sql`DROP INDEX CONCURRENTLY IF EXISTS idx_products_sku`.execute(db);
await db.schema.alterTable("products").dropColumn("sku").execute();
}
// Rollback test: run in staging before production
// 1. Apply migration (up)
// 2. Verify application works
// 3. Rollback migration (down)
// 4. Verify application still works
// 5. Re-apply migration (up)Test rollbacks in staging. A migration that can't be rolled back safely needs extra caution and a detailed runbook.
Key Takeaways
- Use the expand-contract pattern for zero-downtime schema changes
- Never add a NOT NULL column with a default on large tables — add nullable, backfill, then add constraint
- Create indexes concurrently to avoid blocking writes during index builds
- Backfill data in batches to prevent long-running transactions and lock contention
- Test rollbacks in staging before applying migrations to production
- Every migration needs a down function — even if it's imperfect, it's better than nothing
