LLM Best Practices

Database Migrations

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Treat migrations as code: forward-only, idempotent where practical, versioned with the app, applied by CI before the new binary takes traffic. On live postgres tables, use lock_timeout and CONCURRENTLY, and rename columns with the four-step expand and contract. The Prisma workflow is in prisma-migrations.

Write forward-only migrations and never edit a shipped one

  • Once a migration lands in main, it is immutable; fixes go in a new migration. Edit a shipped file only to fix a syntax error nobody has run.
  • Use IF NOT EXISTS and IF EXISTS (CREATE TABLE, ADD COLUMN, DROP INDEX) so a re-run on a half-applied database is a no-op.
  • Skip down scripts in production. A rollback is a new forward migration that restores the old shape, written calmly, not invoked under stress.
  • Name files with a sortable prefix so order is deterministic everywhere: 20260514_120000_add_orders_idempotency_key.sql.

Ship migrations with the app

Keep the migration in the same repo and PR as the code that needs it. Run prisma migrate deploy, alembic upgrade head, or sqlx migrate run as a pre-deploy CI step, and block deploys when the database is on a newer schema version than the artifact.

Use the four-step rename for zero-downtime changes

A rename or in-place type change locks the table and breaks old app instances mid-deploy. Split it across deploys.

  1. Add the new column, nullable, with no default that touches every row.
  2. Dual-write: the application writes both columns. Deploy.
  3. Backfill old rows in a separate batched job, then verify with a row count.
  4. Move readers to the new column, then drop the old column in a later deploy.

The same steps cover type changes, column splits, and replacing a foreign-key target.

Protect live tables from lock queues

Many ALTER TABLE forms take an ACCESS EXCLUSIVE lock and hold it until the transaction commits; rewrites such as a type change hold it for the whole rewrite. Even a quick ALTER can queue behind a long query, and every later query then queues behind it.

  • Start migrations with SET lock_timeout = '5s'; so a blocked migration fails and retries instead of freezing traffic.
  • Build indexes with CREATE INDEX CONCURRENTLY (postgres-indexes). It cannot run in a transaction block, so give it its own migration. In Alembic use op.get_context().autocommit_block(). Prisma has no documented way to disable the migration transaction; see prisma-migrations.
  • Add NOT NULL safely: add a CHECK (col IS NOT NULL) NOT VALID constraint, run VALIDATE CONSTRAINT (which does not block writes), then SET NOT NULL, which skips the table scan once a valid check exists. Postgres 18 can also add NOT NULL constraints as NOT VALID.
  • Partition very large tables so DDL touches one slice at a time.

Run backfills as separate jobs

A migration adds the column and index and finishes in seconds. A worker backfills in batches of 1,000 to 10,000 rows, sleeps between batches, and is resumable, with progress in a backfill_state table. Verify with SELECT count(*) WHERE new_column IS NULL before the cleanup migration.

Use the framework’s commands

  • Prisma: prisma migrate dev to author, prisma migrate deploy in CI.
  • Python: Alembic. alembic revision --autogenerate -m "...", then edit by hand; autogenerate misses CHECK constraints and sees a rename as a drop plus an add.
  • Rust: sqlx migrate add and sqlx migrate run, with SQL files as the source of truth.
  • SQLite: the same contract, but column type changes need a table rebuild (create new table, copy, drop, rename); see sqlite.

See general-principles for the broader rule on small, reversible deploys.

Backlinks