
关于
数据库迁移最佳实践,涵盖 Schema 变更、数据迁移、回滚和零停机部署,支持 PostgreSQL、MySQL 和常见 ORM(Prisma、Drizzle、Kysely、Django、TypeORM、golang-migrate)。
name: database-migrations description: 数据库迁移最佳实践,涵盖 Schema 变更、数据迁移、回滚和零停机部署,适用于 PostgreSQL、MySQL 及常见 ORM(Prisma、Drizzle、Kysely、Django、TypeORM、golang-migrate)。 origin: ECC
数据库迁移模式
面向生产系统的安全、可逆数据库 Schema 变更。
何时启用
- 创建或修改数据库表
- 添加/删除列或索引
- 执行数据迁移(回填、转换)
- 规划零停机 Schema 变更
- 为新项目搭建迁移工具
核心原则
- 每次变更都是一次迁移 — 永远不要手动修改生产数据库
- 生产环境只能前进 — 回滚通过新的前向迁移实现
- Schema 迁移和数据迁移分离 — 永远不要在一次迁移中混合 DDL 和 DML
- 用生产规模数据测试迁移 — 在 100 行上正常的迁移可能在 1000 万行上锁表
- 已部署的迁移不可修改 — 永远不要编辑已在生产环境运行过的迁移
迁移安全检查清单
应用任何迁移之前:
- [ ] 迁移包含 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);
兼容工具
Claude CodeCursor
标签
后端开发

