476 lines
12 KiB
Markdown
476 lines
12 KiB
Markdown
下面是一份结合目前（约 2026 年）社区共识的 **better-sqlite3 使用指南 + 最佳实践实战版**，你可以直接拿来做项目的参考模板。
|
||
|
||
---
|
||
|
||
# better-sqlite3 使用指南（含最佳实践）
|
||
|
||
## 一、定位与总体建议
|
||
|
||
### 1.1 为什么选 better-sqlite3
|
||
|
||
在 Node.js 中操作 SQLite，目前主流选型是：
|
||
|
||
- 新项目首选 **better-sqlite3**
|
||
- Node.js 24+ 内置的 `node:sqlite` 适合：轻量脚本、小工具、对依赖敏感的场景
|
||
- 旧项目上的 `node-sqlite3` 已被弃用，不建议新项目继续使用
|
||
|
||
**better-sqlite3 的优点：**
|
||
|
||
- **同步 API**：代码简单可读，事务逻辑不被 Promise/回调搅乱
|
||
- **性能优秀**：普遍被认为是 Node 生态里最快的 SQLite 绑定之一
|
||
- **完整事务支持**：内置事务封装，写多步业务逻辑非常顺手
|
||
- **PRAGMA / 备份等高级能力完备**
|
||
|
||
安装：
|
||
|
||
```bash
|
||
npm install better-sqlite3
|
||
```
|
||
|
||
---
|
||
|
||
## 二、连接管理：一个进程一个连接
|
||
|
||
### 2.1 最佳实践：进程级单例
|
||
|
||
SQLite 本质是操作一个文件；和 MySQL/Postgres 不同，连接很轻量，不需要连接池。
|
||
|
||
**推荐模式：**
|
||
|
||
- 应用启动时创建 **一个全局 Database 实例**
|
||
- 全程复用，不要在每个请求里频繁 open/close
|
||
- 在进程退出前再统一 close（很多服务甚至不显式 close）
|
||
|
||
```js
|
||
// db/index.js
|
||
import Database from 'better-sqlite3';
|
||
|
||
let db;
|
||
|
||
export function getDB() {
|
||
if (!db) {
|
||
db = new Database('app.sqlite', {
|
||
readonly: false,
|
||
fileMustExist: false,
|
||
timeout: 5000, // 等待锁超时时间
|
||
// verbose: console.log // 调试时可打开
|
||
});
|
||
|
||
// 基础 PRAGMA，见后文性能调优部分
|
||
db.pragma('journal_mode = WAL');
|
||
db.pragma('synchronous = NORMAL');
|
||
db.pragma('cache_size = -64000'); // ~64MB
|
||
db.pragma('temp_store = MEMORY');
|
||
}
|
||
return db;
|
||
}
|
||
```
|
||
|
||
**反例（不要这样写）：**
|
||
|
||
```js
|
||
// ❌ 每次请求新建 & 关闭连接
|
||
function handler(req, res) {
|
||
const db = new Database('app.sqlite');
|
||
const row = db.prepare('SELECT * FROM users WHERE id = ?').get(req.params.id);
|
||
db.close();
|
||
res.json(row);
|
||
}
|
||
```
|
||
|
||
问题：
|
||
|
||
- 无谓的 open/close 开销
|
||
- 预编译语句无法复用
|
||
- 容易出现并发锁和资源浪费
|
||
|
||
### 2.2 多线程（Worker Threads）场景
|
||
|
||
当读操作很多、单线程成为瓶颈时：
|
||
|
||
- 使用 **Worker Threads**
|
||
- **每个 worker 线程自己 new 一个 Database 实例**
|
||
- 不在多个线程之间共享同一个 `Database` 对象
|
||
|
||
示意：
|
||
|
||
```js
|
||
// workers/readWorker.js
|
||
import { workerData, parentPort } from 'node:worker_threads';
|
||
import Database from 'better-sqlite3';
|
||
|
||
const db = new Database('app.sqlite', {
|
||
readonly: true,
|
||
fileMustExist: true,
|
||
timeout: 3000
|
||
});
|
||
|
||
db.pragma('cache_size = -32000');
|
||
db.pragma('temp_store = MEMORY');
|
||
db.pragma('query_only = ON'); // 防止误写
|
||
|
||
const stmt = db.prepare(workerData.sql);
|
||
const result = stmt.all(...(workerData.params || []));
|
||
parentPort.postMessage(result);
|
||
```
|
||
|
||
---
|
||
|
||
## 三、查询与预编译语句（Prepared Statements）
|
||
|
||
### 3.1 必须坚持使用预编译
|
||
|
||
**目标：同时保证性能和安全（防 SQL 注入）**。
|
||
|
||
```js
|
||
const db = getDB();
|
||
|
||
// 预编译语句在模块级缓存
|
||
const getUserById = db.prepare('SELECT * FROM users WHERE id = ?');
|
||
const listUsersByEmail = db.prepare('SELECT * FROM users WHERE email = ?');
|
||
|
||
export function findUserById(id) {
|
||
return getUserById.get(id); // 单行
|
||
}
|
||
|
||
export function findUsersByEmail(email) {
|
||
return listUsersByEmail.all(email); // 多行
|
||
}
|
||
```
|
||
|
||
**防注入反例：**
|
||
|
||
```js
|
||
// ❌ 直接拼字符串，存在注入风险
|
||
const sql = `SELECT * FROM users WHERE email = '${email}'`;
|
||
db.prepare(sql).all();
|
||
|
||
// ✅ 使用参数绑定
|
||
db.prepare('SELECT * FROM users WHERE email = ?').all(email);
|
||
```
|
||
|
||
### 3.2 get / all / run / exec 的使用场景
|
||
|
||
| 方法 | 用途 | 返回值典型用法 |
|
||
|--------|-----------------------|--------------------------------------|
|
||
| `get` | 查询**一条**记录 | `const row = stmt.get(id)` |
|
||
| `all` | 查询**多条**记录 | `const rows = stmt.all(status)` |
|
||
| `run` | INSERT/UPDATE/DELETE | `const info = stmt.run(...params)` |
|
||
| `exec` | 执行多条 SQL/DDL 语句 | `db.exec('CREATE TABLE ...; ...')` |
|
||
|
||
示例：
|
||
|
||
```js
|
||
const db = getDB();
|
||
|
||
const findActiveUsers = db.prepare('SELECT * FROM users WHERE active = 1');
|
||
const deactivateUser = db.prepare('UPDATE users SET active = 0 WHERE id = ?');
|
||
|
||
// 多行
|
||
const users = findActiveUsers.all();
|
||
|
||
// 更新
|
||
const info = deactivateUser.run(123);
|
||
console.log(info.changes); // 受影响行数
|
||
console.log(info.lastInsertRowid); // 如是INSERT语句则有意义
|
||
```
|
||
|
||
### 3.3 善用 RETURNING 减少一次查询
|
||
|
||
如果 SQLite 版本支持 `RETURNING`：
|
||
|
||
```js
|
||
const createOrder = db.prepare(`
|
||
INSERT INTO orders (user_id, amount)
|
||
VALUES (?, ?)
|
||
RETURNING id, user_id, amount, created_at
|
||
`);
|
||
|
||
const order = createOrder.get(userId, amount);
|
||
// 这里已经拿到刚插入的订单信息，无需再额外 SELECT
|
||
```
|
||
|
||
---
|
||
|
||
## 四、事务管理：写操作的必备武器
|
||
|
||
任何「多步写入」逻辑（比如：下单、转账、批量导入）都应该放在事务里。
|
||
|
||
### 4.1 使用 better-sqlite3 的事务封装
|
||
|
||
```js
|
||
const db = getDB();
|
||
|
||
const placeOrder = db.transaction((userId, items) => {
|
||
const insertOrder = db.prepare(`
|
||
INSERT INTO orders (user_id, status)
|
||
VALUES (?, 'pending')
|
||
`);
|
||
const insertOrderItem = db.prepare(`
|
||
INSERT INTO order_items (order_id, product_id, qty, price)
|
||
VALUES (?, ?, ?, ?)
|
||
`);
|
||
const updateStock = db.prepare(`
|
||
UPDATE products
|
||
SET stock = stock - ?
|
||
WHERE id = ?
|
||
`);
|
||
|
||
const { lastInsertRowid: orderId } = insertOrder.run(userId);
|
||
|
||
for (const item of items) {
|
||
insertOrderItem.run(orderId, item.productId, item.qty, item.price);
|
||
updateStock.run(item.qty, item.productId);
|
||
}
|
||
|
||
return orderId;
|
||
});
|
||
|
||
// 使用
|
||
try {
|
||
const orderId = placeOrder(userId, items);
|
||
// 成功自动 COMMIT
|
||
} catch (err) {
|
||
// 抛出异常时自动 ROLLBACK
|
||
console.error('placeOrder 失败', err);
|
||
}
|
||
```
|
||
|
||
**注意事项：**
|
||
|
||
- 事务函数体 **必须是同步代码**，不要在里面 `await`/Promise/`setTimeout`
|
||
- 所有需要原子性的写操作都要包进同一次 `db.transaction()`
|
||
- 对于批量插入/更新，**一个大事务远快于 N 个小事务**
|
||
|
||
---
|
||
|
||
## 五、性能调优：常用 PRAGMA 配置
|
||
|
||
### 5.1 典型 Web 服务推荐配置
|
||
|
||
在应用启动时统一设置一次：
|
||
|
||
```js
|
||
const db = getDB();
|
||
|
||
// 提升并发 & 写性能：建议生产使用
|
||
db.pragma('journal_mode = WAL'); // 写前日志
|
||
|
||
// 写安全性 & 性能折中
|
||
db.pragma('synchronous = NORMAL'); // 不要贸然用 OFF
|
||
|
||
// 内存缓存（负数表示 KiB）
|
||
db.pragma('cache_size = -64000'); // ≈64MB
|
||
|
||
// 临时对象走内存
|
||
db.pragma('temp_store = MEMORY');
|
||
|
||
// 利用 mmap 加速 IO（根据机器内存调整）
|
||
db.pragma('mmap_size = 268435456'); // 256MB
|
||
```
|
||
|
||
**不推荐在有写入的生产环境用 `synchronous = OFF`**，否则断电/崩溃时有丢数据风险。
|
||
|
||
### 5.2 读密集型 Worker 优化
|
||
|
||
针对只读 worker：
|
||
|
||
```js
|
||
const db = new Database('app.sqlite', { readonly: true, fileMustExist: true });
|
||
|
||
db.pragma('cache_size = -32000'); // ~32MB
|
||
db.pragma('temp_store = MEMORY');
|
||
db.pragma('mmap_size = 1073741824'); // ~1GB
|
||
db.pragma('query_only = ON'); // 强制只读
|
||
```
|
||
|
||
### 5.3 查询计划与优化
|
||
|
||
数据库结构或索引大改之后，可以执行一次：
|
||
|
||
```js
|
||
db.exec('ANALYZE;');
|
||
db.pragma('optimize');
|
||
```
|
||
|
||
有需要时，用 `EXPLAIN QUERY PLAN` 分析慢查询（开发/排查时用）。
|
||
|
||
---
|
||
|
||
## 六、安全实践：防注入、防误删、防泄露
|
||
|
||
### 6.1 防 SQL 注入
|
||
|
||
核心原则：
|
||
|
||
- 所有用户输入**必须**通过「参数绑定」进入 SQL
|
||
- 不拼接字符串、不直接插入变量
|
||
|
||
```js
|
||
// ✅ 推荐使用形式
|
||
const stmt = db.prepare('SELECT * FROM users WHERE email = ?');
|
||
const user = stmt.get(email);
|
||
```
|
||
|
||
### 6.2 参数校验
|
||
|
||
在进入 SQL 前尽量校验类型 / 长度 / 格式：
|
||
|
||
```js
|
||
function assertId(id) {
|
||
if (!Number.isInteger(id) || id <= 0) {
|
||
throw new Error('非法ID');
|
||
}
|
||
}
|
||
```
|
||
|
||
### 6.3 SQLite 文件存放与权限
|
||
|
||
- **不要**把 `.sqlite` 文件放在可被 Web 服务器直接访问的静态目录下
|
||
- 赋予最小权限：如仅运行用户可读写
|
||
|
||
```bash
|
||
chmod 600 app.sqlite
|
||
chown node_user:node_group app.sqlite
|
||
```
|
||
|
||
---
|
||
|
||
## 七、备份与恢复
|
||
|
||
### 7.1 使用 better-sqlite3 的备份 API（在线备份）
|
||
|
||
```js
|
||
const db = getDB();
|
||
|
||
// 备份到另一个文件（热备，无需停服务）
|
||
await db.backup('backup.sqlite');
|
||
```
|
||
|
||
你可以把这段逻辑放进定时任务里，例如每天低峰执行一次。
|
||
|
||
### 7.2 冷备份（停机拷贝）
|
||
|
||
如果可以短暂停机：
|
||
|
||
1. 停止 Node 进程
|
||
2. 直接复制数据库文件
|
||
|
||
```bash
|
||
cp app.sqlite app.sqlite.bak
|
||
```
|
||
|
||
在 WAL 模式下，确保 `.sqlite` 和相关的 `-wal`/`-shm` 文件也一并妥善处理（备份 API 已经帮你做了这件事，一般更推荐）。
|
||
|
||
---
|
||
|
||
## 八、迁移与工程化
|
||
|
||
### 8.1 简单迁移系统示例
|
||
|
||
建议至少维护一个简易迁移机制，例如：
|
||
|
||
- 建一张 `migrations` 表，记录已执行的版本
|
||
- 在应用启动时按顺序执行 `migrations/*.sql`
|
||
|
||
伪代码：
|
||
|
||
```js
|
||
// db/migrate.js
|
||
import { getDB } from './index.js';
|
||
import fs from 'node:fs';
|
||
import path from 'node:path';
|
||
|
||
export function runMigrations() {
|
||
const db = getDB();
|
||
db.exec(`CREATE TABLE IF NOT EXISTS migrations (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT UNIQUE NOT NULL,
|
||
run_at TEXT NOT NULL
|
||
)`);
|
||
|
||
const applied = new Set(
|
||
db.prepare('SELECT name FROM migrations').all().map(r => r.name)
|
||
);
|
||
|
||
const dir = path.join(process.cwd(), 'src/db/migrations');
|
||
const files = fs.readdirSync(dir).sort(); // 如：001_init.sql, 002_add_user.sql
|
||
|
||
const runMigration = db.transaction((name, sql) => {
|
||
db.exec(sql);
|
||
db.prepare('INSERT INTO migrations (name, run_at) VALUES (?, datetime(\'now\'))')
|
||
.run(name);
|
||
});
|
||
|
||
for (const file of files) {
|
||
if (applied.has(file)) continue;
|
||
const sql = fs.readFileSync(path.join(dir, file), 'utf8');
|
||
runMigration(file, sql);
|
||
console.log('已执行迁移：', file);
|
||
}
|
||
}
|
||
```
|
||
|
||
### 8.2 典型项目结构建议
|
||
|
||
```txt
|
||
src/
|
||
db/
|
||
index.js # 初始化 & 导出 db 实例 + PRAGMA
|
||
migrate.js # 迁移逻辑
|
||
migrations/ # *.sql 文件
|
||
models/
|
||
user.js # 用户相关 SQL & 事务
|
||
order.js
|
||
workers/
|
||
readWorker.js # 高并发只读 worker
|
||
services/
|
||
userService.js # 业务逻辑。只调用 models，不直接写 SQL
|
||
app.js # 应用入口，启动HTTP服务 & 调用 runMigrations()
|
||
```
|
||
|
||
---
|
||
|
||
## 九、与 Node.js 内置 `node:sqlite` 的关系
|
||
|
||
- Node.js 24 LTS 起，内置了 `node:sqlite` 模块，方便做无依赖的小工具
|
||
- 但在实际生产服务里：
|
||
- **性能、特性、生态** 综合考虑，better-sqlite3 依然是首选
|
||
- 两者 API 思路类似：同步操作、小而美，更方便从一种迁移到另一种
|
||
|
||
可以简记为：
|
||
|
||
> **重业务 / 生产服务：better-sqlite3**
|
||
> **简单脚本 / 工具：node:sqlite 也可以考虑**
|
||
|
||
---
|
||
|
||
## 十、一页小抄（速查版）
|
||
|
||
1. **连接管理**：
|
||
- 进程内只创建一个 `Database` 实例，全局复用
|
||
- Worker Threads 中，一线程一实例，不共享连接
|
||
|
||
2. **查询写法**：
|
||
- 所有 SQL 使用 **prepared statement + 参数绑定**
|
||
- 单行用 `get`，多行用 `all`，写操作用 `run`
|
||
|
||
3. **事务**：
|
||
- 多步写操作必须放在 `db.transaction()` 中
|
||
- 事务函数里面不要写任何异步代码
|
||
|
||
4. **PRAGMA 调优**：
|
||
- `journal_mode = WAL`
|
||
- `synchronous = NORMAL`
|
||
- 合理设置 `cache_size` / `temp_store = MEMORY` / `mmap_size`
|
||
|
||
5. **安全**：
|
||
- 禁止拼接 SQL 字符串
|
||
- 校验输入
|
||
- 数据库文件不要暴露在静态目录，限制文件权限
|
||
|
||
6. **扩展读性能**：
|
||
- 使用 Worker Threads
|
||
- 每个 worker 使用只读连接 + `query_only = ON`
|
||
|
||
如果你后续有更具体的场景（例如 Electron 桌面应用、嵌入式设备、日志收集服务等），可以再描述一下，我可以基于这些最佳实践帮你落到更贴近场景的代码模板与配置。 |