migration-patterns

# Migration Patterns Skill

Patterns for safe, reversible database migrations.

## Migration Principles

1. **Always reversible**: Every migration should have a down/rollback
2. **Atomic changes**: One logical change per migration
3. **Non-blocking**: Avoid long locks on production tables
4. **Tested**: Run on production-like data before deploying
5. **Versioned**: Track all migrations in source control

## Prisma Migrations

### Basic Workflow

```bash
# Create migration from schema changes
npx prisma migrate dev --name add_user_role

# Apply migrations in production
npx prisma migrate deploy

# Reset database (development only!)
npx prisma migrate reset
```

### Migration File Structure

```
prisma/
├── schema.prisma
└── migrations/
    ├── 20240101000000_init/
    │   └── migration.sql
    ├── 20240102000000_add_user_role/
    │   └── migration.sql
    └── migration_lock.toml
```

## Safe Migration Patterns

### Adding Columns

```sql
-- Safe: Add nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe: Add column with default
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'user';

-- Then backfill if needed
UPDATE users SET role = 'admin' WHERE is_admin = true;
```

### Removing Columns

```sql
-- Step 1: Stop reading the column in code
-- Step 2: Stop writing to the column
-- Step 3: Deploy code changes
-- Step 4: Drop the column

ALTER TABLE users DROP COLUMN legacy_field;
```

### Renaming Columns

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

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

-- Step 3: Update code to read/write both
-- Step 4: Deploy and verify
-- Step 5: Drop old column
ALTER TABLE users DROP COLUMN name;
```

### Adding NOT NULL Constraint

```sql
-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN email VARCHAR(255);

-- Step 2: Backfill existing rows
UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

-- Step 3: Add constraint
ALTER TABLE