| name |
database-migrations |
| description |
Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Django, TypeORM, golang-migrate). Use when planning or implementing database schema changes.
|
| metadata |
|
Database Migration Patterns
Safe, reversible database schema changes for production systems.
- 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
- 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
-- GOOD: Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;
-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
-- BAD: NOT NULL without default on existing table (requires full rewrite)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row
Adding an Index Without Downtime
-- BAD: Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);
-- GOOD: Non-blocking, allows concurrent writes
CREATE 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 ADD COLUMN display_name TEXT;
-- Step 2: Backfill data (migration 002, data migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;
-- 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 DROP COLUMN username;
-- Step 1: Remove all application references to the column
-- Step 2: Deploy application without the column reference
-- Step 3: Drop column in next migration
ALTER TABLE orders DROP COLUMN legacy_status;
-- For Django: use SeparateDatabaseAndState to remove from model
-- without generating DROP COLUMN (then drop in next migration)
-- 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 IS NULL
LIMIT batch_size
FOR UPDATE SKIP 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)
# 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
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])
}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 manually
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);
Drizzle (TypeScript/Node.js)
# Generate migration from schema changes
npx drizzle-kit generate
# Apply migrations
npx drizzle-kit migrate
# Push schema directly (dev only, no migration file)
npx drizzle-kit push
import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
id: uuid("id").primaryKey().defaultRandom(),
email: text("email").notNull().unique(),
name: text("name"),
isActive: boolean("is_active").notNull().default(true),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow(),
});# 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
from django.db import migrations
def backfill_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)
def reverse_backfill(apps, schema_editor):
pass # Data migration, no reverse needed
class Migration(migrations.Migration):
dependencies = [("accounts", "0015_add_display_name")]
operations = [
migrations.RunPython(backfill_display_names, reverse_backfill),
]Remove a column from the Django model without dropping it from the database immediately:
class Migration(migrations.Migration):
operations = [
migrations.SeparateDatabaseAndState(
state_operations=[
migrations.RemoveField(model_name="user", name="legacy_field"),
],
database_operations=[], # Don't touch the DB yet
),
]# 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
-- migrations/000003_add_user_avatar.up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;
-- migrations/000003_add_user_avatar.down.sql
DROP INDEX IF EXISTS idx_users_avatar;
ALTER 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
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-Pattern |
Why It Fails |
Better Approach |
| Manual SQL in production |
No audit trail, unrepeatable |
Always use migration files |
| Editing deployed migrations |
Causes drift between environments |
Create new migration instead |
| NOT NULL without default |
Locks table, rewrites all rows |
Add nullable, backfill, then add constraint |
| Inline index on large table |
Blocks writes during build |
CREATE INDEX CONCURRENTLY |
| Schema + data in one migration |
Hard to rollback, long transactions |
Separate migrations |
| Dropping column before removing code |
Application errors on missing column |
Remove code first, drop column next deploy |
- Planning database schema changes
- Implementing zero-downtime migrations
- Setting up migration tooling
- Troubleshooting migration issues
- Reviewing migration pull requests
Комментарии
Комментариев пока нет. Будьте первым.