migrations

What this skill does

# Database Migrations Core Knowledge

> **Deep Knowledge**: Use `mcp__documentation__fetch_docs` with technology: `migrations` for comprehensive documentation.

## Migration Fundamentals

### What is a Migration?
A migration is a version-controlled change to your database schema or data.

### Migration Types
| Type | Description | Example |
|------|-------------|---------|
| Schema | DDL changes | Add column, create index |
| Data | DML changes | Backfill data, transform values |
| Combined | Both schema and data | Add column with default, populate |

## Version Naming Conventions

### Timestamp-based (Recommended)
```
V20240115103000__create_users_table.sql
V20240115104500__add_email_index.sql
V20240116090000__add_status_column.sql
```

### Sequential
```
V001__create_users_table.sql
V002__add_email_index.sql
V003__add_status_column.sql
```

### Semantic
```
V1.0.0__initial_schema.sql
V1.1.0__add_orders_table.sql
V1.1.1__fix_orders_constraint.sql
```

## Migration File Structure

### Flyway Format
```sql
-- V20240115103000__create_users_table.sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
```

### With Rollback (Flyway Pro/Enterprise)
```sql
-- V20240115103000__create_users_table.sql
CREATE TABLE users (...);

-- U20240115103000__create_users_table.sql (undo)
DROP TABLE IF EXISTS users;
```

### Liquibase Format
```xml
<!-- changelog.xml -->
<databaseChangeLog>
    <changeSet id="1" author="dev">
        <createTable tableName="users">
            <column name="id" type="int" autoIncrement="true">
                <constraints primaryKey="true"/>
            </column>
            <column name="email" type="varchar(255)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>
        <rollback>
            <dropTable tableName="users"/>
        </rollback>
    </changeSet>
</databaseChangeLog>
```

## Migration Strategies

### Expand-Contract Pattern

For backward-compatible changes:

```
Phase 1: EXPAND
├── Add new column (nullable or with default)
├── Add new table
├── Deploy new code that writes to both old and new
└── Backfill existing data

Phase 2: CONTRACT
├── Remove old column usage from code
├── Make new column non-nullable if needed
├── Drop old column
└── Deploy final code
```

Example - Renaming a column:
```sql
-- Phase 1: Expand
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);
UPDATE users SET full_name = name;
-- Deploy code that reads from both, writes to both

-- Phase 2: Contract (after verification)
ALTER TABLE users DROP COLUMN name;
```

### Blue-Green Deployment

```
┌─────────────┐     ┌─────────────┐
│   Blue      │     │   Green     │
│  (Current)  │     │   (New)     │
└──────┬──────┘     └──────┬──────┘
       │                   │
       └───────┬───────────┘
               │
        ┌──────┴──────┐
        │  Database   │
        │  (Shared)   │
        └─────────────┘

1. Green environment runs migrations
2. Test Green with new schema
3. Switch traffic Blue → Green
4. Blue becomes standby
```

### Rolling Updates

```
1. Apply backward-compatible migration
2. Update servers one by one
3. Old code continues working
4. After all servers updated, remove old code paths
5. Apply cleanup migration
```

## Zero-Downtime Patterns

### Add Column (Safe)

```sql
-- Safe: Column added as nullable
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe: Column added with default
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- PostgreSQL 11+: Fast default
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();
```

### Add Non-Nullable Column

```sql
-- Step 1: Add nullable
ALTER TABLE users ADD COLUMN email_verified BOOLEAN;

-- Step 2: Backfill
UPDATE users SET email_verified = FALSE WHERE email_verified IS NULL;

-- Step 3: Add constraint
ALTER TABLE users ALTER COLUMN email_verified SET NOT NULL;
```

### Rename Column

```sql
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

-- Step 2: Copy data
UPDATE users SET full_name = name;

-- Step 3: Add trigger for sync (during transition)
CREATE TRIGGER sync_name BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_name_columns();

-- Step 4: Update application to use new column

-- Step 5: Remove old column
ALTER TABLE users DROP COLUMN name;
DROP TRIGGER sync_name ON users;
```

### Add Index (Non-Blocking)

```sql
-- PostgreSQL: CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- MySQL: ALGORITHM=INPLACE, LOCK=NONE
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;

-- SQL Server: ONLINE
CREATE INDEX idx_users_email ON users(email) WITH (ONLINE = ON);
```

### Drop Column (Safe)

```sql
-- Step 1: Stop writing to column (application change)
-- Step 2: Deploy application
-- Step 3: Drop column
ALTER TABLE users DROP COLUMN deprecated_field;
```

### Rename Table

```sql
-- Step 1: Create view with old name pointing to new table
ALTER TABLE orders RENAME TO order_records;
CREATE VIEW orders AS SELECT * FROM order_records;

-- Step 2: Update application to use new name
-- Step 3: Drop view
DROP VIEW orders;
```

## Data Migration Patterns

### Batch Processing

```sql
-- Process in batches to avoid locking
DO $$
DECLARE
    batch_size INT := 1000;
    affected INT := 1;
BEGIN
    WHILE affected > 0 LOOP
        UPDATE users
        SET status = 'active'
        WHERE id IN (
            SELECT id FROM users
            WHERE status IS NULL
            LIMIT batch_size
            FOR UPDATE SKIP LOCKED
        );
        GET DIAGNOSTICS affected = ROW_COUNT;
        COMMIT;
        PERFORM pg_sleep(0.1);  -- Small delay
    END LOOP;
END $$;
```

### Background Job Migration

```python
# Instead of SQL, use application code
def migrate_user_status():
    batch_size = 1000
    offset = 0

    while True:
        users = User.query.filter(User.status == None) \
                         .limit(batch_size).all()
        if not users:
            break

        for user in users:
            user.status = calculate_status(user)

        db.session.commit()
        time.sleep(0.1)  # Rate limiting
```

### ETL Pattern

```sql
-- 1. Create new table with desired structure
CREATE TABLE users_new (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    full_name VARCHAR(200) NOT NULL,  -- Combined from first_name, last_name
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Copy and transform data
INSERT INTO users_new (id, email, full_name, created_at)
SELECT id, email, first_name || ' ' || last_name, created_at
FROM users;

-- 3. Swap tables
ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_new RENAME TO users;

-- 4. Update sequences
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- 5. Drop old table (after verification)
DROP TABLE users_old;
```

## Rollback Strategies

### Immediate Rollback Script

```sql
-- migration.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- rollback.sql
ALTER TABLE users DROP COLUMN phone;
```

### Point-in-Time Recovery

```bash
# PostgreSQL
pg_restore --target-time="2024-01-15 10:00:00" -d mydb backup.dump

# MySQL
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" binlog.000001 | mysql
```

### Forward-Fix (Preferred)

Instead of rollback, deploy a fix:
```sql
-- Original migration had bug
-- V2: Create new migration to fix
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'pending';  -- Fix the default
```

## Migration Testing

### Pre-deployment Checklist

```markdown
- [ ] Migration tested on copy of production data
- [ ] Rollback script tested
- [ ] Application compatible with both old and new schema
- [ ] Index creation time estimated
- [ ] Lock duration estimated
- [ ] Disk space requirements checked
- [ ] Backup taken before migration
```

### Test Environment S