constructor

sqlite.Database.constructor

constructor Database(

filename?: string,

Open or create a SQLite3 database

@param filename

The filename of the database to open. Pass an empty string ("") or ":memory:" or undefined for an in-memory database.

@param options

defaults to {readwrite: true, create: true}, also for an object that sets none of readonly, create and readwrite. If a number, then it's treated as SQLITE_OPEN_* constant flags.

Referenced types

interface DatabaseOptions

Options for Database

create?: boolean
Allow creating a new database
Equivalent to constants.SQLITE_OPEN_CREATE
When false, opening a file that does not exist throws an error with the code SQLITE_CANTOPEN.
readonly?: boolean
Open the database as read-only (no write operations, no create).
Equivalent to constants.SQLITE_OPEN_READONLY
Together with create: true or readwrite: true, the constructor throws a TypeError.
readwrite?: boolean
Open the database as read-write
Equivalent to constants.SQLITE_OPEN_READWRITE
readwrite: true without create: true does not create the file. readwrite: false is the same as readonly: true.
safeIntegers?: boolean
When true, integers are returned as bigint.
When false, integers are returned as number, and integers beyond 53 bits are rounded to the nearest number.
When true, a bigint parameter that does not fit in a signed 64-bit integer throws a RangeError. When false, it keeps its low 64 bits: 2n ** 64n - 1n is stored as -1.
strict?: boolean
When set to true, a query with a missing bound parameter throws an error. When set to false or undefined, the parameter is NULL.
In both modes, the key of a named parameter can include the $, :, or @ prefix or leave it out. If an object has both keys, the key without the prefix wins in strict mode, and the key with the prefix otherwise.
const db = new Database(":memory:", { strict: true }); db.run("INSERT INTO foo (name) VALUES ($name)", { name: "foo" }); db.run("INSERT INTO foo (name) VALUES ($name)", { nmae: "foo" }); // throws

class Database

A SQLite3 database

const db = new Database("mydb.sqlite");
db.run("CREATE TABLE foo (bar TEXT)");
db.run("INSERT INTO foo VALUES (?)", ["baz"]);
console.log(db.query("SELECT * FROM foo").all());

readonly filename: string

The filename passed when new Database() was called

const db = new Database("mydb.sqlite");
console.log(db.filename);
// => "mydb.sqlite"

readonly handle: number
A number that identifies this Database in the process
This is not a file descriptor. It stays the same after close(), and no other Database gets it.
static MAX_QUERY_CACHE_SIZE: number
Maximum number of distinct SQL strings Database.query keeps cached per Database. Least-recently-used entries are evicted first. SQL text over 64 KB is never cached.

[Symbol.dispose](): void;

Closes the database at the end of a using block (explicit resource management)

using db = new Database("myapp.db");
doSomethingWithDatabase(db);
// Automatically closed when `db` goes out of scope

aggregate<T = any>(
name: string,
options: AggregateOptions<T>
): this;
Define a SQL aggregate function, such as sum() or group_concat(), that calls JavaScript functions. With inverse it is also a window function.
What is said about arguments, results, thrown values and the use of the Database in Database.function holds for each callback.
@param name
Name of the SQL function
@returns
The Database
db.aggregate("add_all", { start: 0, step: (total, value) => total + value, }); db.query("SELECT add_all(dollars) AS total FROM expenses").get(); // => { total: 92 }
clearQueryCache(): void;
Finalize the statements that Database.query has cached and empty the cache.
close(
throwOnError?: boolean
): void;
Close the database connection.
Statements created with Database.query are finalized immediately. With throwOnError unset or false, statements created with Database.prepare keep working until they are finalized or garbage collected, and the underlying connection is released after the last one (sqlite3_close_v2). With throwOnError: true, every outstanding statement is finalized, the connection is released immediately (sqlite3_close), and an error is thrown if SQLite fails to close it.
It is safe to call this method multiple times. If the database is already closed, this is a no-op. Creating new statements after close throws; using a statement that close finalized throws Database has closed, except toString(), which returns an empty string, and finalize(), which stays safe to call.
@param throwOnError
If true, finalize every outstanding statement and throw if the connection fails to close
db.close();
If a Database is garbage collected without being closed, the connection is released once every statement created from it has also been finalized or collected. using db = ... calls close(true).
fileControl(
op: number,
arg?: null | number | ArrayBufferView<ArrayBufferLike>
): number;
Call sqlite3_file_control on the main database.
SQLite reads the input of op from arg and writes its output there.
A number is passed as a 32-bit integer. What SQLite writes back is not returned.
A TypedArray or DataView of at least 8 bytes is passed as is. Use it to read what SQLite writes back.
null or no argument is for an op that takes none, such as SQLITE_FCNTL_RESET_CACHE. An op that reads its argument reads -1.
@returns
The SQLite status code. 0 is SQLITE_OK. 12 is SQLITE_NOTFOUND: the VFS does not implement op, or the database is in memory.
// -1 asks for the flag. SQLite writes 0 or 1 to the first element. const flag = new Int32Array([-1, 0]); db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, flag); console.log(flag[0]);
fileControl(
zDbName: string,
op: number,
arg?: null | number | ArrayBufferView<ArrayBufferLike>
): number;
Call sqlite3_file_control on the database with the schema name zDbName: "main" or the name of an attached database.
function(
name: string,
fn: (...args: any[]) => SQLFunctionResult
): this;
Define a SQL function that calls a JavaScript function.
The number of parameters of fn (fn.length) is the number of arguments the SQL function takes, unless varargs is set. Functions with the same name and different numbers of arguments exist side by side. A function with the same name and number of arguments replaces the earlier one, also a function that is built into SQLite.
An argument has the type that a column of a result row has: INTEGER is a number (a bigint with safeIntegers), BLOB is a Uint8Array. The return value is stored like a bound parameter, and undefined is NULL. Any other return value, such as an object or a promise, makes the query throw a TypeError.
What fn throws ends the query, and the call that ran the query (get(), all(), run(), ...) throws the same value.
While fn runs, every other use of this Database and of its statements throws TypeError: This database connection is busy executing a query.
@param name
Name of the SQL function
@param fn
Called with this set to undefined
@returns
The Database
db.function("add2", (a, b) => a + b); db.query("SELECT add2(12, 4) AS sum").get(); // => { sum: 16 } db.function("count_args", { varargs: true }, (...args) => args.length); db.query("SELECT count_args(1, 2, 3) AS n").get(); // => { n: 3 }
function(
name: string,
options: FunctionOptions,
fn: (...args: any[]) => SQLFunctionResult
): this;
Define a SQL function that calls a JavaScript function.
The number of parameters of fn (fn.length) is the number of arguments the SQL function takes, unless varargs is set. Functions with the same name and different numbers of arguments exist side by side. A function with the same name and number of arguments replaces the earlier one, also a function that is built into SQLite.
An argument has the type that a column of a result row has: INTEGER is a number (a bigint with safeIntegers), BLOB is a Uint8Array. The return value is stored like a bound parameter, and undefined is NULL. Any other return value, such as an object or a promise, makes the query throw a TypeError.
What fn throws ends the query, and the call that ran the query (get(), all(), run(), ...) throws the same value.
While fn runs, every other use of this Database and of its statements throws TypeError: This database connection is busy executing a query.
@param name
Name of the SQL function
@param fn
Called with this set to undefined
@returns
The Database
db.function("add2", (a, b) => a + b); db.query("SELECT add2(12, 4) AS sum").get(); // => { sum: 16 } db.function("count_args", { varargs: true }, (...args) => args.length); db.query("SELECT count_args(1, 2, 3) AS n").get(); // => { n: 3 }
loadExtension(
extension: string,
entryPoint?: string
): void;
Load a SQLite3 extension
On macOS, this requires linking a custom SQLite3 library because the Apple build of SQLite disables loading extensions. See Database.setCustomSQLite
Bun chooses the Apple build of SQLite on macOS because it brings a ~50% performance improvement.
@param extension
name/path of the extension to load
@param entryPoint
optional entry point of the extension
prepare<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
sql: string,
params?: ParamsType
): Statement<ReturnType, ParamsType extends any[] ? ParamsType<ParamsType> : [ParamsType]>;
Compile a SQL query and return a Statement object.
This does not cache the compiled query and does not execute the query.
Internally, this calls sqlite3_prepare_v3, which compiles the first statement of the string and ignores the rest. run runs them all.
@param sql
The SQL query to compile
@param params
Optional bindings for the query
@returns
A Statement instance
// compile the query const stmt = db.query("SELECT * FROM foo WHERE bar = ?"); // run the query stmt.all("baz");
query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
sql: string
): Statement<ReturnType, ParamsType extends any[] ? ParamsType<ParamsType> : [ParamsType]>;
Compile a SQL query and return a Statement object. This is the same as prepare except that it caches the compiled query if possible.
This does not execute the query; it prepares it for later execution.
Internally, this calls sqlite3_prepare_v3, which compiles the first statement of the string and ignores the rest. run runs them all.
@param sql
The SQL query to compile
@returns
A Statement instance
// compile the query const stmt = db.query("SELECT * FROM foo WHERE bar = ?"); // run the query stmt.all("baz"); // run the query again stmt.all();
run<ParamsType extends SQLQueryBindings[]>(
sql: string,
...bindings: ParamsType[]
): Changes;
Execute a SQL query without returning any results.
This does not cache the query. To run a query multiple times, use prepare instead.
Internally, this calls sqlite3_prepare_v3 followed by sqlite3_step and sqlite3_finalize.
The string can hold several statements, separated by ;. They run in order, and the first one gets the bindings. The first statement that fails throws, and the statements after it do not run.
The following types can be used when binding parameters:
JavaScript type SQLite type
string TEXT
number INTEGER or REAL
boolean INTEGER (1 or 0)
Uint8Array BLOB
Buffer BLOB
bigint INTEGER
null NULL
Useful for queries like:
CREATE TABLE
INSERT INTO
UPDATE
DELETE FROM
DROP TABLE
PRAGMA
ATTACH DATABASE
DETACH DATABASE
REINDEX
VACUUM
EXPLAIN ANALYZE
CREATE INDEX
CREATE TRIGGER
CREATE VIEW
CREATE VIRTUAL TABLE
CREATE TEMPORARY TABLE
@param sql
The SQL query to run
@param bindings
Optional bindings for the query
@returns
A Changes object with changes and lastInsertRowid properties
db.run("CREATE TABLE foo (bar TEXT)"); db.run("INSERT INTO foo VALUES (?)", ["baz"]); // => { changes: 1, lastInsertRowid: 1 }
serialize(
name?: string
): Buffer;
Save the database to an in-memory Buffer object.
Internally, this calls sqlite3_serialize.
@param name
Name to save the database as
@returns
Buffer containing the serialized database
transaction<A extends any[], T>(
insideTransaction: (...args: A) => T
): (...args: A) => T;
Creates a function that always runs inside a transaction. When the function is invoked, it begins a new transaction. When the function returns, the transaction is committed. If an exception is thrown, the transaction is rolled back (and the exception propagates as usual).
The function must be synchronous, because the transaction is committed when it returns. An async function returns at its first await: everything after that await runs outside the transaction, and an error thrown there does not roll anything back. Do the awaited work first and pass the results in.
If the function returns a promise that is already rejected, as an async function does when it throws before its first await, the transaction is rolled back and the promise is returned.
@param insideTransaction
The synchronous callback which runs inside a transaction
// setup import { Database } from "bun:sqlite"; const db = Database.open(":memory:"); db.run( "CREATE TABLE cats (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE, age INTEGER)" ); const insert = db.prepare("INSERT INTO cats (name, age) VALUES ($name, $age)"); const insertMany = db.transaction((cats) => { for (const cat of cats) insert.run(cat); }); insertMany([ { $name: "Joey", $age: 2 }, { $name: "Sally", $age: 4 }, { $name: "Junior", $age: 1 }, ]);

JavaScript type	SQLite type
`string`	`TEXT`
`number`	`INTEGER` or `REAL`
`boolean`	`INTEGER` (1 or 0)
`Uint8Array`	`BLOB`
`Buffer`	`BLOB`
`bigint`	`INTEGER`
`null`	`NULL`

static deserialize(

serialized: ArrayBufferLike | TypedArray<ArrayBufferLike>,

isReadOnly?: boolean

): Database;

Load a serialized SQLite3 database

Internally, this calls sqlite3_deserialize.

@param serialized

Data to load

@returns

Database instance

test("supports serialize/deserialize", () => {
    const db = Database.open(":memory:");
    db.run("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
    db.run('INSERT INTO test (name) VALUES ("Hello")');
    db.run('INSERT INTO test (name) VALUES ("World")');

    const input = db.serialize();
    const db2 = new Database(input);

    const stmt = db2.prepare("SELECT * FROM test");
    expect(JSON.stringify(stmt.get())).toBe(
      JSON.stringify({
        id: 1,
        name: "Hello",
      }),
    );

    expect(JSON.stringify(stmt.all())).toBe(
      JSON.stringify([
        {
          id: 1,
          name: "Hello",
        },
        {
          id: 2,
          name: "World",
        },
      ]),
    );
    db2.run("insert into test (name) values ('foo')");
    expect(JSON.stringify(stmt.all())).toBe(
      JSON.stringify([
        {
          id: 1,
          name: "Hello",
        },
        {
          id: 2,
          name: "World",
        },
        {
          id: 3,
          name: "foo",
        },
      ]),
    );

    const db3 = Database.deserialize(input, true);
    try {
      db3.run("insert into test (name) values ('foo')");
      throw new Error("Expected error");
    } catch (e) {
      expect(e.message).toBe("attempt to write a readonly database");
    }
});