database-migration

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database Migration

## Migration File Conventions

Every schema change must be captured in a versioned migration file. Follow these conventions:

- **Timestamped naming**: Use `YYYYMMDDHHMMSS_descriptive_name` (e.g., `20240315143022_add_status_to_orders.sql`).
- **Sequential ordering**: Migrations run in order. Never insert a migration before one that has already been applied.
- **One concern per file**: Each migration handles a single logical change. Do not combine unrelated schema changes.
- **Descriptive names**: The filename should describe the change, not the ticket number. Use verbs like `add`, `remove`, `create`, `drop`, `rename`, `alter`.
- **Idempotent when possible**: Use `IF NOT EXISTS` / `IF EXISTS` guards to make re-runs safe.

```
migrations/
  20240301100000_create_users_table.sql
  20240305120000_add_email_index_to_users.sql
  20240310090000_create_orders_table.sql
  20240315143022_add_status_to_orders.sql
```

## Forward and Rollback Pairs

Every migration must have a corresponding rollback. If you cannot write a safe rollback, document why and flag it for review.

```sql
-- Migration: 20240315143022_add_status_to_orders.sql

-- UP
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';
CREATE INDEX idx_orders_status ON orders (status);

-- DOWN
DROP INDEX IF EXISTS idx_orders_status;
ALTER TABLE orders DROP COLUMN IF EXISTS status;
```

### Rollback Rules

| Scenario | Rollback Strategy |
|----------|-------------------|
| Add column | Drop column |
| Add index | Drop index |
| Create table | Drop table |
| Add constraint | Drop constraint |
| Rename column | Rename back |
| Drop column | Cannot auto-rollback — must restore from backup or use prior migration to re-add |
| Data backfill | Reverse backfill or accept data state |

## Zero-Downtime Schema Changes

When your application cannot tolerate downtime during deploys, follow the expand-and-contract pattern. Never make breaking schema changes in a single step.

### Step-by-Step: Renaming a Column

Renaming a column directly (`ALTER TABLE ... RENAME COLUMN`) breaks running application instances that reference the old name. Instead:

1. **Deploy 1 — Expand**: Add the new column alongside the old one.
   ```sql
   ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
   ```

2. **Deploy 2 — Dual Write**: Update application code to write to both columns.
   ```javascript
   // Write to both columns
   await db.query(
     'UPDATE users SET name = $1, full_name = $1 WHERE id = $2',
     [name, userId]
   );
   ```

3. **Deploy 3 — Backfill**: Copy existing data from old column to new column.
   ```sql
   UPDATE users SET full_name = name WHERE full_name IS NULL;
   ```

4. **Deploy 4 — Cut Over**: Update application to read from and write to new column only.
   ```javascript
   // Read from new column
   const user = await db.query('SELECT full_name FROM users WHERE id = $1', [userId]);
   ```

5. **Deploy 5 — Contract**: Drop the old column after a safe observation period.
   ```sql
   ALTER TABLE users DROP COLUMN name;
   ```

### Step-by-Step: Adding a NOT NULL Constraint

Adding a `NOT NULL` constraint on an existing column with null values will fail or lock the table. Safe approach:

1. Add a check constraint as `NOT VALID` (PostgreSQL) to avoid scanning existing rows:
   ```sql
   ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
     CHECK (status IS NOT NULL) NOT VALID;
   ```

2. Backfill any null values:
   ```sql
   UPDATE orders SET status = 'unknown' WHERE status IS NULL;
   ```

3. Validate the constraint (acquires lighter lock):
   ```sql
   ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
   ```

4. Optionally convert to a proper `NOT NULL` column constraint:
   ```sql
   ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
   ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;
   ```

## Dangerous Operations

| Operation | Risk | Safe Alternative |
|-----------|------|-----------------|
| `RENAME COLUMN` | Breaks queries referencing old name | Expand-and-contract pattern (add new, migrate, drop old) |
| `CHANGE COLUMN TYPE` | May require full table rewrite, long lock | Add new column with new type, backfill, swap |
| `DROP COLUMN` | Irreversible data loss | Verify no code references, back up data, then drop |

From Claude-Code-Community-Ireland/claude-code-resources, MIT.

More from Claude-Code-Community-Ireland/claude-code-resources