Zero-Downtime Database Migrations: Deploy Schema Changes Without Taking Your App Offline

Database migrations are when most production deployments go wrong. A simple column rename can take your entire application offline for minutes—or hours if something breaks.

Zero-downtime migrations aren't magic. They're disciplined, multi-step processes that keep your application running while schema changes roll out.

This guide shows you how to migrate databases in production without downtime.

The Problem with Traditional Migrations

// Traditional migration (causes downtime)
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.renameColumn('name', 'full_name');
  });
}

// Deployment sequence:
// 1. Take application offline
// 2. Run migration
// 3. Deploy new code using 'full_name'
// 4. Bring application back online
// Downtime: 5-30 minutes

Problems:

• Application breaks while migration runs
• No rollback if migration fails
• Long downtime for large tables

Zero-Downtime Migration Principles

Backwards compatibility: Old code works with new schema Forward compatibility: New code works with old schema Multi-phase deployments: Changes happen in stages Graceful rollbacks: Can revert without data loss

Pattern 1: Adding a Column

Safe to do in one deployment.

// Migration: Add new column
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.string('phone_number').nullable();
  });
}

export async function down(knex: Knex) {
  await knex.schema.table('users', table => {
    table.dropColumn('phone_number');
  });
}

// Deploy code that writes to new column
await db.users.update({
  where: { id: userId },
  data: {
    email: newEmail,
    phone_number: newPhone,  // New column
  },
});

// Old deployed instances ignore phone_number
// New deployed instances write to it
// Zero downtime!

Pattern 2: Renaming a Column

Requires 4-phase deployment.

Phase 1: Add New Column

// Migration 1: Add new column
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.string('full_name').nullable();
  });
}

// Deploy migration
// Old code continues using 'name'
// New column exists but unused

Phase 2: Dual Writes

// Code change: Write to both columns
await db.users.update({
  where: { id: userId },
  data: {
    name: newName,       // Old column
    full_name: newName,  // New column
  },
});

// Backfill existing data
await db.$executeRaw`
  UPDATE users
  SET full_name = name
  WHERE full_name IS NULL
`;

// Deploy code
// Both columns now have data

Phase 3: Read from New Column

// Code change: Read from new column
const user = await db.users.findOne({ id: userId });
const name = user.full_name;  // Use new column

// Still write to both for rollback safety
await db.users.update({
  where: { id: userId },
  data: {
    name: newName,
    full_name: newName,
  },
});

// Deploy code
// Reading from new column, writing to both

Phase 4: Drop Old Column

// Stop writing to old column
await db.users.update({
  where: { id: userId },
  data: {
    full_name: newName,  // Only write to new column
  },
});

// Deploy code

// After validation, drop old column
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.dropColumn('name');
  });
}

// Deploy migration
// Migration complete!

Pattern 3: Removing a Column

3-phase deployment.

Phase 1: Stop Writing

// Remove column from writes
await db.users.update({
  where: { id: userId },
  data: {
    email: newEmail,
    // Removed: phone_number
  },
});

// Deploy code
// Column still exists but not written to

Phase 2: Stop Reading

// Remove column from reads
const user = await db.users.findOne({ id: userId });
// Don't access user.phone_number

// Deploy code
// Column ignored completely

Phase 3: Drop Column

// Migration: Drop column
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.dropColumn('phone_number');
  });
}

// Deploy migration
// Column removed safely

Pattern 4: Changing Column Type

Requires new column approach.

// Phase 1: Add new column with new type
export async function up(knex: Knex) {
  await knex.schema.table('products', table => {
    table.decimal('price_decimal', 10, 2).nullable();
  });
}

// Phase 2: Dual writes with type conversion
await db.products.update({
  where: { id: productId },
  data: {
    price: newPrice,  // Old integer column
    price_decimal: newPrice / 100,  // New decimal column
  },
});

// Backfill
await db.$executeRaw`
  UPDATE products
  SET price_decimal = price / 100
  WHERE price_decimal IS NULL
`;

// Phase 3: Read from new column
const product = await db.products.findOne({ id });
const price = product.price_decimal;

// Phase 4: Drop old column
// (After full deployment and validation)

Pattern 5: Adding NOT NULL Constraint

Cannot be done directly on large tables.

// Phase 1: Add column as nullable
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.string('country_code').nullable();
  });
}

// Phase 2: Write default value
await db.users.update({
  where: { id: userId },
  data: {
    country_code: countryCode || 'US',  // Always provide value
  },
});

// Backfill
await db.$executeRaw`
  UPDATE users
  SET country_code = 'US'
  WHERE country_code IS NULL
`;

// Phase 3: Add constraint
export async function up(knex: Knex) {
  await knex.schema.table('users', table => {
    table.string('country_code').notNullable().alter();
  });
}

// For large tables, use NOT VALID and validate separately:
await knex.raw(`
  ALTER TABLE users
  ADD CONSTRAINT country_code_not_null
  CHECK (country_code IS NOT NULL) NOT VALID
`);

// Validate in background (doesn't lock table)
await knex.raw(`
  ALTER TABLE users
  VALIDATE CONSTRAINT country_code_not_null
`);

Testing Migration Safety

// Test dual-write compatibility
describe('User migration', () => {
  it('should handle dual writes', async () => {
    const user = await createUser({
      name: 'John Doe',
      full_name: 'John Doe',
    });

    expect(user.name).toBe('John Doe');
    expect(user.full_name).toBe('John Doe');
  });

  it('should read from new column', async () => {
    const user = await db.users.create({
      data: {
        name: 'Old Name',
        full_name: 'New Name',
      },
    });

    const retrieved = await getUser(user.id);
    expect(retrieved.name).toBe('New Name');  // Reads from full_name
  });
});

Monitoring Migrations

// Track migration progress
async function backfillColumn(
  tableName: string,
  oldColumn: string,
  newColumn: string,
  batchSize: number = 1000
) {
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const result = await db.$executeRaw`
      UPDATE ${tableName}
      SET ${newColumn} = ${oldColumn}
      WHERE ${newColumn} IS NULL
      AND id IN (
        SELECT id FROM ${tableName}
        WHERE ${newColumn} IS NULL
        LIMIT ${batchSize}
      )
    `;

    processed += result.count;

    console.log(`Backfilled ${processed} rows`);

    hasMore = result.count === batchSize;

    // Pause to avoid overwhelming database
    await new Promise(resolve => setTimeout(resolve, 100));
  }

  console.log(`Backfill complete: ${processed} total rows`);
}

Rollback Strategy

// Always test rollback procedure
// Phase 2 rollback: Stop dual writes
await db.users.update({
  where: { id: userId },
  data: {
    name: newName,  // Only write to old column
  },
});

// Phase 3 rollback: Read from old column
const user = await db.users.findOne({ id: userId });
const name = user.name;  // Read from old column

// Can roll back to any previous phase safely

Conclusion

Zero-downtime migrations require discipline:

Add before remove: Always additive first Dual writes: Write to both old and new Gradual transition: Multi-phase deployments Backfill carefully: Batch updates, avoid locks Test rollbacks: Practice reverting changes Monitor progress: Track migration status

Never rush database changes. Plan phases, test thoroughly, deploy incrementally. Your users won't notice the migration—and that's the point.