@beenotung/better-sqlite3-helper

一个用于 better-sqlite3（"Node.js 中最快、最简洁的 SQLite3 库"）的 Node.js 封装库。它专为简单的 Node.js 服务端应用设计，提供了一些新函数和数据库迁移系统。

本包 fork 自 Kauto 的 better-sqlite3-helper。大多数改动已回合，但部分 pull request 仍在等待中。

4.x 版本新特性

• 破坏性变更：fork 为 @beenotung/better-sqlite3-helper 发布到 npm
• 破坏性变更：现在使用 better-sqlite3 版本 12。
• 新功能：将 @types/better-sqlite3 添加为可选和对等依赖
• 新功能：建立数据库连接时自动配置同步 pragma
• 修复：修正创建 DB 新实例的类型签名
• 杂项：不再依赖 mkdirp，改用 fs 内置版本

3.0 版本新特性

现在使用 better-sqlite3 第 7 版。这意味着 "memory" 选项已被移除（请改用路径 :memory:——在第 2 版中同样有效），并且不再支持 Node.js 10 以下的版本。对于旧版 Node.js，可继续使用本库的第 2 版。

2.0 版本新特性

现在可以使用 better-sqlite3 第 5 版的全部命令（如 function 和 backup）。第 4 版的命令已移除。此外，本库现已提供 TypeScript 声明文件。

如何安装

例如，使用以下命令安装：

npm i @beenotung/better-sqlite3-helper

如何使用

在任意需要访问 SQLite3 数据库的文件中，直接引入库并使用即可。

anyServerFile.js

import DB from '@beenotung/better-sqlite3-helper'

let row = new DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
console.log(row.firstName, row.lastName, row.email);

要初始化数据库，请在程序根目录的 migrations 文件夹中创建一个名为 001-init.sql 的 sql 文件。

~/migrations/001-init.sql

-- Up
CREATE TABLE `users` (
  id INTEGER PRIMARY KEY,
  firstName TEXT NOT NULL,
  lastName TEXT NOT NULL,
  email TEXT NOT NULL
);

-- Down
DROP TABLE IF EXISTS `users`;

就这么简单！

全局单例

一个普通的简单应用通常只使用一个数据库。为了简化类的管理，本库以单例模式为你处理访问控制。（当然，你也可以创建新实例来访问其他数据库。）

数据库采用懒加载方式。只有在第一次使用时，才会从文件中读取数据库、启动迁移并设置 WAL 日志模式。数据库的默认路径为 './data/sqlite3.db'。

如果想修改默认值，可以在服务端代码的入口处调用一次库来进行配置：

index.js

import DB from '@beenotung/better-sqlite3-helper'

// 第一次调用会使用你的配置创建全局实例
new DB({
  path: './data/sqlite3.db', // 默认值
  readonly: false, // 是否只读
  fileMustExist: false, // 若数据库文件不存在则抛出错误
  WAL: true, // 自动启用 'PRAGMA journal_mode = WAL'
  migrate: {  // 设置 `migrate: false` 可完全禁用迁移
    force: false, // 设为 'last' 可在每次启动时自动回滚并重新应用最后一个迁移文件
    table: 'migration', // 用于记录迁移状态的数据库表名
    migrationsPath: './migrations' // 迁移文件所在目录
  }
})

之后无需传参即可使用：

anotherAPIFile.js

import DB from '@beenotung/better-sqlite3-helper'

// 再次调用构造函数会直接返回全局实例
let row = new DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
console.log(row.firstName, row.lastName, row.email);

新增函数

本类为 better-sqlite3 实现了一系列简写方法。

// db.prepare('SELECT * FROM users').all() 的简写
let allUsers = new DB().query('SELECT * FROM users');
// 返回值：[{id: 1, firstName: 'a', lastName: 'b', email: '[email protected]'},{},...]
// 无结果时返回：[]

// db.prepare('SELECT * FROM users WHERE id=?').get(userId) 的简写
let row = new DB().queryFirstRow('SELECT * FROM users WHERE id=?', userId);
// 返回值：{id: 1, firstName: 'a', lastName: 'b', email: '[email protected]'}
// 无结果时返回：undefined

// db.prepare('SELECT * FROM users WHERE id=?').get(999) || {} 的简写
let {id, firstname} = new DB().queryFirstRowObject('SELECT * FROM users WHERE id=?', userId);
// 返回值：id = 1; firstName = 'a'
// 无结果时返回：id = undefined; firstName = undefined

// db.prepare('SELECT * FROM users WHERE id=?').pluck(true).get(userId) 的简写
let email = new DB().queryFirstCell('SELECT email FROM users WHERE id=?', userId);
// 返回值：'[email protected]'
// 无结果时返回：undefined

// db.prepare('SELECT * FROM users').all().map(e => e.email) 的简写
let emails = new DB().queryColumn('email', 'SELECT email FROM users');
// 返回值：['[email protected]', '[email protected]', ...]
// 无结果时返回：[]

// db.prepare('SELECT * FROM users').all().reduce((o, e) => {o[e.lastName] = e.email; return o;}, {}) 的简写
let emailsByLastName = new DB().queryKeyAndColumn('lastName', 'email', 'SELECT lastName, name FROM users');
// 返回值：{b: '[email protected]', c: '[email protected]', ...}
// 无结果时返回：{}

插入、更新与替换

update、insert、replace 和 delete 均有对应的简写方法，旨在简化 CRUD REST API 函数的编写。通过 blacklist（黑名单）或 whitelist（白名单），甚至可以直接将请求的 query 或 body 写入数据库。

更新

// const numberOfChangedRows = new DB().update(table, data, where, whitelist = undefined)

// 使用对象作为 where 条件，不使用白名单的简单示例
new DB().update('users', {
  lastName: 'Mustermann',
  firstName: 'Max'
}, {
  email: '[email protected]'
})

// 来自请求的数据，使用数组作为 where 条件，且只允许修改 lastName 和 firstName
new DB().update('users', req.body, ['email = ?', req.body.email], ['lastName', 'firstName'])


// 使用黑名单更新（不允许 id 和 email；只允许表中有效列），where 是 ['id = ?', req.body.id] 的简写
new DB().updateWithBlackList('users', req.body, req.body.id, ['id', 'email'])

插入与替换

// const lastInsertID = new DB().insert(table, datas, whitelist = undefined)
// const lastInsertID = new DB().replace(table, datas, whitelist = undefined)

// 使用对象，不使用白名单的简单示例
new DB().insert('users', {
  lastName: 'Mustermann',
  firstName: 'Max',
  email: '[email protected]'
})

// 插入两条用户记录
new DB().insert('users', [{
  lastName: 'Mustermann',
  firstName: 'Max',
  email: '[email protected]'
}, {
  lastName: 'Mustermann2',
  firstName: 'Max2',
  email: '[email protected]'
}])

// 来自请求的数据，只设置 lastName 和 firstName
new DB().replace('users', req.body, ['lastName', 'firstName'])


// 使用黑名单替换（不允许 id 和 email；只允许表中有效列）
new DB().replaceWithBlackList('users', req.body, ['id', 'email']) // 或 insertWithBlackList

删除

// 删除 id 为 4 的用户
new DB().delete('users', {id: 4})

Try-Catch 处理

如果向数据库写入无效值，函数会抛出错误。因此请务必用 try-catch 包裹相关函数。以下是一个 Express 服务端的示例：

import { Router } from 'express'
import bodyParser from 'body-parser'
import DB from '@beenotung/better-sqlite3-helper'

router.patch('/user/:id', bodyParser.json(), function (req, res, next) {
  try {
    if (!req.params.id) {
      res.status(400).json({error: 'missing id'})
      return
    }
    new DB().updateWithBlackList(
      'users',
      req.body,
      req.params.id,
      ['id']
    )

    res.statusCode(200)
  } catch (e) {
    console.error(e)
    res.status(503).json({error: e.message})
  }
})

数据库迁移

本库的迁移功能仿照了 Kriasoft 的优秀库 sqlite 的迁移系统。

要使用此功能，需要在根目录创建 migrations 文件夹，并在其中创建包含 up 和 down 两部分的 sql 文件：

migrations/001-initial-schema.sql

-- Up
CREATE TABLE Category (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE Post (id INTEGER PRIMARY KEY, categoryId INTEGER, title TEXT,
  CONSTRAINT Post_fk_categoryId FOREIGN KEY (categoryId)
    REFERENCES Category (id) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO Category (id, name) VALUES (1, 'Business');
INSERT INTO Category (id, name) VALUES (2, 'Technology');

-- Down
DROP TABLE Category
DROP TABLE Post;

migrations/002-missing-index.sql

-- Up
CREATE INDEX Post_ix_categoryId ON Post (categoryId);

-- Down
DROP INDEX Post_ix_categoryId;

文件需要按编号命名，会在数据库首次使用前自动执行。

注意：在开发环境中调整数据库结构时，可以设置 force: 'last'（默认为 false），这样每次 Node.js 应用启动时，迁移 API 都会回滚并重新应用最后一个迁移文件。详见"全局单例"章节。

也可以传入一个包含迁移内容的数组：

import DB from '@beenotung/better-sqlite3-helper'

const db = new DB({
  migrate: {
    migrations: [
      `-- Up
      CREATE TABLE Setting (
        key TEXT NOT NULL UNIQUE,
        value BLOB,
        type INT NOT NULL DEFAULT 0,
        PRIMARY KEY(key)
      );
      CREATE INDEX IF NOT EXISTS Setting_index_key ON Setting (key);

      -- Down
      DROP INDEX IF EXISTS Setting_index_key;
      DROP TABLE IF EXISTS Setting;
      `,
      `-- Up
      INSERT INTO Setting (key, value, type) VALUES ('test', 'now', 0);
      INSERT INTO Setting (key, value, type) VALUES ('testtest', 'nownow', 6);

      -- Down
      DELETE FROM Setting WHERE key = 'test';
      DELETE FROM Setting WHERE key = 'testtest';
      `
    ]
  }
})

better-sqlite3 更多文档

• API 文档

许可证

MIT