sqlite 轻型数据库 | Node.js v22 文档

Node.js 中文网

sqlite 轻型数据库

SQLite#

新增于: v22.5.0

稳定性: 1.1 - 积极开发。使用 --experimental-sqlite CLI 标志启用此 API。

¥Stability: 1.1 - Active development. Enable this API with the --experimental-sqlite CLI flag.

源代码: lib/sqlite.js

node:sqlite 模块有助于使用 SQLite 数据库。要访问它：

¥The node:sqlite module facilitates working with SQLite databases. To access it:

import sqlite from 'node:sqlite';const sqlite = require('node:sqlite');

此模块仅在 node: 协议下可用。以下将不起作用：

¥This module is only available under the node: scheme. The following will not work:

import sqlite from 'sqlite';const sqlite = require('sqlite');

以下示例显示了 node:sqlite 模块的基本用法，即打开内存数据库、将数据写入数据库，然后读回数据。

¥The following example shows the basic usage of the node:sqlite module to open an in-memory database, write data to the database, and then read the data back.

import { DatabaseSync } from 'node:sqlite';
const database = new DatabaseSync(':memory:');

// Execute SQL statements from strings.
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// Create a prepared statement to insert data into the database.
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// Execute the prepared statement with bound values.
insert.run(1, 'hello');
insert.run(2, 'world');
// Create a prepared statement to read data from the database.
const query = database.prepare('SELECT * FROM data ORDER BY key');
// Execute the prepared statement and log the result set.
console.log(query.all());
// Prints: [ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]'use strict';
const { DatabaseSync } = require('node:sqlite');
const database = new DatabaseSync(':memory:');

// Execute SQL statements from strings.
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);
// Create a prepared statement to insert data into the database.
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// Execute the prepared statement with bound values.
insert.run(1, 'hello');
insert.run(2, 'world');
// Create a prepared statement to read data from the database.
const query = database.prepare('SELECT * FROM data ORDER BY key');
// Execute the prepared statement and log the result set.
console.log(query.all());
// Prints: [ { key: 1, value: 'hello' }, { key: 2, value: 'world' } ]

类：`DatabaseSync`#

¥Class: DatabaseSync

新增于: v22.5.0

此类表示 SQLite 数据库的单个 connection。此类公开的所有 API 均同步执行。

¥This class represents a single connection to a SQLite database. All APIs exposed by this class execute synchronously.

`new DatabaseSync(location[, options])`#

新增于: v22.5.0

location <string> 数据库的位置。SQLite 数据库可以存储在文件中或完全在内存中中。要使用文件支持的数据库，位置应该是文件路径。要使用内存数据库，位置应该是特殊名称 ':memory:'。

¥location <string> The location of the database. A SQLite database can be stored in a file or completely in memory. To use a file-backed database, the location should be a file path. To use an in-memory database, the location should be the special name ':memory:'.
options <Object> 数据库连接的配置选项。支持以下选项：

¥options <Object> Configuration options for the database connection. The following options are supported:
- open <boolean> 如果是 true，则数据库由构造函数打开。当此值为 false 时，必须通过 open() 方法打开数据库。默认值：true。
  
  ¥open <boolean> If true, the database is opened by the constructor. When this value is false, the database must be opened via the open() method. Default: true.
- enableForeignKeyConstraints <boolean> 如果是 true，则启用外键约束。建议这样做，但可以禁用它以兼容旧数据库模式。使用 PRAGMA foreign_keys 打开数据库后，可以启用和禁用外键约束的实现。默认值：true。
  
  ¥enableForeignKeyConstraints <boolean> If true, foreign key constraints are enabled. This is recommended but can be disabled for compatibility with legacy database schemas. The enforcement of foreign key constraints can be enabled and disabled after opening the database using PRAGMA foreign_keys. Default: true.
- enableDoubleQuotedStringLiterals <boolean> 如果是 true，SQLite 将接受双引号字符串字面量。不建议这样做，但可以启用它以兼容旧数据库模式。默认值：false。
  
  ¥enableDoubleQuotedStringLiterals <boolean> If true, SQLite will accept double-quoted string literals. This is not recommended but can be enabled for compatibility with legacy database schemas. Default: false.

构造一个新的 DatabaseSync 实例。

¥Constructs a new DatabaseSync instance.

`database.close()`#

新增于: v22.5.0

关闭数据库连接。如果数据库未打开，则抛出异常。此方法是 sqlite3_close_v2() 的封装器。

¥Closes the database connection. An exception is thrown if the database is not open. This method is a wrapper around sqlite3_close_v2().

`database.exec(sql)`#

新增于: v22.5.0

sql <string> 要执行的 SQL 字符串。

¥sql <string> A SQL string to execute.

此方法允许执行一个或多个 SQL 语句而不返回任何结果。此方法在执行从文件读取的 SQL 语句时很有用。此方法是 sqlite3_exec() 的封装器。

¥This method allows one or more SQL statements to be executed without returning any results. This method is useful when executing SQL statements read from a file. This method is a wrapper around sqlite3_exec().

`database.open()`#

新增于: v22.5.0

打开 DatabaseSync 构造函数的 location 参数中指定的数据库。仅当未通过构造函数打开数据库时，才应使用此方法。如果数据库已打开，则抛出异常。

¥Opens the database specified in the location argument of the DatabaseSync constructor. This method should only be used when the database is not opened via the constructor. An exception is thrown if the database is already open.

`database.prepare(sql)`#

新增于: v22.5.0

sql <string> 要编译为准备好的语句的 SQL 字符串。

¥sql <string> A SQL string to compile to a prepared statement.
返回：StatementSync 准备好的语句。

¥Returns: StatementSync The prepared statement.

将 SQL 语句编译为准备好的语句。此方法是 sqlite3_prepare_v2() 的封装器。

¥Compiles a SQL statement into a prepared statement. This method is a wrapper around sqlite3_prepare_v2().

类：`StatementSync`#

¥Class: StatementSync

新增于: v22.5.0

此类表示单个准备好的语句。此类无法通过其构造函数实例化。相反，实例是通过 database.prepare() 方法创建的。此类公开的所有 API 均同步执行。

¥This class represents a single prepared statement. This class cannot be instantiated via its constructor. Instead, instances are created via the database.prepare() method. All APIs exposed by this class execute synchronously.

准备好的语句是用于创建它的 SQL 的有效二进制表示。准备好的语句是可参数化的，可以使用不同的绑定值多次调用。参数还提供针对 SQL 注入攻击的保护。出于这些原因，在处理用户输入时，准备好的语句比手工制作的 SQL 字符串更受青睐。

¥A prepared statement is an efficient binary representation of the SQL used to create it. Prepared statements are parameterizable, and can be invoked multiple times with different bound values. Parameters also offer protection against SQL injection attacks. For these reasons, prepared statements are preferred over hand-crafted SQL strings when handling user input.

`statement.all([namedParameters][, ...anonymousParameters])`#

新增于: v22.5.0

namedParameters <Object> 用于绑定命名参数的可选对象。此对象的键用于配置映射。

¥namedParameters <Object> An optional object used to bind named parameters. The keys of this object are used to configure the mapping.
...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> 零个或多个值绑定到匿名参数。

¥...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> Zero or more values to bind to anonymous parameters.
返回：<Array> 对象数组。每个对象对应于执行准备好的语句返回的一行。每个对象的键和值对应于行的列名和值。

¥Returns: <Array> An array of objects. Each object corresponds to a row returned by executing the prepared statement. The keys and values of each object correspond to the column names and values of the row.

此方法执行准备好的语句并将所有结果作为对象数组返回。如果准备好的语句没有返回任何结果，则此方法返回一个空数组。使用 namedParameters 和 anonymousParameters 中的值的准备好的语句参数已绑定。

¥This method executes a prepared statement and returns all results as an array of objects. If the prepared statement does not return any results, this method returns an empty array. The prepared statement parameters are bound using the values in namedParameters and anonymousParameters.

`statement.expandedSQL`#

新增于: v22.5.0

<string> 扩展为包含参数值的源 SQL。

¥<string> The source SQL expanded to include parameter values.

准备好的语句的源 SQL 文本，其中参数占位符替换为此准备好的语句最近执行期间使用的值。此属性是 sqlite3_expanded_sql() 的封装器。

¥The source SQL text of the prepared statement with parameter placeholders replaced by the values that were used during the most recent execution of this prepared statement. This property is a wrapper around sqlite3_expanded_sql().

`statement.get([namedParameters][, ...anonymousParameters])`#

新增于: v22.5.0

namedParameters <Object> 用于绑定命名参数的可选对象。此对象的键用于配置映射。

¥namedParameters <Object> An optional object used to bind named parameters. The keys of this object are used to configure the mapping.
...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> 零个或多个值绑定到匿名参数。

¥...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> Zero or more values to bind to anonymous parameters.
返回：<Object> | <undefined> 执行准备好的语句返回的第一行对应的对象。对象的键和值对应于行的列名和值。如果数据库未返回任何行，则此方法将返回 undefined。

¥Returns: <Object> | <undefined> An object corresponding to the first row returned by executing the prepared statement. The keys and values of the object correspond to the column names and values of the row. If no rows were returned from the database then this method returns undefined.

此方法执行准备好的语句并返回第一个结果作为对象。如果准备好的语句没有返回任何结果，则此方法返回 undefined。使用 namedParameters 和 anonymousParameters 中的值的准备好的语句参数已绑定。

¥This method executes a prepared statement and returns the first result as an object. If the prepared statement does not return any results, this method returns undefined. The prepared statement parameters are bound using the values in namedParameters and anonymousParameters.

`statement.run([namedParameters][, ...anonymousParameters])`#

新增于: v22.5.0

namedParameters <Object> 用于绑定命名参数的可选对象。此对象的键用于配置映射。

¥namedParameters <Object> An optional object used to bind named parameters. The keys of this object are used to configure the mapping.
...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> 零个或多个值绑定到匿名参数。

¥...anonymousParameters <null> | <number> | <bigint> | <string> | <Buffer> | <Uint8Array> Zero or more values to bind to anonymous parameters.
返回：<Object>

¥Returns: <Object>
- changes：<number> | <bigint> 最近完成的 INSERT、UPDATE 或 DELETE 语句修改、插入或删除的行数。此字段是数字或 BigInt，具体取决于准备好的语句的配置。此属性是 sqlite3_changes64() 的结果。
  
  ¥changes: <number> | <bigint> The number of rows modified, inserted, or deleted by the most recently completed INSERT, UPDATE, or DELETE statement. This field is either a number or a BigInt depending on the prepared statement's configuration. This property is the result of sqlite3_changes64().
- lastInsertRowid：<number> | <bigint> 最近插入的 rowid。此字段是数字或 BigInt，具体取决于准备好的语句的配置。此属性是 sqlite3_last_insert_rowid() 的结果。
  
  ¥lastInsertRowid: <number> | <bigint> The most recently inserted rowid. This field is either a number or a BigInt depending on the prepared statement's configuration. This property is the result of sqlite3_last_insert_rowid().

此方法执行准备好的语句并返回总结结果更改的对象。使用 namedParameters 和 anonymousParameters 中的值的准备好的语句参数已绑定。

¥This method executes a prepared statement and returns an object summarizing the resulting changes. The prepared statement parameters are bound using the values in namedParameters and anonymousParameters.

`statement.setAllowBareNamedParameters(enabled)`#

新增于: v22.5.0

enabled <boolean> 启用或禁用对不带前缀字符的命名参数的绑定支持。

¥enabled <boolean> Enables or disables support for binding named parameters without the prefix character.

SQLite 参数的名称以前缀字符开头。默认情况下，node:sqlite 要求在绑定参数时存在此前缀字符。但是，除了美元符号字符外，这些前缀字符在对象键中使用时也需要额外的引号。

¥The names of SQLite parameters begin with a prefix character. By default, node:sqlite requires that this prefix character is present when binding parameters. However, with the exception of dollar sign character, these prefix characters also require extra quoting when used in object keys.

为了提高人机工程学，此方法还可用于允许裸命名参数，这些参数不需要 JavaScript 代码中的前缀字符。启用裸命名参数时需要注意以下几点：

¥To improve ergonomics, this method can be used to also allow bare named parameters, which do not require the prefix character in JavaScript code. There are several caveats to be aware of when enabling bare named parameters:

SQL 中仍然需要前缀字符。

¥The prefix character is still required in SQL.
JavaScript 中仍然允许使用前缀字符。实际上，带前缀的名称将具有略好的绑定性能。

¥The prefix character is still allowed in JavaScript. In fact, prefixed names will have slightly better binding performance.
在同一个准备好的语句中使用模糊命名参数（例如 $k 和 @k）将导致异常，因为无法确定如何绑定裸名称。

¥Using ambiguous named parameters, such as $k and @k, in the same prepared statement will result in an exception as it cannot be determined how to bind a bare name.

`statement.setReadBigInts(enabled)`#

新增于: v22.5.0

enabled <boolean> 从数据库读取 INTEGER 字段时启用或禁用 BigInt 的使用。

¥enabled <boolean> Enables or disables the use of BigInts when reading INTEGER fields from the database.

从数据库读取时，SQLite INTEGER 默认映射到 JavaScript 数字。但是，SQLite INTEGER 可以存储比 JavaScript 数字能够表示的更大的值。在这种情况下，此方法可用于使用 JavaScript BigInt 读取 INTEGER 数据。此方法对始终支持数字和 BigInt 的数据库写入操作没有影响。

¥When reading from the database, SQLite INTEGERs are mapped to JavaScript numbers by default. However, SQLite INTEGERs can store values larger than JavaScript numbers are capable of representing. In such cases, this method can be used to read INTEGER data using JavaScript BigInts. This method has no impact on database write operations where numbers and BigInts are both supported at all times.

`statement.sourceSQL`#

新增于: v22.5.0

<string> 用于创建此准备好的语句的源 SQL。

¥<string> The source SQL used to create this prepared statement.

准备好的语句的源 SQL 文本。此属性是 sqlite3_sql() 的封装器。

¥The source SQL text of the prepared statement. This property is a wrapper around sqlite3_sql().

JavaScript 和 SQLite 之间的类型转换#

¥Type conversion between JavaScript and SQLite

当 Node.js 写入或读取 SQLite 时，需要在 JavaScript 数据类型和 SQLite 的数据类型之间进行转换。由于 JavaScript 支持的数据类型比 SQLite 多，因此仅支持 JavaScript 类型的子集。尝试将不受支持的数据类型写入 SQLite 将导致异常。

¥When Node.js writes to or reads from SQLite it is necessary to convert between JavaScript data types and SQLite's data types. Because JavaScript supports more data types than SQLite, only a subset of JavaScript types are supported. Attempting to write an unsupported data type to SQLite will result in an exception.

SQLite	JavaScript
`NULL`	<null>
`INTEGER`	<number> 或 <bigint>
`REAL`	<number>
`TEXT`	<string>
`BLOB`	<Uint8Array>

SQLite#

类：DatabaseSync#

new DatabaseSync(location[, options])#

database.close()#

database.exec(sql)#

database.open()#

database.prepare(sql)#

类：StatementSync#

statement.all([namedParameters][, ...anonymousParameters])#

statement.expandedSQL#

statement.get([namedParameters][, ...anonymousParameters])#

statement.run([namedParameters][, ...anonymousParameters])#

statement.setAllowBareNamedParameters(enabled)#

statement.setReadBigInts(enabled)#

statement.sourceSQL#

JavaScript 和 SQLite 之间的类型转换#

类：`DatabaseSync`#

`new DatabaseSync(location[, options])`#

`database.close()`#

`database.exec(sql)`#

`database.open()`#

`database.prepare(sql)`#

类：`StatementSync`#

`statement.all([namedParameters][, ...anonymousParameters])`#

`statement.expandedSQL`#

`statement.get([namedParameters][, ...anonymousParameters])`#

`statement.run([namedParameters][, ...anonymousParameters])`#

`statement.setAllowBareNamedParameters(enabled)`#

`statement.setReadBigInts(enabled)`#

`statement.sourceSQL`#