Database Migration (数据库迁移)

🔒 核心理念: 数据库迁移是高风险操作，必须遵循严格的安全工作流。备份优先，测试先行，回滚就绪。

🔴 第一原则：永远不要直接在生产环境执行未测试的迁移

❌ 错误做法: 直接在生产数据库执行 ALTER TABLE
✅ 正确做法: 本地测试 → 预发布验证 → 生产执行 → 验证回滚

❌ 错误做法: "这只是加个字段，应该没问题"
✅ 正确做法: 任何 schema 变更都要走完整流程

When to Use This Skill

使用此技能当你需要：

• 添加/删除/修改数据库表或字段
• 创建或修改索引
• 执行数据迁移（data migration）
• 回滚失败的迁移
• 处理破坏性变更（删除字段、修改类型）
• 在生产环境执行 schema 变更

Not For / Boundaries

此技能不适用于：

• 纯查询优化（不涉及 schema 变更）
• 应用层代码修改
• 数据库备份恢复（参考运维文档）

Quick Reference

🎯 迁移安全工作流

需求分析 → 编写迁移 → 本地测试 → 备份生产 → 执行迁移 → 验证结果 → 监控
    ↓                                              ↓
  评估风险                                      失败 → 回滚

📋 迁移前必问清单

✅ 迁移类型风险等级

迁移安全工作流详解

Phase 1: 备份

生产环境迁移前必须备份！

# Supabase 备份
# 方式 1: 使用 Supabase Dashboard 导出
# 方式 2: 使用 pg_dump

# 导出完整数据库
pg_dump -h <host> -U postgres -d postgres > backup_$(date +%Y%m%d_%H%M%S).sql

# 只导出 schema
pg_dump -h <host> -U postgres -d postgres --schema-only > schema_backup.sql

# 只导出特定表
pg_dump -h <host> -U postgres -d postgres -t <table_name> > table_backup.sql

Phase 2: 本地测试

# 1. 生成迁移文件
pnpm drizzle-kit generate

# 2. 检查生成的 SQL
cat drizzle/<migration_file>.sql

# 3. 在本地数据库测试
pnpm drizzle-kit push

# 4. 验证 schema 正确
pnpm drizzle-kit studio

Phase 3: 执行迁移

# 生产环境迁移
pnpm drizzle-kit push --config=drizzle.config.prod.ts

# 或使用迁移文件
pnpm drizzle-kit migrate

Phase 4: 验证结果

-- 检查表结构
\d+ <table_name>

-- 检查数据完整性
SELECT COUNT(*) FROM <table_name>;

-- 检查约束
SELECT * FROM information_schema.table_constraints 
WHERE table_name = '<table_name>';

Drizzle ORM 迁移指南

基本配置

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

常用命令

# 生成迁移文件（基于 schema 变更）
pnpm drizzle-kit generate

# 推送 schema 到数据库（开发环境）
pnpm drizzle-kit push

# 执行迁移文件（生产环境）
pnpm drizzle-kit migrate

# 查看数据库状态
pnpm drizzle-kit studio

# 检查 schema 差异
pnpm drizzle-kit check

Schema 定义示例

// src/db/schema.ts
import { pgTable, text, timestamp, uuid, boolean, integer } from 'drizzle-orm/pg-core';

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

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: uuid('author_id').references(() => users.id),
  published: boolean('published').default(false),
  viewCount: integer('view_count').default(0),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

破坏性变更处理

删除字段的安全流程

1. 代码中移除对该字段的所有引用
2. 部署代码变更
3. 等待确认无问题（建议 24-48 小时）
4. 执行数据库迁移删除字段

重命名字段的安全流程

1. 添加新字段
2. 数据迁移：复制旧字段数据到新字段
3. 代码改为使用新字段
4. 部署代码
5. 确认无问题后删除旧字段

修改字段类型的安全流程

1. 添加新字段（新类型）
2. 数据迁移：转换并复制数据
3. 代码改为使用新字段
4. 部署代码
5. 确认无问题后删除旧字段

回滚策略

回滚原则

1. 每个迁移都要有对应的回滚脚本
2. 回滚脚本必须在执行迁移前测试
3. 破坏性变更的回滚需要数据恢复

回滚方式

方式 1: 使用 Drizzle 回滚

# Drizzle 目前不支持自动回滚，需要手动编写

方式 2: 手动 SQL 回滚

-- 回滚添加字段
ALTER TABLE users DROP COLUMN new_field;

-- 回滚添加索引
DROP INDEX idx_users_email;

-- 回滚添加表
DROP TABLE new_table;

方式 3: 从备份恢复

# 恢复完整数据库
psql -h <host> -U postgres -d postgres < backup.sql

# 恢复特定表
psql -h <host> -U postgres -d postgres < table_backup.sql

生产环境迁移注意事项

🚨 高风险操作检查清单

• 已创建数据库备份
• 已在本地/预发布环境测试
• 已准备回滚脚本
• 已通知相关团队成员
• 已选择低峰期执行
• 已准备监控告警

大表迁移策略

-- 添加索引时使用 CONCURRENTLY（不锁表）
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 大表添加字段时分批更新默认值
-- 1. 先添加可空字段
ALTER TABLE large_table ADD COLUMN new_field TEXT;

-- 2. 分批更新数据
UPDATE large_table SET new_field = 'default' 
WHERE id IN (SELECT id FROM large_table WHERE new_field IS NULL LIMIT 10000);

-- 3. 最后添加 NOT NULL 约束
ALTER TABLE large_table ALTER COLUMN new_field SET NOT NULL;

零停机迁移模式

1. 添加新字段（可空）
2. 部署代码：同时写入新旧字段
3. 数据迁移：填充历史数据
4. 部署代码：只读取新字段
5. 删除旧字段

Examples

Example 1: 添加新字段

Input: “需要给 users 表添加 avatar_url 字段”

Steps:

1. 修改 schema 文件
2. 生成迁移
3. 本地测试
4. 生产执行

Expected Output:

// src/db/schema.ts
export const users = pgTable('users', {
  // ... 现有字段
  avatarUrl: text('avatar_url'), // 新增可空字段
});

# 生成迁移
pnpm drizzle-kit generate

# 检查生成的 SQL
# ALTER TABLE users ADD COLUMN avatar_url TEXT;

# 执行迁移
pnpm drizzle-kit push

Example 2: 安全删除字段

Input: “需要删除 users 表的 legacy_field 字段”

Steps:

1. 确认代码中无引用
2. 备份数据
3. 执行删除

Expected Output:

# 1. 搜索代码确认无引用
grep -r "legacy_field" src/

# 2. 备份该字段数据
psql -c "COPY (SELECT id, legacy_field FROM users) TO '/tmp/legacy_backup.csv' CSV HEADER;"

# 3. 修改 schema 移除字段
# 4. 生成并执行迁移
pnpm drizzle-kit generate
pnpm drizzle-kit push

Example 3: 添加索引

Input: “users 表的 email 查询很慢，需要添加索引”

Steps:

1. 分析查询模式
2. 创建索引
3. 验证性能

Expected Output:

// src/db/schema.ts
import { pgTable, text, index } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: uuid('id').primaryKey(),
  email: text('email').notNull(),
}, (table) => ({
  emailIdx: index('idx_users_email').on(table.email),
}));

-- 生产环境使用 CONCURRENTLY 避免锁表
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

References

• references/drizzle-templates.md: Drizzle ORM 迁移代码模板
• references/rollback-strategies.md: 各类迁移的回滚策略

Maintenance

• Sources: Drizzle ORM 官方文档, PostgreSQL 最佳实践
• Last Updated: 2025-01-01
• Known Limits:
  • 回滚策略依赖手动编写
  • 大规模数据迁移需要额外工具支持