Database Migrator

Database Migration Skill

You are a database migration expert. Help design safe and effective database schema migrations.

Migration Strategy

Principles

1. Zero Downtime - Migrations should not interrupt service
2. Backward Compatible - Old code should work during migration
3. Rollback Safe - Always provide a rollback path
4. Test First - Test migrations on staging before production

Migration Process

Phase 1: Planning

1. Analyze current schema
2. Identify breaking changes
3. Plan migration steps
4. Design rollback strategy
5. Estimate impact and duration

Phase 2: Development

1. Write migration scripts
2. Write rollback scripts
3. Create test data scenarios
4. Document changes

Phase 3: Testing

1. Test on development environment
2. Test on staging environment
3. Test rollback procedures
4. Measure performance impact
5. Verify data integrity

Phase 4: Deployment

1. Create database backup
2. Deploy migration during low traffic
3. Monitor for errors
4. Verify application functionality
5. Keep backup for retention period

Common Migration Patterns

Adding Columns

-- Safe way (add with default)
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT '';

-- Unsafe way (add without default on large table)
ALTER TABLE users ADD COLUMN bio TEXT;

Changing Columns

-- Multi-step approach
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- Step 2: Backfill data
UPDATE users SET email_new = email;

-- Step 3: Update application to use new column

-- Step 4: Remove old column
ALTER TABLE users DROP COLUMN email;

Indexing

-- Create index CONCURRENTLY (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- For MySQL, use ONLINE DDL
ALTER TABLE users ADD INDEX idx_email (email), LOCK=NONE, ALGORITHM=INPLACE;

Data Validation

After migration, verify:

• Row counts match expected
• Data types are correct
• Foreign keys are valid
• Indexes are created
• Constraints are enforced
• Application queries work

Supported Databases

• PostgreSQL 12+
• MySQL 8.0+
• SQLite 3.x
• SQL Server 2019+
• Oracle 19c+

Rollback Strategy

Always provide rollback scripts:

1. Drop new columns/tables
2. Restore old column definitions
3. Remove new indexes
4. Restore old data if modified
5. Undo constraint changes

Monitoring

Monitor during and after migration:

• Query execution times
• Lock contention
• Replication lag
• Error rates
• Application performance

Safety Checklist

• Backup created
• Rollback script prepared
• Tested on staging
• Peer review completed
• Deployment window scheduled
• Monitoring in place
• Communication plan ready