8# Database Migration Patterns
9
10Safe, reversible database schema changes for production systems.
11
12## When to Activate
13
14- Creating or altering database tables
15- Adding/removing columns or indexes
16- Running data migrations (backfill, transform)
17- Planning zero-downtime schema changes
18- Setting up migration tooling for a new project
19
20## Core Principles
21
221. **Every change is a migration** — never alter production databases manually
232. **Migrations are forward-only in production** — rollbacks use new forward migrations
243. **Schema and data migrations are separate** — never mix DDL and DML in one migration
254. **Test migrations against production-sized data** — a migration that works on 100 rows may lock on 10M
265. **Migrations are immutable once deployed** — never edit a migration that has run in production
27
28## Migration Safety Checklist
29
30Before applying any migration:
31
32- [ ] Migration has both UP and DOWN (or is explicitly marked irreversible)
33- [ ] No full table locks on large tables (use concurrent operations)
34- [ ] New columns have defaults or are nullable (never add NOT NULL without default)
35- [ ] Indexes created concurrently (not inline with CREATE TABLE for existing tables)
36- [ ] Data backfill is a separate migration from schema change
37- [ ] Tested against a copy of production data
38- [ ] Rollback plan documented
39
40## PostgreSQL Patterns
41
42### Adding a Column Safely
43
44```sql
45-- GOOD: Nullable column, no lock
46ALTER TABLE users ADD COLUMN avatar_url TEXT;
47
48-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
49ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
50
51-- BAD: NOT NULL without default on existing table (requires full rewrite)
52ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
53-- This locks the table and rewrites every row
54```
55
56### Adding an Index Without Downtime
57
58```sql
59-- BAD: Blocks writes on large tables
60CREATE INDEX idx_users_email ON users (email);
61
62-- GOOD: Non-blocking, allows concurrent writes
63CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
64
65-- Note: CONCURRENTLY cannot run inside a transaction block
66-- Most migration tools need special handling for this
67```
68
69### Renaming a Column (Zero-Downtime)
70
71Never rename directly in production. Use the expand-contract pattern:
72
73```sql
74-- Step 1: Add new column (migration 001)
75ALTER TABLE users ADD COLUMN display_name TEXT;
76
77-- Step 2: Backfill data (migration 002, data migration)
78UPDATE users SET display_name = username WHERE display_name IS NULL;
79
80-- Step 3: Update application code to read/write both columns
81-- Deploy application changes
82
83-- Step 4: Stop writing to old column, drop it (migration 003)
84ALTER TABLE users DROP COLUMN username;
85```
86
87### Removing a Column Safely
88
89```sql
90-- Step 1: Remove all application references to the column
91-- Step 2: Deploy application without the column reference
92-- Step 3: Drop column in next migration
93ALTER TABLE orders DROP COLUMN legacy_status;
94
95-- For Django: use SeparateDatabaseAndState to remove from model
96-- without generating DROP COLUMN (then drop in next migration)
97```
98
99### Large Data Migrations
100
101```sql
102-- BAD: Updates all rows in one transaction (locks table)
103UPDATE users SET normalized_email = LOWER(email);
104
105-- GOOD: Batch update with progress
106DO $$
107DECLARE
108 batch_size INT := 10000;
109 rows_updated INT;
110BEGIN
111 LOOP
112 UPDATE users
113 SET normalized_email = LOWER(email)
114 WHERE id IN (
115 SELECT id FROM users
116 WHERE normalized_email IS NULL
117 LIMIT batch_size
118 FOR UPDATE SKIP LOCKED
119 );
120 GET DIAGNOSTICS rows_updated = ROW_COUNT;
121 RAISE NOTICE 'Updated % rows', rows_updated;
122 EXIT WHEN rows_updated = 0;
123 COMMIT;
124 END LOOP;
125END $$;
126```
127
128## Prisma (TypeScript/Node.js)
129
130### Workflow
131
132```bash
133# Create migration from schema changes
134npx prisma migrate dev --name add_user_avatar
135
136# Apply pending migrations in production
137npx prisma migrate deploy
138
139# Reset database (dev only)
140npx prisma migrate reset
141
142# Generate client after schema changes
143npx prisma generate
144```
145
146### Schema Example
147
148```prisma
149model User {
150 id String @id @default(cuid())
151 email String @unique
152 name String?
153 avatarUrl String? @map("avatar_url")
154 createdAt DateTime @default(now()) @map("created_at")
155 updatedAt DateTime @updatedAt @map("updated_at")
156 orders Order[]
157
158 @@map("users")
159 @@index([email])
160}
161```
162
163### Custom SQL Migration
164
165For operations Prisma cannot express (concurrent indexes, data backfills):
166
167```bash
168# Create empty migration, then edit the SQL manually
169npx prisma migrate dev --create-only --name add_email_index
170```
171
172```sql
173-- migrations/20240115_add_email_index/migration.sql
174-- Prisma cannot generate CONCURRENTLY, so we write it manually
175CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);
176```
177
178## Drizzle (TypeScript/Node.js)
179
180### Workflow
181
182```bash
183# Generate migration from schema changes
184npx drizzle-kit generate
185
186# Apply migrations
187npx drizzle-kit migrate
188
189# Push schema directly (dev only, no migration file)
190npx drizzle-kit push
191```
192
193### Schema Example
194
195```typescript
196import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";
197
198export const users = pgTable("users", {
199 id: uuid("id").primaryKey().defaultRandom(),
200 email: text("email").notNull().unique(),
201 name: text("name"),
202 isActive: boolean("is_active").notNull().default(true),
203 createdAt: timestamp("created_at").notNull().defaultNow(),
204 updatedAt: timestamp("updated_at").notNull().defaultNow(),
205});
206```
207
208## Kysely (TypeScript/Node.js)
209
210### Workflow (kysely-ctl)
211
212```bash
213# Initialize config file (kysely.config.ts)
214kysely init
215
216# Create a new migration file
217kysely migrate make add_user_avatar
218
219# Apply all pending migrations
220kysely migrate latest
221
222# Rollback last migration
223kysely migrate down
224
225# Show migration status
226kysely migrate list
227```
228
229### Migration File
230
231```typescript
232// migrations/2024_01_15_001_create_user_profile.ts
233import { type Kysely, sql } from 'kysely'
234
235// IMPORTANT: Always use Kysely<any>, not your typed DB interface.
236// Migrations are frozen in time and must not depend on current schema types.
237export async function up(db: Kysely<any>): Promise<void> {
238 await db.schema
239 .createTable('user_profile')
240 .addColumn('id', 'serial', (col) => col.primaryKey())
241 .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
242 .addColumn('avatar_url', 'text')
243 .addColumn('created_at', 'timestamp', (col) =>
244 col.defaultTo(sqlnow()).notNull()
245 )
246 .execute()
247
248 await db.schema
249 .createIndex('idx_user_profile_avatar')
250 .on('user_profile')
251 .column('avatar_url')
252 .execute()
253}
254
255export async function down(db: Kysely<any>): Promise<void> {
256 await db.schema.dropTable('user_profile').execute()
257}
258```
259
260### Programmatic Migrator
261
262```typescript
263import { Migrator, FileMigrationProvider } from 'kysely'
264import { promises as fs } from 'fs'
265import * as path from 'path'
266// ESM only — CJS can use __dirname directly
267import { fileURLToPath } from 'url'
268const migrationFolder = path.join(
269 path.dirname(fileURLToPath(import.meta.url)),
270 './migrations',
271)
272
273// db is your Kysely<any> database instance
274const migrator = new Migrator({
275 db,
276 provider: new FileMigrationProvider({
277 fs,
278 path,
279 migrationFolder,
280 }),
281 // WARNING: Only enable in development. Disables timestamp-ordering
282 // validation, which can cause schema drift between environments.
283 // allowUnorderedMigrations: true,
284})
285
286const { error, results } = await migrator.migrateToLatest()
287
288results?.forEach((it) => {
289 if (it.status === 'Success') {
290 console.log(migration "${it.migrationName}" executed successfully)
291 } else if (it.status === 'Error') {
292 console.error(failed to execute migration "${it.migrationName}")
293 }
294})
295
296if (error) {
297 console.error('migration failed', error)
298 process.exit(1)
299}
300```
301
302## Django (Python)
303
304### Workflow
305
306```bash
307# Generate migration from model changes
308python manage.py makemigrations
309
310# Apply migrations
311python manage.py migrate
312
313# Show migration status
314python manage.py showmigrations
315
316# Generate empty migration for custom SQL
317python manage.py makemigrations --empty app_name -n description
318```
319
320### Data Migration
321
322```python
323from django.db import migrations
324
325def backfill_display_names(apps, schema_editor):
326 User = apps.get_model("accounts", "User")
327 batch_size = 5000
328 users = User.objects.filter(display_name="")
329 while users.exists():
330 batch = list(users[:batch_size])
331 for user in batch:
332 user.display_name = user.username
333 User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)
334
335def reverse_backfill(apps, schema_editor):
336 pass # Data migration, no reverse needed
337
338class Migration(migrations.Migration):
339 dependencies = [("accounts", "0015_add_display_name")]
340
341 operations = [
342 migrations.RunPython(backfill_display_names, reverse_backfill),
343 ]
344```
345
346### SeparateDatabaseAndState
347
348Remove a column from the Django model without dropping it from the database immediately:
349
350```python
351class Migration(migrations.Migration):
352 operations = [
353 migrations.SeparateDatabaseAndState(
354 state_operations=[
355 migrations.RemoveField(model_name="user", name="legacy_field"),
356 ],
357 database_operations=[], # Don't touch the DB yet
358 ),
359 ]
360```
361
362## golang-migrate (Go)
363
364### Workflow
365
366```bash
367# Create migration pair
368migrate create -ext sql -dir migrations -seq add_user_avatar
369
370# Apply all pending migrations
371migrate -path migrations -database "$DATABASE_URL" up
372
373# Rollback last migration
374migrate -path migrations -database "$DATABASE_URL" down 1
375
376# Force version (fix dirty state)
377migrate -path migrations -database "$DATABASE_URL" force VERSION
378```
379
380### Migration Files
381
382```sql
383-- migrations/000003_add_user_avatar.up.sql
384ALTER TABLE users ADD COLUMN avatar_url TEXT;
385CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;
386
387-- migrations/000003_add_user_avatar.down.sql
388DROP INDEX IF EXISTS idx_users_avatar;
389ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;
390```
391
392## Zero-Downtime Migration Strategy
393
394For critical production changes, follow the expand-contract pattern:
395
396```
397Phase 1: EXPAND
398 - Add new column/table (nullable or with default)
399 - Deploy: app writes to BOTH old and new
400 - Backfill existing data
401
402Phase 2: MIGRATE
403 - Deploy: app reads from NEW, writes to BOTH
404 - Verify data consistency
405
406Phase 3: CONTRACT
407 - Deploy: app only uses NEW
408 - Drop old column/table in separate migration
409```
410
411### Timeline Example
412
413```
414Day 1: Migration adds new_status column (nullable)
415Day 1: Deploy app v2 — writes to both status and new_status
416Day 2: Run backfill migration for existing rows
417Day 3: Deploy app v3 — reads from new_status only
418Day 7: Migration drops old status column
419```
420
421## Anti-Patterns
422
423| Anti-Pattern | Why It Fails | Better Approach |
424|-------------|-------------|-----------------|
425| Manual SQL in production | No audit trail, unrepeatable | Always use migration files |
426| Editing deployed migrations | Causes drift between environments | Create new migration instead |
427| NOT NULL without default | Locks table, rewrites all rows | Add nullable, backfill, then add constraint |
428| Inline index on large table | Blocks writes during build | CREATE INDEX CONCURRENTLY |
429| Schema + data in one migration | Hard to rollback, long transactions | Separate migrations |
430| Dropping column before removing code | Application errors on missing column | Remove code first, drop column next deploy |
431