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

To install node-sqlite3-wasm, run

npm install node-sqlite3-wasm

To use it, run

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.

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

• new Database()

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

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

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

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

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

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

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.

db.close();

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

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 function
• func: the implementation of the function
• options (optional)
  • deterministic (default: false): if true, the function is considered deterministic

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

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

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

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

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

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

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

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

Property determining whether the database is currently in a transaction.

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

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

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

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

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

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 SQLite or in the API occurs. SQLite3Error 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 -2⁵³ + 1 to 2⁵³ - 1, inclusive. SQLite3 works with 8-byte signed integers with a range of -2⁶³ to 2⁶³ - 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.

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 and dist/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.