Database migrations are risky. A bad migration can cause downtime or data loss. Here's how to migrate safely with zero downtime.
Migration Principles#
Safe Migrations:
- Backward compatible
- Reversible
- Tested in staging
- Monitored during rollout
Dangerous Operations:
- Dropping columns/tables
- Renaming columns
- Changing column types
- Adding NOT NULL constraints
Adding Columns Safely#
1-- ✅ Safe: Add nullable column
2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
3
4-- ❌ Unsafe: Add NOT NULL without default
5ALTER TABLE users ADD COLUMN phone VARCHAR(20) NOT NULL;
6
7-- ✅ Safe: Add NOT NULL with default
8ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';
9
10-- For existing rows, backfill in batches
11UPDATE users SET phone = ''
12WHERE phone IS NULL
13LIMIT 1000;Removing Columns (Expand/Contract Pattern)#
1// Step 1: Stop writing to column (deploy code change)
2// Code no longer references the column
3
4// Step 2: Remove column from ORM/model
5// Wait for all instances to update
6
7// Step 3: Drop column in migration
8// migration: 20240301_remove_legacy_field.sql
9await prisma.$executeRaw`ALTER TABLE users DROP COLUMN legacy_field`;
10
11// Full expand/contract timeline:
12// Day 1: Deploy code that ignores column
13// Day 2: Verify no errors, no column usage
14// Day 3: Run migration to drop columnRenaming Columns#
1// Step 1: Add new column
2// migration_001_add_new_column.sql
3await db.execute(`ALTER TABLE users ADD COLUMN full_name VARCHAR(200)`);
4
5// Step 2: Backfill data
6// migration_002_backfill_data.ts
7async function backfill() {
8 const batchSize = 1000;
9 let offset = 0;
10
11 while (true) {
12 const users = await db.query(
13 `SELECT id, name FROM users WHERE full_name IS NULL LIMIT $1 OFFSET $2`,
14 [batchSize, offset]
15 );
16
17 if (users.length === 0) break;
18
19 for (const user of users) {
20 await db.execute(
21 `UPDATE users SET full_name = $1 WHERE id = $2`,
22 [user.name, user.id]
23 );
24 }
25
26 offset += batchSize;
27 await sleep(100); // Prevent database overload
28 }
29}
30
31// Step 3: Deploy code using new column
32// Update application to read/write full_name
33
34// Step 4: Drop old column (after verification)
35// migration_003_drop_old_column.sql
36await db.execute(`ALTER TABLE users DROP COLUMN name`);Changing Column Types#
1-- ❌ Dangerous: Direct type change (locks table)
2ALTER TABLE orders ALTER COLUMN total TYPE DECIMAL(12,2);
3
4-- ✅ Safe: Add new column, migrate, swap
5-- Step 1: Add new column
6ALTER TABLE orders ADD COLUMN total_new DECIMAL(12,2);
7
8-- Step 2: Create trigger for sync (PostgreSQL)
9CREATE OR REPLACE FUNCTION sync_total() RETURNS TRIGGER AS $$
10BEGIN
11 NEW.total_new := NEW.total::DECIMAL(12,2);
12 RETURN NEW;
13END;
14$$ LANGUAGE plpgsql;
15
16CREATE TRIGGER orders_sync_total
17 BEFORE INSERT OR UPDATE ON orders
18 FOR EACH ROW EXECUTE FUNCTION sync_total();
19
20-- Step 3: Backfill existing data
21UPDATE orders SET total_new = total::DECIMAL(12,2)
22WHERE total_new IS NULL;
23
24-- Step 4: Update application to use total_new
25
26-- Step 5: Drop old column and rename
27DROP TRIGGER orders_sync_total ON orders;
28ALTER TABLE orders DROP COLUMN total;
29ALTER TABLE orders RENAME COLUMN total_new TO total;Adding Indexes Without Locking#
1-- ❌ Blocks writes
2CREATE INDEX idx_orders_user_id ON orders(user_id);
3
4-- ✅ Non-blocking (PostgreSQL)
5CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
6
7-- ✅ Non-blocking (MySQL 5.6+)
8ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;Large Table Migrations#
1// Migrate large tables in batches
2async function migrateLargeTable(): Promise<void> {
3 const BATCH_SIZE = 5000;
4 const SLEEP_MS = 100;
5
6 let processed = 0;
7 let lastId = '';
8
9 while (true) {
10 const result = await db.execute(`
11 UPDATE orders
12 SET migrated = true, new_field = compute_value(old_field)
13 WHERE id > $1 AND migrated = false
14 ORDER BY id
15 LIMIT $2
16 RETURNING id
17 `, [lastId, BATCH_SIZE]);
18
19 if (result.rows.length === 0) break;
20
21 lastId = result.rows[result.rows.length - 1].id;
22 processed += result.rows.length;
23
24 console.log(`Migrated ${processed} rows`);
25 await sleep(SLEEP_MS);
26 }
27
28 console.log(`Migration complete: ${processed} rows`);
29}
30
31// Monitor progress
32async function checkMigrationProgress(): Promise<number> {
33 const result = await db.query(`
34 SELECT
35 COUNT(*) FILTER (WHERE migrated = true) as migrated,
36 COUNT(*) as total
37 FROM orders
38 `);
39
40 const { migrated, total } = result.rows[0];
41 return (migrated / total) * 100;
42}Prisma Migration Workflow#
1// prisma/schema.prisma
2model User {
3 id String @id @default(cuid())
4 email String @unique
5 name String
6 // Add new optional field first
7 phone String? // Step 1: Optional
8 createdAt DateTime @default(now())
9}
10
11// Generate migration
12// npx prisma migrate dev --name add_phone_field
13
14// After backfill, make it required
15model User {
16 id String @id @default(cuid())
17 email String @unique
18 name String
19 phone String @default("") // Step 2: Required with default
20 createdAt DateTime @default(now())
21}Rollback Strategy#
1// Always plan for rollback
2interface Migration {
3 name: string;
4 up: () => Promise<void>;
5 down: () => Promise<void>;
6}
7
8const migration: Migration = {
9 name: '20240301_add_status_column',
10
11 async up() {
12 await db.execute(`ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending'`);
13 },
14
15 async down() {
16 await db.execute(`ALTER TABLE orders DROP COLUMN status`);
17 },
18};
19
20// Migration runner with rollback
21async function runMigration(migration: Migration): Promise<void> {
22 try {
23 console.log(`Running migration: ${migration.name}`);
24 await migration.up();
25 await recordMigration(migration.name);
26 console.log(`Migration complete: ${migration.name}`);
27 } catch (error) {
28 console.error(`Migration failed: ${migration.name}`, error);
29 console.log('Rolling back...');
30 await migration.down();
31 throw error;
32 }
33}Testing Migrations#
1// Test migrations against production-like data
2describe('Migration: add_status_column', () => {
3 beforeEach(async () => {
4 // Seed with production-like data
5 await seedTestData();
6 });
7
8 it('adds status column with correct default', async () => {
9 await migration.up();
10
11 const orders = await db.query('SELECT status FROM orders LIMIT 1');
12 expect(orders[0].status).toBe('pending');
13 });
14
15 it('can be rolled back', async () => {
16 await migration.up();
17 await migration.down();
18
19 const columns = await getTableColumns('orders');
20 expect(columns).not.toContain('status');
21 });
22
23 it('handles large datasets efficiently', async () => {
24 // Create 100k rows
25 await createManyOrders(100000);
26
27 const start = Date.now();
28 await migration.up();
29 const duration = Date.now() - start;
30
31 expect(duration).toBeLessThan(30000); // Under 30 seconds
32 });
33});Best Practices#
Planning:
✓ Review migrations before applying
✓ Test in staging first
✓ Have rollback plan ready
✓ Schedule during low-traffic periods
Execution:
✓ Use non-blocking operations
✓ Migrate data in batches
✓ Monitor database load
✓ Keep migrations small
Safety:
✓ Never drop columns immediately
✓ Use expand/contract pattern
✓ Backfill before constraints
✓ Verify data integrity
Conclusion#
Safe database migrations require planning and patience. Use the expand/contract pattern, migrate data in batches, and always have a rollback plan. Test migrations thoroughly before production and monitor during execution.