数据库与迁移

学习目标

课程、进度、笔记、草稿——四类数据该是什么关系？本课从空库开始设计表结构，用带编号的 SQL 迁移文件管理每一次结构变更，让任何一台新机器跑完迁移都得到一模一样的库。

问题场景

你在 SQLite 命令行里手敲 CREATE TABLE 建了库，跑了两周。换台电脑、或者重新 clone 代码——新环境库里没有这些表，程序第一句查询就崩。更麻烦的是后来 note 表要加一列：手工 ALTER 过的库和没 ALTER 过的库已经不一样了。结构变更必须可追踪、可重放。

核心解释

关系设计先行。 四类数据的骨架（简化版）：

-- lib/db/migrations/0001_init.sql
CREATE TABLE IF NOT EXISTS course (
  slug TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  track TEXT NOT NULL,
  order_index INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS lesson_progress (
  id TEXT PRIMARY KEY,
  course_slug TEXT NOT NULL REFERENCES course(slug),
  lesson_key TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'in_progress',
  updated_at TEXT NOT NULL,
  UNIQUE (course_slug, lesson_key)   -- 同一节课只允许一条进度
);

CREATE TABLE IF NOT EXISTS note (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  revision INTEGER NOT NULL DEFAULT 1,
  archived INTEGER NOT NULL DEFAULT 0,
  course_slug TEXT REFERENCES course(slug),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS note_draft (
  note_id TEXT PRIMARY KEY REFERENCES note(id),
  body TEXT NOT NULL,
  saved_at TEXT NOT NULL
);

要点：进度表用 UNIQUE (course_slug, lesson_key) 把「一课一进度」压到数据库层，应用代码写错也插不进两条；笔记对课程是弱关联（可空外键），课程内容重建时不该连带删笔记；草稿以笔记为主键，一对一。

迁移机制：编号文件 + 已应用台账。

// scripts/migrate.ts（与 lib/db/client.ts 的 migrate() 同思路）
import fs from "node:fs";
import path from "node:path";
import { getDb } from "../lib/db/client";

const dir = path.join(process.cwd(), "lib", "db", "migrations");
const db = getDb();
db.exec(
  "CREATE TABLE IF NOT EXISTS _migrations (id TEXT PRIMARY KEY, applied_at TEXT NOT NULL);"
);
const applied = new Set(
  db.prepare("SELECT id FROM _migrations").all().map((r) => r.id as string)
);

for (const file of fs.readdirSync(dir).filter((f) => f.endsWith(".sql")).sort()) {
  if (applied.has(file)) continue;          // 幂等：应用过就跳过
  db.exec("BEGIN");
  try {
    db.exec(fs.readFileSync(path.join(dir, file), "utf8"));
    db.prepare("INSERT INTO _migrations (id, applied_at) VALUES (?, ?)")
      .run(file, new Date().toISOString());
    db.exec("COMMIT");
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

文件名用 0001_ 0002_ 前缀保证排序即顺序；已应用清单记录在库内的 _migrations 表——结构与台账在同一个文件里，天然一致。加列就新增 0002_note_tags.sql，从不修改旧文件。

示例拆解

本机从零验证：

1. 把 data/database/ 改名（回到空库状态），启动应用触发 migrate()。
2. 查 SELECT id FROM _migrations——所有 SQL 文件在列，业务表全部就位。
3. 再跑一次 migrate()——无变化、无报错，这就是幂等。
4. 手动插入两条相同 (course_slug, lesson_key) 的进度——第二条被 UNIQUE 约束拒绝。

关键点：迁移文件一旦在其他环境应用过就只增不改；要撤销就写新的反向迁移，保证所有环境的变更序列一致。

动手任务

在本机工程里完成：

1. 在 lib/db/migrations/ 新增一个递增编号的迁移（比如给 note 加 tags 列），确认旧库应用后新列生效。
2. 藏掉数据库重启应用，确认从空库一路迁移到最新。
3. 尝试向进度表插入重复的 (course_slug, lesson_key)，确认 UNIQUE 约束报错。

常见错误

• 修改已应用的旧迁移文件：应用过的库不会重放它，各环境结构悄悄分叉。
• 迁移文件名不排序：0002 依赖 0001 却先执行，报「表不存在」；文件名前缀决定顺序。
• 把业务数据写进迁移：迁移管结构，种子数据走单独的 seed 脚本，重置数据时不必动结构。
• 外键忘了 PRAGMA foreign_keys = ON：SQLite 默认不启用外键，REFERENCES 写了也形同虚设。

检查与复盘

自查清单：

• 空库一条命令迁移到位，重复执行无副作用
• 每个结构变更都有编号文件，台账与实际表结构一致
• 唯一约束在数据库层验证过，不依赖应用代码自觉

参考资料

• Node.js 文档：node:sqlite —— DatabaseSync 的执行与预处理
• SQLite 文档：CREATE TABLE —— 约束、外键与唯一索引语法
• Next.js 文档：项目结构 —— lib/ 目录的组织惯例