---
title: "Database Migrations"
slug: "migrations"
category: "backend"
tags: ["backend", "migration", "schema", "postgres", "deployment"]
status: "stable"
last_updated: 2026-05-14
summary: "Idempotent up-only migrations, zero-downtime patterns, and the four-step playbook for renaming columns in production."
related: ["[[backend/postgres]]", "[[backend/prisma]]", "[[backend/sqlite]]", "[[coding/general-principles]]", "[[ops/hostinger-vps]]"]
---

## Overview

Migrations ship schema changes to production. Treat them as code: [[glossary/idempotent|idempotent]], append-only, version-controlled with the app, applied by CI. This page covers the contract, the zero-downtime patterns, and the framework integrations for [[backend/prisma]] and the rest of the stack.

## Write idempotent, up-only migrations

Forward scripts only. Once a migration lands in `main`, it is immutable; fixes go in a new migration.

- Use `CREATE TABLE IF NOT EXISTS`, `ADD COLUMN IF NOT EXISTS`, and `DROP INDEX IF EXISTS`. A re-run on a partially applied database must be a [[glossary/idempotent|no-op]].
- Skip `down` scripts in production. Rollbacks are a forward migration that restores the old shape, not a `down` invoked under stress.
- Name files with a sortable timestamp prefix: `20260514_120000_add_orders_idempotency_key.sql`. Application order must be deterministic across machines.
- Edit a shipped migration only to fix a syntax error nobody has run yet. If any environment has applied it, write a follow-up.

## Ship migrations with the app

Migrations live in the same repo and the same PR as the code that depends on them. A schema change without the code is dead weight; code without the schema crashes on boot.

- Run `prisma migrate deploy`, `alembic upgrade head`, or `sqlx migrate run` in CI as a pre-deploy step, before the new binary takes traffic.
- Block deploys when the database is on a newer version than the artifact. Mismatched schema and code is the first symptom of a bad rollback.

## Use the four-step rename for zero-downtime changes

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

1. Add the new column. Nullable, no default that touches every row. `ALTER TABLE users ADD COLUMN email_address TEXT;`
2. Dual-write. Application code writes both `email` and `email_address` on every update. Ship.
3. Backfill. A separate batched job copies `email` into `email_address` for old rows. Verify with a row-count check.
4. Drop the old column once readers are gone. `ALTER TABLE users DROP COLUMN email;` Ship.

The same four steps apply to changing a column type, splitting a column, or replacing a foreign-key target.

## Never wrap a huge migration in a single transaction

Postgres holds an `ACCESS EXCLUSIVE` lock for the duration of a transactional DDL. On a 100M-row table that blocks every reader and writer until the statement finishes. [[backend/postgres-partitioning|Partition huge tables]] first when you can, so DDL only touches one slice at a time.

- Use `CREATE INDEX CONCURRENTLY` for hot tables (see [[backend/postgres-indexes]]). It cannot run inside a transaction; most tools have a per-migration flag (`-- prisma-migrate-disable-tx` or Alembic's `op.execute` outside a transaction context).
- Add `NOT NULL` in two steps: add the column nullable, backfill, then `ALTER COLUMN ... SET NOT NULL` once the data is clean.
- Split heavy DDL across multiple migrations so one bad statement does not block the queue.

## Run backfills as separate jobs

A backfill that scans every row does not belong inside the migration. Migrations should finish in seconds; jobs can run for hours.

- The migration adds the column and the index. A separate worker iterates in batches of 1k-10k rows, sleeps between batches, and is resumable.
- Track progress in a `backfill_state` table. A backfill that cannot resume cannot be retried safely.
- Verify completeness with `SELECT count(*) WHERE new_column IS NULL` before the cleanup migration runs.

## Pick the right framework integration

The contract is the same; the commands differ.

- Prisma: `prisma migrate dev` locally to author, `prisma migrate deploy` in CI. See [[backend/prisma]].
- Python: Alembic. `alembic revision --autogenerate -m "..."`, then hand-edit. Autogenerate misses indexes and `CHECK` constraints.
- Rust: `sqlx-cli` with `sqlx migrate add` and `sqlx migrate run`. SQL files are the source of truth.
- SQLite: same contract, but `ALTER TABLE` is limited; use the [[backend/sqlite]] copy-and-rename pattern for type changes.

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

## Related

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