# Database Migrations

> When to activate: Flyway, Liquibase, Alembic, migrations, schema changes, zero-downtime migration, rollback, database versioning

- Skill: `mattakushi432/database-migrations` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/database-migrations`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/database-migrations/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/database-migrations

---

# Database Migration Patterns

## Flyway (Java/SQL)

```sql
-- V1__create_users.sql
CREATE TABLE users (
  id         BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ  DEFAULT NOW()
);

-- V2__add_users_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active' NOT NULL;

-- V2.1__backfill_users_status.sql (repeatable: R__)
-- R__views.sql — re-run when checksum changes
CREATE OR REPLACE VIEW active_users AS
  SELECT * FROM users WHERE status = 'active';
```

```yaml
# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=app
flyway.password=${DB_PASS}
flyway.locations=classpath:db/migration
flyway.baselineOnMigrate=true
flyway.validateOnMigrate=true
flyway.outOfOrder=false
```

## Alembic (Python/SQLAlchemy)

```python
# alembic/env.py — autogenerate config
from app.models import Base
target_metadata = Base.metadata

# Generate migration
# alembic revision --autogenerate -m "add status to users"

# Migration file: versions/abc123_add_status_to_users.py
def upgrade() -> None:
    op.add_column('users', sa.Column('status', sa.String(50),
                  nullable=False, server_default='active'))
    op.create_index('ix_users_status', 'users', ['status'])

def downgrade() -> None:
    op.drop_index('ix_users_status', table_name='users')
    op.drop_column('users', 'status')

# Run migrations
# alembic upgrade head
# alembic downgrade -1
# alembic history --verbose
```

## Zero-Downtime Migration Patterns

```sql
-- PATTERN 1: Add nullable column (safe — no lock on Postgres 11+)
ALTER TABLE orders ADD COLUMN new_col TEXT;

-- PATTERN 2: Add NOT NULL with default (use DEFAULT in pg 11+, avoids rewrite)
ALTER TABLE orders ADD COLUMN priority INT NOT NULL DEFAULT 0;

-- PATTERN 3: Rename column safely (expand-contract)
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Deploy code that writes both columns
-- Step 3: Backfill
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Step 4: Add NOT NULL constraint (low-overhead if backfilled)
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
-- Step 5: Deploy code using only full_name
-- Step 6: Drop old column
ALTER TABLE users DROP COLUMN name;

-- PATTERN 4: Add index without locking
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- PATTERN 5: Add foreign key without full-table lock
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;  -- skip historical rows
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_users;  -- validates in background
```

## Large Table Migration

```sql
-- Batch backfill to avoid lock contention
DO $$
DECLARE
  batch_size INT := 10000;
  last_id    BIGINT := 0;
  max_id     BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM orders;
  WHILE last_id <= max_id LOOP
    UPDATE orders
    SET status = 'legacy'
    WHERE id > last_id AND id <= last_id + batch_size
      AND status IS NULL;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.1);  -- breathing room
  END LOOP;
END $$;
```

## Migration Checklist

- [ ] Migration is idempotent (can re-run safely)
- [ ] Has `downgrade()` / rollback path
- [ ] Tested on production-size data snapshot
- [ ] `CREATE INDEX CONCURRENTLY` for new indexes on large tables
- [ ] No `ALTER TABLE ... ADD COLUMN NOT NULL` without `DEFAULT` on Postgres < 11
- [ ] Long-running backfills done in batches with sleep intervals
- [ ] Monitoring in place during deployment (row counts, lock waits, replication lag)