better-sqlite3简介及常用操作
2023/05/10    

better-sqlite3 是一个非常快速、轻量级且易于使用的 SQLite3 数据库封装库,它是 SQLite3 官方 C API 的一个简单的 JavaScript 封装。以下是该库的一些主要特点:

  1. 高性能better-sqlite3 的性能非常出色,比 Node.js 中许多其他 SQLite3 库都要快。它在性能方面的表现与 C 语言编写的 SQLite3 库相当接近。

  2. 易于使用:该库的 API 设计非常简单和易于使用。它提供了一组简单的方法,使得创建数据库、执行查询、准备语句、绑定参数等操作都非常简单。

  3. 安全性better-sqlite3 在执行语句时自动进行参数绑定,从而减少了 SQL 注入攻击的风险。此外,该库还具有防止数据损坏的机制,包括事务和数据库文件的自动检查和修复。

  4. 跨平台支持:该库可在 Windows、macOS 和 Linux 等主要平台上使用,并且在这些平台上都表现良好。

总的来说,better-sqlite3 是一个出色的 SQLite3 数据库封装库,它提供了一个快速、简单和安全的方法来处理 SQLite3 数据库。如果您需要在 Node.js 应用程序中使用 SQLite3 数据库,并且需要快速、易于使用和安全性方面的保障,那么 better-sqlite3 可能是一个很好的选择。


下面是使用 better-sqlite3 库创建表、插入、删除、更新和查询数据的例子

创建表

const sqlite3 = require('better-sqlite3');

// 创建一个名为 example.db 的 SQLite3 数据库
const db = sqlite3('example.db');

// 创建一个名为 users 的表,包含 id、name 和 email 字段
db.prepare('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)').run();

插入数据

// 向 users 表中插入一条记录
const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
insert.run('John Doe', 'john.doe@example.com');

删除数据

// 从 users 表中删除 id 为 1 的记录
const remove = db.prepare('DELETE FROM users WHERE id = ?');
remove.run(1);

更新数据

// 将 name 为 'John Doe' 的记录的 email 更新为 'john.doe@newemail.com'
const update = db.prepare('UPDATE users SET email = ? WHERE name = ?');
update.run('john.doe@newemail.com', 'John Doe');

查询数据

// 查询 users 表中所有记录
const getAll = db.prepare('SELECT * FROM users');
const rows = getAll.all();

// 查询 name 为 'John Doe' 的记录
const getByName = db.prepare('SELECT * FROM users WHERE name = ?');
const row = getByName.get('John Doe');

处理事务

// 使用事务来执行多个插入操作
const inserts = [
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
  { name: 'Charlie', email: 'charlie@example.com' },
];

db.transaction(() => {
  const insert = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
  for (const item of inserts) {
    insert.run(item.name, item.email);
  }
})();

请注意,在使用事务时,所有操作都必须在同一事务内进行,以确保数据的一致性和完整性


更新多个字段,可以在 UPDATE 语句中使用多个 SET 子句来分别更新每个字段,例如

const update = db.prepare('UPDATE users SET name = ?, email = ? WHERE id = ?');
update.run('John Doe', 'john.doe@example.com', 1);

在上面的代码中,我们将 name 和 email 字段都更新为新的值,并使用 id = 1 的条件来确定要更新的记录。注意,? 占位符的数量必须与 SET 子句中的字段数量相同,并按照相应的顺序提供更新值。


如果需要更新的字段比较多,您也可以使用对象来指定要更新的字段和值,然后使用 Object.entries() 方法来将它们转换为数组。例如:

const updateData = {
  name: 'John Doe',
  email: 'john.doe@example.com',
  age: 30,
  city: 'New York'
};

const updateFields = Object.entries(updateData).map(([field, value]) => `${field} = ?`).join(', ');
const update = db.prepare(`UPDATE users SET ${updateFields} WHERE id = ?`);

const params = Object.values(updateData).concat(1);
update.run(...params);


在上面的代码中,我们首先定义了一个包含要更新的字段和值的对象 updateData,然后使用 Object.entries() 方法将其转换为一个二维数组,其中每个子数组包含一个字段名和相应的值。然后,我们使用 Array.prototype.map() 方法将每个子数组转换为一个 field = ? 字符串,再使用 Array.prototype.join() 方法将它们连接起来,形成一个包含多个 field = ? 的字符串,例如 name = ?, email = ?, age = ?, city = ?。最后,我们将该字符串插入到 UPDATE 语句中,并将更新值和条件参数作为数组提供给 run() 方法。


提高 better-sqlite3 执行性能的方法主要包括以下几个方面:


  1. 合理使用索引

索引是一种提高查询性能的常用方法。可以在创建表时定义索引,也可以后期通过 CREATE INDEX 语句添加索引。在查询时,应该尽可能使用索引来限制结果集的大小,以减少扫描的数据量。需要注意的是,过多的索引也会影响写入性能,因此需要权衡好查询和写入的需求,避免过度索引。

  1. 合理设计表结构

表结构的设计对于执行性能也有一定的影响。在设计表时,应该尽可能减少表的大小,减少重复的数据,并将经常一起使用的字段放在同一个表中,以减少关联查询的次数。

  1. 使用参数化查询

参数化查询可以避免 SQL 注入攻击,并且可以重用预编译的查询计划,从而提高执行效率。可以使用 prepare() 方法来预编译查询,然后使用 run()all() 方法执行查询。

  1. 使用事务

在执行多个相关操作时,可以使用事务来保证数据的完整性和一致性。在事务中,可以将多个操作作为一个原子操作执行,避免中途出现错误或异常导致部分操作执行成功而部分操作失败的情况。

  1. 减少 I/O 操作

在大量数据插入或查询时,磁盘 I/O 操作是一个很大的瓶颈。为了减少 I/O 操作,可以将多次写入或查询操作合并为一次,或者使用内存数据库等技术来减少磁盘 I/O 操作。

  1. 使用连接池

在高并发的情况下,数据库连接的创建和销毁会成为一个瓶颈。可以使用连接池来缓存已创建的连接,避免频繁创建和销毁连接,从而提高性能。

以上是一些常用的提高 better-sqlite3 执行性能的方法,具体的优化策略需要根据具体的场景和需求进行选择。



db.pragma('journal_mode = WAL') 是一个 SQLite 的 PRAGMA 语句,用于将数据库的日志模式设置为 "Write-Ahead Logging"(WAL)模式。WAL 模式是一种 SQLite 数据库的日志模式,可以提高并发读写操作的性能。


在 SQLite 的默认日志模式下,每次写入操作都会导致数据库文件被锁定,直到写入操作完成并提交到磁盘上。这会导致在高并发写入的情况下出现锁竞争,从而影响性能。WAL 模式通过使用一个单独的日志文件来记录写入操作,避免了对数据库文件的频繁锁定,从而提高了并发写入操作的性能。


设置 WAL 模式后,SQLite 会在数据库文件和一个额外的 WAL 文件中分别记录数据和日志,从而实现并发写入和读取。当一个事务提交时,它会将相关的 WAL 记录合并到数据库文件中,这个过程称为 "checkpoint",这个过程可以通过 PRAGMA wal_checkpoint 命令进行手动触发。在 WAL 模式下,同时只能有一个事务在执行 checkpoint 操作,因此需要根据具体情况进行调整。


需要注意的是,WAL 模式并不适用于所有场景。在一些特殊的场景下,如大量的并发写入或者频繁地进行数据清理等操作,可能会出现 WAL 文件过大或者损坏等问题,因此需要根据具体的应用场景进行评估和选择。


`better-sqlite3` 中的 `iterate` 方法用于在查询的结果集中逐行迭代。该方法可以接收一个回调函数作为参数,该回调函数将被执行多次,每次执行时传递当前行的数据作为参数。


`iterate` 方法比 `all` 和 `each` 方法更适合处理大量数据的查询结果,因为它不会一次性加载所有的结果数据到内存中,而是逐行读取数据并处理,从而避免了内存溢出等问题。


以下是 `iterate` 方法的示例代码:

javascript
const stmt = db.prepare('SELECT id, name FROM users');
stmt.iterate((row) => {
  console.log(row.id, row.name);
});


在上面的示例代码中,首先通过 `db.prepare` 方法准备了一个查询语句,然后通过 `iterate` 方法对查询结果逐行进行迭代,每次将当前行的 `id` 和 `name` 字段打印到控制台上。


需要注意的是,回调函数中的参数是当前行的数据,它是一个对象类型,其中的键值对对应了每个查询的字段名和对应的值。在回调函数中,可以根据具体的需求对当前行的数据进行处理,例如对结果进行聚合、计算等操作。