Org Skills
BackendBackend (general)Development

database-migrations

Plan and write relational schema changes that deploy without downtime or data loss in any back-end stack — expand/contract sequencing, lock-safe DDL per engine, backfills over large tables, online index builds, migration tooling (EF Core, Prisma, Drizzle, Flyway, Liquibase, Alembic), and what rollback actually means once data has moved.

Download .zip Raw Source
When agents use itUse this whenever the user writes or changes a migration, adds or drops a column, renames anything, adds a NOT NULL or a constraint to an existing table, adds an index to a large table, backfills or rewrites data, sees a migration lock up production or time out, plans the deploy order of schema and code, or asks whether a schema change is safe to ship. This skill owns migration safety; the stack skills own the ORM idioms.

Install

Copilot (VS Code, Visual Studio, Copilot CLI and github.com) reads skills from the repository — commit them so the whole team gets them.

npx skills add AGCO-Global/org-skills --skill database-migrations -a github-copilot
# or with the org installer (adds .github/skills/database-migrations):
npx -y github:AGCO-Global/org-skills add skill database-migrations

Uses the open skills CLI. Works with Claude Code, Codex, Cursor, Copilot, Gemini CLI, OpenCode, Windsurf and 60+ others — it asks which agent to install into.

npx skills add AGCO-Global/org-skills --skill database-migrations
# user-level instead of project-level:
npx skills add AGCO-Global/org-skills --skill database-migrations -g

Installs the backend-shared-skills plugin, which bundles all Backend / Shared skills and keeps them updated.

/plugin marketplace add AGCO-Global/org-skills
/plugin install backend-shared-skills@org-skills
# or just this skill, in this repository:
npx skills add AGCO-Global/org-skills --skill database-migrations -a claude-code

Use Download .zip above, then upload it under Customize → Skills → Upload skill. Team and Enterprise admins can sync this repo as a plugin marketplace instead.

Installs into .agents/skills/, which Codex reads.

npx skills add AGCO-Global/org-skills --skill database-migrations -a codex

Installs into Cursor's skills folder.

npx skills add AGCO-Global/org-skills --skill database-migrations -a cursor

Installs into Gemini CLI's skills folder.

npx skills add AGCO-Global/org-skills --skill database-migrations -a gemini-cli

Any tool with rules, instructions or custom prompts: use Copy SKILL.md above and paste it in. It is plain Markdown.

Commands use your normal git sign-in to GitHub, so they work while the repository is private. Node.js 20+ required.

Try it — example prompts

Prompts this skill is tested against, and what a good answer includes.

  • I need to rename users.name to full_name. Write the migration.

    refuses a single in-place rename expand/contract sequence: add nullable column, dual-write, backfill, switch reads, stop writing old, drop later each phase is a separate deploy old and new code run simultaneously during a rolling deploy the drop happens in a later release

  • Adding an index to our 80-million-row orders table locked up production last time. What should we do?

    CREATE INDEX CONCURRENTLY on PostgreSQL / ONLINE = ON on SQL Server CONCURRENTLY cannot run inside a transaction, so the migration tool must not wrap it check for an INVALID index afterwards and drop/rebuild if found set lock_timeout so the DDL fails fast instead of queueing every query

  • Our migration adds a NOT NULL column with a default to a large table. Is that safe?

    a constant default is metadata-only in current PostgreSQL; a volatile or computed default rewrites the table NOT NULL with no default requires add-nullable, backfill, then constrain add the constraint NOT VALID then VALIDATE CONSTRAINT separately test against production-like volume

  • We need to backfill a new column for 200 million rows. How?

    batched by primary key range with a commit per batch resumable: record progress so a restart does not start over idempotent, e.g. WHERE full_name IS NULL throttled, watching replication lag run outside the migration, not inside its transaction

  • Should our service run EF Core migrations automatically at startup?

    no — multiple instances race and a failed migration takes the app down apply as one explicit pipeline step before the new version starts dotnet ef migrations bundle for the deployment artifact EnsureCreated is not migrations

  • Write a down-migration so we can roll back this column drop.

    a down-migration cannot restore dropped data plan a forward fix instead a tested restore path and stated recovery point objective for destructive steps delay the drop until the old code is gone everywhere

Skill contents

Database Migrations

Targets: PostgreSQL, SQL Server, MySQL · EF Core 10, Prisma 7, Drizzle, Flyway, Liquibase, Alembic · Verified: 2026-09 against https://www.postgresql.org/docs/current/sql-altertable.html and https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/

During a rolling deploy, old code and new code run against the same database at the same time. That single fact drives every rule here: a schema change is safe only if the version of the application that is still running can survive it. The database is also the one part of the system with no undo — code rolls back, deleted columns do not. Sequence every change so that each step is compatible with the code on both sides of it, and treat a migration that takes a lock on a large table as a production incident waiting for enough rows.

1. Decide first

Question Default Change when
Schema and code in one deploy No — expand, deploy, migrate, contract across separate deploys Only for a brand-new table nothing reads yet
Renaming a column or table Never in place: add new, dual-write, backfill, switch reads, drop later A table created in the same release and never deployed
Adding a column Nullable, or with a default the engine applies without a rewrite Adding NOT NULL with no default requires the expand/contract dance
Adding an index to a big table CREATE INDEX CONCURRENTLY (Postgres) / ONLINE = ON (SQL Server, edition permitting) Small table, or a maintenance window you actually have
Backfill strategy Batched, throttled, resumable, outside the migration transaction A single UPDATE only when the table is genuinely small
Destructive step (drop column/table) A later release, after the old code is gone everywhere Never in the same deploy as the code that stopped using it
Migration runner The stack's tool, applied as an explicit step before the app starts Automatic migrate-on-startup is unsafe with multiple instances
Rollback plan Forward fix, plus a tested restore path for data loss A down-migration only where it is genuinely lossless

2. Expand / contract

The only sequence that survives a rolling deploy. Renaming users.name to full_name:

  1. Expand — add full_name, nullable. Deploy. Old code ignores it.
  2. Dual-write — deploy code that writes both columns and reads name.
  3. Backfill — copy name into full_name in batches for existing rows.
  4. Switch reads — deploy code that reads full_name and still writes both.
  5. Stop writing the old column — deploy.
  6. Contract — drop name, in a later release, once every instance runs the new code.

Steps 2–5 are separate deploys. Collapsing them is what turns a rename into an outage: between the migration and the last instance restarting, the old code is selecting a column that no longer exists.

The same shape covers type changes, splitting a column and moving data between tables. Per-change recipes: references/change-recipes.md.

3. Lock-safe DDL

What a statement locks, and for how long, decides whether it is safe:

  • Postgres — ADD COLUMN with a non-volatile default is metadata-only in current versions; ALTER TYPE, adding NOT NULL and most constraint additions rewrite or scan the table. Build indexes with CREATE INDEX CONCURRENTLY (outside a transaction, and check for an INVALID index afterwards). Add constraints as NOT VALID, then VALIDATE CONSTRAINT separately.
  • SQL Server — ONLINE = ON for index operations where the edition supports it; ALTER COLUMN is generally a size-of-data operation; WITH (ONLINE = ON, RESUMABLE = ON) helps on long index builds.
  • MySQL/InnoDB — most ALTER TABLE supports ALGORITHM=INPLACE, LOCK=NONE, but not all; state the algorithm explicitly so it fails fast rather than silently copying the table.

Always set a lock timeout (and a statement timeout) in the migration session. Without one, a DDL statement blocked behind a long-running query queues every subsequent query on that table — the classic "one migration took the whole site down". With one, it fails and you retry.

Engine-by-engine detail and the safe form of each operation: references/lock-safe-ddl.md.

4. Backfills

  • Batch by primary key range, commit per batch, and make it resumable — record progress so a restart does not start over.
  • Throttle: sleep between batches, and watch replication lag rather than only CPU. A backfill that outruns replication breaks read replicas.
  • Run it outside the migration. A migration that holds a transaction open for an hour blocks DDL, bloats WAL and cannot be interrupted safely.
  • Make it idempotent (WHERE full_name IS NULL), so re-running is free.
  • For very large tables, a backfill is a job with monitoring, not a script someone runs in a terminal.

5. Tooling

Tool Apply step Watch out for
EF Core dotnet ef migrations bundle → run the bundle in the pipeline EnsureCreated is not migrations; auto-migrate on startup races across instances
Prisma prisma migrate deploy in the pipeline migrate dev is for development only; it can reset the database
Drizzle drizzle-kit generate then apply the SQL Generated SQL is a starting point — read it before shipping
Flyway / Liquibase Versioned scripts applied by the pipeline Never edit an applied migration; add a new one
Alembic alembic upgrade head Autogenerate misses data changes and some constraint edits

Whatever the tool: migrations are applied as an explicit deployment step, by one runner, before the new code starts — not by every application instance on boot.

6. Rollback

Rolling back code is easy; rolling back a schema is mostly a fiction. Once a column is dropped or data is transformed, the previous state exists only in a backup.

  • Plan forward fixes. Keep each step small enough that rolling forward is quick.
  • Write a down-migration only where it is genuinely lossless (dropping a column you just added).
  • Before any destructive step, confirm the restore path: a tested backup, and a stated recovery point objective.
  • The contract step is the dangerous one, and it is also the one under least time pressure — leave it until you are sure.

Deliverable format

## Change — what is changing and why
## Sequence — numbered steps, each marked as migration or deploy, in order
## DDL — the statement for each step, with lock and timeout settings
## Backfill — batching, throttling, resumability, and how progress is observed
## Compatibility — what the currently-running code does at each step
## Rollback — forward fix per step; restore path for anything destructive
## Verification — what to check after each step, including on a replica

Checklist

  • Old and new code can both run against the schema at every step.
  • Nothing is renamed or dropped in the same deploy as the code change.
  • Every destructive step is in a later release than the code that stopped using it.
  • Index creation on large tables is concurrent/online.
  • Constraints are added unvalidated, then validated separately, where the engine allows it.
  • A lock timeout and statement timeout are set in the migration session.
  • Backfills are batched, throttled, resumable and idempotent, and run outside the migration.
  • Replication lag is watched during the backfill.
  • Migrations are applied by one explicit step, not by application startup.
  • The rollback story is written down, and the restore path for destructive steps is tested.
  • The migration was run against a realistic data volume, not an empty dev database.

Anti-patterns

Anti-pattern Why it hurts Fix
Rename column + change code in one deploy Old instances query a column that no longer exists Expand/contract across separate deploys
ADD COLUMN NOT NULL with no default on a big table Table rewrite under an exclusive lock Add nullable, backfill, then set NOT NULL (validate separately)
CREATE INDEX without CONCURRENTLY on a hot table Blocks writes for the duration of the build CONCURRENTLY / ONLINE = ON, then verify the index is valid
Backfill as one UPDATE inside the migration Long transaction, lock escalation, no way to stop it safely Batched, resumable job outside the migration
Migrate on application startup Several instances race; a failed migration takes the app down with it One explicit pipeline step before the new version starts
No lock timeout One long query turns a fast DDL into a site-wide stall lock_timeout/SET LOCK_TIMEOUT in the migration session
Editing an already-applied migration Environments diverge silently; checksums break Add a new migration
Trusting a down-migration for a destructive change The data is already gone Forward fix, plus a tested restore path
Testing only on an empty dev database Everything is fast on zero rows Rehearse on production-like volume

Go deeper

  • references/change-recipes.md — step-by-step sequences for rename, type change, NOT NULL, split/merge column, moving data between tables, and adding a foreign key.
  • references/lock-safe-ddl.md — per-engine lock behaviour, the safe form of each operation, timeout settings and how to verify an online index build.
  • Sibling skills: messaging-patterns when a change must be published as an event; backend-code-review for reviewing someone else's migration; service-operability for the monitoring a long backfill needs.

References

Deeper material the agent loads only when needed.