Create a new database. The file at the given path will be opened with the mode specified in options. The default mode is create.

If no path is given, or if the memory option is set, the database is opened in memory.

Examples

Create an in-memory database.

const db = new DB();

Open a database backed by a file on disk.

const db = new DB("path/to/database.sqlite");

Pass options to open a read-only database.

const db = new DB("path/to/database.sqlite", { mode: "read" });

Returns true when in auto commit mode and false otherwise. This corresponds to the SQLite function sqlite3_get_autocommit.

Return the number of rows modified, inserted or deleted by the most recently completed query. This corresponds to the SQLite function sqlite3_changes.

Returns true when the database handle is closed and can no longer be used.

Get last inserted row id. This corresponds to the SQLite function sqlite3_last_insert_rowid.

Before a row is inserted for the first time (since the database was opened), this returns 0.

Return the number of rows modified, inserted or deleted since the database was opened. This corresponds to the SQLite function sqlite3_total_changes.

Close the database. This must be called if the database is no longer used to avoid leaking open file descriptors.

If called with force = true, any non-finalized PreparedQuery objects will be finalized. Otherwise, this throws if there are active queries.

close may safely be called multiple times.

Creates a custom (scalar) SQL function that can be used in queries.

Examples

const log = (value: unknown) => {
  console.log(value);
  return value;
};
db.createFunction(log);
db.query("SELECT name, log(updated_at) FROM users");

If a function is pure (i.e. always returns the same result given the same input), it can be marked as deterministic to enable additional optimizations.

const discount = (price: number, salePercent: number) => num * (1 - salePercent / 100);
db.createFunction(discount, { deterministic: true });
db.query("SELECT name, discount(price, :sale) FROM products", { sale: 15 });

The function name can be set explicitly.

db.createFunction(() => Math.random(), { name: "jsRandom" });
db.query("SELECT jsRandom()");

Functions can also take a variable number of arguments.

const sum = (...nums: number[]) => nums.reduce((sum, num) => sum + num, 0);
db.createFunction(sum, { deterministic: true });
db.query("SELECT sum(1, 2), sum(1,2,3,4)");

Delete a user-defined SQL function previously created with createFunction.

After the function is deleted, it can no longer be used in queries, and is free to be re-defined.

Example

const double = (num: number) => num * 2;
db.createFunction(double);
// use the function ...
db.deleteFunction("double");

Deserialize a database.

The format is the same as would be read from disk when opening a database from a file.

When the database is deserialized, the contents of the passed data buffer are copied.

Examples

Replace the default (main) database schema with the contents from data.

db.deserialize(data);

Create an in-memory database from a buffer.

const db = new DB();
db.deserialize(data);

Deserialize data as a read-only database.

db.deserialize(data, { mode: "read" });

Specify a schema name different from main. Note that it is not possible to deserialize into the temp database.

db.execute("ATTACH DATABASE ':memory:' AS other"); // create schema 'other'
db.deserialize(data, { schema: "other" });

For more details see https://www.sqlite.org/c3ref/deserialize.html and https://www.sqlite.org/lang_attach.html.

Run multiple semicolon-separated statements from a single string.

This method cannot bind any query parameters, and any result rows are discarded. It is only for running a chunk of raw SQL; for example, to initialize a database.

Example

db.execute(`
  CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    age REAL,
    city TEXT
  );
  INSERT INTO people (name, age, city) VALUES ('Peter Parker', 21, 'nyc');
`);

Prepares the given SQL query, so that it can be run multiple times and potentially with different parameters.

If a query will be issued a lot, this is more efficient than using query. A prepared query also provides more control over how the query is run, as well as access to meta-data about the issued query.

The returned PreparedQuery object must be finalized by calling its finalize method once it is no longer needed.

Typing Queries

Prepared query objects accept three type parameters to specify precise types for returned data and query parameters.

• The first type parameter R indicates the tuple type for rows returned by the query.

• The second type parameter O indicates the record type for rows returned as entries (mappings from column names to values).

• The third type parameter P indicates the type this query accepts as parameters.

Note, that the correctness of those types must be guaranteed by the caller of this function.

Example

const query = db.prepareQuery<
  [string, number],
  { name: string, age: number },
  { city: string },
 >("SELECT name, age FROM people WHERE city = :city");
// use query ...
query.finalize();

Query the database and return all matching rows.

This is equivalent to calling all on a prepared query which is then immediately finalized.

The type parameter R may be supplied by the user to indicated the type for the rows returned by the query. Notice that the user is responsible for ensuring the correctness of the supplied type.

To avoid SQL injection, user-provided values should always be passed to the database through a query parameter.

See QueryParameterSet for documentation on how values can be bound to SQL statements.

See QueryParameter for documentation on how values are returned from the database.

Examples

const rows = db.query<[string, number]>("SELECT name, age FROM people WHERE city = ?", [city]);
// rows = [["Peter Parker", 21], ...]

const rows = db.query<[string, number]>(
  "SELECT name, age FROM people WHERE city = :city",
  { city },
 );
// rows = [["Peter Parker", 21], ...]

Like query except each row is returned as an object containing key-value pairs.

Examples

const rows = db.queryEntries<{ name: string, age: number }>("SELECT name, age FROM people");
// rows = [{ name: "Peter Parker", age: 21 }, ...]

const rows = db.queryEntries<{ name: string, age: number }>(
  "SELECT name, age FROM people WHERE age >= :minAge",
  { minAge },
 );
// rows = [{ name: "Peter Parker", age: 21 }, ...]

Serialize a database.

The format is the same as would be written to disk when modifying a database opened from a file. So for an on-disk database file, this is just a copy of the file contents on disk.

If no schema name is specified the default (main) schema is serialized.

Examples

const data = db.serialize();

Serialize the in-memory temporary database

const temp = db.serialize("temp");

Run a function within the context of a database transaction. If the function throws an error, the transaction is rolled back. Otherwise, the transaction is committed when the function returns.

Calls to transaction may be nested. Nested transactions behave like SQLite save points.

Example

db.transaction(() => {
  // call db.query ...
  db.transaction(() => {
    // nested transaction
  });
  // throw to roll back everything
});