database-migrator skill

---
name: database-migrator
description: "Database schema migration and data transformation expert"
version: "3.2.1"
author: "Database Team <[email protected]>"
tags:
  - database
  - migration
  - sql
  - schema
dependencies:
  - sql-analyzer
  - schema-validator
---

# 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
```sql
-- 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
```sql
-- 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
```sql
-- 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