SQLite - Bun

Bun includes a high-performance SQLite driver built into the runtime. It’s powered by SQLite3 and provides a simple, synchronous API.

Quick Start

import { Database } from "bun:sqlite";

const db = new Database("mydb.sqlite");

// Create table
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )
`);

// Insert data
db.run("INSERT INTO users (name, email) VALUES (?, ?)", [
  "Alice",
  "alice@example.com",
]);

// Query data
const users = db.query("SELECT * FROM users").all();
console.log(users);

Opening a Database

File Database

const db = new Database("app.db");

In-Memory Database

const db = new Database(":memory:");
// or
const db = new Database();

With Options

const db = new Database("app.db", {
  readonly: false, // Allow writes (default)
  create: true, // Create if doesn't exist (default)
  readwrite: true, // Read-write mode (default)
  safeIntegers: false, // Return numbers as bigint for >52 bits
  strict: true, // Throw on missing parameters
});

Using Database.open()

const db = Database.open("app.db");
// Same as new Database("app.db")

Executing Queries

db.run()

Execute a query without returning results:

const result = db.run(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["Bob", "bob@example.com"]
);

console.log(result.changes); // 1
console.log(result.lastInsertRowid); // 2

db.query()

Prepare and cache a query:

const query = db.query("SELECT * FROM users WHERE id = ?");

// Execute multiple times
const user1 = query.get(1);
const user2 = query.get(2);

db.prepare()

Prepare a query without caching:

const stmt = db.prepare("SELECT * FROM users WHERE name = ?");
const users = stmt.all("Alice");

Querying Data

Get Single Row

const query = db.query("SELECT * FROM users WHERE id = ?");
const user = query.get(1);

console.log(user); // { id: 1, name: "Alice", email: "alice@example.com" }

Get All Rows

const query = db.query("SELECT * FROM users");
const users = query.all();

console.log(users); // [{ id: 1, ... }, { id: 2, ... }]

Get Values as Arrays

const query = db.query("SELECT name, email FROM users");
const rows = query.values();

console.log(rows); // [["Alice", "alice@example.com"], ["Bob", "bob@example.com"]]

Iterate Rows

const query = db.query("SELECT * FROM users");

for (const user of query.iterate()) {
  console.log(user.name);
}

Parameter Binding

Positional Parameters

// ? placeholders
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

// Multiple parameters
const query = db.query("SELECT * FROM users WHERE name = ? AND email = ?");
const user = query.get("Alice", "alice@example.com");

Named Parameters

// With strict mode disabled
const db = new Database("app.db", { strict: false });
db.run("INSERT INTO users (name, email) VALUES ($name, $email)", {
  $name: "Alice",
  $email: "alice@example.com",
});

// With strict mode enabled (default in newer versions)
const db = new Database("app.db", { strict: true });
db.run("INSERT INTO users (name, email) VALUES ($name, $email)", {
  name: "Alice", // No $ prefix needed
  email: "alice@example.com",
});

Supported Types

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

Transactions

Basic Transaction

const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

const insertMany = db.transaction((users) => {
  for (const user of users) {
    insertUser.run(user.name, user.email);
  }
});

// Execute transaction
insertMany([
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
]);

Transaction Modes

const insertMany = db.transaction((users) => {
  // ...
});

// Default (DEFERRED)
insertMany(users);

// IMMEDIATE
insertMany.immediate(users);

// EXCLUSIVE
insertMany.exclusive(users);

// DEFERRED
insertMany.deferred(users);

Manual Transactions

db.run("BEGIN");
try {
  db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  db.run("INSERT INTO users (name) VALUES (?)", ["Bob"]);
  db.run("COMMIT");
} catch (err) {
  db.run("ROLLBACK");
  throw err;
}

Custom Classes

Return custom class instances:

class User {
  id!: number;
  name!: string;
  email!: string;
  
  greet() {
    return `Hello, ${this.name}!`;
  }
}

const query = db.query("SELECT * FROM users").as(User);
const user = query.get();

console.log(user.greet()); // "Hello, Alice!"

Column Metadata

const query = db.query("SELECT id, name, age FROM users");

// Column names
console.log(query.columnNames); // ["id", "name", "age"]

// Number of parameters
console.log(query.paramsCount); // 0

// Execute first to get types
query.get();

// Column types from schema
console.log(query.declaredTypes); // ["INTEGER", "TEXT", "INTEGER"]

// Actual types from first row
console.log(query.columnTypes); // ["INTEGER", "TEXT", "INTEGER"]

Database Methods

Close Database

db.close();

// Close and throw if in use
db.close(true);

Check Transaction Status

if (db.inTransaction) {
  console.log("In transaction");
}

Serialize/Deserialize

// Serialize to Buffer
const data = db.serialize();

// Deserialize from Buffer  
const db2 = Database.deserialize(data);

// Read-only deserialize
const db3 = Database.deserialize(data, true);

// With options
const db4 = Database.deserialize(data, {
  readonly: false,
  strict: true,
  safeIntegers: false,
});

Load Extension

// Set custom SQLite library (macOS only, must be done before opening)
Database.setCustomSQLite("/usr/local/lib/libsqlite3.dylib");

// Load extension
db.loadExtension("./my-extension.so", "entry_point");

File Control

import { constants } from "bun:sqlite";

// Control WAL persistence
db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0);

Statement Methods

const stmt = db.query("SELECT * FROM users WHERE id = ?");

// Get expanded SQL
console.log(stmt.toString()); // "SELECT * FROM users WHERE id = 1"

// Finalize statement
stmt.finalize();

// Auto-finalize with using
using stmt2 = db.query("SELECT * FROM users");
// Automatically finalized when out of scope

Error Handling

import { SQLiteError } from "bun:sqlite";

try {
  db.run("INVALID SQL");
} catch (err) {
  if (err instanceof SQLiteError) {
    console.error("SQLite error:", err.message);
    console.error("Error code:", err.code); // "SQLITE_ERROR"
    console.error("Error number:", err.errno); // 1
  }
}

Using Disposables

// Database auto-closes when out of scope
using db = new Database(":memory:");
db.run("CREATE TABLE test (id INTEGER)");
// Automatically closed here

// Statement auto-finalizes
using stmt = db.query("SELECT * FROM test");
stmt.all();
// Automatically finalized here

Type Signatures

class Database {
  constructor(filename?: string, options?: DatabaseOptions);
  static open(filename: string, options?: DatabaseOptions): Database;
  
  run<T extends SQLQueryBindings[]>(sql: string, ...params: T[]): Changes;
  query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
    sql: string
  ): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;
  prepare<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
    sql: string,
    params?: ParamsType
  ): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;
  
  transaction<A extends any[], T>(
    fn: (...args: A) => T
  ): {
    (...args: A): T;
    deferred: (...args: A) => T;
    immediate: (...args: A) => T;
    exclusive: (...args: A) => T;
  };
  
  close(throwOnError?: boolean): void;
  serialize(name?: string): Buffer;
  static deserialize(data: Buffer | ArrayBuffer, options?: DatabaseOptions): Database;
  
  loadExtension(path: string, entryPoint?: string): void;
  fileControl(op: number, arg?: ArrayBufferView | number): number;
  
  readonly filename: string;
  readonly handle: number;
  readonly inTransaction: boolean;
  
  [Symbol.dispose](): void;
}

class Statement<ReturnType = unknown, ParamsType extends SQLQueryBindings[] = any[]> {
  all(...params: ParamsType): ReturnType[];
  get(...params: ParamsType): ReturnType | null;
  run(...params: ParamsType): Changes;
  values(...params: ParamsType): any[][];
  iterate(...params: ParamsType): IterableIterator<ReturnType>;
  
  as<T>(Class: new (...args: any[]) => T): Statement<T, ParamsType>;
  
  finalize(): void;
  toString(): string;
  
  readonly columnNames: string[];
  readonly paramsCount: number;
  readonly columnTypes: Array<"INTEGER" | "FLOAT" | "TEXT" | "BLOB" | "NULL" | null>;
  readonly declaredTypes: Array<string | null>;
  
  [Symbol.dispose](): void;
}

interface Changes {
  changes: number;
  lastInsertRowid: number | bigint;
}

​Quick Start

​Opening a Database

​File Database

​In-Memory Database

​With Options

​Using Database.open()

​Executing Queries

​db.run()

​db.query()

​db.prepare()

​Querying Data

​Get Single Row

​Get All Rows

​Get Values as Arrays

​Iterate Rows

​Parameter Binding

​Positional Parameters

​Named Parameters

​Supported Types

​Transactions

​Basic Transaction

​Transaction Modes

​Manual Transactions

​Custom Classes

​Column Metadata

​Database Methods

​Close Database

​Check Transaction Status

​Serialize/Deserialize

​Load Extension

​File Control

​Statement Methods

​Error Handling

​Using Disposables

​Type Signatures