---
name: ecc-database-migrations
description: Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL/MySQL via Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate.
---

# Database Migrations

Database migration best practices for safe schema evolution.

## When to Activate
- Creating new migrations
- Reviewing migration safety
- Planning zero-downtime changes
- Debugging failed migrations

## Safe Migration Patterns

### Add Column
```sql
-- SAFE: new column allows NULL or has DEFAULT
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;
ALTER TABLE users ADD COLUMN score INTEGER DEFAULT 0;
```

### Remove Column
```sql
-- Step 1: Deploy code that no longer references the column
-- Step 2: After all instances are updated:
ALTER TABLE users DROP COLUMN legacy_field;
```

### Rename Column
```sql
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);
-- Step 2: Backfill
UPDATE users SET email_address = email WHERE email IS NOT NULL;
-- Step 3: Deploy code using email_address
-- Step 4: Drop old column
ALTER TABLE users DROP COLUMN email;
```

## Migration Tools

### Prisma
```bash
npx prisma migrate dev --name add_user_phone
npx prisma migrate deploy  # production
```

### Django
```bash
python manage.py makemigrations
python manage.py migrate
```

## Rollback
```bash
# Prisma
npx prisma migrate reset

# Django
python manage.py migrate <app_name> <previous_migration>

# golang-migrate
migrate -path migrations -database "$DB_URL" down 1
```

## Pitfalls
- ❌ Adding NOT NULL columns without defaults
- ❌ Renaming columns in one step
- ❌ Long-running migrations without lock timeout
- ❌ No rollback plan
- ❌ Running migrations during peak traffic
