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}. 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
readonly?: boolean
Open the database as read-only (no write operations, no create).
Equivalent to constants.SQLITE_OPEN_READONLY
readwrite?: boolean
Open the database as read-write
Equivalent to constants.SQLITE_OPEN_READWRITE
safeIntegers?: boolean
When true, integers are returned as bigint.
When false, integers are returned as number and truncated to 52 bits.
strict?: boolean
When set to false or undefined:
Queries missing bound parameters do NOT throw an error
Bound named parameters in JavaScript need to exactly match the SQL query.
const db = new Database(":memory:", { strict: false }); db.run("INSERT INTO foo (name) VALUES ($name)", { $name: "foo" });
When set to true:
Queries missing bound parameters throw an error
Bound named parameters in JavaScript no longer need the $, :, or @ prefix. The SQL query keeps its prefix.

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
The underlying sqlite3 database handle
In native code, this is not a file descriptor, but an index into an array of database handles
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.

[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

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?: number | ArrayBufferView<ArrayBufferLike>
): number;
See sqlite3_file_control for more information.
fileControl(
zDbName: string,
op: number,
arg?: number | ArrayBufferView<ArrayBufferLike>
): number;
See sqlite3_file_control for more information.
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.
@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.
@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 following types can be used when binding parameters:
JavaScript type SQLite type
string TEXT
number INTEGER or DECIMAL
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

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

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

@param insideTransaction

The callback which runs inside a transaction

// setup
import { Database } from "bun:sqlite";
const db = Database.open(":memory:");
db.exec(
  "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 },
]);

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.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)");
    db.exec('INSERT INTO test (name) VALUES ("Hello")');
    db.exec('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.exec("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.exec("insert into test (name) values ('foo')");
      throw new Error("Expected error");
    } catch (e) {
      expect(e.message).toBe("attempt to write a readonly database");
    }
});