在应用中连接 SQLite：Python、Node、Go、Java

连接只是一个打开的文件

SQLite 没有服务器。没有监听端口的守护进程，没有要连接的主机，也没有要验证的凭据。“连接”的意思就是驱动打开磁盘上的一个文件，开始读写其中的页。这就是全部的心智模型。

每种语言都有封装了 SQLite C 库的驱动。写法各不相同，但组成部分都一样：数据库文件的路径、一次打开调用、一个用来执行语句的句柄，以及用完后的一次关闭调用。

-- Conceptually, every driver does this:
-- 1. Open or create the file at the given path.
-- 2. Acquire a handle.
-- 3. Run SQL via prepared statements.
-- 4. Close the handle.

本页剩下的内容就是这些步骤在真实代码中的样子，以及在第一条查询之前值得设置好的几项配置。

Python：标准库中的 sqlite3

Python 自带 sqlite3，无需安装。基本写法如下：

-- Python
import sqlite3

conn = sqlite3.connect("app.db")
conn.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
conn.execute("INSERT INTO notes (body) VALUES (?)", ("first note",))
conn.commit()

for row in conn.execute("SELECT id, body FROM notes"):
    print(row)

conn.close()

有几点值得了解：

• 如果文件不存在，sqlite3.connect("app.db") 会创建它。传入 ":memory:" 可以得到一个只存在于内存中的数据库。
• sqlite3.connect("file:app.db?mode=ro", uri=True) 通过 URI 形式以只读方式打开。
• SQL 中的 ? 是占位符：要用参数绑定，绝不要拼接字符串。下一章会深入讲这一点。
• 必须调用 conn.commit()，除非你使用会自动提交的上下文管理器（with conn:）。

对于长时间运行的应用，设置一个忙等待超时，让并发写入时等待而不是报错：

-- Python
conn.execute("PRAGMA busy_timeout = 5000")   -- wait up to 5s
conn.execute("PRAGMA journal_mode = WAL")    -- better concurrency

Node.js：better-sqlite3

Node 生态中有好几个选择，但大多数团队用的是 better-sqlite3。它是同步的（这对 Node 来说听起来不对，但对 SQLite 其实更快，因为查询在几微秒内就能返回）。

-- Node.js
const Database = require("better-sqlite3");
const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");

const insert = db.prepare("INSERT INTO notes (body) VALUES (?)");
insert.run("first note");

const rows = db.prepare("SELECT id, body FROM notes").all();
console.log(rows);

db.close();

db.prepare(...) 返回一个可以重复使用的语句对象。.run() 用于写入，.all() 返回所有行，.get() 返回一行。和大多数 SQL 驱动的模式一样。

在启动时设置 pragma：

-- Node.js
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");   -- off by default, almost always wanted

foreign_keys = ON 值得特别说明：除非你明确要求，否则 SQLite 不会强制执行外键约束，而且这个设置是“按连接”生效的。如果忘了设置，你的 REFERENCES 子句就只是摆设。

Go：database/sql 加驱动

Go 的标准包 database/sql 与具体驱动无关。对于 SQLite，常见的选择是 modernc.org/sqlite（纯 Go 实现，不需要 CGO）和 github.com/mattn/go-sqlite3（需要 CGO）。

-- Go
import (
    "database/sql"
    _ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", "app.db?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
if err != nil { panic(err) }
defer db.Close()

_, err = db.Exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)")
_, err = db.Exec("INSERT INTO notes (body) VALUES (?)", "first note")

rows, _ := db.Query("SELECT id, body FROM notes")
defer rows.Close()
for rows.Next() {
    var id int; var body string
    rows.Scan(&id, &body)
    fmt.Println(id, body)
}

文件名后面的查询字符串是这个驱动在连接时传入 pragma 的方式。不同驱动的格式不一样，所以请查看你所选驱动的文档。

sql.Open 实际上并不会打开连接，第一条查询才会。db 是一个连接池。对于 SQLite，小连接池（对于写入频繁的负载，甚至用 db.SetMaxOpenConns(1)）通常是正确的选择。

Java：JDBC

标准驱动是 org.xerial:sqlite-jdbc。JDBC URL 的格式是 jdbc:sqlite:<path>：

-- Java
import java.sql.*;

try (Connection conn = DriverManager.getConnection("jdbc:sqlite:app.db")) {
    try (Statement st = conn.createStatement()) {
        st.execute("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
        st.execute("PRAGMA journal_mode = WAL");
        st.execute("PRAGMA busy_timeout = 5000");
    }

    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO notes (body) VALUES (?)")) {
        ps.setString(1, "first note");
        ps.executeUpdate();
    }

    try (PreparedStatement ps = conn.prepareStatement("SELECT id, body FROM notes");
         ResultSet rs = ps.executeQuery()) {
        while (rs.next()) System.out.println(rs.getInt(1) + " " + rs.getString(2));
    }
}

内存数据库：jdbc:sqlite::memory:。只读：在后面加上 ?open_mode=1，或者使用 SQLiteConfig 对象。

PHP：PDO

PDO 的 SQLite DSN 是 sqlite:<path>：

-- PHP
$db = new PDO("sqlite:app.db");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT)");
$db->exec("PRAGMA journal_mode = WAL");
$db->exec("PRAGMA busy_timeout = 5000");

$stmt = $db->prepare("INSERT INTO notes (body) VALUES (?)");
$stmt->execute(["first note"]);

foreach ($db->query("SELECT id, body FROM notes") as $row) {
    echo $row["id"] . " " . $row["body"] . "\n";
}

内存数据库用 sqlite::memory:。一定要把 ATTR_ERRMODE 设置为抛出异常，静默失败非常难调试。

连接字符串与文件路径

在各种驱动中，你会看到两种“连接字符串”：

• 普通路径：app.db、./data/app.db、/var/lib/myapp/app.db。相对路径是相对于进程的工作目录的，而这在生产环境中很少是你想要的。尽量使用绝对路径。
• URI 形式：file:app.db?mode=rwc&cache=shared。可以设置 mode=ro（只读）、mode=rwc（读写并创建，默认值）、cache=shared 和 nolock=1 等标志。

你会遇到的几个特殊值：

• :memory:：一个私有的内存数据库，每个连接各有一个。
• file::memory:?cache=shared：同一进程中多个连接可以共享的内存数据库。
• ""（空字符串）：一个私有的临时磁盘数据库，关闭时会被删除。

JDBC 在 URI 前加上 jdbc:sqlite: 前缀，PDO 用 sqlite:。Go 驱动和 Python 的 sqlite3 直接接受路径或 URI。

连接池怎么办？

SQLite 是单写入者数据库。任何时刻都只有一个连接持有写锁，其他连接都要等待。把很多写连接放进池里并不会让写入更快，只会让更多连接去争抢同一把锁。

尽管如此，小连接池在以下场景中仍然有用：

• WAL 模式下的并发读取，此时读操作之间互不阻塞，也不会阻塞写操作。
• 避免队头阻塞，即一条慢查询拖住整个应用。

Web 应用的合理默认配置：

• 开启 WAL 模式。
• busy_timeout 设为几秒，让争用时礼貌地等待而不是报错。
• 连接池大小为 1 个写连接加 N 个读连接；如果流量不大，只用一个共享连接也可以。
• 每个连接都开启外键约束。

-- Apply these on every new connection:
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;   -- safe with WAL; faster than FULL

synchronous = NORMAL 是与 WAL 的典型搭配：应用崩溃时数据不会丢，操作系统崩溃时保障稍弱，但比默认的 FULL 快得多。

关闭连接（以及为什么这很重要）

每个驱动都有关闭调用：conn.close()、db.Close()、db.close()。不关闭会泄漏文件描述符，还可能让 WAL 文件不断变大。

在长时间运行的服务中，更常见的模式是“整个进程生命周期内只用一个连接（或连接池）”，而不是每个请求都打开再关闭。打开 SQLite 连接的开销很小，但每次都重新设置 pragma 既浪费又容易忘。

-- Python - connection per process, reused across requests
DB = sqlite3.connect("app.db", check_same_thread=False)
DB.execute("PRAGMA journal_mode = WAL")
DB.execute("PRAGMA busy_timeout = 5000")
DB.execute("PRAGMA foreign_keys = ON")

具体到 Python，如果要在多个线程中使用同一个连接，就需要 check_same_thread=False，而且你还需要一把锁或一个连接池来串行化调用。

上线前的检查清单

在让真实流量访问 SQLite 数据库之前：

• 为数据库文件使用绝对路径。
• 开启 WAL 模式（PRAGMA journal_mode = WAL）。
• 把 busy_timeout 设为 2 至 10 秒。
• 在每个连接上开启外键约束。
• 使用带参数绑定的预处理语句，绝不要用字符串插值。
• 确保数据库所在目录对进程可写（在 WAL 模式下，SQLite 会在主文件旁边写一个 -wal 文件和一个 -shm 文件）。
• 在需要备份之前就想好备份方案：VACUUM INTO 和 .backup 命令会在后面讲到。

下一篇：迁移

连接是简单的部分。更难的是在不手动修改生产数据库的前提下，随着时间演进表结构。迁移能把 ALTER TABLE 变成可重复、可版本控制的流程，这就是下一页的内容。