DocsReferenceGuidesBlog
Discord logo
Discord
GitHub logo
Bun

module

bun:sqlite

The 'bun:sqlite' module is a high-performance SQLite3 driver built directly into Bun. The API is simple, synchronous, and inspired by better-sqlite3, offering a clean and intuitive interface for database operations.

Key features include prepared statements, transactions, both named and positional parameters, datatype conversions (BLOB to Uint8Array), mapping query results to classes, and support for bigint. Multi-query statements can be executed in a single call to database.run().

Performance benchmarks show bun:sqlite is approximately 3-6x faster than better-sqlite3 and 8-9x faster than deno.land/x/sqlite for read queries, making it the fastest SQLite driver available for JavaScript.

  • namespace constants

    Constants from sqlite3.h

    This list isn't exhaustive, but some of the ones which are relevant

  • 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

    • 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[]

      This is an alias of Database.run

    • op: number,
      arg?: number | ArrayBufferView<ArrayBufferLike>
      ): number;

      See sqlite3_file_control for more information.

      zDbName: string,
      op: number,
      arg?: number | ArrayBufferView<ArrayBufferLike>
      ): number;

      See sqlite3_file_control for more information.

    • 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[]

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

    • 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

      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 }

      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 }

      This is an alias of new Database()

      See Database

    • path: string
      ): boolean;

      Change the dynamic library path to SQLite

      @param path

      The path to the SQLite library

  • class SQLiteError

    Errors from SQLite have a name SQLiteError.

    • readonly byteOffset: number

      The UTF-8 byte offset of the sqlite3 query that failed, if known

      This corresponds to sqlite3_error_offset.

    • cause?: unknown

      The cause of the error.

    • code?: string

      The name of the SQLite3 error code

      "SQLITE_CONSTRAINT_UNIQUE"
    • errno: number

      The SQLite3 extended error code

      This corresponds to sqlite3_extended_errcode.

    • message: string
    • readonly name: 'SQLiteError'
    • stack?: string
    • static prepareStackTrace?: (err: Error, stackTraces: CallSite[]) => any

      Optional override for formatting stack traces

    • static stackTraceLimit: number

      The maximum number of stack frames to capture.

    • targetObject: object,
      constructorOpt?: Function
      ): void;

      Creates a .stack property on targetObject, which when accessed returns a string representing the location in the code at which Error.captureStackTrace() was called.

      const myObject = {};
Error.captureStackTrace(myObject);
myObject.stack;  // Similar to `new Error().stack`

      The first line of the trace will be prefixed with ${myObject.name}: ${myObject.message}.

      The optional constructorOpt argument accepts a function. If given, all frames above constructorOpt, including constructorOpt, will be omitted from the generated stack trace.

      The constructorOpt argument is useful for hiding implementation details of error generation from the user. For instance:

      function a() {
  b();
}

function b() {
  c();
}

function c() {
  // Create an error without stack trace to avoid calculating the stack trace twice.
  const { stackTraceLimit } = Error;
  Error.stackTraceLimit = 0;
  const error = new Error();
  Error.stackTraceLimit = stackTraceLimit;

  // Capture the stack trace above function b
  Error.captureStackTrace(error, b); // Neither function c, nor b is included in the stack trace
  throw error;
}

a();
    • static isError(
      value: unknown
      ): value is Error;

      Check if a value is an instance of Error

      @param value

      The value to check

      @returns

      True if the value is an instance of Error, false otherwise

  • class Statement<ReturnType = unknown, ParamsType extends SQLQueryBindings[] = any[]>

    A prepared statement.

    This is returned by Database.prepare and Database.query.

    const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");
stmt.all("baz");
// => [{bar: "baz"}]
    • readonly columnNames: string[]

      The names of the columns returned by the prepared statement.

      const stmt = db.prepare("SELECT bar FROM foo WHERE bar = ?");

console.log(stmt.columnNames);
// => ["bar"]
    • readonly native: any

      Native object representing the underlying sqlite3_stmt

      This is left untyped because the ABI of the native bindings may change at any time.

    • readonly paramsCount: number

      The number of parameters expected in the prepared statement.

      const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");
console.log(stmt.paramsCount);
// => 1

    • Calls finalize if it wasn't already called.

    • [Symbol.iterator](): IterableIterator<ReturnType>;
    • ...params: ParamsType
      ): ReturnType[];

      Execute the prepared statement and return all results as objects.

      @param params

      optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

      const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.all("baz");
// => [{bar: "baz"}]

stmt.all();
// => []

stmt.all("foo");
// => [{bar: "foo"}]
    • as<T = unknown>(
      Class: new (...args: any[]) => T
      ): Statement<T, ParamsType>;

      Make get and all return an instance of the provided Class instead of the default Object.

      @param Class

      A class to use

      @returns

      The same statement instance, modified to return an instance of Class

      This lets you attach methods, getters, and setters to the returned objects.

      For performance reasons, constructors for classes are not called, which means initializers will not be called and private fields will not be accessible.

      Custom class

      class User {
   rawBirthdate: string;
   get birthdate() {
     return new Date(this.rawBirthdate);
   }
}

const db = new Database(":memory:");
db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, rawBirthdate TEXT)");
db.run("INSERT INTO users (rawBirthdate) VALUES ('1995-12-19')");
const query = db.query("SELECT * FROM users");
query.as(User);
const user = query.get();
console.log(user.birthdate);
// => Date(1995, 12, 19)
    • finalize(): void;

      Finalize the prepared statement, freeing the resources used by the statement and preventing it from being executed again.

      This is called automatically when the prepared statement is garbage collected.

      It is safe to call this multiple times. Calling this on a finalized statement has no effect.

      Internally, this calls sqlite3_finalize.

    • ...params: ParamsType
      ): null | ReturnType;

      Execute the prepared statement and return the first result.

      If no result is returned, this returns null.

      @param params

      optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

      const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.get("baz");
// => {bar: "baz"}

stmt.get();
// => null

stmt.get("foo");
// => {bar: "foo"}

      The following types can be used when binding parameters:

      JavaScript typeSQLite type
      stringTEXT
      numberINTEGER or DECIMAL
      booleanINTEGER (1 or 0)
      Uint8ArrayBLOB
      BufferBLOB
      bigintINTEGER
      nullNULL
    • ...params: ParamsType
      ): IterableIterator<ReturnType>;

      Execute the prepared statement and return an

      @param params

      optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

    • ...params: ParamsType

      Execute the prepared statement. This returns undefined.

      @param params

      optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

      const stmt = db.prepare("UPDATE foo SET bar = ?");
stmt.run("baz");
// => undefined

stmt.run();
// => undefined

stmt.run("foo");
// => undefined

      The following types can be used when binding parameters:

      JavaScript typeSQLite type
      stringTEXT
      numberINTEGER or DECIMAL
      booleanINTEGER (1 or 0)
      Uint8ArrayBLOB
      BufferBLOB
      bigintINTEGER
      nullNULL
    • toString(): string;

      Return the expanded SQL string for the prepared statement.

      Internally, this calls sqlite3_expanded_sql() on the underlying sqlite3_stmt.

      const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?", "baz");
console.log(stmt.toString());
// => "SELECT * FROM foo WHERE bar = 'baz'"
console.log(stmt);
// => "SELECT * FROM foo WHERE bar = 'baz'"
    • ...params: ParamsType
      ): string | number | bigint | boolean | Uint8Array<ArrayBufferLike>[][];

      Execute the prepared statement and return the results as an array of arrays.

      In Bun v0.6.7 and earlier, this method returned null if there were no results instead of []. This was changed in v0.6.8 to align more with what people expect.

      @param params

      optional values to bind to the statement. If omitted, the statement is run with the last bound values or no parameters if there are none.

      const stmt = db.prepare("SELECT * FROM foo WHERE bar = ?");

stmt.values("baz");
// => [['baz']]

stmt.values();
// => [['baz']]

stmt.values("foo");
// => [['foo']]

stmt.values("not-found");
// => []

      The following types can be used when binding parameters:

      JavaScript typeSQLite type
      stringTEXT
      numberINTEGER or DECIMAL
      booleanINTEGER (1 or 0)
      Uint8ArrayBLOB
      BufferBLOB
      bigintINTEGER
      nullNULL
  • const native: any

    The native module implementing the sqlite3 C bindings

    It is lazily-initialized, so this will return undefined until the first call to new Database().

    The native module makes no gurantees about ABI stability, so it is left untyped

    If you need to use it directly for some reason, please let us know because that probably points to a deficiency in this API.

Type definitions

  • interface Changes

    An object representing the changes made to the database since the last run or exec call.

    • changes: number

      The number of rows changed by the last run or exec call.

    • lastInsertRowid: number | bigint

      If safeIntegers is true, this is a bigint. Otherwise, it is a number.

  • type SQLQueryBindings = string | bigint | NodeJS.TypedArray | number | boolean | null | Record<string, string | bigint | NodeJS.TypedArray | number | boolean | null>