数据库演进：SQLite 起步、Postgres 生产、MySQL 兼容

「一套代码支持三种数据库」听起来像架构能力的证明，实际更像一份长期维护合同。这篇以 Limit Blog 为例，说明本地为什么用 SQLite、生产为什么推荐 PostgreSQL、MySQL 兼容到底兼容到什么程度，以及 GORM 的 AutoMigrate、三种全文检索、连接池和迁移回滚各自的天花板在哪里。

1. 三种数据库的诱惑与账单

先看一个常见的失败路径：本地用 SQLite 开发，所有测试通过；上线切 PostgreSQL，第一周遇到三个问题——LIKE 大小写行为变了，唯一键冲突的错误文本对不上，搜索接口直接报「关系不存在」。代码没改，行为却变了。

这不是 GORM 的错。ORM 能抹平「增删改查」的语法，抹不平三件事：

• 方言能力：全文索引、JSON 查询、ON CONFLICT、窗口函数；
• 类型语义：布尔、时间、字符串长度、排序规则；
• 错误形态：唯一约束、死锁、连接中断的报错各不相同。

所以项目定了一条硬约束：核心逻辑不使用方言特有语法，三种数据库跑同一套业务代码；只有全文检索按方言实现，但藏在统一接口后面。

2. 角色分工：SQLite 起步、Postgres 生产、MySQL 兼容

三种库不是三个平等的选项，它们在这个项目里各有明确位置：

本地默认 SQLite，是为了让「克隆仓库、make run」不需要先装数据库。生产示例配置默认 PostgreSQL，是因为写入并发、备份工具和全文索引都更省心。仓库里同时保留了 MySQL 的生产配置，Compose 默认也起 MySQL，目的是给已有 MySQL 的环境一个入口，而不是说两套代码。

但 SQLite 上的测试通过，不等于 PostgreSQL 上通过。三库兼容要靠真实实例上的集成测试，后面单独讲。

3. 切换点只有一处

数据库驱动的选择集中在 pkg/repository.NewDB。业务代码拿到的始终是 *gorm.DB，不感知方言：

switch driver := conf.Data.Db.Type; driver {
case "mysql":
	gormConfig.PrepareStmt = true
	db, err = gorm.Open(mysql.Open(dsn), gormConfig)
case "postgres":
	gormConfig.PrepareStmt = true
	db, err = gorm.Open(postgres.New(postgres.Config{
		DSN:                  dsn,
		PreferSimpleProtocol: true,
	}), gormConfig)
case "sqlite":
	gormConfig.PrepareStmt = false
	db, err = gorm.Open(sqlite.Open(dsn), gormConfig)
default:
	panic("unknown db driver")
}

三个驱动的配置差异不是随手写的：

• MySQL / PostgreSQL 开启 PrepareStmt，减少重复 SQL 的解析开销；
• SQLite 关闭 PrepareStmt，因为 schema 变化后旧的 prepared statement 可能失效；
• PostgreSQL 使用 PreferSimpleProtocol，避免隐式 prepared statement 与某些 DDL 场景冲突。

公共部分放在 DefaultGormConfig：SkipDefaultTransaction: true 让单条写入不再自动包事务，事务边界由 Service 显式声明；NowFunc 统一返回 UTC，规避 SQLite 文本时间、MySQL datetime、PostgreSQL timestamptz 的时区差异；TranslateError: false 则把唯一键识别交给自己的 IsDuplicateKey，保证三库拿到的错误形态一致。

配置只改两个字段：

data:
  db:
    type: postgres          # sqlite | mysql | postgres
    dsn: host=postgres user=blog password=<db-password> dbname=blog port=5432 sslmode=disable TimeZone=Asia/Shanghai
  pool:
    maxOpen: 8
    maxIdle: 4
    maxLifeTime: 60

SQLite 的 DSN 建议显式打开 WAL 与忙等待，否则并发写入很容易直接报 database is locked：

file:./data/blog.db?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL&_foreign_keys=on

「只改配置」只对代码成立，不对运维成立。 换库之后，连接池参数、备份方式、全文索引、迁移脚本都要跟着换。下面逐项说。

4. AutoMigrate 的边界

开发环境建表走 POST /api/init/db，内部是 GORM 的 AutoMigrate：

if !initEnabled {
	return nil, api.ErrFeatureDisabled
}
if err := db.AutoMigrate(models...); err != nil {
	return nil, api.MapError(ctx, err, nil)
}

models 由 system.Models() 与 business.Models() 聚合，顺序是先系统表、后业务表。生产环境必须把 server.init.enable 关掉：这个入口没有鉴权，却能触发全库 DDL。

AutoMigrate 的能力边界必须记清楚：它只做增量。新增列、新增索引可以；删除列、修改列类型、重命名表、重命名索引都不行。它也不会理解数据迁移，比如把 status 从字符串 "0"/"1" 改成 int8，只能另写脚本。

因此项目维护两条路径：

schema.sql 是目标契约，不是进度快照。测试会把 schema.sql 与 AutoMigrate 分别建库，断言共同覆盖的表上列名与归一化类型一致；某个模型落地后如果还留在「待补白名单」里，测试会失败，强制把白名单收紧。这条测试用 make schema-check 执行，是防止 DDL 悄悄漂移的关键。

真正需要改结构时，走 deploy/sql/migrations。仓库里已经有一个表重命名的例子：m0-01-rename-oss-to-media.sql。它的注释写得很直白——AutoMigrate 不会重命名表，也不会改列类型，有数据时必须单独执行。

5. FTS 三种实现与迁移代价

全文检索是「三库兼容」里唯一不装作统一的部分。接口只有三个方法：

type Searcher interface {
	Ensure(ctx context.Context) error
	Search(ctx context.Context, keyword string, limit, offset int) ([]Result, int64, error)
	Reindex(ctx context.Context) (int64, error)
}

search.New(db) 按 db.Dialector.Name() 选择实现，默认落到 SQLite。三种实现的能力差异如下：

SQLite 的实现依赖三个触发器，让 biz_article 的增删改自动同步到 FTS 表：

CREATE VIRTUAL TABLE IF NOT EXISTS article_search USING fts5(
  article_id UNINDEXED, title, summary, content, tokenize='trigram'
);
CREATE TRIGGER IF NOT EXISTS article_search_ai AFTER INSERT ON biz_article BEGIN
  INSERT INTO article_search(article_id,title,summary,content)
  VALUES (new.article_id,new.title,new.summary,new.content);
END;

tokenize='trigram' 让中文至少三个字时能命中；少于三个字（比如「Go」）走 LIKE 回退。查询用 bm25(article_search) 排序，比 LIKE 的「包含即命中」更接近相关性排序。

PostgreSQL 这边，simple 分词器不会切中文，所以实现里同时保留 tsvector 与 ILIKE：

CREATE INDEX IF NOT EXISTS idx_article_search
ON biz_article USING GIN (to_tsvector('simple',
  coalesce(title,'') || ' ' || coalesce(summary,'') || ' ' || coalesce(content,'')));

这条 SQL 有两个代价。第一，CREATE INDEX 不是 CONCURRENTLY，大表上会阻塞写入；数据量大时应该在维护窗口手动建并发索引。第二，表达式索引要求查询侧的表达式完全一致，改一个 coalesce 就会失效。

迁移数据库时，检索是最容易被忘记的一步：

• Ensure 只创建索引表、索引和触发器，不会回填已有文章；
• 切库后必须执行一次 POST /api/admin/search/reindex；
• MySQL 的 Reindex 实际是 OPTIMIZE TABLE，返回的计数是 0，不要拿它当「重建了多少篇」；
• SQLite 重建会先 DELETE 再 INSERT，期间检索结果可能短暂为空。

还有一个跨库差异：SQLite 的 FTS5 表不在 GORM 模型里，AutoMigrate 不会创建它，只能靠启动时 Ensure；漏掉就会报「no such table: article_search」。

6. 连接池参数与容量估算

连接池当前支持三个参数：

这里有一个必须指出的现状：NewDB 直接调用 SetMaxOpenConns(conf.Data.Pool.MaxOpen)，没有按驱动覆写。也就是说，如果配置里写 maxOpen: 8，SQLite 也会真的开 8 个连接。切到 SQLite 部署时，要手动把 maxOpen 改成 1，否则并发写入会开始报锁等待。

容量怎么估？公开 API 的目标是 P95 小于 100ms，缓存命中时小于 20ms。按 200 QPS 估算，Little's Law 下平均并发大约是 200 × 0.02 = 4 个请求；即使全部落到数据库、每个查询 10ms，也只有 2 个并发。所以 PostgreSQL / MySQL 的 20 个连接对单实例足够，真正的瓶颈通常不是连接数，而是慢查询和缺失索引。

SQLite 的账不一样：maxOpen = 1 把所有读写串行化。个人博客的写入是低频的（发布、评论、计数落库），读又大部分被缓存挡住，所以 1 个连接够用；如果哪天的读 QPS 真的上来，正确做法是加缓存或换 PostgreSQL，而不是把 SQLite 的连接数调大。

配置里的 maxLifeTime: 60 是示例值，生产建议提到 3600 秒，避免连接频繁重建；项目没有单独的 maxIdleTime 配置。

7. 备份、恢复与迁移步骤

三种库的备份方式不同，但原则相同：先备份，再验证能恢复，最后才切库。

# SQLite：停写后复制，或用在线备份
sqlite3 ./data/blog.db ".backup './backup/blog-$(date +%F).db'"

# PostgreSQL
pg_dump -Fc blog > backup/blog-$(date +%F).dump

# MySQL
mysqldump --single-transaction --routines blog > backup/blog-$(date +%F).sql

项目还提供内容级备份接口：GET /api/admin/backup/export 导出 11 张内容与配置表，POST /api/admin/backup/import 只允许空库导入。这个接口的边界要看清：它不包含用户、令牌、媒体二进制。所以它是「内容迁移」工具，不是「整站灾备」工具；整站恢复仍然要靠数据库原生备份加上 data/uploads。

从 SQLite 迁到 PostgreSQL，推荐按下面的顺序做：

1. 冻结写入：进入维护窗口，停止管理端写入，记录当前版本号与各表行数。
2. 备份并验证：备份旧库与 data/uploads，在临时目录恢复一次，确认能查询。
3. 新库建表：开发/自托管可用 POST /api/init/db；生产执行 schema.sql，再执行 seed.sql。
4. 迁移数据：空库可以用备份导入接口恢复内容；用户、令牌、媒体记录需要原生导出或单独的 ETL。项目没有内置跨库 ETL，这一步不要假设「点一下就能迁」。
5. 同步权限：执行 POST /api/admin/permissions/sync，把内置权限点补齐；否则角色授权可能缺项。
6. 切换配置：修改 data.db.type 与 dsn，重启服务，跑一遍登录、发文、评论、上传的冒烟。
7. 重建检索：执行 POST /api/admin/search/reindex，分别用英文与中文关键词验证。
8. 观察并保留旧库：旧库保持只读至少一个备份周期，确认无回滚需求后再下线。

回滚策略要提前写清楚，而不是出事时再想：

• 保留旧配置与旧库，回滚时把 type / dsn 切回去并重启；
• 回滚会丢切换后的新写入，所以切库必须配维护窗口，不能边写边切；
• 不要指望 AutoMigrate 降级：它不会删列、不会改回类型，回滚靠「切回旧库」，不是「把新库改旧」；
• 二进制版本要匹配：新版本可能写入了旧代码不认识的列，回滚时通常要一起回滚应用版本。

8. 一套代码三种库的测试矩阵

三库兼容不能靠 review，要靠可重复执行的测试。当前仓库的测试分四层：

集成测试的关键断言不是「能连上」，而是三件事：每个模型都建出了表、SELECT 1 能执行、repository.IsDuplicateKey 能识别当前数据库的唯一键错误。最后一条最容易被忽略：MySQL 返回 errno 1062，PostgreSQL 返回 SQLSTATE 23505，SQLite 返回 UNIQUE constraint failed 文本，三者必须统一，否则 slug 冲突处理会在某个库上失效。

FTS 测试则要求真实实例，因为三种实现完全不同。仓库里的 test-fts 会用英文和中文关键词各查一次，验证的不是「有没有结果」，而是「分词与回退逻辑是否按预期工作」。

需要如实说明：CI 里的 MySQL / PostgreSQL 矩阵还没有完全跑全。目前本地用 Docker 起实例验证，make test-fts 需要手动注入 DSN；make ci-local 默认只跑 SQLite。这是一个已知缺口，不是「已经三库全绿」。

最后列一下写代码时最容易踩的方言坑：

• SQLite 忽略 size，不要指望在 SQLite 上断言 varchar(255)；
• SQLite 与 PostgreSQL 的索引名共享 schema 命名空间，索引名必须带表名前缀；
• MySQL 的 LIKE 大小写取决于 collation，PostgreSQL 的 LIKE 区分大小写，统一用 LOWER(col) LIKE LOWER(?)；
• 不使用 ON DUPLICATE KEY / ON CONFLICT，改成「先查后写 + 唯一索引兜底」；
• 不使用数据库端 JSON 查询，meta / config 列只做整存整取。

9. 小结

「一套代码三种库」的真正成本不在驱动切换，而在四件事：schema 的演进方式、全文检索的方言实现、连接池与备份的运维差异、以及测试矩阵是否真的覆盖了三种库。

这个项目的取舍是：SQLite 负责「零依赖启动」，PostgreSQL 负责「生产推荐」，MySQL 负责「已有环境兼容」；核心逻辑不碰方言，检索按方言实现但收敛到 Searcher 接口；AutoMigrate 只用于开发建表，生产以 schema.sql 为契约，结构变更走迁移脚本。

如果只能记住一句话：数据库可以换，迁移路径必须提前设计；AutoMigrate 不是迁移工具，它只是建表工具。