DocsReferenceGuidesBlog
Discord logo
Discord
GitHub logo

Bun

Symbol

Database.constructor

constructor (filename?: string, options?: number | { create: boolean; readonly: boolean; readwrite: boolean; safeIntegers: boolean; strict: boolean }): Database

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

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

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

  • exec<ParamsType extends SQLQueryBindings[]>(sql: string, ...bindings: ParamsType[]): Changes

    This is an alias of Database.run

  • 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 typeSQLite type
    stringTEXT
    numberINTEGER or DECIMAL
    booleanINTEGER (1 or 0)
    Uint8ArrayBLOB
    BufferBLOB
    bigintINTEGER
    nullNULL
    @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(insideTransaction: (...args: any) => void): CallableFunction & { deferred: (...args: any) => void; exclusive: (...args: any) => void; immediate: (...args: any) => void }

    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 },
]);
  • 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");
    }
});
    static deserialize(serialized: ArrayBufferLike | TypedArray<ArrayBufferLike>, options?: { readonly: boolean; safeIntegers: boolean; strict: boolean }): Database

    Load a serialized SQLite3 database. This version enables you to specify additional options such as strict to put the database into strict mode.

    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 = Database.deserialize(input, { strict: true });

    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)", { foo: "baz" });
    expect(JSON.stringify(stmt.all())).toBe(
      JSON.stringify([
        {
          id: 1,
          name: "Hello",
        },
        {
          id: 2,
          name: "World",
        },
        {
          id: 3,
          name: "baz",
        },
      ]),
    );

    const db3 = Database.deserialize(input, { readonly: true, strict: true });
    try {
      db3.exec("insert into test (name) values ($foo)", { foo: "baz" });
      throw new Error("Expected error");
    } catch (e) {
      expect(e.message).toBe("attempt to write a readonly database");
    }
});
  • static open(filename: string, options?: number | { create: boolean; readonly: boolean; readwrite: boolean }): Database

    This is an alias of new Database()

    See Database

  • static setCustomSQLite(path: string): boolean

    Change the dynamic library path to SQLite

    @param path

    The path to the SQLite library