SQLite 迁移脚本怎么写：版本表、事务与回滚边界

数据库迁移最怕的不是 SQL 写错，而是状态不可重复：一次发布跑到一半失败，下一次启动又从半新不旧的结构继续跑。SQLite 常被当成“一个文件而已”，迁移逻辑也容易变成几段散落在启动代码里的 ALTER TABLE。小项目可以这样撑一阵，但只要遇到多实例启动、表重建、回滚发布，就会暴露出边界。

更稳的做法并不重：每个迁移有递增版本；启动时只允许一个写者执行迁移；能进事务的结构变更必须进事务；不能真正回滚的破坏性变更，提前备份并分阶段发布。

先建版本表

SQLite 有 PRAGMA user_version，但它只能保存一个整数，排障时看不到每一步的名字和执行时间。应用级迁移更适合用一张表记录事实：

CREATE TABLE IF NOT EXISTS schema_migrations (
  version INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);

版本号建议用连续整数。应用版本可以回退，数据库结构通常只能向前走，所以不要把 v1.4.2 这类发布号直接塞进迁移顺序里。name 只服务于人读，执行顺序只看 version。

如果迁移来自文件，也不要只依赖字典序。10_add_index.sql 会排在 2_add_status.sql 前面，这是最常见的低级坑。文件名可以写成 000010_add_index.sql，但入库前仍然要解析出整数版本，并在代码里检查是否有重复、缺号和倒序。迁移系统宁可在启动时失败，也不要悄悄跳过一个结构版本。

用 BEGIN IMMEDIATE 收敛并发

SQLite 同一时间只有一个写者。迁移阶段这反而是优势：用 BEGIN IMMEDIATE 在入口拿写锁，其他实例要么等待，要么在 busy_timeout 到期后失败，不会两个进程同时改结构。

下面是一个足够小的 Go 实现，适合放在 HTTP 服务监听端口之前执行：

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"
)

type Migration struct {
	Version int
	Name    string
	SQL     string
}

func Migrate(ctx context.Context, db *sql.DB, ms []Migration) error {
	plan := append([]Migration(nil), ms...)
	if err := validateMigrations(plan); err != nil {
		return err
	}

	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()

	if _, err = conn.ExecContext(ctx, `BEGIN IMMEDIATE`); err != nil {
		return err
	}
	done := false
	defer func() {
		if !done {
			_, _ = conn.ExecContext(context.Background(), `ROLLBACK`)
		}
	}()

	_, err = conn.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
		version INTEGER PRIMARY KEY,
		name TEXT NOT NULL,
		applied_at TEXT NOT NULL DEFAULT (datetime('now'))
	)`)
	if err != nil {
		return err
	}

	var current int
	err = conn.QueryRowContext(ctx,
		`SELECT COALESCE(MAX(version), 0) FROM schema_migrations`,
	).Scan(&current)
	if err != nil {
		return err
	}
	if len(plan) > 0 && current > plan[len(plan)-1].Version {
		return fmt.Errorf("database schema version %d is newer than binary migration version %d",
			current, plan[len(plan)-1].Version)
	}

	for _, m := range plan {
		if m.Version <= current {
			continue
		}
		if _, err = conn.ExecContext(ctx, m.SQL); err != nil {
			return fmt.Errorf("migration %d %s: %w", m.Version, m.Name, err)
		}
		if _, err = conn.ExecContext(ctx,
			`INSERT INTO schema_migrations(version, name) VALUES (?, ?)`,
			m.Version, m.Name,
		); err != nil {
			return err
		}
	}

	if _, err = conn.ExecContext(ctx, `COMMIT`); err != nil {
		return err
	}
	done = true
	return nil
}

func validateMigrations(ms []Migration) error {
	sort.Slice(ms, func(i, j int) bool {
		return ms[i].Version < ms[j].Version
	})

	for i, m := range ms {
		want := i + 1
		if m.Version != want {
			return fmt.Errorf("migration version gap: got %d, want %d", m.Version, want)
		}
		if strings.TrimSpace(m.Name) == "" {
			return fmt.Errorf("migration %d has empty name", m.Version)
		}
		if strings.TrimSpace(m.SQL) == "" {
			return fmt.Errorf("migration %d %s has empty SQL", m.Version, m.Name)
		}
	}
	return nil
}

这里没有用 db.BeginTx，因为普通 BEGIN 是延迟事务，真正写入时才升级锁。迁移更适合一开始就明确声明“我要写结构”，让锁竞争尽早暴露。示例还刻意检查了两件事：迁移列表必须连续，数据库里的版本不能高于当前二进制认识的最新版本。后者能防止旧程序误连已经升级过的数据库。

如果使用的 SQLite 驱动不支持一次 Exec 执行多条语句，就把同一个版本拆成多个 Exec，或者把 Migration.SQL 改成迁移函数。不要靠简单的分号切字符串处理复杂 SQL；触发器、字符串字面量和注释都会让这种切法出错。

生产环境还应该配合 PRAGMA busy_timeout。它不是提高并发能力的魔法，只是告诉 SQLite 遇到锁时等待多久。内容站启动迁移通常可以等几秒；后台任务 worker 则应该在迁移完成后再启动，避免应用一边改结构，一边有旧代码继续写入旧字段。

健康检查也要放在迁移之后再变绿，顺序要清晰。否则负载均衡器可能在实例还持有写锁时就把流量打进来，读请求虽然通常能继续工作，但写请求会和迁移竞争，日志里只剩一堆间歇性的 database is locked。

重建表是正常手段

SQLite 的 ALTER TABLE 能力比很多服务端数据库克制。新增列、重命名列通常没问题；修改约束、调整列定义、删除旧列，很多时候要走“建新表、搬数据、改名”的路径：

CREATE TABLE posts_new (
  id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'draft'
);

INSERT INTO posts_new (id, slug, title, body, status)
SELECT id, slug, title, body, COALESCE(status, 'draft')
FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

这段 SQL 应该作为某个版本的 SQL 字段进入迁移列表，并和版本表写入放在同一个事务里。失败时结构和版本记录一起回滚，下一次启动可以重新尝试。

重建表时还要记得处理索引、触发器和外键。SQLite 不会因为你把 posts_new 改名成 posts，就自动恢复旧表上的所有索引定义。实践里我会把“建新表、搬数据、建索引、必要时执行 PRAGMA foreign_key_check”放在同一个迁移里，并在测试库上跑一次完整启动流程，而不是只测试单条 SQL。

如果这张表被其他表通过外键引用，重建边界要再收紧一层。PRAGMA foreign_keys 不能在事务中临时切换；需要关闭外键检查的迁移，必须在迁移入口明确设置、提交后恢复，并用 PRAGMA foreign_key_check 验证结果。对普通内容站，优先选择不需要关闭外键的分阶段迁移，少把一次发布做成全库级维护动作。

别把事务当成万能回滚

SQLite 的多数 DDL 可以放进事务，这是它适合轻量应用的原因。但“SQL 能回滚”不等于“业务后果能回滚”。例如把空状态统一改成 draft 后，如果新版本已经据此生成缓存、写入搜索索引或发送通知，单纯回滚数据库并不能撤销外部副作用。

还有几类操作要谨慎：VACUUM 不能放在事务里；大表重建会长时间占用写锁；给旧表新增没有默认值的 NOT NULL 列会直接失败。遇到这些变更，先在备份库上跑一遍，记录耗时和锁占用，再决定是停机迁移、分批补数据，还是拆成多次发布。

迁移也不应该在请求路径上懒执行。第一次访问页面时顺手建表，看起来省了启动步骤，实际会把结构变更的不确定性暴露给用户。更清晰的边界是：进程启动后先打开数据库、设置 PRAGMA、执行迁移；全部成功以后才启动 HTTP server 和后台 worker。启动失败就让进程退出，由 systemd 或容器编排重启并留下明确日志。

回滚策略优先向前修

很多工具要求 up/down 成对存在，但 SQLite 单体应用里不必迷信自动 down。删除列、合并字段、清理旧数据之后，down 往往只能恢复表形状，恢复不了信息。更可靠的流程是发布前用 .backup 或 VACUUM INTO 生成可恢复副本；迁移只向前执行；失败后写一个新的修复迁移继续前进。

破坏性变更尤其要分阶段。比如要把 posts.body 拆成 summary 和 content，第一版先加新列并让代码双写；第二版补齐历史数据；第三版确认没有旧读路径后，再重建表删除 body。这比一次性改完慢一点，但事故半径小得多。

SQLite 迁移需要的不是复杂平台，而是纪律：版本表记录事实，BEGIN IMMEDIATE 收敛并发，事务保护结构变更，破坏性操作分阶段。做到这些，一个单文件数据库也能有可审计、可重复、可恢复的演进过程。