node sqlite3 wasm

WebAssembly port of SQLite3 for Node.js with file system access

tndrle
68
9
JavaScript

node-sqlite3-wasm

npm
SQLite

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

Getting Started

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

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

Note: Foreign key support is enabled by default.

Example

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

Constructor

Methods

Properties

new Database(path, [options])

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 });

Database.all(sql, [values, options]) -> rows

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",
]);

Database.close()

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

Database.exec(sql)

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)"
);

Database.function(name, func, [options]) -> this

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.*");

Database.get(sql, [values, options]) -> row

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",
]);

Database.prepare(sql) -> Statement

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

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

Database.run(sql, [values]) -> info

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",
});

Database.inTransaction

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

Database.isOpen

Property determining whether the database is currently open.

class Statement

Methods

Properties

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

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

Statement.all([values, options]) -> rows

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

Statement.finalize()

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.

Statement.get([values, options]) -> row

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

Statement.iterate([values, options]) -> IterableIterator<row>

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

Statement.run([values]) -> info

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

Statement.database

The Database object that instantiated this statement.

Statement.isFinalized

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

class SQLite3Error

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

Numbers

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.

Binary Large Objects (BLOBs)

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.