Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate). Use when writing a schema or data migration, planning a rollback, or aiming for zero-downtime deployment.
Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate). Use when writing a schema or data migration, planning a rollback, or aiming for zero-downtime deployment.
metadata
{"origin":"ECC"}
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
Every change is a migration — never alter production databases manually
Migrations are forward-only in production — rollbacks use new forward migrations
Schema and data migrations are separate — never mix DDL and DML in one migration
Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
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
-- GOOD: Nullable column, no lockALTER TABLE users ADDCOLUMN avatar_url TEXT;
-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)ALTER TABLE users ADDCOLUMN is_active BOOLEANNOT NULLDEFAULTtrue;
-- BAD: NOT NULL without default on existing table (requires full rewrite)ALTER TABLE users ADDCOLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row
Adding an Index Without Downtime
-- BAD: Blocks writes on large tablesCREATE INDEX idx_users_email ON users (email);
-- GOOD: Non-blocking, allows concurrent writesCREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- Note: CONCURRENTLY cannot run inside a transaction block-- Most migration tools need special handling for this
Renaming a Column (Zero-Downtime)
Never rename directly in production. Use the expand-contract pattern:
-- Step 1: Add new column (migration 001)ALTER TABLE users ADDCOLUMN display_name TEXT;
-- Step 2: Backfill data (migration 002, data migration)UPDATE users SET display_name = username WHERE display_name ISNULL;
-- Step 3: Update application code to read/write both columns-- Deploy application changes-- Step 4: Stop writing to old column, drop it (migration 003)ALTER TABLE users DROPCOLUMN username;
Removing a Column Safely
-- Step 1: Remove all application references to the column-- Step 2: Deploy application without the column reference-- Step 3: Drop column in next migrationALTER TABLE orders DROPCOLUMN legacy_status;
-- For Django: use SeparateDatabaseAndState to remove from model-- without generating DROP COLUMN (then drop in next migration)
Large Data Migrations
-- BAD: Updates all rows in one transaction (locks table)UPDATE users SET normalized_email =LOWER(email);
-- GOOD: Batch update with progress
DO $$
DECLARE
batch_size INT :=10000;
rows_updated INT;
BEGIN
LOOP
UPDATE users
SET normalized_email =LOWER(email)
WHERE id IN (
SELECT id FROM users
WHERE normalized_email ISNULL
LIMIT batch_size
FORUPDATESKIP LOCKED
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
RAISE NOTICE 'Updated % rows', rows_updated;
EXIT WHEN rows_updated =0;
COMMIT;
END LOOP;
END $$;
Prisma (TypeScript/Node.js)
Workflow
# Create migration from schema changes
npx prisma migrate dev --name add_user_avatar
# Apply pending migrations in production
npx prisma migrate deploy
# Reset database (dev only)
npx prisma migrate reset
# Generate client after schema changes
npx prisma generate
Schema Example
model User {
id String @id @default(cuid())
email String @unique
name String?
avatarUrl String? @map("avatar_url")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
orders Order[]
@@map("users")
@@index([email])
}
Custom SQL Migration
For operations Prisma cannot express (concurrent indexes, data backfills):
# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index
-- migrations/20240115_add_email_index/migration.sql-- Prisma cannot generate CONCURRENTLY, so we write it manuallyCREATE INDEX CONCURRENTLY IF NOTEXISTS idx_users_email ON users (email);
# Initialize config file (kysely.config.ts)
kysely init
# Create a new migration file
kysely migrate make add_user_avatar
# Apply all pending migrations
kysely migrate latest
# Rollback last migration
kysely migrate down
# Show migration status
kysely migrate list
Migration File
// migrations/2024_01_15_001_create_user_profile.tsimport { typeKysely, sql } from'kysely'// IMPORTANT: Always use Kysely<any>, not your typed DB interface.// Migrations are frozen in time and must not depend on current schema types.exportasyncfunctionup(db: Kysely<any>): Promise<void> {
await db.schema
.createTable('user_profile')
.addColumn('id', 'serial', (col) => col.primaryKey())
.addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
.addColumn('avatar_url', 'text')
.addColumn('created_at', 'timestamp', (col) =>
col.defaultTo(sql`now()`).notNull()
)
.execute()
await db.schema
.createIndex('idx_user_profile_avatar')
.on('user_profile')
.column('avatar_url')
.execute()
}
exportasyncfunctiondown(db: Kysely<any>): Promise<void> {
await db.schema.dropTable('user_profile').execute()
}
Programmatic Migrator
import { Migrator, FileMigrationProvider } from'kysely'import { promises as fs } from'fs'import * as path from'path'// ESM only — CJS can use __dirname directlyimport { fileURLToPath } from'url'const migrationFolder = path.join(
path.dirname(fileURLToPath(import.meta.url)),
'./migrations',
)
// `db` is your Kysely<any> database instanceconst migrator = newMigrator({
db,
provider: newFileMigrationProvider({
fs,
path,
migrationFolder,
}),
// WARNING: Only enable in development. Disables timestamp-ordering// validation, which can cause schema drift between environments.// allowUnorderedMigrations: true,
})
const { error, results } = await migrator.migrateToLatest()
results?.forEach((it) => {
if (it.status === 'Success') {
console.log(`migration "${it.migrationName}" executed successfully`)
} elseif (it.status === 'Error') {
console.error(`failed to execute migration "${it.migrationName}"`)
}
})
if (error) {
console.error('migration failed', error)
process.exit(1)
}
Django (Python)
Workflow
# Generate migration from model changes
python manage.py makemigrations
# Apply migrations
python manage.py migrate
# Show migration status
python manage.py showmigrations
# Generate empty migration for custom SQL
python manage.py makemigrations --empty app_name -n description
Data Migration
from django.db import migrations
defbackfill_display_names(apps, schema_editor):
User = apps.get_model("accounts", "User")
batch_size = 5000
users = User.objects.filter(display_name="")
while users.exists():
batch = list(users[:batch_size])
for user in batch:
user.display_name = user.username
User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)
defreverse_backfill(apps, schema_editor):
pass# Data migration, no reverse neededclassMigration(migrations.Migration):
dependencies = [("accounts", "0015_add_display_name")]
operations = [
migrations.RunPython(backfill_display_names, reverse_backfill),
]
SeparateDatabaseAndState
Remove a column from the Django model without dropping it from the database immediately:
classMigration(migrations.Migration):
operations = [
migrations.SeparateDatabaseAndState(
state_operations=[
migrations.RemoveField(model_name="user", name="legacy_field"),
],
database_operations=[], # Don't touch the DB yet
),
]
golang-migrate (Go)
Workflow
# Create migration pair
migrate create -ext sql -dir migrations -seq add_user_avatar
# Apply all pending migrations
migrate -path migrations -database "$DATABASE_URL" up
# Rollback last migration
migrate -path migrations -database "$DATABASE_URL" down 1
# Force version (fix dirty state)
migrate -path migrations -database "$DATABASE_URL" force VERSION
Migration Files
-- migrations/000003_add_user_avatar.up.sqlALTER TABLE users ADDCOLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url ISNOT NULL;
-- migrations/000003_add_user_avatar.down.sqlDROP INDEX IF EXISTS idx_users_avatar;
ALTER TABLE users DROPCOLUMN 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
