鱼梦江湖 @Yumengjh

简体中文

English

简体中文

English

外观

pnpm + SQLite（高性能 better-sqlite3）完整安装步骤

复制页面

约 1570 字大约 5 分钟

2026-01-15

初始化项目

mkdir sqlite-demo
cd sqlite-demo
pnpm init

安装 better-sqlite3（关键：允许 build scripts）

pnpm add better-sqlite3

如果你看到类似提示：

Ignored build scripts: better-sqlite3 Run "pnpm approve-builds" ...

那就执行：

pnpm approve-builds
pnpm rebuild better-sqlite3

解释：better-sqlite3 是原生模块，需要安装脚本去下载/编译 .node 文件；pnpm 出于安全默认可能禁用脚本，所以要 approve。

写一个最小可用 smoke 测试（会生成 .db）

新建文件：smoke.js

const Database = require("better-sqlite3");
const path = require("path");
const fs = require("fs");

const dbPath = path.join(process.cwd(), "data", "app.db");
fs.mkdirSync(path.dirname(dbPath), { recursive: true });

console.log("cwd:", process.cwd());
console.log("dbPath:", dbPath);

const db = new Database(dbPath);

// 推荐的性能配置（常用平衡点）
db.pragma("journal_mode = WAL");
db.pragma("synchronous = NORMAL");
db.pragma("foreign_keys = ON");

db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    name TEXT,
    created_at TEXT DEFAULT (datetime('now'))
  );
`);

const insert = db.prepare("INSERT INTO users(email, name) VALUES (?, ?)");
insert.run("a@example.com", "Alice");

const row = db.prepare("SELECT * FROM users WHERE email=?").get("a@example.com");
console.log("selected:", row);

db.close();
console.log("done");
node smoke.js

终端输出 selected 的数据

文件生成：./data/app.db

如果开了 WAL，还会看到 app.db-wal 和 app.db-shm（正常！）

性能测试（事务批量写入）

// bench.js
const Database = require("better-sqlite3");
const path = require("path");
const fs = require("fs");

const dbPath = path.join(process.cwd(), "data", "bench.db");
fs.mkdirSync(path.dirname(dbPath), { recursive: true });

const db = new Database(dbPath);

db.pragma("journal_mode = WAL");
db.pragma("synchronous = NORMAL");
db.pragma("foreign_keys = ON");

// 为了压测干净，重建表
db.exec(`
  DROP TABLE IF EXISTS events;
  CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts INTEGER NOT NULL,
    kind TEXT NOT NULL,
    payload TEXT NOT NULL
  );
  CREATE INDEX idx_events_ts ON events(ts);
`);

const insert = db.prepare("INSERT INTO events(ts, kind, payload) VALUES (?, ?, ?)");

// 关键：事务包裹批量写入，速度差一个数量级
const insertMany = db.transaction((n) => {
  for (let i = 0; i < n; i++) {
    insert.run(Date.now(), "click", JSON.stringify({ i, ok: true }));
  }
});

const N = Number(process.argv[2] || 100000);

console.time(`insert ${N}`);
insertMany(N);
console.timeEnd(`insert ${N}`);

console.time("query last 10");
const rows = db.prepare("SELECT id, ts, kind FROM events ORDER BY id DESC LIMIT 10").all();
console.timeEnd("query last 10");

console.log("last10:", rows);

db.close();
console.log("db:", dbPath);

跑 10 万条：

node bench.js 100000

或者：

node bench.js 1000000

CLI 操作

如果是 Win10 / Win11（基本都有 winget）：

winget install SQLite.SQLite

装完立刻验证：

sqlite3 --version

然后直接用你的 db：

sqlite3 F:\sqlite-demo\data\app.db

万一没包管理器：官网下载（最原始）

  1. 打开官网：https://www.sqlite.org/download.html
  2. 找 “sqlite-tools-win-x64”
  3. 下载 zip → 解压
  4. 把解压目录加到 PATH
  5. 终端里：
sqlite3 your.db

常用命令

假设有两张表：users、orders

先把 CLI 显示调舒服

进入 sqlite3 your.db 之后先敲：

.headers on
.mode box
.timer on

看数据库里有什么

.tables                 -- 所有表
.schema                 -- 所有建表 SQL
.schema users           -- 某张表的建表 SQL
.databases              -- 当前打开的库/附加库

建表（CREATE TABLE）

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  total INTEGER NOT NULL,
  created_at TEXT DEFAULT (datetime('now')),
  FOREIGN KEY(user_id) REFERENCES users(id)
);

插入数据（INSERT）

插一条：

INSERT INTO users(email, name) VALUES ('a@example.com', 'Alice');

插多条：

INSERT INTO users(email, name) VALUES
('b@example.com', 'Bob'),
('c@example.com', 'Cindy');

给 orders 插两条（假设 user_id=1 存在）：

INSERT INTO orders(user_id, total) VALUES (1, 199);
INSERT INTO orders(user_id, total) VALUES (1, 35);

查询（SELECT）

查全部（慎用，表大时会爆屏）：

SELECT * FROM users;

常用：限制行数 + 排序：

SELECT * FROM users ORDER BY id DESC LIMIT 20;

条件查询：

SELECT id, email, name
FROM users
WHERE email = 'a@example.com';

模糊匹配：

SELECT * FROM users
WHERE email LIKE '%@example.com';

分页（第 2 页，每页 10 条）：

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 10;

更新（UPDATE）

UPDATE users
SET name = 'Alice Updated'
WHERE email = 'a@example.com';

更新多列：

UPDATE users
SET name = 'Bob Updated', email = 'bob@example.com'
WHERE id = 2;

删除（DELETE）

删某条：

DELETE FROM users WHERE id = 3;

清空表（删除所有行）：

DELETE FROM users;

注意：SQLite 没有 TRUNCATE TABLE，清空一般用 DELETE FROM table;

联表查询（JOIN）

查用户和订单：

SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY o.id DESC
LIMIT 20;

LEFT JOIN（没订单的用户也显示）：

SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id;

分组统计（GROUP BY）

统计每个用户的订单数和总金额：

SELECT u.id, u.name,
       COUNT(o.id) AS order_count,
       COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

UPSERT（有就更新，没有就插入）

按 email 去重：

INSERT INTO users(email, name)
VALUES ('a@example.com', 'Alice NewName')
ON CONFLICT(email) DO UPDATE SET
  name = excluded.name;

索引（性能相关）

给常查字段建索引：

CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

看有哪些索引：

SELECT name, tbl_name, sql
FROM sqlite_master
WHERE type='index'
ORDER BY name;

快速查看“表里有多少行”

SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM orders;

SQLite 非常适合做“个人私密数据仓库”，但前提是：你必须给它加“锁”（加密 + 操作规范）。

裸 SQLite ≈ 便携 U 盘

加密 SQLite ≈ 本地密码保险箱

便携性：完美

  • 一个 .db 文件就是全部
  • 拷走、隐藏、备份都极其方便
  • 不依赖服务、端口、后台进程

易隐藏：非常强

  • 文件名可以随便改（data.bin、.cache、.sys）
  • 放在任意目录
  • 不跑服务、不监听端口，攻击面极小

本地安全性：取决于你有没有加密

  • 未加密 SQLite： 任何人拿到文件 = 直接看明文
  • 加密 SQLite： 没密码 ≈ 一坨随机噪声

三种安全等级方案

方案 1：字段级加密（最低门槛）

用普通 SQLite，但敏感字段自己加密。

用 AES / libsodium，存之前加密，读出来再解密。

INSERT INTO secrets(key, value_encrypted) VALUES (?, encrypt(value));

优点：简单，不依赖特殊 SQLite 版本

缺点：schema / 元数据是明文，容易犯错（忘记加密某字段）

适合：临时/轻量敏感信息

方案 2（强烈推荐）：SQLCipher（整库加密）

这是专业级方案。SQLCipher = 加密版 SQLite（行业标准）

特点

整个 .db 文件 AES-256 加密，没密码完全打不开，表名 / 索引 / 数据 全部加密，被：Signal，WhatsApp，各类密码管理器大量使用

使用体验上，几乎还是 SQLite：

安全性：文件被偷 ≈ 没用，只要密码不泄露，安全性很高

适合：账号密码 / API Key / 私钥 / 个人机密

方案 3：SQLite + OS 安全（最高）

组合拳：

  • SQLCipher（库加密）
  • 主密码只存在内存
  • 数据库文件权限最小化
  • 磁盘再加 BitLocker / FileVault

这是“本地密码保险箱”级别