SQLite 本地文件库、CLI 与嵌入式数据工具手册

SQLite 本地文件库、CLI 与嵌入式数据工具手册参考图SQLite 从 sqlite3 CLI、本地文件库、WAL、PRAGMA、备份恢复、驱动接入、容器 volume、CI 临时库到锁和团队治理的落地链路。高清原图

从一份被多人打开的数据库文件说起

SQLite 最容易被低估。它不是“玩具数据库”，也不是 MySQL / PostgreSQL 的迷你版本。它是一套嵌入式关系数据库引擎：没有独立数据库服务进程，数据通常落在一个本地文件里，应用进程直接通过库函数读写。这个特点让 SQLite 非常适合本地工具、桌面应用、测试 fixture、CI 临时库、轻量脚本、小型单机服务和边缘环境；也让它不适合被当成跨机器共享、多写高并发、服务端高可用数据库来用。

最常见的失控现场不是 SQL 写错，而是三个人同时打开一份共享 .db：应用在写，GUI 留着未结束的事务，备份脚本只复制主文件。随后应用报 database is locked，恢复出来的库又缺少刚写入的数据。SQLite 没有独立服务进程替团队管理账号、连接和备份；应用进程直接通过库函数读写本地文件，文件生命周期就是数据库生命周期。

因此它很适合本地工具、桌面应用、测试 fixture、CI 临时库、轻量脚本、小型单机服务和边缘节点，也能在受控的单机生产环境中稳定工作。需要多台机器共享写、集中账号审计、自动故障切换或跨可用区恢复时，应尽早选择 MySQL、PostgreSQL 或托管数据库，而不是把锁等待时间越调越长。

第一次创建数据库前，先准备：

一个只用于实验的项目目录，例如 your-project/。官方或系统包安装的 sqlite3 CLI。没有 CLI 时，也可以先用语言内置库验证，但团队文档必须给出 CLI 入口。明确数据库文件放在哪里，例如 data/dev/app.db，不要散落在源码根目录、桌面或临时下载目录。

确认当前数据库文件不是生产数据、不是客户导出数据、不是含个人信息的真实样本。如果在容器里使用 SQLite，提前规划 volume、UID/GID、文件权限、备份目录和清理方式。如果在 CI 里使用 SQLite，明确每个 job 使用独立临时文件或 :memory:，避免并发 job 写同一个文件。

建议项目先准备目录：

your-project/
  data/
    dev/
      .gitkeep
  db/
    sqlite/
      init/
      verify/
      backup/
  docs/
    dependency-setup.md
  scripts/
    sqlite-verify.sh
    sqlite-backup.sh

仓库里可以保留 data/dev/.gitkeep 和脚本，不应该提交真实 .db、.db-wal、.db-shm、备份文件和客户数据。示例库如果必须提交，只能是脱敏 fixture，并且要有 owner、用途和刷新方式。

先确认真正运行的 SQLite

SQLite 经常随操作系统、语言运行时或驱动一起交付，所以“机器装了 SQLite”并不能证明 CLI、Python、Node 和 Java 实际加载的是同一版本、同一构建。SQLite 下载页当前稳定发布线是 3.53.0，但项目仍应选择自己验证过的精确版本，而不是让 CLI、运行时和驱动各自追随系统更新。先记录每条运行链路的版本与编译能力：

sqlite3 --version
sqlite3 ':memory:' 'SELECT sqlite_version();'
sqlite3 ':memory:' 'PRAGMA compile_options;'

SQLite 下载页 提供源码、amalgamation、autoconf、文档包，以及 Windows、Linux x64、macOS x64 / arm64 等预编译工具；具体架构应以下载页实际列出的包为准。系统包管理器、Homebrew、winget、Chocolatey 与 Scoop 是不同分发渠道，更新节奏和编译选项可能不同。需要固定团队能力时，不要只写“安装最新版”，而要把 CLI 输出、语言驱动版本和下面的能力探针一起放进 CI：

SELECT sqlite_version();
SELECT json_valid('{"ok":true}');
CREATE VIRTUAL TABLE temp.fts_probe USING fts5(content);
DROP TABLE temp.fts_probe;

3.38.0 起 JSON 函数默认编入 SQLite，但构建仍可用 SQLITE_OMIT_JSON 移除；FTS5、可加载扩展和线程模式也可能受构建方式影响。JSONB 不是 PostgreSQL JSONB 的兼容实现；跨语言项目要以实际查询结果为准，不能仅凭函数名推断行为。版本升级前查看 Release History，重点检查文件格式、查询计划、CLI 批处理输出和所用扩展的变化。

连接级配置同样不能只检查一次。外键约束需要每个连接启用 PRAGMA foreign_keys=ON；WAL 模式会持久化到数据库文件并产生 -wal、-shm 文件；同一数据库仍然同时只有一个写者。应用启动探针应同时读取 PRAGMA foreign_keys、PRAGMA journal_mode 和 PRAGMA busy_timeout，这样测试连接、GUI 连接和生产连接不会悄悄采用不同语义。

备份动作要匹配日志模式。运行中的数据库优先使用 Online Backup API、CLI .backup 或 VACUUM INTO 生成一致快照；WAL 模式下只复制 .db 主文件可能漏掉已提交事务。网络文件系统还可能破坏 WAL 依赖的共享内存与锁语义，因此跨机器共享文件不是部署捷径。

SQLite Copyright 将核心代码和文档置于公共领域，可以复制、修改、分发和商用；这不等于所有周边组件都采用同一条款。SQLite Encryption Extension 是单独授权的商业扩展，SQLCipher 是第三方项目，语言驱动、GUI、构建脚本和随产品分发的二进制也要分别检查许可。SQLite 公共核心库不提供默认透明加密，磁盘加密和平台密钥管理还会带来密钥轮换、备份恢复和驱动兼容成本。GUI 工具可以检查 schema 和少量数据，但真实客户库、导出文件、最近打开路径和查询历史都应按敏感数据管理。

部署方式选择

SQLite 的“部署”不是拉起一个数据库服务，而是决定数据库文件、运行进程、文件权限、连接策略、备份方式和清理边界。架构师要先判断它解决的是哪类问题。

方式	定义	适用场景	关键风险
本地文件库	应用或 CLI 直接读写一个 .db 文件	本地工具、脚本、桌面应用、开发样例、离线分析	文件路径混乱、权限错误、误提交数据、备份不一致
项目内开发库	每个开发者在本机生成独立 dev db	后端 demo、单元测试、轻量管理端、脚手架样例	旧文件污染新迁移、多人结果不一致
内存库	:memory: 或临时内存数据库	单元测试、快速验证、无持久化脚本	连接关闭即消失，多个连接不是同一库
CI 临时库	每个 CI job 生成独立数据库文件	迁移测试、DAO 测试、fixture 验证	并发 job 写同一文件、缓存污染、测试后未清理
容器内文件库	应用容器里使用 SQLite 文件，文件挂到 volume	单容器工具、演示环境、内部小工具	UID/GID、volume 路径、只备份 .db 不备份 WAL
只读模板库	预先生成 template.db，运行时只读打开或复制后写入	文档搜索索引、规则库、只读配置库、演示数据	误以为只读连接能防止文件被替换，模板版本失控
单机嵌入式生产	一个应用进程在单台机器上持久使用 SQLite	单机产品、边缘节点、桌面端、轻量内部工具	多写并发、恢复演练、文件系统、审计与权限能力不足
跨机器共享文件	多台机器通过共享盘访问同一个 .db 文件	通常不建议	锁不可靠、WAL 不适配、数据损坏和不可解释锁等待

选型口诀不是“量小就用 SQLite”。更准确的判断是：

数据和应用能否在同一台机器、同一个文件系统生命周期里管理。是否需要集中账号、权限、审计、连接池、监控、备份平台和高可用。写入是否由少数进程串行完成，还是需要很多服务同时写。

数据丢失、文件损坏或单机故障时，业务是否能接受恢复窗口。后续是否很可能演进成多服务共享数据库。

如果答案指向多服务、多租户、多写、高可用和集中治理，SQLite 不该被用作核心共享库。它可以继续用于本地 fixture、缓存、临时队列或只读索引，但主数据应进入 MySQL、PostgreSQL、SQL Server、Oracle 或云托管数据库。

官方预编译工具

Windows 可以从 SQLite 下载页获取与机器架构匹配的 sqlite-tools 压缩包，核对页面给出的 SHA3-256 后，解压并把 sqlite3.exe 放入团队约定目录，再把该目录加入 PATH。不要把下载目录、桌面路径或个人目录写进项目脚本。

验证：

sqlite3 --version
sqlite3 ".help"

Linux x64 与 macOS x64 / arm64 可以从 SQLite 下载页获取对应预编译工具，也可以使用发行版包管理器或 Homebrew。macOS 预编译工具是未签名二进制，下载页要求放入 PATH 后移除隔离属性；团队不能把这个动作扩成跳过任意二进制的 Gatekeeper 检查。包管理器不是 SQLite 项目发布的预编译包，版本可能滞后，编译选项也可能不同，所以安装后必须做能力验证：

xattr -d com.apple.quarantine /team/tools/sqlite3  # 仅 macOS 下载包
sqlite3 --version
sqlite3 ':memory:' 'PRAGMA compile_options;'

如果项目依赖 JSON、FTS5、RTree、扩展加载或特定 SQLite 版本，不能只写“安装 sqlite3 即可”。必须把最小版本、编译选项和验证命令写进项目文档。

语言内置或驱动

很多语言运行时自带 SQLite 绑定或常用驱动：

场景	入口	连接示例	注意
Python	官方 sqlite3 模块	sqlite3.connect("data/dev/app.db")	SQLite 库版本随 Python 构建变化，URI 需要 uri=True
Node.js	内置 node:sqlite 模块	new DatabaseSync("data/dev/app.db")	Node 24.15.0 起处于 release candidate 稳定级别；更低版本可能仍属 experimental 或没有该模块
Java	Xerial SQLite JDBC	jdbc:sqlite:data/dev/app.db	社区主流驱动，native 库、平台、JDBC 能力要在 CI 验证
CLI	sqlite3	sqlite3 data/dev/app.db	适合验证、导入导出、备份和排障
GUI	DB Browser for SQLite、SQLiteStudio	打开 .db 文件	GUI 不能代替脚本验证，保存连接和导出要防敏感数据

团队默认应该保留 CLI 验证脚本。只靠 GUI 操作，会让初始化、备份、恢复和 CI 复现都失去证据。

SQLite 没有一个像 my.cnf 或 postgresql.conf 那样统一的 server 配置文件。多数配置发生在连接级、数据库文件级或编译级。journal_mode 一类设置可以写入数据库文件，foreign_keys、busy_timeout 等则需要新连接逐一建立基线；混淆两者会造成 CLI 验证成功、应用连接却采用另一套语义。

文件路径

推荐路径：

data/dev/app.db
data/dev/app.db-wal
data/dev/app.db-shm
db/sqlite/init/001_schema.sql
db/sqlite/init/002_seed.sql
db/sqlite/verify/verify.sql
db/sqlite/backup/

不要使用：

app.db
test.db
~/Desktop/app.db
Downloads/app.db
/tmp/app.db

原因很实际：多人协作时，根目录里的 app.db 很容易被误提交；桌面和下载目录不可复现；/tmp 会被系统清理；路径不固定会让连接串、备份脚本和排障记录无法复用。

.gitignore 建议覆盖：

data/**/*.db
data/**/*.sqlite
data/**/*.sqlite3
data/**/*-wal
data/**/*-shm
db/sqlite/backup/*.db
db/sqlite/backup/*.sqlite

如果要提交 fixture，请单独放在 test/fixtures/sqlite/，并使用明确命名，例如 catalog-fixture-v1.db，不要和运行库共用路径。

PRAGMA 基线

开发环境可以用一份初始化 SQL 固化基线：

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA user_version = 1;
-- 示例值；真实项目应登记并固定自己的 application_id。
PRAGMA application_id = 1414745932;

CREATE TABLE IF NOT EXISTS schema_version (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  version INTEGER NOT NULL,
  updated_at TEXT NOT NULL
);

INSERT INTO schema_version(id, version, updated_at)
VALUES (1, 1, datetime('now'))
ON CONFLICT(id) DO UPDATE SET
  version = excluded.version,
  updated_at = excluded.updated_at;

这里的关键不是“这些参数永远最佳”，而是让团队知道它们的含义：

配置	作用	风险
foreign_keys=ON	当前连接启用外键检查	每个连接都要启用，迁移脚本和应用连接都不能漏
journal_mode=WAL	使用 WAL 日志模式	不适合网络文件系统，会产生 -wal / -shm
synchronous=NORMAL	在 WAL 场景下降低部分 fsync 成本	可用性和持久性取舍要按数据价值确认
busy_timeout=5000	遇到锁等待时最多等 5 秒	只能缓解短锁，不能解决并发写架构问题
user_version	记录应用 schema 版本	需要迁移脚本维护，不能只靠人工记忆
application_id	标识数据库文件用途	适合避免误打开错误文件，但要固定值

建议把运行时连接启动钩子写成“每次连接都执行”的逻辑，而不是只在 init SQL 里写一次。foreign_keys、busy_timeout 等是连接级习惯，换连接后需要重新设置。

URI 与只读连接

SQLite 支持 URI 文件名，可以表达只读、只在文件存在时打开、共享缓存等行为。

常用边界：

app.db
:memory:
file:app.db?mode=ro
file:app.db?mode=rwc
file:template.db?immutable=1

mode=ro 适合打开只读模板库，能避免应用误写；但它不能防止别人替换文件。immutable=1 表示调用方承诺文件不会变化，适合只读介质或随包分发资源；如果文件实际会变化，可能读到错误结果。

编译选项验证

不同渠道的 SQLite 可能启用不同能力。项目依赖前必须验证：

sqlite3 ':memory:' 'PRAGMA compile_options;'
sqlite3 ':memory:' "SELECT json_valid('{""ok"": true}');"
sqlite3 ':memory:' "CREATE VIRTUAL TABLE fts_doc USING fts5(title, body);"

如果命令失败，不要在业务代码里“试试看”。要把项目基线改成明确版本，或者移除对该能力的依赖。

下面是一套可落到 scripts/sqlite-verify.sh 的最小验证。目标不是证明 SQLite 强大，而是证明团队当前环境可以创建文件、启用约束、写入、读取、备份、恢复和清理。

创建目录：

mkdir -p data/dev db/sqlite/backup

确认版本：

sqlite3 --version
sqlite3 ':memory:' 'PRAGMA compile_options;'

创建数据库并查看文件：

sqlite3 data/dev/app.db ".databases"
sqlite3 data/dev/app.db "PRAGMA database_list;"

初始化 schema：

sqlite3 data/dev/app.db <<'SQL'
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;

CREATE TABLE IF NOT EXISTS project_user (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS project_note (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  body TEXT NOT NULL,
  created_at TEXT NOT NULL,
  FOREIGN KEY(user_id) REFERENCES project_user(id)
);

INSERT INTO project_user(id, username, created_at)
VALUES (1, 'demo', datetime('now'))
ON CONFLICT(id) DO UPDATE SET username = excluded.username;

INSERT INTO project_note(id, user_id, body, created_at)
VALUES (1, 1, 'hello sqlite', datetime('now'))
ON CONFLICT(id) DO UPDATE SET body = excluded.body;

SELECT u.id, u.username, n.body
FROM project_user u
JOIN project_note n ON n.user_id = u.id
ORDER BY u.id;
SQL

查询应返回一行 1|demo|hello sqlite。若没有结果，先检查实际打开的文件路径，再检查初始化事务是否提交。

随后故意写入一个不存在的 user_id，验证当前连接确实执行外键约束：

sqlite3 data/dev/app.db <<'SQL'
PRAGMA foreign_keys = ON;
INSERT INTO project_note(id, user_id, body, created_at)
VALUES (2, 999, 'must fail', datetime('now'));
SQL

预期失败证据是 FOREIGN KEY constraint failed，进程返回非零退出码；再执行下面的查询应得到 0，证明失败语句没有留下孤儿记录：

sqlite3 data/dev/app.db "SELECT count(*) FROM project_note WHERE id = 2;"

验证外键、WAL 和完整性：

sqlite3 data/dev/app.db "PRAGMA foreign_keys=ON; PRAGMA foreign_keys;"
sqlite3 data/dev/app.db "PRAGMA journal_mode;"
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(PASSIVE);"
sqlite3 data/dev/app.db "PRAGMA integrity_check;"
sqlite3 data/dev/app.db "PRAGMA foreign_key_check;"

验证 JSON 和 FTS 能力：

sqlite3 ':memory:' "SELECT json_valid('{""ok"":true}');"
sqlite3 ':memory:' "CREATE VIRTUAL TABLE fts_doc USING fts5(title, body); INSERT INTO fts_doc VALUES('SQLite','local file db'); SELECT rowid, title FROM fts_doc WHERE fts_doc MATCH 'local';"

备份和恢复：

sqlite3 data/dev/app.db ".backup 'db/sqlite/backup/app.backup.db'"
sqlite3 db/sqlite/backup/app.backup.db "PRAGMA integrity_check;"

sqlite3 data/dev/app.db "VACUUM INTO 'db/sqlite/backup/app.compact.db';"
sqlite3 db/sqlite/backup/app.compact.db "SELECT count(*) FROM project_user;"

清理测试对象：

sqlite3 data/dev/app.db <<'SQL'
PRAGMA foreign_keys = ON;
DELETE FROM project_note WHERE id = 1;
DELETE FROM project_user WHERE id = 1;
VACUUM;
SQL

如果是 Windows PowerShell，不建议一开始就把复杂 here-doc 塞进去。可以把 SQL 放入 db/sqlite/verify/verify.sql，然后执行：

sqlite3 data/dev/app.db ".read db/sqlite/verify/verify.sql"

Java

Java 项目常用 Xerial SQLite JDBC：

<dependency>
  <groupId>org.xerial</groupId>
  <artifactId>sqlite-jdbc</artifactId>
  <version>项目依赖锁定的版本</version>
</dependency>

连接字符串：

jdbc:sqlite:data/dev/app.db
jdbc:sqlite:file:data/dev/app.db?mode=ro

连接后必须执行基线：

PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

Java 项目要特别注意：

不要照搬 MySQL / PostgreSQL 的连接池配置。SQLite 是本地文件库，过多连接只会制造锁竞争。写操作建议串行化，或者在应用层集中到单写队列。每次应用启动都要确认数据库文件路径。相对路径在 IDE、测试、打包运行时可能不同。

native 库加载要在 Windows、macOS、Linux、CI 镜像里分别验证。迁移工具要用 SQLite 方言，不要默认沿用生产 MySQL / PostgreSQL DDL。

Python

Python 官方 sqlite3 模块适合脚本、测试和本地工具：

import sqlite3

conn = sqlite3.connect("data/dev/app.db")
conn.execute("PRAGMA foreign_keys = ON")
conn.execute("PRAGMA busy_timeout = 5000")

conn.execute("""
CREATE TABLE IF NOT EXISTS task(
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  created_at TEXT NOT NULL
)
""")
conn.execute(
    "INSERT INTO task(title, created_at) VALUES (?, datetime('now'))",
    ("demo",),
)
conn.commit()

rows = conn.execute("SELECT id, title FROM task ORDER BY id").fetchall()
print(rows)
conn.close()

只读 URI：

conn = sqlite3.connect("file:data/dev/app.db?mode=ro", uri=True)

Python 脚本不要把数据库文件默认放在当前工作目录。脚本被 CI、IDE、定时任务或容器调用时，cwd 可能完全不同。建议从项目根目录、环境变量或参数传入。

Node.js

Node 文档提供 node:sqlite 模块。项目是否能使用，要按锁定的 Node 版本和运行时策略验证：

import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("data/dev/app.db");
db.exec(`
  PRAGMA foreign_keys = ON;
  PRAGMA busy_timeout = 5000;
  CREATE TABLE IF NOT EXISTS task(
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT NOT NULL
  );
`);

const insert = db.prepare(
  "INSERT INTO task(title, created_at) VALUES (?, datetime('now'))",
);
insert.run("demo");

const rows = db.prepare("SELECT id, title FROM task ORDER BY id").all();
console.log(rows);
db.close();

如果项目 Node 版本不能使用官方模块，团队可以选择第三方驱动，但必须把维护状态、native 依赖、CI 平台、打包方式和安全更新写入项目文档。

迁移工具

SQLite 适合用迁移脚本管理 schema。不要让每个开发者手动点 GUI 改表。

推荐约束：

迁移文件按序号命名，例如 001_init.sql、002_add_index.sql。每个迁移在临时库跑通后再进入主分支。DDL 要用 SQLite 支持的语法，不要直接复制 MySQL / PostgreSQL。

外键、索引、唯一约束、触发器和 STRICT 表要通过 .schema 验证。迁移后跑 PRAGMA integrity_check; 和业务最小查询。schema 版本要写入 PRAGMA user_version 或迁移表，不要只靠文件名和人工记忆。

SQLite 的部分破坏性 DDL 需要“建新表、搬数据、重命名、重建索引和触发器”，这类迁移必须先在备份库演练。

典型重建表迁移骨架如下。PRAGMA foreign_keys 在事务内切换是 no-op，所以必须先在没有待处理 BEGIN 或 SAVEPOINT 的连接上关闭，提交后再恢复并检查；如果连接由框架或连接池管理，还要确认框架没有提前开启事务：

PRAGMA foreign_keys = OFF;
BEGIN IMMEDIATE;

CREATE TABLE project_user_new (
  id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
);

INSERT INTO project_user_new(id, username, created_at)
SELECT id, username, created_at
FROM project_user;

DROP TABLE project_user;
ALTER TABLE project_user_new RENAME TO project_user;
PRAGMA user_version = 2;

COMMIT;
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;

BEGIN IMMEDIATE 会提前申请写锁，适合让迁移脚本尽早失败，而不是执行到一半才发现无法写入。它不是高并发解法，只是让破坏性迁移的锁行为更可预期。迁移脚本必须在错误时执行 ROLLBACK，重新开启外键后要求 PRAGMA foreign_keys 返回 1，并且 PRAGMA foreign_key_check 不返回任何行；否则不要替换原库或发布新文件。

查看数据库：

sqlite3 data/dev/app.db ".databases"
sqlite3 data/dev/app.db ".tables"
sqlite3 data/dev/app.db ".schema"

格式化输出：

sqlite3 data/dev/app.db ".headers on" ".mode column" "SELECT * FROM project_user;"
sqlite3 data/dev/app.db ".mode json" "SELECT * FROM project_user;"

导入 SQL：

sqlite3 data/dev/app.db ".read db/sqlite/init/001_schema.sql"
sqlite3 data/dev/app.db ".read db/sqlite/init/002_seed.sql"

导入 CSV：

sqlite3 data/dev/app.db ".mode csv" ".import --skip 1 db/sqlite/init/user_seed.csv project_user"

导出 SQL：

sqlite3 data/dev/app.db ".dump" > db/sqlite/backup/app.dump.sql

备份数据库：

sqlite3 data/dev/app.db ".backup 'db/sqlite/backup/app.backup.db'"
sqlite3 data/dev/app.db "VACUUM INTO 'db/sqlite/backup/app.compact.db';"

恢复数据库：

sqlite3 data/dev/app.restored.db ".restore 'db/sqlite/backup/app.backup.db'"
sqlite3 data/dev/app.restored.db "PRAGMA integrity_check;"

检查 WAL 文件：

ls -lh data/dev/app.db*
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(PASSIVE);"
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(TRUNCATE);"

压缩与清理：

sqlite3 data/dev/app.db "VACUUM;"
sqlite3 data/dev/app.db "PRAGMA optimize;"
sqlite3 data/dev/app.db "ANALYZE;"

只读打开：

sqlite3 "file:data/dev/app.db?mode=ro" "SELECT count(*) FROM project_user;"

诊断编译能力：

sqlite3 ':memory:' 'PRAGMA compile_options;'

查看执行计划：

sqlite3 data/dev/app.db "EXPLAIN QUERY PLAN SELECT * FROM project_user WHERE username = 'demo';"

SQLite 的 EXPLAIN QUERY PLAN 不是 MySQL Explain 的同义替换。它足够帮助判断是否全表扫描、是否走索引，但不要照搬 MySQL 的 rows、type、extra 等解读方式。

观察容量、WAL 与恢复成本

SQLite 不收服务端实例费，但并非零成本。数据库主文件、WAL 峰值、备份副本、迁移临时文件、容器 volume 和设备磁盘都要进入预算；工程成本还包括锁竞争排查、加密授权、跨平台驱动验证和迁出服务端数据库的改造。

先在同一业务负载下记录文件与页指标：

sqlite3 data/dev/app.db "PRAGMA page_size; PRAGMA page_count; PRAGMA freelist_count; PRAGMA max_page_count;"
sqlite3 data/dev/app.db "PRAGMA wal_autocheckpoint; PRAGMA wal_checkpoint(PASSIVE);"
ls -lh data/dev/app.db*

page_size * page_count 是主文件逻辑页规模，freelist_count 表示可复用空闲页，不代表必须立刻 VACUUM。WAL 默认自动 checkpoint 阈值通常是 1000 页，但构建和运行时都可能改变它；wal_checkpoint(PASSIVE) 返回的 busy、日志页和已 checkpoint 页能帮助识别长读事务造成的 checkpoint 饥饿。不要把某次文件大小直接写成统一阈值，应观察每轮导入后的主文件增长、WAL 峰值、备份耗时、恢复耗时和 integrity_check 时长是否持续恶化。

普通 VACUUM 可能需要接近原库两倍的额外可用空间，并且会重建数据库；VACUUM INTO 生成一致快照且清除空闲页，但输出文件必须不存在或为空，任务意外中断时输出可能不完整。上线前应在接近真实大小的副本上测量磁盘峰值和恢复时间。若容量增长让备份、校验或单机恢复超过业务目标，或者 WAL 经常因长读事务无法推进，应优先拆分数据生命周期或迁出 SQLite，而不是把自动 checkpoint 和 busy_timeout 一路调大。

database is locked

常见原因：

一个事务长时间未提交。应用连接、游标或 GUI 窗口没有关闭。多个进程同时写同一个数据库文件。

读事务长期不结束，导致 WAL checkpoint 无法推进。数据库文件在网络盘、同步盘或容器异常挂载目录上。

排查：

sqlite3 data/dev/app.db "PRAGMA journal_mode;"
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(PASSIVE);"
sqlite3 data/dev/app.db "PRAGMA busy_timeout;"

处理思路：

先找长事务和未关闭连接。把写操作收敛到单进程或单队列。给短锁设置 busy_timeout。

避免多个 GUI、脚本、应用同时写。如果业务确实需要多进程高并发写，换服务端数据库。

busy_timeout 不是万能药。它只是让短时间锁等待不立刻失败，不能改变“同一文件单写者”的架构事实。

如果是迁移脚本或批量导入，建议显式事务：

BEGIN IMMEDIATE;
-- migration or batch writes
COMMIT;

这样可以提前暴露写锁竞争。若 BEGIN IMMEDIATE 经常拿不到锁，说明团队的写入模型已经超过 SQLite 舒适区。

外键没有生效

现象：

明明建了外键，孤儿记录仍然写入成功。测试环境能拦住，应用运行时拦不住。

判断：

sqlite3 data/dev/app.db "PRAGMA foreign_keys;"
sqlite3 data/dev/app.db "PRAGMA foreign_key_check;"

处理：

每个连接启动后执行 PRAGMA foreign_keys=ON;。迁移脚本、应用连接、测试连接和 GUI 操作都要执行。把外键验证纳入 CI，不只看建表语句。

WAL 文件被漏掉

现象：

备份恢复后数据少了一部分。复制 .db 文件到另一台机器后看不到最新写入。目录里出现 app.db-wal、app.db-shm，但团队不知道该不该提交或删除。

判断：

ls -lh data/dev/app.db*
sqlite3 data/dev/app.db "PRAGMA journal_mode;"
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(PASSIVE);"

处理：

运行中备份使用 .backup、Online Backup API 或 VACUUM INTO。不要只复制 .db 主文件。.db-wal 和 .db-shm 不提交仓库。

稳妥关闭所有连接后再做文件级迁移。

如果 app.db-wal 长期膨胀，通常不是“删掉 WAL 文件”这么简单，而是有长读事务、未关闭连接或 checkpoint 无法推进：

sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(PASSIVE);"
sqlite3 data/dev/app.db "PRAGMA wal_checkpoint(TRUNCATE);"

TRUNCATE 应在确认没有长事务和重要写入窗口后执行。它是运维动作，不是定时粗暴清理文件。

类型和日期错乱

SQLite 的类型亲和非常灵活。灵活带来便利，也会让团队在字段类型上偷懒。

高发问题：

把字符串写入数字字段，查询时还能成功。不同语言写入不同时间格式。金额用浮点数导致精度问题。

布尔值混用 true、false、0、1。

治理：

使用 STRICT 表时先检查项目实际加载的 SQLite 版本。日期时间统一为 UTC ISO8601 文本或 Unix timestamp。金额使用整数分或十进制定点文本。

用 CHECK 约束固化枚举、布尔、范围。迁移脚本里写清字段语义，不靠字段名猜。

主键也要有团队规范。大多数业务表使用 INTEGER PRIMARY KEY 就能获得 rowid 语义；AUTOINCREMENT 会改变 rowid 复用策略，并带来额外元数据维护成本，不要因为名字好听就默认加。

GUI 打开后应用失败

GUI 工具经常保持连接或事务，导致应用脚本锁等待。

处理：

写入测试前关闭 GUI。GUI 默认只读打开共享 fixture。重要操作使用 CLI 脚本，不以 GUI 成功为准。

不允许 GUI 保存生产或客户数据路径。

容器里路径和权限错误

现象：

容器里能启动，写库时报 permission denied。本机备份文件属于 root，开发者无法清理。重建容器后数据丢失。

处理：

services:
  app:
    image: your-app:dev
    user: "1000:1000"
    volumes:
      - ./data/dev:/workspace/data/dev
    environment:
      APP_DB_PATH: /workspace/data/dev/app.db

SQLite 在容器里不是“跑一个 sqlite 服务”，而是应用进程读写挂载目录里的文件。volume、UID/GID 和备份目录才是重点。

SQLite 本身没有服务端账号体系，不存在像 MySQL GRANT 或 PostgreSQL role 那样的数据库用户权限。它的权限主要来自文件系统、应用层和操作流程。

需要治理：

文件权限：谁能读 .db，谁能写目录，谁能删除备份。路径权限：应用是否可能通过参数打开任意本地文件。只读连接：报表、导出、检查工具默认 mode=ro。

GUI 工具：不能保存真实客户数据路径和导出记录。备份文件：.backup.db、.dump.sql、VACUUM INTO 输出都可能含敏感数据。加密边界：SQLite 核心库不等于加密方案，磁盘加密、SEE、SQLCipher 或平台密钥管理需要单独选型。

代理边界：SQLite 不通过 HTTP 代理、数据库代理或连接网关访问；如果需要远程访问，通常应该由应用服务封装 API，而不是共享 .db 文件。

权限检查：

ls -lh data/dev/app.db*
sqlite3 "file:data/dev/app.db?mode=ro" "SELECT count(*) FROM sqlite_master;"

团队要把“谁拥有数据库文件”写清楚。个人本地库由个人负责；共享 fixture 由项目 owner 负责；客户导出库禁止进入普通开发目录。

SQLite 用得好，会大幅提升本地开发、测试和工具脚本效率；用得差，会把单文件变成无主数据垃圾场。

命名规范

建议：

data/dev/{app-name}.db
data/ci/{job-id}.db
test/fixtures/sqlite/{domain}-fixture-v{n}.db
db/sqlite/backup/{app-name}-{yyyyMMdd-HHmmss}.backup.db

禁止：

test.db
new.db
tmp.db
prod-copy.db
customer.db

名字里必须体现环境、用途和是否 fixture。只写 test.db，三个月后没人敢删。

脚本规范

至少沉淀四类脚本：

scripts/sqlite-init.sh
scripts/sqlite-verify.sh
scripts/sqlite-backup.sh
scripts/sqlite-reset-dev.sh

脚本必须做环境确认：

case "$APP_ENV" in
  local|ci)
    ;;
  *)
    echo "Refuse to reset SQLite database outside local/ci."
    exit 1
    ;;
esac

本地重置可以删除 dev db；共享 fixture 和客户导出数据不能通过同一脚本删除。

CI 规范

CI 推荐每个 job 使用独立库：

export APP_DB_PATH="$RUNNER_TEMP/app-${GITHUB_RUN_ID}-${GITHUB_RUN_ATTEMPT}.db"
sqlite3 "$APP_DB_PATH" ".read db/sqlite/init/001_schema.sql"
sqlite3 "$APP_DB_PATH" ".read db/sqlite/verify/verify.sql"

不要把 CI 数据库缓存到全局目录。SQLite 文件很轻，重建通常比排查缓存污染更便宜。

什么时候迁出 SQLite

出现以下信号，就不要继续靠 SQLite 硬撑：

多个服务或多台机器需要同时写同一份主数据。写入锁等待成为常态，busy_timeout 越调越大。需要集中账号、行列权限、审计日志、数据库代理和统一运维。

需要主从、备份平台、PITR、跨 AZ 高可用和自动故障切换。数据模型开始依赖复杂 SQL、存储过程、并行查询、分区和大型索引治理。线上恢复演练不能接受“恢复一份单机文件”的方式。

迁出不是失败。SQLite 经常是从零到一的最好工具，但架构师要在它的边界到来前主动升级底座。

深水区	现象	判断入口	配置 / 命令 / 取舍
文件路径漂移	IDE 能跑，命令行或 CI 找不到表	打印绝对路径，查 PRAGMA database_list;	连接串使用项目根路径或环境变量，不依赖 cwd
误提交数据库文件	MR 里出现 .db、.db-wal、.dump.sql	git status --short、secret scan	.gitignore 覆盖运行库和备份，只允许脱敏 fixture
database is locked	偶发写失败，GUI 打开后更明显	查长事务、连接数、PRAGMA journal_mode;	关闭 GUI，写操作串行化，短锁用 busy_timeout，高并发写迁出
WAL 备份不一致	恢复后数据缺失	ls app.db*、PRAGMA wal_checkpoint(PASSIVE);	运行中使用 .backup / Backup API / VACUUM INTO
网络盘共享库	多机器访问同一文件后锁异常或损坏	文件路径是否在 NFS、SMB、同步盘	不跨机器共享写；需要共享数据库时换服务端数据库
外键未启用	孤儿记录写入成功	PRAGMA foreign_keys;、PRAGMA foreign_key_check;	每个连接启动后执行 PRAGMA foreign_keys=ON;
类型亲和误判	字符串写进数字列，排序和比较异常	typeof(column)、.schema	关键表用 STRICT、CHECK、统一数据格式
时间格式混乱	跨语言查询时间错乱	抽样查询时间字段	固定 UTC ISO8601 或 Unix timestamp，不混用本地时间
JSON 能力不可用	本地 SQL 成功，CI 报 no such function	PRAGMA compile_options;、SELECT json_valid(...)	固定 SQLite 基线或移除 JSON 依赖
FTS 能力不可用	创建 FTS5 虚拟表失败	CREATE VIRTUAL TABLE ... USING fts5	选择具备 FTS5 的构建，或改用搜索引擎
扩展加载风险	.load 失败或加载未知动态库	查 PRAGMA compile_options;、审查路径	默认禁用，扩展库入白名单，生产慎用
容器权限	容器内写库失败，备份文件归 root	ls -ln data/dev、容器用户	固定 user、volume、宿主目录权限
只读模板被污染	fixture 被测试改坏	使用 mode=ro 验证	模板只读打开，测试先复制再写
内存库误用	单测通过，集成测试没数据	连接字符串是否 :memory:	多连接共享需求不要用普通 :memory:
GUI 保存敏感数据	本地工具残留客户库路径	检查 GUI 历史连接和导出目录	客户数据只在隔离目录，导出必须脱敏
加密误解	以为 .db 天然安全	直接用十六进制或文本查看文件	核心库不自带透明加密，敏感数据另做加密方案
清理脚本误删	一条 reset 删除共享 fixture	脚本是否检查环境和路径	删除前校验 APP_ENV、路径前缀和 owner
SQLite 被当生产共享库	用户增长后锁、备份、审计都失控	看写并发、机器数、恢复要求	主数据迁到服务端数据库，SQLite 保留本地缓存或 fixture

深水区的核心结论是：SQLite 的风险不在安装，而在文件生命周期。路径、锁、WAL、备份、权限和边界没有治理，单文件就会从效率工具变成事故源。

合并与发布门禁

每次新增 SQLite 使用场景或升级驱动时逐项确认：

架构决策确认 SQLite 作为嵌入式文件数据库使用，没有把它伪装成服务端共享库。CLI、应用运行时与驱动实际加载的版本、WAL、备份、外键、类型、JSON 和 FTS 能力都有可重复探针。数据库文件使用固定绝对基准目录，不依赖进程当前工作目录。

.gitignore 阻止 .db、.db-wal、.db-shm、dump 和备份进入仓库。CI 执行 sqlite3 --version、PRAGMA compile_options; 和依赖能力探针。初始化脚本能够创建表、写入、读取并清理测试记录。

每个连接启用并验证 PRAGMA foreign_keys=ON;。WAL 的 -wal / -shm 生命周期、checkpoint 和网络文件系统限制进入备份与清理流程。.backup 或 VACUUM INTO 产物已恢复到新文件，并通过 integrity_check 与业务抽样查询。

database is locked 先定位长事务、未关闭连接和写者数量，没有只增加 timeout。Java、Python、Node 的连接字符串、初始化 PRAGMA 与关闭顺序已在各自运行时测试。GUI 默认只读打开共享 fixture，初始化、迁移和恢复仍由脚本与 CI 执行。

容器把 SQLite 当作应用文件依赖管理，volume、UID/GID、备份目录和删除顺序已经验证。敏感库采用独立的磁盘或数据库加密方案，并完成密钥与恢复演练。多机写、持续锁等待、集中权限或高可用需求出现时，已有迁出决策与数据迁移路径。

仓库扫描确认没有真实 .db、客户数据、内网路径、个人目录和敏感导出。

SQLite 的正确位置，是把本地数据能力做得极轻、极稳、极可复现。它越轻，越需要团队把边界写明白：该用它时别上重型数据库，该换底座时也别让一个单文件承担不该承担的组织复杂度。