_node-sqlite3-wasm_


WebAssembly build of SQLite3 for Node.js

_node-sqlite3-wasm_ is a port of SQLite3 to
WebAssembly for Node.js with
file system access. _node-sqlite3-wasm_ brings
SQLite3 to your Node.js
environment without recompiling on every target platform. This is especially
useful for Electron applications.

The port to WebAssembly that SQLite introduced in version 3.40.0 only targets
web browsers but not Node.js. Other WebAssembly ports also target Node.js, most
notably sql.js, but none supports
persistent storage with direct file access. There also exist native bindings
like better-sqlite3 or
node-sqlite3. However, native
bindings must be recompiled for every target platform or pre-built binaries must
be shipped. This is tedious, especially for Electron deployments.

_node-sqlite3-wasm_ supports persistent storage with direct file access by
implementing an SQLite OS Interface or "VFS"
that translates SQLite file access to Node.js' file system
API.

_node-sqlite3-wasm_ is currently based on SQLite 3.51.2.

Getting Started

To install _node-sqlite3-wasm_, run

``
npm install node-sqlite3-wasm
`

To use it, run

`js
const { Database } = require("node-sqlite3-wasm");
const db = new Database("database.db");
`

Important: _node-sqlite3-wasm_ is not fully garbage-collected. You **have to
manually close a database, otherwise you risk memory leaks** (see
Database.close()). Also, if you use prepared statements explicitly (see
Database.prepare()), you have to manually finalize them. Alternatively, the
Database class provides the convenience methods

- Database.all()
- Database.get()
- Database.run()

These convenience methods use a prepared statement internally and take care of
finalizing it.

Note: Foreign key support is enabled by default.

Example

`js
const { Database } = require("node-sqlite3-wasm");
const db = new Database("database.db");

db.exec(
"DROP TABLE IF EXISTS employees; " +
"CREATE TABLE IF NOT EXISTS employees (name TEXT, salary INTEGER)"
);

db.run("INSERT INTO employees VALUES (:n, :s)", {
":n": "James",
":s": 50000,
});

const r = db.all("SELECT * from employees");
console.log(r);
// [ { name: 'James', salary: 50000 } ]

db.close();
`

API

- class Database
- class Statement
- class SQLite3Error

$3

Constructor

- new Database()

Methods

- Database.all()
- Database.close()
- Database.exec()
- Database.function()
- Database.get()
- Database.prepare()
- Database.run()

Properties

- Database.inTransaction
- Database.isOpen

$3

Creates a new database connection. By default, the database file is created if
it doesn't exist.

Important: You have to manually close the database, otherwise you risk
memory leaks (see Database.close()).

Arguments

- path: the path to the database file
- options (optional)
- fileMustExist (default: false): if the database file does not exist it
will not be created. Instead an SQLite3Error will
be thrown. This option is ignored if readOnly is true.
- readOnly (default: false): opens the database in read-only mode

`js
const db = new Database("database.db");
`

`js
const db = new Database("database.db", { fileMustExist: true });
`

$3

Creates a prepared statement, executes it with the given values and returns the
resulting rows as an array of objects. The prepared statement is finalized
automatically.

Arguments

- sql: string containing the SQL statement
- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.
- options (optional)
- expand (default: false): if true, each returned row is a nested object
with keys corresponding to tables in the query. If a result column is an
expression or subquery, it will be returned under the key $.

`js
db.all("SELECT * FROM book");
`

`js
db.all("SELECT * FROM book WHERE title = ?", "The Little Prince");
`

`js
db.all("SELECT * FROM book WHERE title = :t", { ":t": "The Little Prince" });
`

`js
db.all("SELECT * FROM book WHERE title IN (?, ?)", [
"The Little Prince",
"The Hobbit",
]);
`

$3

Closes the database.

Important: You have to manually close the database, otherwise you risk
memory leaks.

Important: Closing the database with Database.close() does not automatically
finalize pending prepared statements.

`js
db.close();
`

$3

Executes the given SQL string. The SQL string may contain several
semicolon-separated statements.

`js
db.exec(
"DROP TABLE IF EXISTS book; CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT)"
);
`

$3

Registers a user-defined function.

Arguments

- name: the name of the function
- func: the implementation of the function
- options (optional)
- deterministic (default: false): if true, the function is considered
deterministic

`js
db.function("regexp", (y, x) => new RegExp(y, "i").test(x), {
deterministic: true,
});
db.all("SELECT FROM book WHERE title REGEXP ?", ".little.*");
`

$3

Creates a prepared statement, executes it with the given values and returns the
first resulting row as an object. The prepared statement is finalized
automatically.

Arguments

- sql: string containing the SQL statement
- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.
- options (optional)
- expand (default: false): if true, the returned row is a nested object
with keys corresponding to tables in the query. If a result column is an
expression or subquery, it will be returned under the key $.

`js
db.get("SELECT * FROM book WHERE id = ?", 7);
`

`js
db.get("SELECT * FROM book WHERE id = $id", { $id: 7 });
`

`js
db.get("SELECT * FROM book WHERE id = ? AND title = ?", [
3,
"The Little Prince",
]);
`

$3

Creates a prepared statement from the given SQL string.

Important: You have to manually finalize a statement, otherwise you risk
memory leaks. See Statement and, in particular,
Statement.finalize().

`js
const stmt = db.prepare("INSERT INTO book (title) VALUES (?)");
try {
// do something with the statement here
} finally {
stmt.finalize();
}
`

The Database class provides the convenience methods

- Database.all()
- Database.get()
- Database.run()

These convenience methods use a prepared statement internally and take care of
finalizing it.

$3

Creates a prepared statement, executes it with the given values and returns an
object with the properties changes and lastInsertRowid describing the number
of modified rows and the id of the last row inserted. lastInsertRowid is a
BigInt
if its value exceeds
Number.MAX_SAFE_INTEGER.
The prepared statement is finalized automatically.

Arguments

- sql: string containing the SQL statement
- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.

`js
db.run("INSERT INTO book (title) VALUES (?)", "The Little Prince");
`

`js
db.run("INSERT INTO book VALUES (?, ?)", [10, "The Little Prince"]);
`

`js
db.run("INSERT INTO book VALUES (@id, :title)", {
"@id": 10,
":title": "The Little Prince",
});
`

$3

Property determining whether the database is currently in a transaction.

`js
const stmt = db.prepare("INSERT INTO book (title) VALUES (?)");
try {
db.exec("BEGIN TRANSACTION");
stmt.run("The Little Prince");
stmt.run("The Hobbit");
db.exec("COMMIT");
} catch (err) {
if (db.inTransaction) db.exec("ROLLBACK");
console.log(err);
} finally {
stmt.finalize();
}
`

$3

Property determining whether the database is currently open.

$3

Methods

- Statement.all()
- Statement.finalize()
- Statement.get()
- Statement.iterate()
- Statement.run()

Properties

- Statement.database
- Statement.isFinalized

Important: You have to manually finalize a statement, otherwise you risk
memory leaks (see Statement.finalize()).

`js
const stmt = db.prepare("SELECT * FROM book WHERE id = ?");
try {
// do something with the statement here
} finally {
stmt.finalize();
}
`

As an alternative, the Database class provides the
convenience methods

- Database.all()
- Database.get()
- Database.run()

These convenience methods use a prepared statement internally and take care of
finalizing it.

$3

Executes the prepared statement with the given values and returns the resulting
rows as an array of objects.

Arguments

- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.
- options (optional)
- expand (default: false): if true, each returned row is a nested object
with keys corresponding to tables in the query. If a result column is an
expression or subquery, it will be returned under the key $.

See also Database.all()

$3

Finalizes the statement and frees all allocated memory. Once a statement has
been finalized, it cannot be used anymore.

Important: You have to manually finalize a statement, otherwise you risk
memory leaks.

Important: Closing the database with Database.close() does not automatically
finalize pending prepared statements.

$3

Executes the prepared statement with the given values and returns the first
resulting row as an object.

Arguments

- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.
- options (optional)
- expand (default: false): if true, the returned row is a nested object
with keys corresponding to tables in the query. If a result column is an
expression or subquery, it will be returned under the key $.

See also Database.get()

$3

Executes the prepared statement with the given values and returns the resulting
rows as an iterator of objects.

Arguments

- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.
- options (optional)
- expand (default: false): if true, each returned row is a nested object
with keys corresponding to tables in the query. If a result column is an
expression or subquery, it will be returned under the key $.

$3

Executes the prepared statement with the given values and returns an object with
the properties changes and lastInsertRowid describing the number of modified
rows and the id of the last row inserted. lastInsertRowid is a
BigInt
if its value exceeds
Number.MAX_SAFE_INTEGER.

Arguments

- values (optional): values to bind to the statement's parameters. Either a
single value, an array, or an object in case of named parameters.

See also Database.run()

$3

The Database object that instantiated this statement.

$3

Property determining whether the statement has been finalized using
Statement.finalize(). A finalized statement must not be used anymore.

$3

_node-sqlite3-wasm_ throws an SQLite3Error whenever an error in SQLite
or in the API occurs. SQLite3Error is a subclass of Error.

Notes About Types

$3

JavaScript's
Number
type is a double-precision 64-bit binary format IEEE 754 value. Integers can
only be represented without loss of precision in the range -253 + 1
to 253 - 1, inclusive. SQLite3 works with 8-byte signed
integers with a range of -263
to 263 - 1, inclusive. Since this range exceeds the range of safe
integers in JavaScript, _node-sqlite3-wasm_ automatically converts integers
outside this safe range to
BigInt.
It is your responsibility to ensure that you handle the returned values, whether
Number or BigInt, correctly. _node-sqlite3-wasm_ also allows you to input
BigInt values as query parameters, or arguments or return values of
user-defined functions.

$3

An SQLite Binary Large Object (BLOB) is represented by a
Uint8Array
in JavaScript.

Building

Docker and npm are required
for building. Mocha is required to run tests.

To build _node-sqlite3-wasm_, simply run

`
npm run build
`

This will download the emscripten Docker
image and the SQLite source
files. Then it will compile the project
source files and generate dist/node-sqlite3-wasm.js and
dist/node-sqlite3-wasm.wasm`.

License

_node-sqlite3-wasm_ is
MIT licensed.

Parts of the code are from sql.js, which is
also MIT licensed. SQLite is in the public
domain.