---

name: database-migrations description: 数据库迁移最佳实践，涵盖 Schema 变更、数据迁移、回滚和零停机部署，适用于 PostgreSQL、MySQL 及常见 ORM（Prisma、Drizzle、Kysely、Django、TypeORM、golang-migrate）。 origin: ECC

数据库迁移模式

面向生产系统的安全、可逆数据库 Schema 变更。

何时启用

• 创建或修改数据库表
• 添加/删除列或索引
• 执行数据迁移（回填、转换）
• 规划零停机 Schema 变更
• 为新项目搭建迁移工具

核心原则

1. 每次变更都是一次迁移 — 永远不要手动修改生产数据库
2. 生产环境只能前进 — 回滚通过新的前向迁移实现
3. Schema 迁移和数据迁移分离 — 永远不要在一次迁移中混合 DDL 和 DML
4. 用生产规模数据测试迁移 — 在 100 行上正常的迁移可能在 1000 万行上锁表
5. 已部署的迁移不可修改 — 永远不要编辑已在生产环境运行过的迁移

迁移安全检查清单

应用任何迁移之前：

• [ ] 迁移包含 UP 和 DOWN（或明确标记为不可逆）
• [ ] 大表无全表锁（使用并发操作）
• [ ] 新列有默认值或可为空（永远不要添加无默认值的 NOT NULL）
• [ ] 索引并发创建（对已有表不要内联在 CREATE TABLE 中）
• [ ] 数据回填与 Schema 变更分开
• [ ] 已在生产数据副本上测试
• [ ] 回滚方案已记录

PostgreSQL 模式

安全添加列

-- GOOD: Nullable column, no lock
ALTER TABLE users ADD COLUMN avatar_url TEXT;

-- GOOD: Column with default (Postgres 11+ is instant, no rewrite)
ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

-- BAD: NOT NULL without default on existing table (requires full rewrite)
ALTER TABLE users ADD COLUMN role TEXT NOT NULL;
-- This locks the table and rewrites every row

无停机添加索引

-- BAD: Blocks writes on large tables
CREATE INDEX idx_users_email ON users (email);

-- GOOD: Non-blocking, allows concurrent writes
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

-- Note: CONCURRENTLY cannot run inside a transaction block
-- Most migration tools need special handling for this

重命名列（零停机）

永远不要在生产环境直接重命名。使用扩展-收缩模式：

-- Step 1: Add new column (migration 001)
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Step 2: Backfill data (migration 002, data migration)
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- Step 3: Update application code to read/write both columns
-- Deploy application changes

-- Step 4: Stop writing to old column, drop it (migration 003)
ALTER TABLE users DROP COLUMN username;

安全删除列

-- Step 1: Remove all application references to the column
-- Step 2: Deploy application without the column reference
-- Step 3: Drop column in next migration
ALTER TABLE orders DROP COLUMN legacy_status;

-- For Django: use SeparateDatabaseAndState to remove from model
-- without generating DROP COLUMN (then drop in next migration)

大规模数据迁移

-- BAD: Updates all rows in one transaction (locks table)
UPDATE users SET normalized_email = LOWER(email);

-- GOOD: Batch update with progress
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）

工作流

# Create migration from schema changes
npx prisma migrate dev --name add_user_avatar

# Apply pending migrations in production
npx prisma migrate deploy

# Reset database (dev only)
npx prisma migrate reset

# Generate client after schema changes
npx prisma generate

Schema 示例

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])
}

自定义 SQL 迁移

对于 Prisma 无法表达的操作（并发索引、数据回填）：

# Create empty migration, then edit the SQL manually
npx prisma migrate dev --create-only --name add_email_index

-- migrations/20240115_add_email_index/migration.sql
-- Prisma cannot generate CONCURRENTLY, so we write it manually
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);