Database Migration

Plan and execute database schema changes and data migrations safely. Covers migration file generation, zero-downtime deployment strategies, rollback plans, data backfilling, and testing migrations against production-like data.

Workflow

Step 1: Understand the Change

Gather context about the migration:

• What is changing: New table, new column, column type change, index addition, data transformation.
• Why: Feature requirement, performance optimization, data model normalization.
• Current schema: Read existing migration files or schema definition. Check the ORM models/entities.
• Data volume: How many rows are in the affected tables? This determines the migration strategy.
• Downtime tolerance: Can the application go offline during migration, or must it be zero-downtime?

Detect the migration tool:

• Look for prisma/schema.prisma, knexfile.js, sequelize config, alembic.ini, db/migrate/ (Rails), Django migrations/, TypeORM entities, drizzle.config.ts.
• Check for raw SQL migration directories.

Step 2: Assess Risk

Classify the migration by risk level:

Low Risk (safe, backward compatible):

• Adding a new nullable column
• Adding a new table
• Adding an index (with CONCURRENTLY on PostgreSQL)
• Adding a new enum value

Medium Risk (requires coordination):

• Adding a NOT NULL column with a default value
• Renaming a column (requires application code change)
• Adding a unique constraint (may fail on existing data)
• Changing column type with compatible conversion

High Risk (potential downtime or data loss):

• Dropping a column or table
• Changing column type with incompatible conversion
• Large data backfill on tables with millions of rows
• Splitting or merging tables

Step 3: Design the Migration

Adding a column (safe pattern):

-- Migration: up
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL;

-- Migration: down (rollback)
ALTER TABLE users DROP COLUMN phone;

Adding a NOT NULL column (safe pattern):

-- Step 1: Add nullable column with default
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Step 2: Backfill existing rows (in batches for large tables)
UPDATE orders SET status = 'completed' WHERE completed_at IS NOT NULL;
UPDATE orders SET status = 'pending' WHERE completed_at IS NULL;

-- Step 3: Add NOT NULL constraint (after backfill is verified)
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;

Renaming a column (zero-downtime pattern):

Phase 1 (migration): Add new column, copy data
Phase 2 (deploy):    Application writes to both old and new columns
Phase 3 (migration): Backfill any remaining rows
Phase 4 (deploy):    Application reads from new column only
Phase 5 (migration): Drop old column

-- Phase 1
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
UPDATE users SET full_name = name;

-- Phase 5 (after application is updated)
ALTER TABLE users DROP COLUMN name;

Adding an index (zero-downtime):

-- PostgreSQL: use CONCURRENTLY to avoid table lock
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

-- MySQL: use ALGORITHM=INPLACE for online DDL
ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE;

Large data migration (batched):

-- Do NOT run a single UPDATE on millions of rows
-- Instead, batch in chunks:

-- Pseudocode for batched migration:
-- loop:
--   UPDATE table SET new_col = transform(old_col)
--   WHERE id IN (SELECT id FROM table WHERE new_col IS NULL LIMIT 1000);
--   COMMIT;
--   SLEEP(0.1);  -- reduce load on the database
--   IF affected_rows == 0: BREAK

Step 4: Write the Migration File

Generate the migration using the project's tool:

Prisma:

npx prisma migrate dev --name add_user_phone

Knex:

npx knex migrate:make add_user_phone

exports.up = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.string('phone', 20).nullable();
  });
};

exports.down = function(knex) {
  return knex.schema.alterTable('users', (table) => {
    table.dropColumn('phone');
  });
};

Alembic (Python/SQLAlchemy):

alembic revision --autogenerate -m "add_user_phone"

def upgrade():
    op.add_column('users', sa.Column('phone', sa.String(20), nullable=True))

def downgrade():
    op.drop_column('users', 'phone')

Django:

python manage.py makemigrations --name add_user_phone

Rails:

rails generate migration AddPhoneToUsers phone:string

Step 5: Test the Migration

Before applying to production:

1. Run on a local database with realistic data volumes.
2. Test the rollback: Run migrate down, verify the schema reverts cleanly.
3. Test with production data (anonymized): If possible, restore a production backup to a staging database and run the migration there.
4. Measure execution time: Time the migration on a dataset similar in size to production.
5. Check for locks: Monitor for table locks during the migration, especially on large tables.
6. Verify application compatibility: Deploy the application code against both the old and new schema to confirm backward compatibility during the transition.

Step 6: Deploy the Migration

Deployment checklist:

• Migration tested on staging with production-like data
• Rollback migration tested and verified
• Application code is compatible with both old and new schema
• Database backup taken before migration
• Monitoring alerts reviewed (disk space, CPU, lock wait times)
• Maintenance window scheduled (if needed for high-risk migrations)
• Rollback trigger criteria defined (what constitutes a failed migration)

Rollback strategy:

• For schema changes: Run the down migration.
• For data migrations: Restore from the pre-migration backup.
• For large migrations: Keep the old column/table until the new version is verified, then clean up in a subsequent migration.

Output Format

## Database Migration Plan

### Change Description
<what is changing and why>

### Risk Assessment
**Risk Level**: Low / Medium / High
**Downtime Required**: Yes / No
**Estimated Duration**: <time for migration on production data>
**Affected Tables**: <list with row counts>

### Migration Steps

#### Step 1: <description>
```sql
<SQL or migration code>

Rollback:

<rollback SQL>

Pre-Migration Checklist

• Backup taken
• Tested on staging
• Rollback verified
• Application code deployed (if needed first)

Post-Migration Verification

• Schema matches expected state
• Application health checks passing
• Query performance verified
• Data integrity checks passing

Rollback Plan

Trigger: <when to rollback> Procedure:

1. <step 1>
2. <step 2>

## Examples

**User**: "Add a status column to the orders table"
**Action**: Check the table size, design a safe migration with nullable column first, generate the migration file using the project's tool, include rollback, provide deployment instructions.

**User**: "Rename the 'name' column to 'full_name' in users table without downtime"
**Action**: Design a multi-phase migration (add new column, dual-write, backfill, switch reads, drop old column), generate migration files for each phase, provide application code changes needed.

**User**: "We need to split the address fields out of the users table into a separate addresses table"
**Action**: Design a data migration plan with batched data copy, foreign key setup, application code changes, and cleanup migration. Include rollback strategy.

**User**: "The migration is taking too long on production"
**Action**: Analyze the migration SQL, check for table locks, suggest batching strategies, recommend index-related optimizations, consider running during low-traffic periods.

## Best Practices

- Always write both `up` and `down` migrations. A migration without a rollback is a one-way door.
- Make migrations backward compatible when possible. The old application version should still work with the new schema during rolling deployments.
- Never modify a migration that has already been applied to production. Create a new migration to make corrections.
- Use batched updates for data migrations on large tables. A single UPDATE on 10 million rows will lock the table and potentially crash the database.
- Add indexes concurrently (PostgreSQL) or with online DDL (MySQL) to avoid blocking writes.
- Test migrations with production-scale data before deploying. A migration that runs in 2 seconds on 1,000 rows may take 2 hours on 10 million rows.
- Take a database backup before every production migration. Automate this as part of the deployment process.
- Monitor database metrics during migration: lock waits, CPU, disk I/O, replication lag.
- Keep migrations small and focused. One migration per logical change makes rollbacks surgical rather than catastrophic.
- Document the business reason for each migration in the migration file comments. Future developers need to understand why, not just what.