Skip to content

skillDatabases

Database Migration Safety

Plans schema changes that run without downtime: expand and contract steps, safe backfills, lock checks and a tested rollback for each migration.

npx workspacetemplates add database-migration-safety --tool claude-code

Writes 1 file to .claude/skills/database-migration-safety/

.claude/skills/database-migration-safety/SKILL.md30 lines · 1.8 KBFile
---
name: database-migration-safety
description: "Plans schema changes that run without downtime: expand and contract steps, safe backfills, lock checks and a tested rollback for each migration."
---

# Database migration safety

Assume the old and the new application code run at the same time during the deploy.

## Steps

1. Read the migration and find the table size and traffic. Name the lock each statement takes (`ACCESS EXCLUSIVE` blocks all reads and writes).
2. Split risky changes using expand and contract:
   - Expand: add the new column as nullable with no default, or add the new table. Deploy code that writes both old and new.
   - Backfill in batches.
   - Switch reads to the new column, deploy, observe.
   - Contract: drop the old column in a later release, never the same one.
3. Safe forms (PostgreSQL): `CREATE INDEX CONCURRENTLY` outside a transaction; add a constraint as `NOT VALID` then `VALIDATE CONSTRAINT`; add a foreign key the same way; set `NOT NULL` only after a validated check constraint exists. Set `lock_timeout` (for example 3s) and retry, so a waiting migration cannot block traffic.
4. Backfills: update by primary key ranges of 1,000 to 10,000 rows, commit each batch, sleep briefly between batches, make it resumable and idempotent. Never one giant UPDATE.
5. Renames and type changes are breaking: add a new column and copy, do not rename in place.
6. Write the down migration or a documented reverse plan, and say what data cannot be recovered (dropped columns).
7. Run it on a restored copy of production-size data, record the duration and locks, then run the down path.

## Rules

- Never run a migration against production yourself.

## Report

Verdict (safe, safe with changes, unsafe), each risky statement with its lock and fix, the staged plan, the rollback.

What it does

A migration that works on your laptop can lock a production table for minutes. This skill reviews or writes migrations with that in mind, splitting risky changes into steps that old and new application versions can both survive.

It treats the rollback as part of the change and asks for it up front, so a bad deploy becomes a quick revert and not an incident.

Example

you › Review migrations/0042_rename_status.sql

agent › Unsafe: ALTER TABLE orders RENAME COLUMN status TO state takes an ACCESS EXCLUSIVE lock and breaks the running app version. Plan: 1) add state, write both; 2) backfill 2.4M rows in batches of 5,000 (about 9 minutes); 3) read from state; 4) drop status next release. Rollback: stop writing state and read status again; no data is lost until step 4.

Questions

Does it run the migration?
Only on a local or test database you name. It never touches production; it hands you the plan and the commands.
Why drop the old column in a separate release?
If the new code has a bug you need the old column to roll back. Dropping it in the same deploy removes your way out.
  • Database Connection Pooling

    SkillDatabases

    Free
  • Database Schema Design

    SkillDatabases

    Free
  • MongoDB Schema Modeling

    Skillmongodb

    Free
  • MySQL Query Tuning

    Skillmysql

    Free