SQLite3 专题：把单文件数据库用成默认之选

先把边界划死，再学用法。SQLite 被误用（拿它扛高并发写）和被埋没（明明该用却上了 MySQL）一样常见。

1.1 锁定版本

版本纪律：以上版本全部来自全系列锁定版本清单（09 模块 §4），照抄即可，不要自己去"更新到最新"。Go 侧的 SQLite 是随驱动二进制一起发布的——你锁了 modernc v1.60.1，就等于锁了它内嵌的那份 SQLite 引擎版本，服务器上不需要再装任何 sqlite3 软件包。

1.2 适用边界（对照 08 §8.1 四件套分工表）

SQLite 的"安装"比任何数据库都简单：它不是服务，没有 systemd unit，没有监听端口。你只需要两样东西之一——命令行工具（手动玩库用），或 Go 驱动（程序用，内嵌引擎）。

2.1 Windows 本机

1. 方式 A（推荐，手动操作库时用）：去 SQLite 官方下载页拿预编译的命令行工具 sqlite3.exe（zip 包，解压即用），放进一个已加入 PATH 的目录（如 C:\Tools\sqlite3\）。不需要安装程序、不需要管理员权限。
2. 方式 B（程序开发的默认形态）：什么都不装。Go 用 modernc 驱动时，SQLite 引擎已经编译进你的二进制，程序运行时自己打开一个 .db 文件即可。工作台走的就是这条路。
3. 验证（PowerShell）：

   # 方式 A：验证命令行工具
   sqlite3 -version
   # 预期输出类似：3.53.4 ... x64
   
   sqlite3 D:\data\workbench.db
   # 进入交互提示符后：
   sqlite> SELECT sqlite_version();
   # 预期：3.53.4
   sqlite> .quit

2.2 Linux 服务器（1GB）

1. 推荐形态：不装任何东西。Windows 本机交叉编译出的 Go 单二进制（GOOS=linux GOARCH=amd64，modernc 纯 Go 驱动）拷到服务器即可运行，引擎内嵌，不需要 gcc、不需要 apt install。这正是 1GB 机器的最大红利：省掉编译工具链，也省掉数据库常驻内存。
2. 仅当你要在服务器上手动排查库文件，才装命令行工具：

   # Debian/Ubuntu（一次性，仅排查用）
   sudo apt update && sudo apt install -y sqlite3
   sqlite3 --version
   # 预期：3.53.x（发行版打包版本可能略旧，不影响程序，程序用的是驱动内嵌版本）

关键认知：服务器上 apt install sqlite3 装的命令行工具，和你程序实际使用的 SQLite 引擎是两份独立的代码——前者只是给你手敲 SQL 排查用；后者是 Go 二进制内嵌的那一份。不要以为"服务器装了 sqlite3 包"就等于"程序用的是系统版本"。

只讲让你"不踩坑"的那部分理论。教科书式的 B 树细节、页结构演进，等阶段四再补。

3.1 单文件与无服务器架构

• 一个数据库 = 一个文件（如 workbench.db）。备份 = 复制这个文件（见 §8 的热备份方式）；迁移 = 拷贝这个文件；删除 = 删文件。
• 没有服务端进程：你的程序就是服务端。多个进程可以打开同一个 .db 文件，但写者全局只有一个，这是理解后面所有锁问题的钥匙。
• WAL 模式下会额外出现 workbench.db-wal（预写日志）和 workbench.db-shm（共享内存索引）两个旁支文件——这是正常的，不要删；正常关闭/检查点后 WAL 会合并回主库。

3.2 类型亲和（type affinity）——与 MySQL/PG 的思维差异

SQLite 是动态类型：列声明的类型只是"亲和倾向"，不是硬约束。你声明 age INTEGER，真塞进去一个字符串它也存得下（按亲和规则做转换尝试）。所以：数据合法性靠你的程序（和 GORM）把关，不要指望数据库拦你。建表时仍要写合理的类型（INTEGER / TEXT / REAL / BLOB），这是为了走对索引亲和，不是为了强校验。

3.3 WAL 模式：本教程的默认 journal 模式

记住这句：WAL 提升的是"读并发"，不是"写并发"。两个写事务仍然只能排队进行。看到 database is locked 时，先怀疑是不是有写事务没提交/没回滚，而不是"我没开 WAL"。

3.4 必记的 PRAGMA（连接建立后立刻设置）

三条连接级铁律：① 每个新连接都要执行这组 PRAGMA（WAL 是库级持久的，但 busy_timeout / foreign_keys 是连接级，连接池里每个连接都得设到）；② 外键不开 = 你写的 REFERENCES 全是摆设；③ busy_timeout 不设 = 偶发锁直接报错给用户。

3.5 事务与单写者语义；VACUUM

• 事务：BEGIN IMMEDIATE / BEGIN DEFERRED / COMMIT / ROLLBACK 与其他库一致。区别：写事务一旦开始，就独占"写锁"直到 COMMIT/ROLLBACK——事务体里不要放网络请求、不要放用户交互，否则等于把数据库锁着等人。
• VACUUM：DELETE 大量数据后，文件不会自动变小（页标记为空闲但不还给系统）。VACUUM 重建整个文件回收空间；线上热备份的高级形态 VACUUM INTO 见 §8。

语法与 MySQL 高度相似（你有 ThinkPHP 底子），只列差异点和高频命令。

4.1 建表与 CRUD（对照工作台 Task 模型）

-- DDL：建表（注意类型亲和，INTEGER PRIMARY KEY = 自增主键）
CREATE TABLE IF NOT EXISTS tasks (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  title      TEXT    NOT NULL,
  note       TEXT    DEFAULT '',
  done       INTEGER NOT NULL DEFAULT 0,   -- SQLite 无原生 BOOLEAN，用 0/1
  created_at  TEXT    NOT NULL,              -- 时间用 TEXT 存 RFC3339，Go 侧 time.Time 自动映射
  updated_at  TEXT    NOT NULL
);

-- DML
INSERT INTO tasks (title, note, done, created_at, updated_at)
  VALUES ('写周报', '周五前', 0, '2026-10-14T09:00:00+08:00', '2026-10-14T09:00:00+08:00');
SELECT id, title, done FROM tasks WHERE done = 0 ORDER BY id DESC LIMIT 20;
UPDATE tasks SET done = 1, updated_at = '2026-10-14T10:00:00+08:00' WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

-- 事务（写事务尽量短）
BEGIN IMMEDIATE;
  UPDATE tasks SET done = 1 WHERE id = 1;
  UPDATE tasks SET done = 1 WHERE id = 2;
COMMIT;   -- 出错就 ROLLBACK;

4.2 高频 PRAGMA 与诊断命令

PRAGMA journal_mode=WAL;        -- 库级，执行一次永久生效
PRAGMA busy_timeout=5000;       -- 连接级，每个连接都要设
PRAGMA foreign_keys=ON;         -- 连接级，默认 OFF！
PRAGMA synchronous=NORMAL;

PRAGMA integrity_check;          -- 体检：返回 ok 即库文件完好
PRAGMA wal_checkpoint;           -- 手动把 WAL 合并回主库
.tables                          -- 命令行点命令：列出所有表
.schema tasks                    -- 命令行点命令：看建表语句

4.3 备份 / 导出 / 恢复（命令行点命令）

# 在线备份（不锁库，推荐给正在运行的库）：
sqlite3 workbench.db ".backup 'backups/workbench-20261014.db'"

# 导出为 SQL 文本（适合做版本化迁移/小库）：
sqlite3 workbench.db .dump > dump.sql

# 从 SQL 文本恢复：
sqlite3 restored.db < dump.sql

不要在运行中的服务上直接 cp 主库文件——WAL 模式下主库与 -wal 是分离的，只 cp 主库会丢最近的数据。要么走 §8 的 VACUUM INTO / .backup，要么先正常停服务再 cp 三件套（.db + .db-wal + .db-shm）。

这是本模块的核心。先讲为什么选 modernc，再给完整可运行代码。

5.1 驱动选型结论

5.2 完整示例：建表 / CRUD / 事务 / WAL PRAGMA / 连接池

go.mod（依赖清单，锁版本照抄）：

module sqlite-demo

go 1.27

require (
  modernc.org/sqlite v1.60.1
)

main.go（整文件可运行）：

package main

import (
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "modernc.org/sqlite" // 注册 driver name "sqlite"
)

func main() {
	// DSN：打开文件；?_pragma=... 会在每个新连接上自动执行 PRAGMA
	dsn := "workbench.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(ON)&_pragma=synchronous(NORMAL)"

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// SQLite 下单写者语义下，连接池不要放大。
	// MaxOpenConns=1 写最稳；读多可放到 4-8，但写仍排队。
	db.SetMaxOpenConns(4)
	db.SetMaxIdleConns(2)
	db.SetConnMaxLifetime(time.Hour)

	// 建表
	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS tasks (
		id         INTEGER PRIMARY KEY AUTOINCREMENT,
		title      TEXT    NOT NULL,
		done       INTEGER NOT NULL DEFAULT 0,
		created_at TEXT    NOT NULL
	)`)
	if err != nil {
		log.Fatal(err)
	}

	// 插入
	res, err := db.Exec(
		`INSERT INTO tasks(title, done, created_at) VALUES(?, ?, ?)`,
		"写周报", 0, time.Now().Format(time.RFC3339),
	)
	if err != nil {
		log.Fatal(err)
	}
	id, _ := res.LastInsertId()
	fmt.Println("inserted id =", id)

	// 查询
	var (
		title string
		done  int
	)
	row := db.QueryRow(`SELECT title, done FROM tasks WHERE id = ?`, id)
	if err := row.Scan(&title, &done); err != nil {
		log.Fatal(err)
	}
	fmt.Println("row:", title, done)

	// 事务：BEGIN IMMEDIATE 显式拿写锁，避免 deferred 升级时的死锁
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	_, err = tx.Exec(`UPDATE tasks SET done = 1 WHERE id = ?`, id)
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
		log.Fatal(err)
	}
	fmt.Println("tx committed")
}

运行与交叉编译：

# Windows 本机：
go mod tidy
go run .

# 交叉编译到 1GB Linux 服务器（纯 Go 驱动，一把过，不需要 gcc）：
set GOOS=linux
set GOARCH=amd64
go build -o sqlite-demo-linux .
# 然后把 sqlite-demo-linux 拷到服务器，chmod +x 直接跑

工作台（模块 11）的 internal/db/db.go 就是这段代码的展开。这里给出"深度配置版"：WAL + busy_timeout + 外键 + 自动迁移 + 事务。

6.1 go.mod 依赖

require (
  github.com/glebarez/sqlite v1.11.0   // 纯 Go GORM 驱动，内部即 modernc v1.60.1
  gorm.io/gorm v1.31.2                 // GORM v2 API 线
)

6.2 db.go：工作台 SQLite 生产配置

package db

import (
	"fmt"
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"

	"workbench/internal/model" // User / Task（见工作台契约 §5）
)

// Open 按 DB_DRIVER 打开数据库；sqlite 分支即工作台默认。
// dbPath 来自环境变量 DB_PATH，例如 ./data/workbench.db
func Open(driver, dbPath string) (*gorm.DB, error) {
	switch driver {
	case "sqlite", "":
		return openSQLite(dbPath)
	case "mysql":
		// 见 16 MySQL 模块：gorm.io/driver/mysql，同一结构
		return nil, fmt.Errorf("mysql 分支见 16 模块")
	default:
		return nil, fmt.Errorf("unknown driver: %s", driver)
	}
}

func openSQLite(dbPath string) (*gorm.DB, error) {
	// DSN：用 _pragma 把连接级 PRAGMA 注入每个新连接。
	dsn := fmt.Sprintf(
		"%s?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_pragma=foreign_keys(ON)&_pragma=synchronous(NORMAL)",
		dbPath,
	)

	gdb, err := gorm.Open(sqlite.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Warn), // 生产用 Warn，别开 Info 刷屏
	})
	if err != nil {
		return nil, err
	}

	// 拿到底层 *sql.DB 设连接池（SQLite 单写者，池要小）
	sqlDB, err := gdb.DB()
	if err != nil {
		return nil, err
	}
	sqlDB.SetMaxOpenConns(4)
	sqlDB.SetMaxIdleConns(2)
	sqlDB.SetConnMaxLifetime(time.Hour)

	// 自动迁移（工作台契约 §5 的 User / Task）
	if err := gdb.AutoMigrate(&model.User{}, &model.Task{}); err != nil {
		return nil, err
	}
	return gdb, nil
}

// WithTx 演示工作台事务写法：短事务、无 IO
func WithTx(gdb *gorm.DB, fn func(tx *gorm.DB) error) error {
	return gdb.Transaction(fn)
}

1GB 内存的机器，数据库选型第一原则是"常驻内存越小越好"。按下表决策，不要拍脑袋。

1GB 服务器的物理账：MySQL 8 默认配置光缓冲池就想吃 400MB+，PG 类似；而 SQLite 没有常驻进程，你的 Go 二进制吃多少内存它就吃多少。在 1GB 机器上，这个差距就是"能跑"和"OOM"的差距。

8.1 热备份：VACUUM INTO（本教程首选）

服务运行中、WAL 未合并时，VACUUM INTO 会生成一个干净、一致、 checkpoint 过的新库文件，全程不锁你的业务库：

-- 在任意连接上执行，目标文件不能已存在
VACUUM INTO '/var/backups/workbench-2026-10-14.db';

-- Go 里就是一行 Exec：
db.Exec(`VACUUM INTO ?`, "/var/backups/workbench-"+time.Now().Format("20060102")+".db")

8.2 定时备份 cron 脚本（服务器上照抄）

#!/usr/bin/env bash
# /usr/local/bin/sqlite-backup.sh —— 工作台 SQLite 每日热备份
set -euo pipefail

DB=/opt/workbench/data/workbench.db
BAK_DIR=/var/backups/workbench
KEEP_DAYS=14

mkdir -p "$BAK_DIR"
STAMP=$(date +%Y%m%d-%H%M%S)
OUT="$BAK_DIR/workbench-$STAMP.db"

# 方式一：用 VACUUM INTO（需要 sqlite3 命令行；库在运行也安全）
sqlite3 "$DB" "VACUUM INTO '$OUT';"

# 备份完顺手校验完整性
sqlite3 "$OUT" "PRAGMA integrity_check;" | grep -q '^ok$'

# 清理超期备份
find "$BAK_DIR" -name 'workbench-*.db' -mtime "+$KEEP_DAYS" -delete

echo "backup ok: $OUT"

# crontab -e 加一行：每天凌晨 3:15 跑
15 3 * * * /usr/local/bin/sqlite-backup.sh >> /var/log/sqlite-backup.log 2>&1

8.3 恢复与只读模式

• 恢复：停服务 → 把备份文件改名替换 workbench.db（连同删掉旧的 -wal/-shm）→ 启动。恢复前先 PRAGMA integrity_check。
• 只读打开（排查、报表）：DSN 加 mode=ro，或命令行 sqlite3 file:workbench.db?mode=ro——只读连接完全不参与写锁，适合挂一个独立报表进程。

8.4 并发读与大数据量注意事项

• 并发读：WAL 下多读不互斥；给读用独立连接/连接池，不要让长查询占着写连接。
• 索引：WHERE / ORDER BY / JOIN 的列建索引（CREATE INDEX idx_tasks_done ON tasks(done);）。SQLite 是 B 树引擎，缺索引时全表扫描比 MySQL 还脆（单文件小库读盘快，但几十万行后照样卡）。
• 大批量写：放进一个事务里批量提交（1000 行一个事务），不要逐行 autocommit——逐行提交会疯狂刷 WAL，慢 10–100 倍。

卡住了先查这张表。报错原文照抄进表格第一列。

做完以下两项，本模块与 M3 清单里的"SQLite（WAL + 备份）集成达标"同时达成。

过不了怎么办：先查 §9 报错表；再不行把"报错原文 + 复现步骤 + 相关 PRAGMA"按 08 §6.3 的调试纪律打包给 AI。本模块不要求你去翻官网——该讲的都在上面了。

（编者整理：选型决策表、连接池参数、cron 备份脚本、报错对照表为基于官方文档与工作台契约的工程化整理，无外部独立来源。）