Managing Database Migrations

Purpose

Implement safe, reversible database migrations for production environments:

• Apply expand-contract pattern for zero-downtime changes

• Design rollback strategies for every migration

• Handle large table alterations safely

• Integrate migrations with CI/CD pipelines

• Test migrations before production deployment

Quick Start

Prisma

npx prisma migrate dev --name add_user_roles npx prisma migrate deploy # Production

TypeORM

npm run typeorm migration:generate -- -n AddUserRoles npm run typeorm migration:run

Raw SQL with naming convention

migrations/20241230_001_add_user_roles.sql

// Prisma migration example // prisma/migrations/20241230_add_user_roles/migration.sql ALTER TABLE "users" ADD COLUMN "role" VARCHAR(50) DEFAULT 'user'; CREATE INDEX "idx_users_role" ON "users"("role");

Features

Feature Strategy When to Use

Add Column Add nullable, then backfill, then NOT NULL Always safe

Remove Column Stop using, deploy, then remove Expand-contract

Rename Column Add new, copy data, remove old Zero-downtime required

Change Type Add new column, migrate, swap Data transformation

Add Index CREATE CONCURRENTLY Large tables (>1M rows)

Drop Table Rename first, drop after verification Reversible delete

Common Patterns

Expand-Contract Pattern

-- EXPAND: Add new structure (backward compatible) -- Migration 1: Add new column ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Application: Write to BOTH columns UPDATE users SET full_name = first_name || ' ' || last_name;

-- Deploy application that reads from new column

-- CONTRACT: Remove old structure -- Migration 2: (after app deployed) ALTER TABLE users DROP COLUMN first_name; ALTER TABLE users DROP COLUMN last_name;

Safe Column Addition

-- Step 1: Add nullable column ALTER TABLE orders ADD COLUMN shipping_method VARCHAR(50);

-- Step 2: Backfill in batches (avoid locking) UPDATE orders SET shipping_method = 'standard' WHERE id IN (SELECT id FROM orders WHERE shipping_method IS NULL LIMIT 10000);

-- Step 3: Add NOT NULL constraint ALTER TABLE orders ALTER COLUMN shipping_method SET NOT NULL; ALTER TABLE orders ALTER COLUMN shipping_method SET DEFAULT 'standard';

Safe Column Removal

-- Step 1: Stop application from using column -- (Deploy code that no longer reads/writes to column)

-- Step 2: Drop default and constraints ALTER TABLE users ALTER COLUMN legacy_field DROP DEFAULT; ALTER TABLE users ALTER COLUMN legacy_field DROP NOT NULL;

-- Step 3: Remove column (after verification period) ALTER TABLE users DROP COLUMN legacy_field;

Safe Index Creation (Large Tables)

-- WRONG: Locks table for duration CREATE INDEX idx_orders_status ON orders(status);

-- CORRECT: Non-blocking for PostgreSQL CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- Note: CONCURRENTLY cannot run in transaction -- For Prisma, use raw SQL migration: -- prisma/migrations/xxx/migration.sql -- /* Disable transaction for this migration */ CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

Prisma Migration Workflow

Development: Create and apply migration

npx prisma migrate dev --name add_user_roles

Preview SQL without applying

npx prisma migrate diff
--from-schema-datamodel prisma/schema.prisma
--to-schema-datamodel prisma/schema.prisma.new
--script

Production deployment

npx prisma migrate deploy

Reset for development (DESTRUCTIVE)

npx prisma migrate reset

// schema.prisma - Example with relations model User { id String @id @default(uuid()) email String @unique role Role @default(USER) posts Post[] createdAt DateTime @default(now()) updatedAt DateTime @updatedAt

@@index([email]) }

enum Role { USER ADMIN }

TypeORM Migration

// migrations/1703936400000-AddUserRoles.ts import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddUserRoles1703936400000 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise<void> { await queryRunner.query( ALTER TABLE "users" ADD COLUMN "role" VARCHAR(50) DEFAULT 'user' ); await queryRunner.query( CREATE INDEX "idx_users_role" ON "users"("role") ); }

public async down(queryRunner: QueryRunner): Promise<void> { await queryRunner.query(DROP INDEX "idx_users_role"); await queryRunner.query(ALTER TABLE "users" DROP COLUMN "role"); } }

Data Migration Pattern

// Separate data migration from schema migration export class MigrateUserNames1703936500000 implements MigrationInterface { public async up(queryRunner: QueryRunner): Promise<void> { // Batch process to avoid memory issues const batchSize = 1000; let offset = 0;

while (true) {
  const result = await queryRunner.query(`
    UPDATE users
    SET full_name = first_name || ' ' || last_name
    WHERE full_name IS NULL
    LIMIT ${batchSize}
  `);

  if (result.affectedRows === 0) break;
  offset += batchSize;

  // Optional: Add delay to reduce load
  await new Promise(r => setTimeout(r, 100));
}

}

public async down(queryRunner: QueryRunner): Promise<void> { // Data migrations often aren't reversible console.log('Data migration rollback: manual intervention required'); } }

Use Cases

• Adding new features requiring schema changes

• Refactoring database structure safely

• Splitting or merging tables

• Changing column data types

• Large-scale data migrations

Best Practices

Do Avoid

Test migrations on production-like data Testing only on empty databases

Use expand-contract for breaking changes Direct column renames in production

Create CONCURRENTLY for large table indexes Blocking index creation on live tables

Batch large data updates Updating millions of rows in one transaction

Include rollback in every migration Forward-only migrations without escape

Run migrations in CI before deploy Manual migration execution

Version control all migrations Modifying applied migrations

Separate schema and data migrations Mixing DDL and large DML in one migration

Migration Checklist

Pre-Migration: [ ] Migration tested on staging with production data volume [ ] Rollback script written and tested [ ] Estimated execution time documented [ ] Backup verified

During Migration: [ ] Monitor database locks and connections [ ] Check application error rates [ ] Verify migration progress

Post-Migration: [ ] Verify data integrity [ ] Check application functionality [ ] Monitor performance metrics [ ] Document completion

CI/CD Integration

GitHub Actions example

• name: Run Migrations run: |

  Wait for healthy database

  until pg_isready -h $DB_HOST; do sleep 1; done

  Run migrations with timeout

  timeout 600 npx prisma migrate deploy

  Verify migration status

  npx prisma migrate status env: DATABASE_URL: ${{ secrets.DATABASE_URL }}

Related Skills

See also these related skill documents:

• designing-database-schemas - Schema design principles

• managing-databases - DBA operations and maintenance

• optimizing-databases - Performance tuning