Safe Database Migrations

Zero-downtime migration patterns, rollback strategies, and data backfills

What You’ll Build

After following this guide, you will have a working implementation of safe database migrations in your project. Learn migration patterns that won’t take your production database offline. Covers additive-only changes, the expand-contract pattern for breaking schema changes, safe column renames, backfilling data without locking tables, and rollback strategies.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• PostgreSQL database
• Migration tool (Prisma, Knex, or raw SQL)
• Understanding of database locking

Step-by-Step Implementation

Safe: Add a nullable column (no lock)

The following snippet shows how to safe: add a nullable column (no lock). Copy this into your project and adjust the values for your environment.

-- ✅ SAFE: Adding a nullable column is instant, no table lock
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- ✅ SAFE: Add with a default (PostgreSQL 11+ is instant)
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

Expand-Contract pattern for renames

Never rename a column directly in production. Use the expand-contract pattern: add new column, migrate data, update code, drop old column.

-- Step 1: EXPAND — Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: BACKFILL — Copy data (in batches for large tables)
UPDATE users SET full_name = name WHERE full_name IS NULL LIMIT 10000;
-- Repeat in batches until all rows are updated

-- Step 3: CONTRACT (after all code uses full_name)
ALTER TABLE users DROP COLUMN name;

Batch backfill script

The following snippet shows how to batch backfill script. Copy this into your project and adjust the values for your environment.

async function batchBackfill(batchSize = 5000) {
  let updated = 0;
  while (true) {
    const result = await db.query(
      'UPDATE users SET full_name = name WHERE full_name IS NULL LIMIT $1',
      [batchSize]
    );
    updated += result.rowCount;
    console.log(\`Updated \${updated} rows...\`);
    if (result.rowCount < batchSize) break;
    await new Promise(r => setTimeout(r, 100)); // brief pause to reduce load
  }
  console.log('Backfill complete!');
}

⚠️ Don’t Do This

-- DANGEROUS on large tables: locks entire table until complete!
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
-- This rewrites the ENTIRE table. 10M+ rows = minutes of downtime.

-- Step 1: Add constraint without validating (instant, no lock)
ALTER TABLE orders ADD CONSTRAINT status_not_null
  CHECK (status IS NOT NULL) NOT VALID;

-- Step 2: Validate in background (doesn't lock writes)
ALTER TABLE orders VALIDATE CONSTRAINT status_not_null;

Testing

Verify your implementation with these tests:

// __tests__/safe-database-migrations.test.ts
import { describe, it, expect } from 'vitest';

describe('Safe Database Migrations', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

# Always test migrations on a copy of production data first:
pg_dump prod_db | psql test_db
# Run your migration on test_db
# Verify data integrity:
SELECT COUNT(*) FROM users WHERE full_name IS NULL;
# Should be 0 after backfill