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 set to true, integers are returned as bigint types.
When set to false, integers are returned as number types and truncated to 52 bits.
strict?: boolean
When set to false or undefined:
Queries missing bound parameters will 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 will throw an error
Bound named parameters in JavaScript no longer need to be $, :, or @. The SQL query will remain prefixed.

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
[Symbol.dispose](): void;
Closes the database when using the async resource proposal
using db = new Database("myapp.db"); doSomethingWithDatabase(db); // Automatically closed when `db` goes out of scope
close(
throwOnError?: boolean
): void;
Close the database connection.
It is safe to call this method multiple times. If the database is already closed, this is a no-op. Running queries after the database has been closed will throw an error.
@param throwOnError
If true, then the database will throw an error if it is in use
db.close();
This is called automatically when the database instance is garbage collected.
Internally, this calls sqlite3_close_v2.
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
macOS requires a custom SQLite3 library to be linked because the Apple build of SQLite for macOS 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.
Under the hood, 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.
This does not execute the query, but instead prepares it for later execution and caches the compiled query if possible.
Under the hood, this calls sqlite3_prepare_v3.
@param sql
The SQL query to compile
@returns
Statment 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, so if you want to run a query multiple times, you should use prepare instead.
Under the hood, 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
@param sql
The SQL query to run
@param bindings
Optional bindings for the query
@returns
Database instance
db.run("CREATE TABLE foo (bar TEXT)"); db.run("INSERT INTO foo VALUES (?)", ["baz"]);
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
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 will begin a new transaction. When the function returns, the transaction will be committed. If an exception is thrown, the transaction will be rolled back (and the exception will propagate 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 }, ]);

JavaScript type	SQLite type
`string`	`TEXT`
`number`	`INTEGER` or `DECIMAL`
`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.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");
    }
});