SQLite 数据库迁移：用 user_version 管理表结构版本

表结构总会变，提前做好准备

你的第一版表结构永远不会是最后一版。列会增加，表会拆分，索引会重新设计。问题不在于表结构会不会变，而在于这些变化能否顺利落到每一台已经存有旧版数据库的笔记本、服务器和用户设备上。

迁移就是为此而生的：一系列小而有序的脚本，每个脚本把数据库从版本 N 升到版本 N+1。按顺序执行它们，任何数据库都能追上最新版本。不守这个规矩，你就会遇到“在我电脑上明明没问题”的 bug，花一整个下午去排查。

SQLite 只为此提供了一个内置工具：PRAGMA user_version。这是数据库替你保存的一个 32 位整数，SQLite 自己从来不碰它。它代表什么，由你决定。

PRAGMA user_version;
PRAGMA user_version = 1;
PRAGMA user_version;

新建的数据库从 0 开始。每执行完一个迁移，就把它设成对应的迁移编号。程序启动时读取它，就知道当前处在哪个版本。

一个最简单的迁移循环

可以这样理解：每个迁移都是一个带编号的 SQL 脚本。应用读取当前的 user_version，按顺序执行所有编号更大的脚本，并在每个脚本执行完后更新 user_version。

下面是迁移 1，创建初始表结构：

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

PRAGMA user_version = 1;

COMMIT;

有两点要注意。整个过程包在 BEGIN; ... COMMIT; 里，所以它是原子的：如果 CREATE TABLE 失败，user_version 不会被加一，你可以修好后重试。另外，PRAGMA user_version = 1 是提交前的最后一条语句，所以只有其他步骤全部成功，版本号才会改变。

现在假设你需要加一个 created_at 列，这就是迁移 2：

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

BEGIN;

ALTER TABLE users ADD COLUMN created_at TEXT
    NOT NULL DEFAULT (datetime('now'));

PRAGMA user_version = 2;

COMMIT;

SELECT sql FROM sqlite_master WHERE name = 'users';

版本 0 的数据库两个迁移都会执行。版本 1 的数据库只执行第二个。版本 2 的数据库什么都不执行。顺序就是约定。

ALTER TABLE 能做什么，不能做什么

SQLite 的 ALTER TABLE 故意设计得很精简。它支持：

• ADD COLUMN：追加一个新列，可以带默认值。
• DROP COLUMN：删除一个列（从 3.35 开始）。
• RENAME COLUMN：重命名一个列（从 3.25 开始）。
• RENAME TO：重命名表本身。

就这些。你不能改变列的类型、修改 NOT NULL、修改 CHECK 约束，也不能给已有的列加 FOREIGN KEY。

-- Not supported:
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

当你需要做 SQLite 无法直接完成的修改时，官方方案是“重建表”。写起来更啰嗦，但完全可靠。

用重建表来完成更大的修改

套路是：按你想要的结构新建一张表，把数据复制过去，删掉旧表，再把新表改名顶替上去。整个过程都在一个事务里完成。

BEGIN;

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada'), ('b@x.com', 'Boris');

-- Now we want a CHECK constraint on email. Rebuild:
CREATE TABLE users_new (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE CHECK (email LIKE '%@%'),
    name  TEXT NOT NULL
);

INSERT INTO users_new (id, email, name)
    SELECT id, email, name FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

SQLite 官方文档把这叫作 12 步方案，并针对触发器、视图和外键引用补充了几条注意事项，在生产环境的表结构上操作之前值得读一遍。对大多数情况来说，上面这个四步版本就够了。

提醒一下：如果有外键指向你要重建的表，请在迁移前执行 PRAGMA foreign_keys = OFF，迁移后执行 PRAGMA foreign_keys = ON。否则 DROP TABLE 可能在中途破坏参照完整性。

由应用程序驱动迁移

这些记账工作很简单，完全可以自己写。用 Python 标准库实现的话：

-- Conceptually, your app does this on startup:
--
--   current = SELECT user_version FROM PRAGMA;
--   for i in range(current + 1, len(MIGRATIONS) + 1):
--       run MIGRATIONS[i] in a transaction
--       PRAGMA user_version = i;
--
-- The SQL each step runs looks like the migrations above.

PRAGMA user_version;

几条关键的不变规则：

• 迁移从 1 开始连续编号。不能有空缺，也不能调换顺序。
• 每个迁移都和 PRAGMA user_version = N 的版本更新一起包在一个事务里。
• 迁移一旦提交并发布，就再也不要修改它。新的变更放进新的迁移。

最后这条是团队最常违反的。如果同事的数据库已经执行过迁移 3，你再去修改它，他的数据库就会悄无声息地和你的永远不一致。

保留审计记录

user_version 告诉你数据库处在哪个版本，却不会告诉你每一步是什么时候执行的、做了什么。一张小小的记录表就能解决这个问题：

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

BEGIN;

CREATE TABLE posts (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    body   TEXT NOT NULL
);

INSERT INTO schema_migrations (version, name)
    VALUES (1, 'create_posts');
PRAGMA user_version = 1;

COMMIT;

SELECT * FROM schema_migrations;

现在每个迁移都有一行记录，包含名称和时间戳，排查“为什么这个数据库里有一列代码根本不认识”时非常有用。

PRAGMA user_version 仍然是迁移循环的唯一依据；这张表是给人看的。

回滚：事务能帮你做什么，不能做什么

SQLite 的 DDL 支持事务。假如迁移 5 开始创建一张表、复制数据并更新 user_version，而复制做到一半失败了，ROLLBACK 会撤销所有操作，包括 CREATE TABLE。数据库会完全恢复到 BEGIN 之前的样子。

BEGIN;

CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts (id, balance) VALUES (1, 100);

-- Pretend something goes wrong here:
ROLLBACK;

-- The table doesn't exist:
SELECT name FROM sqlite_master WHERE type='table' AND name='accounts';

这解决的是失败的迁移。它解决不了那些已经成功提交、但你现在后悔了的迁移。对于这种情况，你要另写一个回退迁移（down-migration），也就是一个撤销该变更的脚本。SQLite 没有自动反向操作。如果迁移 7 加了一列，回退版本就把它删掉。如果迁移 7 删了一列，回退版本无法恢复数据，最多只能重新建一个空列。

实际上，很多小项目完全不写回退迁移，而是靠备份来“撤销”。只要你确实做了备份，这也是合理的选择。

几个能帮你少踩坑的习惯

• 一个迁移只做一件逻辑上的事。 一次加三个不相关列的迁移，比三个独立的迁移更难审查，也更难回退。
• 在生产数据的副本上测试迁移。 在大表上修改表结构可能很慢，等到生产环境才发现可不好玩。
• 永远不要修改已发布的迁移。 新增一个就好。
• 先备份。 在任何不简单的迁移之前，在命令行里快速执行一次 .backup，或在数据库关闭时复制一下文件，都是代价很小的保险。
• 留意 PRAGMA foreign_keys。 重建表期间把它关掉，完成后再打开。

对于较大的项目，请使用专门的工具，比如配合 SQLAlchemy 的 Alembic、golang-migrate、Knex、Flyway。它们会处理执行顺序、并发执行和团队约定这些你本来要自己重新实现的东西。原理和上面的循环一样，工具只是帮你省掉了样板代码。

下一篇：WAL 模式与并发

迁移通常在应用离线或持有排他锁的时候执行。其余时间，你的数据库要同时为多个连接处理读写，而 SQLite 默认的日志模式并不总是最合适的。下一页会介绍 WAL 模式、它改变了什么，以及什么时候应该切换。