database-migrations

Database Migration Patterns

Safe, reversible database schema changes for production systems.

When to Use

Creating or modifying database tables
Adding/removing columns or indexes
Performing data migrations (data backfill, transform)
Planning zero-downtime schema changes
Setting up a migration toolchain for a new project

Core Principles

All changes are migrations — Manually modifying production databases is strictly prohibited
Production environments only migrate forward — Rollbacks require new forward migrations
Separate schema migrations from data migrations — Mixing DDL and DML in a single migration is strictly prohibited
Test against production-scale data — A migration that works fine on 100 rows may lock a table with 10 million rows
Migrations are immutable after deployment — Editing migration files that have already run in production is strictly prohibited

Migration Safety Checklist

Before executing any migration:

The migration includes UP and DOWN scripts (or is explicitly marked as irreversible)
No full-table locks on large tables (use concurrent operations)
New columns have default values or are nullable (adding NOT NULL without a default is strictly prohibited)
Indexes are created concurrently (for existing tables, not in the same statement as CREATE TABLE)
Data backfills and schema changes are split into separate migrations
Tested against a copy of production data
Rollback plan documented

PostgreSQL Patterns

Safely adding columns

-- 推荐：可为空的列，无锁
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- 推荐：带默认值的列（Postgres 11+ 是瞬时的，无需重写表）
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- 糟糕：在现有表上添加不带默认值的 NOT NULL（需要全表重写）
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- 这会锁定表并重写每一行

Adding indexes without downtime

-- 糟糕：在大表上会阻塞写入
CREATE INDEX idx_users_email ON users (email);

-- 推荐：非阻塞，允许并发写入
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- 注意：CONCURRENTLY 不能在事务块内运行
-- 大多数迁移工具需要对此进行特殊处理

Renaming columns (zero downtime)

Direct renaming in production is strictly prohibited. Use the "expand-contract" pattern:

-- 第 1 步：添加新列（迁移文件 001）
ALTER TABLE users ADD COLUMN display_name TEXT;

-- 第 2 步：回填数据（迁移文件 002，数据迁移）
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- 第 3 步：更新应用代码以同时读/写这两个列
-- 部署应用变更

-- 第 4 步：停止写入旧列，将其删除（迁移文件 003）
ALTER TABLE users DROP COLUMN username;

Safely dropping columns

-- 第 1 步：移除应用中所有对该列的引用
-- 第 2 步：部署不包含该列引用的应用
-- 第 3 步：在下一次迁移中删除列
ALTER TABLE orders DROP COLUMN legacy_status;

-- 对于 Django：使用 SeparateDatabaseAndState 从模型中移除
-- 而不生成 DROP COLUMN 指令（然后在下一次迁移中删除）

Large-scale data migrations

-- 糟糕：在单个事务中更新所有行（会导致锁表）
UPDATE users SET normalized_email = LOWER(email);

-- 推荐：带进度的分批更新
DO $$
DECLARE
  batch_size INT := 10000;
  rows_updated INT;
BEGIN
  LOOP
    UPDATE users
    SET normalized_email = LOWER(email)
    WHERE id IN (
      SELECT id FROM users
      WHERE normalized_email IS NULL
      LIMIT batch_size
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_updated = ROW_COUNT;
    RAISE NOTICE 'Updated % rows', rows_updated;
    EXIT WHEN rows_updated = 0;
    COMMIT;
  END LOOP;
END $$;

Prisma (TypeScript/Node.js)

Workflow

# 根据模式变更创建迁移
npx prisma migrate dev --name add_user_avatar

# 在生产环境执行待处理的迁移
npx prisma migrate deploy

# 重置数据库（仅限开发环境）
npx prisma migrate reset

# 模式变更后生成客户端
npx prisma generate

Schema Example

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  avatarUrl String?  @map("avatar_url")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  orders    Order[]

  @@map("users")
  @@index([email])
}

Custom SQL migrations

For operations that Prisma cannot express (concurrent indexes, data backfills, etc.):

# 仅创建空白迁移，然后手动编辑 SQL
npx prisma migrate dev --create-only --name add_email_index

-- migrations/20240115_add_email_index/migration.sql
-- Prisma 无法生成 CONCURRENTLY，因此我们手动编写
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users (email);

Drizzle (TypeScript/Node.js)

Workflow

# 根据模式变更生成迁移文件
npx drizzle-kit generate

# 执行迁移
npx drizzle-kit migrate

# 直接推送模式（仅限开发环境，不生成迁移文件）
npx drizzle-kit push

Schema Example

import { pgTable, text, timestamp, uuid, boolean } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: text("email").notNull().unique(),
  name: text("name"),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Django (Python)

Workflow

# 根据模型变更生成迁移文件
python manage.py makemigrations

# 执行迁移
python manage.py migrate

# 显示迁移状态
python manage.py showmigrations

# 为自定义 SQL 生成空白迁移
python manage.py makemigrations --empty app_name -n description

Data migration

from django.db import migrations

def backfill_display_names(apps, schema_editor):
    User = apps.get_model("accounts", "User")
    batch_size = 5000
    users = User.objects.filter(display_name="")
    while users.exists():
        batch = list(users[:batch_size])
        for user in batch:
            user.display_name = user.username
        User.objects.bulk_update(batch, ["display_name"], batch_size=batch_size)

def reverse_backfill(apps, schema_editor):
    pass  # 数据迁移，无需回滚逻辑

class Migration(migrations.Migration):
    dependencies = [("accounts", "0015_add_display_name")]

    operations = [
        migrations.RunPython(backfill_display_names, reverse_backfill),
    ]

SeparateDatabaseAndState

Remove a column from the Django model, but do not immediately delete it from the database:

class Migration(migrations.Migration):
    operations = [
        migrations.SeparateDatabaseAndState(
            state_operations=[
                migrations.RemoveField(model_name="user", name="legacy_field"),
            ],
            database_operations=[],  # 暂时不触碰数据库
        ),
    ]

golang-migrate (Go)

Workflow

# 创建一对迁移文件
migrate create -ext sql -dir migrations -seq add_user_avatar

# 执行所有待处理的迁移
migrate -path migrations -database "$DATABASE_URL" up

# 回滚最后一次迁移
migrate -path migrations -database "$DATABASE_URL" down 1

# 强制指定版本（修复 dirty 状态）
migrate -path migrations -database "$DATABASE_URL" force VERSION

Migration file example

-- migrations/000003_add_user_avatar.up.sql
ALTER TABLE users ADD COLUMN avatar_url TEXT;
CREATE INDEX CONCURRENTLY idx_users_avatar ON users (avatar_url) WHERE avatar_url IS NOT NULL;

-- migrations/000003_add_user_avatar.down.sql
DROP INDEX IF EXISTS idx_users_avatar;
ALTER TABLE users DROP COLUMN IF EXISTS avatar_url;

Zero-Downtime Migration Strategy

For critical production changes, follow the "expand-contract" pattern:

阶段 1：扩展（EXPAND）
  - 添加新列/表（可为空或带默认值）
  - 部署：应用同时写入旧列和新列
  - 回填现有数据

阶段 2：迁移（MIGRATE）
  - 部署：应用从新列读取，同时写入旧列和新列
  - 验证数据一致性

阶段 3：收缩（CONTRACT）
  - 部署：应用仅使用新列
  - 在独立的迁移中删除旧列/表

Timeline example

第 1 天：通过迁移添加 new_status 列（可为空）
第 1 天：部署应用 v2 —— 同时写入 status 和 new_status
第 2 天：为现有行运行数据回填迁移
第 3 天：部署应用 v3 —— 仅从 new_status 读取
第 7 天：通过迁移删除旧的 status 列

Anti-Patterns

Anti-Pattern	Why It Fails	Better Approach
Manually executing SQL in production	No audit trail, not repeatable	Always use migration files
Editing deployed migrations	Causes drift between environments	Create a new migration file
NOT NULL without a default value	Locks the table, rewrites all rows	First add a nullable column, backfill the data, then add the constraint
Use an inline index on a large table	Blocks writes during the build	Use CREATE INDEX CONCURRENTLY
Schema and data mixed in the same migration	Hard to roll back, transaction too long	Split it into separate migrations
Drop the column before removing the code	The application errors due to the missing column	Remove the code first, and drop the column in the next deployment

About this skill

When to Use

Core Principles

Migration Safety Checklist

PostgreSQL Patterns

Safely adding columns

Adding indexes without downtime

Renaming columns (zero downtime)

Safely dropping columns

Large-scale data migrations

Prisma (TypeScript/Node.js)

Workflow

Schema Example

Custom SQL migrations

Drizzle (TypeScript/Node.js)

Workflow

Schema Example

Django (Python)

Workflow

Data migration

SeparateDatabaseAndState

golang-migrate (Go)

Workflow

Migration file example

Zero-Downtime Migration Strategy

Timeline example

Anti-Patterns