BattleMonAgent/doc/better-sqlite3.md

下面是一份结合目前（约 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 桌面应用、嵌入式设备、日志收集服务等），可以再描述一下，我可以基于这些最佳实践帮你落到更贴近场景的代码模板与配置。