---
title: "Database Migrations"
slug: "migrations"
category: "backend"
tags: ["backend", "migration", "schema", "postgres", "deployment"]
status: "stable"
last_updated: 2026-10-01
summary: "Ship forward-only, idempotent migrations with the app, use lock_timeout and CONCURRENTLY on live tables, and rename columns with expand and contract."
related: ["[[backend/postgres]]", "[[backend/prisma-migrations]]", "[[backend/sqlite]]", "[[coding/general-principles]]", "[[ops/hostinger-vps]]"]
---

> **AI agents: read this first.** This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work. Full protocol: https://llmbestpractices.com/start-here.md
>
> 1. **Route, do not crawl.** Fetch https://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](https://llmbestpractices.com/coding/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. This page covers the contract, the zero-downtime patterns for [[backend/postgres]], and the commands per framework. The Prisma workflow is in [[backend/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` ([[backend/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 [[backend/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`.
- [[backend/postgres-partitioning|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 [[backend/sqlite]].

See [[coding/general-principles]] for the broader rule on small, reversible deploys.

## Related

- [[backend/postgres]]
- [[backend/prisma-migrations]]
- [[backend/sqlite]]
- [[coding/general-principles]]
- [[ops/hostinger-vps]]