Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Activate

• Creating or altering database tables
• Adding/removing columns or indexes
• Running data migrations (backfill, transform)
• Planning zero-downtime schema changes
• Setting up migration tooling for a new project

Core Principles

1. Every change is a migration — never alter production databases manually
2. Migrations are forward-only in production — rollbacks use new forward migrations
3. Schema and data migrations are separate — never mix DDL and DML in one migration
4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
5. Migrations are immutable once deployed — never edit a migration that has run in production

Migration Safety Checklist

Before applying any migration:

• Migration has both UP and DOWN (or is explicitly marked irreversible)
• No full table locks on large tables (use concurrent operations)
• New columns have defaults or are nullable (never add NOT NULL without default)
• Indexes created concurrently (not inline with CREATE TABLE for existing tables)
• Data backfill is a separate migration from schema change
• Tested against a copy of production data
• Rollback plan documented

PostgreSQL Patterns

Adding a Column Safely

sql
1-- GOOD: Nullable column, no lock
2ALTER TABLE users ADD COLUMN avatar_url TEXT;
3
4-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
5ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
6
7-- BAD: NOT NULL without default on existing table (requires full rewrite)
8ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
9-- This locks the table and rewrites every row

Adding an Index Without Downtime

sql
1-- BAD: Blocks writes on large tables
2CREATE INDEX idx_users_email ON users (email);
3
4-- GOOD: Non-blocking, allows concurrent writes
5CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
6
7-- Note: CONCURRENTLY cannot run inside a transaction block
8-- Most migration tools need special handling for this

Renaming a Column (Zero-Downtime)

Never rename directly in production. Use the expand-contract pattern:

sql
1-- Step 1: Add new column (migration 001)
2ALTER TABLE users ADD COLUMN display_name TEXT;
3
4-- Step 2: Backfill data (migration 002, data migration)
5UPDATE users SET display_name = username WHERE display_name IS NULL;
6
7-- Step 3: Update application code to read/write both columns
8-- Deploy application changes
9
10-- Step 4: Stop writing to old column, drop it (migration 003)
11ALTER TABLE users DROP COLUMN username;

Removing a Column Safely

sql
1-- Step 1: Remove all application references to the column
2-- Step 2: Deploy application without the column reference
3-- Step 3: Drop column in next migration
4ALTER TABLE orders DROP COLUMN legacy_status;
5
6-- For Django: use SeparateDatabaseAndState to remove from model
7-- without generating DROP COLUMN (then drop in next migration)

Large Data Migrations

sql
1-- BAD: Updates all rows in one transaction (locks table)
2UPDATE users SET normalized_email = LOWER(email);
3
4-- GOOD: Batch update with progress
5DO $$
6DECLARE
7  batch_size INT := 10000;
8  rows_updated INT;
9BEGIN
10  LOOP
11    UPDATE users
12    SET normalized_email = LOWER(email)
13    WHERE id IN (
14      SELECT id FROM users
15      WHERE normalized_email IS NULL
16      LIMIT batch_size
17      FOR UPDATE SKIP LOCKED
18    );
19    GET DIAGNOSTICS rows_updated = ROW_COUNT;
20    RAISE NOTICE 'Updated % rows', rows_updated;
21    EXIT WHEN rows_updated = 0;
22    COMMIT;
23  END LOOP;
24END $$;

Prisma (TypeScript/Node.js)

Workflow

bash
1# Create migration from schema changes
2npx prisma migrate dev --name add_user_avatar
3
4# Apply pending migrations in production
5npx prisma migrate deploy
6
7# Reset database (dev only)
8npx prisma migrate reset
9
10# Generate client after schema changes
11npx prisma generate

Schema Example

prisma
1model User {
2  id        String   @id @default(cuid())
3  email     String   @unique
4  name      String?
5  avatarUrl String?  @map("avatar_url")
6  createdAt DateTime @default(now()) @map("created_at")
7  updatedAt DateTime @updatedAt @map("updated_at")
8  orders    Order[]
9
10  @@map("users")
11  @@index([email])
12}

Custom SQL Migration

For operations Prisma cannot express (concurrent indexes, data backfills):

bash
1# Create empty migration, then edit the SQL manually
2npx prisma migrate dev --create-only --name add_email_index

sql
1-- migrations/20240115_add_email_index/migration.sql
2-- Prisma cannot generate CONCURRENTLY, so we write it manually
3CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

Drizzle (TypeScript/Node.js)

Workflow

bash
1# Generate migration from schema changes
2npx drizzle-kit generate
3
4# Apply migrations
5npx drizzle-kit migrate
6
7# Push schema directly (dev only, no migration file)
8npx drizzle-kit push

Schema Example

typescript
1import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";
2
3export const users = pgTable("users", {
4  id: uuid("id").primaryKey().defaultRandom(),
5  email: text("email").notNull().unique(),
6  name: text("name"),
7  isActive: boolean("is_active").notNull().default(true),
8  createdAt: timestamp("created_at").notNull().defaultNow(),
9  updatedAt: timestamp("updated_at").notNull().defaultNow(),
10});

Kysely (TypeScript/Node.js)

Workflow (kysely-ctl)

bash
1# Initialize config file (kysely.config.ts)
2kysely init
3
4# Create a new migration file
5kysely migrate make add_user_avatar
6
7# Apply all pending migrations
8kysely migrate latest
9
10# Rollback last migration
11kysely migrate down
12
13# Show migration status
14kysely migrate list

Migration File

typescript
1// migrations/2024_01_15_001_create_user_profile.ts
2import { type Kysely, sql } from 'kysely'
3
4// IMPORTANT: Always use Kysely<any>, not your typed DB interface.
5// Migrations are frozen in time and must not depend on current schema types.
6export async function up(db: Kysely<any>): Promise<void> {
7  await db.schema
8    .createTable('user_profile')
9    .addColumn('id', 'serial', (col) => col.primaryKey())
10    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
11    .addColumn('avatar_url', 'text')
12    .addColumn('created_at', 'timestamp', (col) =>
13      col.defaultTo(sql`now()`).notNull()
14    )
15    .execute()
16
17  await db.schema
18    .createIndex('idx_user_profile_avatar')
19    .on('user_profile')
20    .column('avatar_url')
21    .execute()
22}
23
24export async function down(db: Kysely<any>): Promise<void> {
25  await db.schema.dropTable('user_profile').execute()
26}

Programmatic Migrator

typescript
1import { Migrator, FileMigrationProvider } from 'kysely'
2import { promises as fs } from 'fs'
3import * as path from 'path'
4// ESM only — CJS can use __dirname directly
5import { fileURLToPath } from 'url'
6const migrationFolder = path.join(
7  path.dirname(fileURLToPath(import.meta.url)),
8  './migrations',
9)
10
11// `db` is your Kysely<any> database instance
12const migrator = new Migrator({
13  db,
14  provider: new FileMigrationProvider({
15    fs,
16    path,
17    migrationFolder,
18  }),
19  // WARNING: Only enable in development. Disables timestamp-ordering
20  // validation, which can cause schema drift between environments.
21  // allowUnorderedMigrations: true,
22})
23
24const { error, results } = await migrator.migrateToLatest()
25
26results?.forEach((it) => {
27  if (it.status === 'Success') {
28    console.log(`migration "${it.migrationName}" executed successfully`)
29  } else if (it.status === 'Error') {
30    console.error(`failed to execute migration "${it.migrationName}"`)
31  }
32})
33
34if (error) {
35  console.error('migration failed', error)
36  process.exit(1)
37}

Django (Python)

Workflow

bash
1# Generate migration from model changes
2python manage.py makemigrations
3
4# Apply migrations
5python manage.py migrate
6
7# Show migration status
8python manage.py showmigrations
9
10# Generate empty migration for custom SQL
11python manage.py makemigrations --empty app_name -n description

Data Migration

python
1from django.db import migrations
2
3def backfill_display_names(apps, schema_editor):
4    User = apps.get_model("accounts", "User")
5    batch_size = 5000
6    users = User.objects.filter(display_name="")
7    while users.exists():
8        batch = list(users[:batch_size])
9        for user in batch:
10            user.display_name = user.username
11        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)
12
13def reverse_backfill(apps, schema_editor):
14    pass  # Data migration, no reverse needed
15
16class Migration(migrations.Migration):
17    dependencies = [("accounts", "0015_add_display_name")]
18
19    operations = [
20        migrations.RunPython(backfill_display_names, reverse_backfill),
21    ]

SeparateDatabaseAndState

Remove a column from the Django model without dropping it from the database immediately:

python
1class Migration(migrations.Migration):
2    operations = [
3        migrations.SeparateDatabaseAndState(
4            state_operations=[
5                migrations.RemoveField(model_name="user", name="legacy_field"),
6            ],
7            database_operations=[],  # Don't touch the DB yet
8        ),
9    ]

golang-migrate (Go)

Workflow

bash
1# Create migration pair
2migrate create -ext sql -dir migrations -seq add_user_avatar
3
4# Apply all pending migrations
5migrate -path migrations -database "$DATABASE_URL" up
6
7# Rollback last migration
8migrate -path migrations -database "$DATABASE_URL" down 1
9
10# Force version (fix dirty state)
11migrate -path migrations -database "$DATABASE_URL" force VERSION

Migration Files

sql
1-- migrations/000003_add_user_avatar.up.sql
2ALTER TABLE users ADD COLUMN avatar_url TEXT;
3CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;
4
5-- migrations/000003_add_user_avatar.down.sql
6DROP INDEX IF EXISTS idx_users_avatar;
7ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

Zero-Downtime Migration Strategy

For critical production changes, follow the expand-contract pattern:

Phase 1: EXPAND
  - Add new column/table (nullable or with default)
  - Deploy: app writes to BOTH old and new
  - Backfill existing data

Phase 2: MIGRATE
  - Deploy: app reads from NEW, writes to BOTH
  - Verify data consistency

Phase 3: CONTRACT
  - Deploy: app only uses NEW
  - Drop old column/table in separate migration

Timeline Example

Day 1: Migration adds new_status column (nullable)
Day 1: Deploy app v2 — writes to both status and new_status
Day 2: Run backfill migration for existing rows
Day 3: Deploy app v3 — reads from new_status only
Day 7: Migration drops old status column

Anti-Patterns