WebAssembly port of SQLite3 for Node.js with file system access
_node-sqlite3-wasm_


_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.
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, theDatabase 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.
`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();
`
- class Database
- class Statement
- class SQLite3Error
Constructor
Methods
- Database.all()
- Database.close()
- Database.exec()
- Database.function()
- Database.get()
- Database.prepare()
- Database.run()
Properties
- Database.inTransaction
- Database.isOpen
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 fileoptions
- (optional)fileMustExist
- (default: false): if the database file does not exist it
will not be created. Instead an SQLite3Error willreadOnly
be thrown. This option is ignored if 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 });
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 statementvalues
- (optional): values to bind to the statement's parameters. Either aoptions
single value, an array, or an object in case of named parameters.
- (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",
]);
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();
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)"
);
Registers a user-defined function.
Arguments
- name: the name of the functionfunc
- : the implementation of the functionoptions
- (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.*");
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 statementvalues
- (optional): values to bind to the statement's parameters. Either aoptions
single value, an array, or an object in case of named parameters.
- (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",
]);
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.
Creates a prepared statement, executes it with the given values and returns an
object with the properties changes and lastInsertRowid describing the numberlastInsertRowid
of modified rows and the id of the last row inserted. is aBigIntNumber.MAX_SAFE_INTEGER
if its value exceeds.
The prepared statement is finalized automatically.
Arguments
- sql: string containing the SQL statementvalues
- (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",
});
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();
}
Property determining whether the database is currently open.
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.
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 aoptions
single value, an array, or an object in case of named parameters.
- (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()
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.
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 aoptions
single value, an array, or an object in case of named parameters.
- (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()
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 aoptions
single value, an array, or an object in case of named parameters.
- (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 .
Executes the prepared statement with the given values and returns an object with
the properties changes and lastInsertRowid describing the number of modifiedlastInsertRowid
rows and the id of the last row inserted. is aBigIntNumber.MAX_SAFE_INTEGER
if its value exceeds.
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()
The Database object that instantiated this statement.
Property determining whether the statement has been finalized using
Statement.finalize(). A finalized statement must not be used anymore.
_node-sqlite3-wasm_ throws an SQLite3Error whenever an error in SQLiteSQLite3Error
or in the API occurs. is a subclass of Error.
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 inputBigInt values as query parameters, or arguments or return values of
user-defined functions.
An SQLite Binary Large Object (BLOB) is represented by a
Uint8Array
in JavaScript.
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 anddist/node-sqlite3-wasm.wasm`.
_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.