- assert 断言
- async_hooks 异步钩子
- async_hooks/context 异步上下文
- buffer 缓冲区
- C++插件
- C/C++插件(使用 Node-API)
- C++嵌入器
- child_process 子进程
- cluster 集群
- CLI 命令行
- console 控制台
- Corepack 核心包
- crypto 加密
- crypto/webcrypto 网络加密
- debugger 调试器
- deprecation 弃用
- dgram 数据报
- diagnostics_channel 诊断通道
- dns 域名服务器
- domain 域
- Error 错误
- events 事件触发器
- fs 文件系统
- global 全局变量
- http 超文本传输协议
- http2 超文本传输协议 2.0
- https 安全超文本传输协议
- inspector 检查器
- Intl 国际化
- module 模块
- module/cjs CommonJS 模块
- module/esm ECMAScript 模块
- module/package 包模块
- module/typescript TS 模块
- net 网络
- os 操作系统
- path 路径
- perf_hooks 性能钩子
- permission 权限
- process 进程
- punycode 域名代码
- querystring 查询字符串
- readline 逐行读取
- repl 交互式解释器
- report 诊断报告
- sea 单个可执行应用程序
Node.js v22.12.0 文档
- Node.js v22.12.0
-
目录
- SQLite
- 类:
DatabaseSync
- 类:
Session
- 类:
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 之间的类型转换
- SQLite 常量
- 类:
- SQLite
-
导航
- assert 断言
- async_hooks 异步钩子
- async_hooks/context 异步上下文
- buffer 缓冲区
- C++插件
- C/C++插件(使用 Node-API)
- C++嵌入器
- child_process 子进程
- cluster 集群
- CLI 命令行
- console 控制台
- Corepack 核心包
- crypto 加密
- crypto/webcrypto 网络加密
- debugger 调试器
- deprecation 弃用
- dgram 数据报
- diagnostics_channel 诊断通道
- dns 域名服务器
- domain 域
- Error 错误
- events 事件触发器
- fs 文件系统
- global 全局变量
- http 超文本传输协议
- http2 超文本传输协议 2.0
- https 安全超文本传输协议
- inspector 检查器
- Intl 国际化
- module 模块
- module/cjs CommonJS 模块
- module/esm ECMAScript 模块
- module/package 包模块
- module/typescript TS 模块
- net 网络
- os 操作系统
- path 路径
- perf_hooks 性能钩子
- permission 权限
- process 进程
- punycode 域名代码
- querystring 查询字符串
- readline 逐行读取
- repl 交互式解释器
- report 诊断报告
- sea 单个可执行应用程序
SQLite#
--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> Iftrue
, the database is opened by the constructor. When this value isfalse
, the database must be opened via theopen()
method. Default:true
. -
readOnly
<boolean> 如果是true
,则数据库以只读模式打开。如果数据库不存在,则打开它将失败。默认值:false
。¥
readOnly
<boolean> Iftrue
, 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> Iftrue
, 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 usingPRAGMA foreign_keys
. Default:true
. -
enableDoubleQuotedStringLiterals
<boolean> 如果是true
,SQLite 将接受 双引号字符串字面量。不建议这样做,但可以启用它以兼容旧数据库模式。默认值:false
。¥
enableDoubleQuotedStringLiterals
<boolean> Iftrue
, 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 语句而不返回任何结果。此方法在执行从文件读取的 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 usingATTACH 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.
此方法执行准备好的语句并将所有结果作为对象数组返回。如果准备好的语句没有返回任何结果,则此方法返回一个空数组。使用 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
#
准备好的语句的源 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
。使用 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])
#
-
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 completedINSERT
,UPDATE
, orDELETE
statement. This field is either a number or aBigInt
depending on the prepared statement's configuration. This property is the result ofsqlite3_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 aBigInt
depending on the prepared statement's configuration. This property is the result ofsqlite3_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)
#
-
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 ofBigInt
s when readingINTEGER
fields from the database.
从数据库读取时,SQLite INTEGER
默认映射到 JavaScript 数字。但是,SQLite INTEGER
可以存储比 JavaScript 数字能够表示的更大的值。在这种情况下,此方法可用于使用 JavaScript BigInt
读取 INTEGER
数据。此方法对始终支持数字和 BigInt
的数据库写入操作没有影响。
¥When reading from the database, SQLite INTEGER
s are mapped to JavaScript
numbers by default. However, SQLite INTEGER
s 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 BigInt
s. This method has no
impact on database write operations where numbers and BigInt
s are both
supported at all times.
statement.sourceSQL
#
准备好的语句的源 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 常量#
¥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 |
当更改遇到冲突时中止并回滚数据库。 |