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

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

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 force = true is passed, any non-finalized PreparedQuery objects will be finalized. Otherwise, this throws if there are active queries.

close may safely be called multiple times.

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.

Examples

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.

Examples

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 }, ...]

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.