duoqi-api/docs/database-migration-guide.md

数据库初始化与迁移指南

本地开发机 → 阿里云 RDS MySQL 8.0+ 的数据库操作完整指南

目录

• 概述
• 前置条件
• 首次初始化
  • Step 0：创建 RDS 数据库
  • Step 1：配置本地连接串
  • Step 2：推送表结构
  • Step 3：导入种子数据
  • Step 4：验证
• 日常表结构变更
  • 开发流程
  • 生产部署流程
• 常用操作速查
• 故障排查

---

概述

技术选型

组件	说明
ORM	Drizzle ORM — 类型安全，schema 即代码
Schema 真相源	src/db/schema.ts — 所有表定义的唯一来源
迁移工具	drizzle-kit — 生成和执行 SQL 迁移文件
迁移文件目录	db/migrations/ — 版本化的 SQL 变更记录
种子数据	content/*.json + db/seeds/index.ts

表结构总览（15 张表）

类别	表名	说明
核心	users, categories, questions, knowledge_cards, user_progress, skill_tree, user_chapter_progress	用户、题目、进度
反馈	question_ratings, user_feedback	评价与反馈
游戏化	achievements, user_achievements, leaderboard_snapshots	成就与排行
商业	subscriptions	订阅与 IAP
管理员	admin_users, admin_audit_log	后台管理与审计

两条核心命令

场景	命令	说明
开发/首次部署	bun run db:push	直接从 schema.ts 同步到数据库，适合快速迭代
生产增量变更	bun run db:migrate	执行 db/migrations/ 中的 SQL 文件，幂等且可审计

选择原则：首次初始化用 db:push（简单直接），后续变更用 db:migrate（安全可控）。

---

前置条件

1. 本地环境

# 确认 bun 已安装
bun --version

# 安装项目依赖
cd duoqi-api
bun install

2. 阿里云 RDS

• 已创建 MySQL 8.0+ 实例
• 已在 RDS 控制台 白名单设置 中添加本机公网 IP
• 已获取连接信息：地址、端口、用户名、密码

3. 网络连通性验证

# 替换为实际 RDS 地址
mysql -h rm-xxxxx.mysql.rds.aliyuncs.com -u root -p -e "SELECT 1;"

如果连接超时，检查：

• RDS 白名单是否已添加本机 IP（阿里云 RDS 控制台 → 数据安全性 → 白名单设置）
• 本机防火墙/代理是否出站 3306 端口

---

首次初始化

适用于：全新的 RDS 实例，数据库为空。

Step 0：创建 RDS 数据库

在阿里云 RDS 控制台的 DMS 数据管理 或本地 MySQL 客户端中执行：

-- 生产库
CREATE DATABASE duoqi_prod CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 测试库
CREATE DATABASE duoqi_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 创建用户并授权（如使用 root 可跳过）
CREATE USER 'duoqi_prod'@'%' IDENTIFIED BY 'your-secure-password';
GRANT ALL PRIVILEGES ON duoqi_prod.* TO 'duoqi_prod'@'%';

CREATE USER 'duoqi_test'@'%' IDENTIFIED BY 'your-secure-password';
GRANT ALL PRIVILEGES ON duoqi_test.* TO 'duoqi_test'@'%';

FLUSH PRIVILEGES;

Step 1：配置本地连接串

编辑本地项目 .env 文件，将 DATABASE_URL 指向目标 RDS 数据库：

# 初始化生产库
DATABASE_URL=mysql://duoqi_prod:your-password@rm-xxxxx.mysql.rds.aliyuncs.com:3306/duoqi_prod

# 或初始化测试库
# DATABASE_URL=mysql://duoqi_test:your-password@rm-xxxxx.mysql.rds.aliyuncs.com:3306/duoqi_test

注意：操作完成后，记得将 DATABASE_URL 改回本地开发值，避免后续开发误操作生产数据库。

Step 2：推送表结构

# 从 schema.ts 直接同步全部 15 张表 + 外键 + 索引
bun run db:push

drizzle-kit push 会：

1. 读取 src/db/schema.ts 中的表定义
2. 连接数据库，对比当前状态
3. 创建所有缺失的表、列、索引和外键

输出示例：

[✓] Changes applied successfully. The following changes were made:
  └─ Table "users" was created
  └─ Table "categories" was created
  └─ ... (共 15 张表)

替代方案：如果不想用 db:push，也可以用 db:migrate 执行已有的迁移文件：

bun run db:migrate

效果相同，区别在于 db:migrate 执行的是 db/migrations/0000_melodic_blacklash.sql 这个固定文件。

Step 3：导入种子数据

bun run db:seed

种子脚本 (db/seeds/index.ts) 按依赖拓扑顺序导入，幂等安全（可重复执行）：

Step 0: admin_users    → 默认管理员 (admin / admin123)
Step 1: categories     → 4 个分类（历史、戏曲、相声...）
Step 2: skill_tree     → 技能树节点
Step 3: questions      → 题目
        knowledge_cards → 知识卡片（依赖 questions）
Step 4: achievements   → 成就定义

输出示例：

Admin user seeded: username=admin, password=admin123 (CHANGE IN PRODUCTION!)
Categories: 4 inserted, 0 skipped
Skill tree: 12 inserted, 0 skipped
Questions: 60 inserted, 0 skipped
Achievements: 8 inserted, 0 skipped

Seed data import complete!

Warning

默认管理员密码 admin123 仅用于初始化，首次登录后必须立即修改。

Step 4：验证

# 方式一：Drizzle Studio（浏览器可视化）
bun run db:studio
# 访问 https://local.drizzle.studio → 浏览所有表和数据

# 方式二：命令行查询
mysql -h rm-xxxxx.mysql.rds.aliyuncs.com -u duoqi_prod -p duoqi_prod \
  -e "SHOW TABLES; SELECT COUNT(*) AS question_count FROM questions;"

预期结果：SHOW TABLES 输出 15 张表，questions 有数据。

---

日常表结构变更

适用于：schema 已初始化，后续迭代需要新增/修改表结构。

开发流程

修改 schema.ts → 生成迁移文件 → 本地验证 → 提交代码

1. 修改 Schema

编辑 src/db/schema.ts，例如新增一张表：

// src/db/schema.ts — 示例：新增通知表
export const notifications = mysqlTable('notifications', {
  id: char('id', { length: 36 }).primaryKey(),
  userId: char('user_id', { length: 36 }).notNull(),
  title: varchar('title', { length: 200 }).notNull(),
  content: text('content'),
  read: tinyint('read').default(0),
  createdAt: datetime('created_at').default(sql`CURRENT_TIMESTAMP`),
});

2. 生成迁移文件

bun run db:generate

drizzle-kit generate 会对比 schema.ts 与上一次快照，在 db/migrations/ 中生成新的 SQL 文件：

db/migrations/
├── 0000_melodic_blacklash.sql   ← 初始化（已存在）
├── 0001_add_notifications.sql   ← 新增
└── meta/
    ├── 0001_snapshot.json
    └── _journal.json

Important

生成的 SQL 文件必须提交到 Git。迁移文件是生产部署的变更记录，不可遗漏。

3. 本地验证

# 方式 A：在本地 MySQL 验证
bun run db:push

# 方式 B：模拟生产流程（推荐）
bun run db:migrate

验证通过后，将迁移文件和 schema 变更一起提交：

git add src/db/schema.ts db/migrations/
git commit -m "feat: 新增通知表"

生产部署流程

迁移文件随代码提交后，通过以下两种方式应用到生产数据库：

方式 A：CI 自动执行（推荐）

在 .gitea/workflows/deploy.yml 的部署 Job 中，在构建镜像之前执行迁移：

# deploy.yml — 在 build-and-deploy-prod 和 build-and-deploy-test 中添加
steps:
  - name: Checkout code
    uses: actions/checkout@v4

  - name: Install dependencies
    run: bun install --frozen-lockfile

  - name: Run database migrations
    env:
      DATABASE_URL: ${{ secrets.DATABASE_URL }}
    run: bun run db:migrate

  # ... 后续 build / deploy 步骤

为什么迁移必须在部署前执行？ 新代码可能依赖新表/新列。先部署新代码再迁移，中间会有服务报错窗口。先迁移则旧代码仍可正常运行（向后兼容的 schema 变更）。

方式 B：手动从本地执行（紧急/首次）

从本地开发机直接连接 RDS 执行：

# 1. 临时将 .env 的 DATABASE_URL 改为生产 RDS
# 2. 执行迁移
bun run db:migrate
# 3. 改回本地 DATABASE_URL

或通过环境变量覆盖（不修改 .env）：

DATABASE_URL=mysql://duoqi_prod:password@rm-xxxxx:3306/duoqi_prod bun run db:migrate

---

常用操作速查

操作	命令	说明
推送表结构	bun run db:push	从 schema.ts 同步到数据库（开发用）
生成迁移文件	bun run db:generate	对比 schema 差异，生成 SQL 文件
执行迁移	bun run db:migrate	执行未应用的迁移 SQL 文件
导入种子数据	bun run db:seed	幂等导入分类、题目、成就等
可视化浏览	bun run db:studio	启动 Drizzle Studio Web UI
类型检查	bun run typecheck	确认 schema.ts 无类型错误

Drizzle Kit 命令对比

schema.ts (源码真相)
    │
    ├── db:push ────────→ 直接同步到数据库（无迁移文件，开发用）
    │
    ├── db:generate ────→ 生成 SQL 迁移文件到 db/migrations/
    │                       （需要提交到 Git）
    │
    └── db:migrate ────→ 执行 db/migrations/ 中的 SQL
                          （生产部署用，幂等）

---

故障排查

1. db:push 报连接超时

# 检查网络连通性
mysql -h rm-xxxxx.mysql.rds.aliyuncs.com -u root -p -e "SELECT 1;"

# 如果超时，检查 RDS 白名单
# 阿里云控制台 → RDS → 数据安全性 → 白名单设置
# 添加本机公网 IP（可通过 curl ifconfig.me 查看）
curl ifconfig.me

2. db:push 报表已存在

Error: Table 'users' already exists

说明数据库中已有表结构。如果是首次初始化，可能是之前执行过。db:push 是幂等的，已有表不会重复创建，可以安全忽略。

3. db:migrate 报迁移已应用

No pending migrations to execute

所有迁移文件已执行过，这是正常输出。

4. db:seed 重复执行

种子脚本是幂等的 — 每条数据插入前都会检查是否已存在，输出 skipped 计数。可安全重复运行。

5. db:generate 未生成新文件

Everything is fine, no changes detected

说明 schema.ts 没有变更，或变更已生成过迁移文件。检查：

• 是否修改了 src/db/schema.ts 并保存
• db/migrations/meta/_journal.json 中是否已记录该变更

6. Schema 变更后类型报错

# 重新运行类型检查
bun run typecheck

# 常见原因：schema.ts 中修改了字段，但引用该字段的 service/routes 未更新

7. 本地误操作生产数据库

如果不小心对生产数据库执行了破坏性操作：

# 1. 立即停止操作，不要重复执行
# 2. 从备份恢复
mysql -h rm-xxxxx -u duoqi_prod -p duoqi_prod < /opt/backups/duoqi_prod_YYYYMMDD.sql

# 3. 如果没有备份，在 RDS 控制台使用「数据恢复」功能（需已开启 binlog）

---

文档版本: v1.0.0 最后更新: 2026-04-22 维护者: Duoqi Team