database-migrations

name	database-migrations
description	数据库迁移的最佳实践，涵盖架构更改、数据迁移、回滚以及跨 PostgreSQL、MySQL 和常用 ORM（Prisma、Drizzle、Kysely、Django、TypeORM、golang-migrate）的零停机部署。
origin	ECC

数据库迁移模式

为生产系统提供安全、可逆的数据库架构更改。

何时激活

• 创建或更改数据库表
• 添加/删除列或索引
• 运行数据迁移（回填、转换）
• 规划零停机架构更改
• 为新项目设置迁移工具

核心原则

1. 每一次更改都是一次迁移 —— 绝不要手动更改生产数据库
2. 生产环境中的迁移仅限向前 —— 回滚需使用新的向前迁移
3. 架构迁移和数据迁移是分离的 —— 绝不要在一次迁移中混合 DDL 和 DML
4. 针对生产规模的数据测试迁移 —— 在 100 行数据上可行的迁移在 1000 万行上可能会导致锁定
5. 迁移一旦部署即不可变 —— 绝不要编辑已在生产环境中运行过的迁移

迁移安全检查清单

在应用任何迁移之前：

• 迁移同时具备 UP 和 DOWN（或明确标记为不可逆）
• 大型表上没有全表锁定（使用并发操作）
• 新列具有默认值或可为空（绝不要在没有默认值的情况下添加 NOT NULL）
• 索引是并发创建的（对于现有表，不要在 CREATE TABLE 中内联创建）
• 数据回填是与架构更改分离的迁移
• 已针对生产数据的副本进行过测试
• 已记录回滚计划

PostgreSQL 模式

安全添加列

-- 推荐：可为空的列，无锁定
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;
-- 这会锁定表并重写每一行

无停机添加索引

-- 不推荐：在大型表上会阻塞写入
CREATE INDEX idx_users_email ON users (email);

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

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

重命名列（零停机）

绝不要在生产环境中直接重命名。使用“扩展-收缩”模式：

-- 第 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;

安全删除列

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

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

大型数据迁移

-- 不推荐：在一次事务中更新所有行（会锁定表）
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)

工作流

# 从架构更改创建迁移
npx prisma migrate dev --name add_user_avatar

# 在生产环境中应用待处理的迁移
npx prisma migrate deploy

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

# 架构更改后生成客户端
npx prisma generate

架构示例

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 无法表达的操作（并发索引、数据回填）：

# 创建仅限创建的迁移，然后手动编辑 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)

工作流

# 从架构更改生成迁移
npx drizzle-kit generate

# 应用迁移
npx drizzle-kit migrate

# 直接推送架构（仅限开发环境，无迁移文件）
npx drizzle-kit push

架构示例

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(),
});

Kysely (TypeScript/Node.js)

工作流 (kysely-ctl)

# 初始化配置文件 (kysely.config.ts)
kysely init

# 创建新的迁移文件
kysely migrate make add_user_avatar

# 应用所有待处理的迁移
kysely migrate latest

# 回滚上一个迁移
kysely migrate down

# 显示迁移状态
kysely migrate list

迁移文件

// migrations/2024_01_15_001_create_user_profile.ts
import { type Kysely, sql } from 'kysely'

// 重要：始终使用 Kysely<any>，而不是您的类型化 DB 接口。
// 迁移在时间上是冻结的，不得依赖当前的架构类型。
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('user_profile')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('email', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('avatar_url', 'text')
    .addColumn('created_at', 'timestamp', (col) =>
      col.defaultTo(sql`now()`).notNull()
    )
    .execute()

  await db.schema
    .createIndex('idx_user_profile_avatar')
    .()
    .()
    .()
}

   (): <> {
   db..().()
}

程序化迁移器

import { Migrator, FileMigrationProvider } from 'kysely'
import { promises as fs } from 'fs'
import * as path from 'path'
// 仅限 ESM —— CJS 可以直接使用 __dirname
import { fileURLToPath } from 'url'
const migrationFolder = path.join(
  path.dirname(fileURLToPath(import.meta.url)),
  './migrations',
)

// `db` 是您的 Kysely<any> 数据库实例
const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder,
  }),
  // 警告：仅在开发环境中启用。禁用时间戳排序验证，这可能会导致环境之间的架构漂移。
  // allowUnorderedMigrations: true,
})

const { error, results } = await migrator.migrateToLatest()

results?.forEach((it) => {
  if (it.status === 'Success') {
    console.log(`迁移 "${it.migrationName}" 执行成功`)
  } else if (it.status === ) {
    .()
  }
})

 (error) {
  .(, error)
  process.()
}

Django (Python)

工作流

# 从模型更改生成迁移
python manage.py makemigrations

# 应用迁移
python manage.py migrate

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

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

数据迁移

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

在不立即从数据库中删除列的情况下从 Django 模型中删除列：

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

golang-migrate (Go)

工作流

# 创建迁移对
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

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

迁移文件

-- 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;

零停机迁移策略

对于关键的生产更改，请遵循“扩展-收缩”模式：

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

第 2 阶段：迁移 (MIGRATE)
  - 部署：应用程序从新列读取，向两列写入
  - 验证数据一致性

第 3 阶段：收缩 (CONTRACT)
  - 部署：应用程序仅使用新列
  - 在单独的迁移中删除旧列/表

时间线示例

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

反模式

反模式	失败原因	更好的方法
在生产中使用手动 SQL	无审计轨迹，不可重复	始终使用迁移文件
编辑已部署的迁移	导致环境之间的漂移	创建新的迁移
不带默认值的 NOT NULL	锁定表，重写所有行	添加可为空的列，回填，然后添加约束
在大型表上使用内联索引	在构建期间阻塞写入	使用 CREATE INDEX CONCURRENTLY
在一次迁移中混合架构和数据	难以回滚，长事务	将迁移分离
在移除代码前删除列	应用程序因缺少列而报错	先移除代码，在下一次部署中删除列