bun:sqlite module | API Reference | Bun

Constants from sqlite3.h

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

Allow creating a new database

Open the database as read-only (no write operations, no create).

Open the database for reading and writing

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());

The filename passed when new Database() was called

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

The underlying sqlite3 database handle

In native code, this is not a file descriptor, but an index into an array of database handles

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

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.

See sqlite3_file_control for more information.

See sqlite3_file_control for more information.

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.

name/path of the extension to load

optional entry point of the extension

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.

The SQL query to compile

Optional bindings for the query

A Statement instance

// compile the query
const stmt = db.query("SELECT * FROM foo WHERE bar = ?");
// run the query
stmt.all("baz");

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.

The SQL query to compile

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();

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:

Useful for queries like:

The SQL query to run

Optional bindings for the query

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 }

Save the database to an in-memory Buffer object.

Internally, this calls sqlite3_serialize.

Name to save the database as

Buffer containing the serialized database

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

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 },
]);

Load a serialized SQLite3 database

Internally, this calls sqlite3_deserialize.

Data to load

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");
}
});

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.

Data to load

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");
}
});

Open or create a SQLite3 databases

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

defaults to {readwrite: true, create: true}. If a number, then it's treated as SQLITE_OPEN_* constant flags.

This is an alias of new Database()

See Database

Change the dynamic library path to SQLite

The path to the SQLite library

Errors from SQLite have a name SQLiteError.

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

This corresponds to sqlite3_error_offset.

The cause of the error.

The name of the SQLite3 error code

"SQLITE_CONSTRAINT_UNIQUE"

The SQLite3 extended error code

This corresponds to sqlite3_extended_errcode.

The Error.stackTraceLimit property specifies the number of stack frames collected by a stack trace (whether generated by new Error().stack or Error.captureStackTrace(obj)).

The default value is 10 but may be set to any valid JavaScript number. Changes will affect any stack trace captured after the value has been changed.

If set to a non-number value, or set to a negative number, stack traces will not capture any frames.

Create .stack property on a target object

Check if a value is an instance of Error

The value to check

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

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"}]

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"]

The actual SQLite column types from the first row of the result set. Useful for expressions and computed columns, which are not covered by declaredTypes

Returns an array of SQLite type constants as uppercase strings:

Requirements:

Behavior:

const stmt = db.prepare("SELECT id, name, age FROM users WHERE id = 1");

console.log(stmt.columnTypes);
// => ["INTEGER", "TEXT", "INTEGER"]

// For expressions:
const exprStmt = db.prepare("SELECT length('bun') AS str_length");
console.log(exprStmt.columnTypes);
// => ["INTEGER"]

The declared column types from the table schema.

Returns an array of declared type strings from sqlite3_column_decltype():

Requirements:

Behavior:

// For table columns:
const stmt = db.prepare("SELECT id, name, weight FROM products");
stmt.get();
console.log(stmt.declaredTypes);
// => ["INTEGER", "TEXT", "REAL"]

// For expressions (no declared types):
const exprStmt = db.prepare("SELECT length('bun') AS str_length");
exprStmt.get();
console.log(exprStmt.declaredTypes);
// => [null]

Native object representing the underlying sqlite3_stmt

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

For stable, typed access to statement metadata, use the typed properties on the Statement class:

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.

Execute the prepared statement and return all results as objects.

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"}]

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

A class to use

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.

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

Execute the prepared statement and return the first result.

If no result is returned, this returns null.

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:

Execute the prepared statement and return an

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.

Execute the prepared statement and return all results as arrays of Uint8Arrays.

This is similar to values() but returns all values as Uint8Array objects, regardless of their original SQLite type.

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.raw("baz");
// => [[Uint8Array(24)]]

stmt.raw();
// => [[Uint8Array(24)]]

Execute the prepared statement.

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.

A Changes object with changes and lastInsertRowid properties

const insert = db.prepare("INSERT INTO users (name) VALUES (?)");
insert.run("Alice");
// => { changes: 1, lastInsertRowid: 1 }
insert.run("Bob");
// => { changes: 1, lastInsertRowid: 2 }

const update = db.prepare("UPDATE users SET name = ? WHERE id = ?");
update.run("Charlie", 1);
// => { changes: 1, lastInsertRowid: 2 }

The following types can be used when binding parameters:

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'"

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.

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:

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.

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

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

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

Options for Database

Allow creating a new database

Equivalent to constants.SQLITE_OPEN_CREATE

Open the database as read-only (no write operations, no create).

Equivalent to constants.SQLITE_OPEN_READONLY

Open the database as read-write

Equivalent to constants.SQLITE_OPEN_READWRITE

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.

When set to false or undefined:

const db = new Database(":memory:", { strict: false });
db.run("INSERT INTO foo (name) VALUES ($name)", { $name: "foo" });

When set to true: