Node.js v22.12.0 文档


SQLite#

稳定性: 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.

以下示例显示了 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

此类表示 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])#

  • 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.

    • readOnly <boolean> 如果是 true,则数据库以只读模式打开。如果数据库不存在,则打开它将失败。默认值:false

      ¥readOnly <boolean> If true, the database is opened in read-only mode. If the database does not exist, opening it will fail. Default: false.

    • 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()#

关闭数据库连接。如果数据库未打开,则抛出异常。此方法是 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)#

  • 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()#

打开 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)#

  • 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().

database.createSession([options])#

  • options <Object> 会话的配置选项。

    ¥options <Object> The configuration options for the session.

    • table <string> 用于跟踪更改的特定表。默认情况下,将跟踪对所有表的更改。

      ¥table <string> A specific table to track changes for. By default, changes to all tables are tracked.

    • db <string> 要跟踪的数据库的名称。当使用 ATTACH DATABASE 添加多个数据库时,这很有用。默认值:'main'

      ¥db <string> Name of the database to track. This is useful when multiple databases have been added using ATTACH DATABASE. Default: 'main'.

  • 返回:Session 会话句柄。

    ¥Returns: Session A session handle.

创建会话并将其附加到数据库。此方法是 sqlite3session_create()sqlite3session_attach() 的封装器。

¥Creates and attaches a session to the database. This method is a wrapper around sqlite3session_create() and sqlite3session_attach().

database.applyChangeset(changeset[, options])#

  • changeset <Uint8Array> 二进制变更集或补丁集。

    ¥changeset <Uint8Array> A binary changeset or patchset.

  • options <Object> 如何应用更改的配置选项。

    ¥options <Object> The configuration options for how the changes will be applied.

    • filter <Function> 跳过更改,当向此函数提供目标表名时,返回真值。默认情况下,将尝试所有更改。

      ¥filter <Function> Skip changes that, when targeted table name is supplied to this function, return a truthy value. By default, all changes are attempted.

    • onConflict <number> 确定如何处理冲突。默认值:SQLITE_CHANGESET_ABORT

      ¥onConflict <number> Determines how conflicts are handled. Default: SQLITE_CHANGESET_ABORT.

      • SQLITE_CHANGESET_OMIT:省略冲突更改。

        ¥SQLITE_CHANGESET_OMIT: conflicting changes are omitted.

      • SQLITE_CHANGESET_REPLACE:冲突的更改将替换现有值。

        ¥SQLITE_CHANGESET_REPLACE: conflicting changes replace existing values.

      • SQLITE_CHANGESET_ABORT:冲突时中止并回滚数据库。

        ¥SQLITE_CHANGESET_ABORT: abort on conflict and roll back database.

  • 返回:<boolean> 变更集是否成功应用而未中止。

    ¥Returns: <boolean> Whether the changeset was applied succesfully without being aborted.

如果数据库未打开,则抛出异常。此方法是 sqlite3changeset_apply() 的封装器。

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

const sourceDb = new DatabaseSync(':memory:');
const targetDb = new DatabaseSync(':memory:');

sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');
targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');

const session = sourceDb.createSession();

const insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
insert.run(1, 'hello');
insert.run(2, 'world');

const changeset = session.changeset();
targetDb.applyChangeset(changeset);
// Now that the changeset has been applied, targetDb contains the same data as sourceDb. 

类:Session#

¥Class: Session

session.changeset()#

  • 返回:<Uint8Array> 可应用于其他数据库的二进制变更集。

    ¥Returns: <Uint8Array> Binary changeset that can be applied to other databases.

检索包含自创建变更集以来所有更改的变更集。可以多次调用。如果数据库或会话未打开,则会引发异常。此方法是 sqlite3session_changeset() 的封装器。

¥Retrieves a changeset containing all changes since the changeset was created. Can be called multiple times. An exception is thrown if the database or the session is not open. This method is a wrapper around sqlite3session_changeset().

session.patchset()#

  • 返回:<Uint8Array> 可应用于其他数据库的二进制补丁集。

    ¥Returns: <Uint8Array> Binary patchset that can be applied to other databases.

与上述方法类似,但生成更紧凑的补丁集。请参阅 SQLite 文档中的 变更集和补丁集。如果数据库或会话未打开,则会引发异常。此方法是 sqlite3session_patchset() 的封装器。

¥Similar to the method above, but generates a more compact patchset. See Changesets and Patchsets in the documentation of SQLite. An exception is thrown if the database or the session is not open. This method is a wrapper around sqlite3session_patchset().

session.close()#

关闭会话。如果数据库或会话未打开,则会引发异常。此方法是 sqlite3session_delete() 的封装器。

¥Closes the session. An exception is thrown if the database or the session is not open. This method is a wrapper around sqlite3session_delete().

类:StatementSync#

¥Class: StatementSync

此类表示单个 准备好的语句。此类无法通过其构造函数实例化。相反,实例是通过 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])#

  • 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.

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

¥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#

  • <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])#

  • 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。使用 namedParametersanonymousParameters 中的值的准备好的语句 参数已绑定

¥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])#

  • 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> 最近完成的 INSERTUPDATEDELETE 语句修改、插入或删除的行数。此字段是数字或 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().

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

¥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)#

  • 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)#

  • 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#

  • <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.

SQLiteJavaScript
NULL<null>
INTEGER<number><bigint>
REAL<number>
TEXT<string>
BLOB<Uint8Array>

SQLite 常量#

¥SQLite constants

node:sqlite 模块导出以下常量。

¥The following constants are exported by the node:sqlite module.

SQLite 会话常量#

¥SQLite Session constants

冲突解决常量#

¥Conflict-resolution constants

以下常量旨在与 database.applyChangeset() 一起使用。

¥The following constants are meant for use with database.applyChangeset().

常量 描述
SQLITE_CHANGESET_OMIT 冲突的更改将被忽略。
SQLITE_CHANGESET_REPLACE 冲突的更改将替换现有值。
SQLITE_CHANGESET_ABORT 当更改遇到冲突时中止并回滚数据库。
Node.js 中文网 - 粤ICP备13048890号