Safe DB migration

Plans and writes zero-downtime schema migrations: expand/contract, batched backfills, concurrent indexes, online DDL, safe defaults, lock timeouts and rollback. Use when the user adds or changes columns, indexes or constraints on a database used in production.

Databases & SQLPack: Databases96 linessafe-db-migration

Install

Run one command — it saves SKILL.md into your agent's skills folder. Restart the agent and the skill is picked up automatically.

Claude Code — macOS, Linux
mkdir -p ~/.claude/skills/safe-db-migration && curl -fsSL https://anymodel.org/en/skills/databases/safe-db-migration/SKILL.md -o ~/.claude/skills/safe-db-migration/SKILL.md
Codex, Cursor — macOS, Linux
mkdir -p ~/.agents/skills/safe-db-migration && curl -fsSL https://anymodel.org/en/skills/databases/safe-db-migration/SKILL.md -o ~/.agents/skills/safe-db-migration/SKILL.md
Claude Code — Windows PowerShell
New-Item -ItemType Directory -Force "$HOME\.claude\skills\safe-db-migration" | Out-Null; Invoke-WebRequest https://anymodel.org/en/skills/databases/safe-db-migration/SKILL.md -UseBasicParsing -OutFile "$HOME\.claude\skills\safe-db-migration\SKILL.md"
Codex, Cursor — Windows PowerShell
New-Item -ItemType Directory -Force "$HOME\.agents\skills\safe-db-migration" | Out-Null; Invoke-WebRequest https://anymodel.org/en/skills/databases/safe-db-migration/SKILL.md -UseBasicParsing -OutFile "$HOME\.agents\skills\safe-db-migration\SKILL.md"

Any other agent: save the file into its skills folder, or give it the link to the file: https://anymodel.org/en/skills/databases/safe-db-migration/SKILL.md

SKILL.md

Raw

Safe DB Migration

Design schema changes that do not block traffic and can be rolled back. Identify the engine and version (Postgres, MySQL/MariaDB), the migration tool (Alembic, Django, Rails, Prisma, Flyway, Knex ...), the table sizes and whether old and new app versions run at the same time. Large, hot tables are where the danger is.

Hard rules

  • Never run a migration against production yourself. Write it, test it on a copy, show the plan, and wait for the user's explicit go-ahead.
  • Never drop or rename a column, table or index that the deployed code may still use. Never TRUNCATE/DROP without a confirmed backup and user approval.
  • Take or confirm a backup (or snapshot) before irreversible steps, and name the restore path.

Step 1: Understand the risk

Collect: row count and size of each affected table, write rate, current indexes, foreign keys, and deploy process (is there a window where old code runs on the new schema?). Ask the user when unknown; do not guess sizes.

Step 2: Choose the pattern

Expand / contract (default for anything non-trivial)

  1. Expand: add the new column/table/index, nullable and unused. Old code keeps working.
  2. Deploy code that writes to both old and new (dual write) and reads the old.
  3. Backfill existing rows in batches.
  4. Deploy code that reads the new; verify.
  5. Contract in a later release: drop the old column/table after no code uses it. Each step is a separate migration and a separate deploy.

Renames

Do not RENAME COLUMN on a live system with rolling deploys. Add the new column, dual write, backfill, switch reads, drop the old one.

Step 3: Statement-level rules

Postgres

  • Always set timeouts so a blocked DDL cannot queue behind a long transaction and block everyone:
SET lock_timeout = '3s';
SET statement_timeout = '60s';
  • For CREATE INDEX CONCURRENTLY, VALIDATE CONSTRAINT and backfills set statement_timeout = 0 (or above the measured duration); keep lock_timeout.
  • Index creation: CREATE INDEX CONCURRENTLY ... (cannot run inside a transaction block; configure the tool to disable the wrapping transaction). Check afterwards for invalid indexes and drop failed ones: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;.
  • New constraints: add as NOT VALID, then VALIDATE CONSTRAINT separately. For NOT NULL on a big table, first add CHECK (col IS NOT NULL) NOT VALID, validate it, then set NOT NULL (Postgres 12+ skips the table scan when a validated CHECK proves no NULLs; drop the CHECK afterwards. On 18+ you can add NOT NULL col NOT VALID directly).
  • Foreign keys: ADD CONSTRAINT ... NOT VALID, then validate.
  • Adding a column with a constant default is fast on Postgres 11+; volatile defaults rewrite the table. Check the version.
  • Changing a column type usually rewrites the table; use the expand/contract pattern.

MySQL / MariaDB

  • Prefer ALGORITHM=INPLACE, LOCK=NONE (or INSTANT where supported) and state it explicitly so the statement fails instead of silently locking.
  • For operations that need a copy, use an online schema change tool (gh-ost, pt-online-schema-change) and tell the user it needs setup and monitoring.
  • Watch replication lag during big changes.

Defaults and NOT NULL

  • Add the column nullable, backfill, then add NOT NULL and the default as a separate step.
  • Do not combine a new NOT NULL column without default and a deploy where old code does not set it; inserts will fail.

Step 4: Backfills

  • Run outside the schema migration, as a resumable script or job.
  • Batch by primary key range, a few thousand rows at a time, commit per batch, sleep between batches, and make it idempotent:
UPDATE orders SET status_new = status
WHERE id BETWEEN $1 AND $2 AND status_new IS NULL;
  • Monitor lock waits, replication lag, CPU and disk; provide a way to pause and resume.
  • Verify counts at the end: SELECT count(*) FROM orders WHERE status_new IS DISTINCT FROM status; should be 0.

Step 5: Test

  • Run the migration on a copy of production-size data, not on a tiny dev database. Measure duration and the longest lock held (pg_stat_activity, pg_locks, SHOW PROCESSLIST).
  • Test the old app version against the new schema and the new version against the old schema.
  • Test the down/rollback path, or document that there is none.

Step 6: Rollback plan

For each step write: what to run to undo it, whether data is lost, and the point after which rollback is no longer possible (usually the contract step). Rolling back code first is usually enough for expand-only steps.

Output format

  1. Ordered list of migrations and deploys (expand, dual write, backfill, switch, contract), each with the SQL or code and a risk note.
  2. Estimated duration and lock behavior, marked as measured or assumed.
  3. Test evidence (what was run, on what data size).
  4. Rollback plan per step.
  5. Questions and approvals needed from the user before anything runs.

Pitfalls

  • A "quick" ALTER TABLE on a large table can take an exclusive lock and stall all queries.
  • ORMs auto-generate blocking DDL; read the generated SQL (sqlmigrate, --sql, prisma migrate diff).
  • Long-running transactions elsewhere make lock_timeout fail repeatedly; find them rather than raising the timeout.
  • Do not squash expand and contract into one release to save time.
  • Data fixes belong in separate, reviewed scripts, not hidden inside schema migrations.

Source: AnyModel skills catalog · License: CC BY 4.0

Connect through AnyModel API

This skill runs on whatever model your agent uses. Point Claude Code, Codex or Cursor at AnyModel to run it on Claude, GPT, Gemini and more with one key — pay per token, no subscription.

More from the pack Databases