How to Use bun:sqlite for Embedded Database Operations in Bun Applications

Bun ships with a first-class SQLite binding via the bun:sqlite module that lets you embed a zero-configuration SQL database directly in your JavaScript or TypeScript code, providing a Database class for connections and a Statement class for prepared queries.

The Bun runtime (available at oven-sh/bun) includes a built-in SQLite implementation that requires no external dependencies or native module compilation. According to the source code in [src/js/bun/sqlite.ts](https://github.com/oven-sh/bun/blob/main/src/js/bun/sqlite.ts), this module exposes a JavaScript wrapper around a high-performance C++ SQLite engine, offering features like automatic statement caching, transaction helpers, and database serialization.

Core Architecture of bun:sqlite

The bun:sqlite module is structured around two primary classes and a constants object that mirrors native SQLite flags.

• Database class (defined at lines 45-124): Manages SQLite connections, handles file or in-memory databases, and orchestrates transactions. It includes a built-in query cache that stores the most recent 20 prepared statements by default to avoid recompilation overhead.
• Statement class (implemented at lines 30-84): Wraps prepared SQLite statements and provides methods like run(), get(), all(), and iterate() that forward to the underlying C++ JSSQLStatement implementation.
• constants object (lines 25-96): Exposes SQLite open flags, prepare flags, and fcntl commands for low-level database control.
• C++ Bridge: The heavy lifting occurs in native code referenced via $cpp("JSSQLStatement.cpp", "createJSSQLStatementConstructor"), instantiated lazily during module initialization.

Opening Database Connections

Create a database connection by instantiating the Database class with a file path or the special :memory: identifier for temporary databases.

import SQLite from "bun:sqlite";

// Persistent file-based database (creates file if missing)
const db = new SQLite.Database("app.db");

// Temporary in-memory database
const memDb = new SQLite.Database(":memory:");

The constructor accepts an options object to control behavior. According to the implementation (lines 77-105), you can pass flags like readonly, create, or readwrite to determine how the database file is opened.

Executing Queries and CRUD Operations

The Database class provides high-level methods for running SQL commands without manual statement preparation.

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

// Insert with parameter binding (prevents SQL injection)
db.run("INSERT INTO users (name) VALUES (?)", "Alice");

// Query a single row
const user = db.query("SELECT * FROM users WHERE name = ?").get("Alice");
console.log(user.id, user.name); // 1 Alice

// Iterate over multiple rows
for (const row of db.query("SELECT id, name FROM users")) {
  console.log(row.id, row.name);
}

The run() method delegates directly to the native layer (lines 31-44), while query() returns a cached Statement instance (lines 66-95). The module automatically caches up to Database.MAX_QUERY_CACHE_SIZE statements (default 20) to improve performance for repeated queries.

Working with Prepared Statements

For queries executed multiple times, explicitly prepare a statement to maximize performance and explicitly manage resources.

const stmt = db.prepare("UPDATE users SET name = ? WHERE id = ?");

// Execute with bound parameters
stmt.run("Bob", 1);

// Retrieve the updated row
const updated = stmt.get(1);
console.log(updated.name); // Bob

// Explicit cleanup (optional but recommended for long-running processes)
stmt.finalize();

The Statement wrapper exposes methods that forward to the underlying C++ object, including run(), get(), all(), values(), raw(), and as() (lines 94-108). These methods handle parameter binding and result serialization automatically.

Managing Transactions

The module provides a specialized transaction helper that ensures atomic operations with automatic rollback on failure.

// Create a transaction function
const insertUsers = db.transaction(() => {
  db.run("INSERT INTO users (name) VALUES ('Carol')");
  db.run("INSERT INTO users (name) VALUES ('Dave')");
});

try {
  insertUsers(); // Automatically commits if successful
} catch (e) {
  console.error("Transaction failed, rolled back:", e);
}

The transaction() method (lines 98-108) supports four isolation levels via properties on the returned function:

• insertUsers.default: Standard immediate transaction
• insertUsers.deferred: Deferred transaction (locks acquired on first read/write)
• insertUsers.immediate: Immediate transaction (locks acquired immediately)
• insertUsers.exclusive: Exclusive transaction (exclusive locks from start)

Internally, the transaction controller caches BEGIN, COMMIT, and ROLLBACK statements using the wrapTransaction helper (lines 58-84) to minimize preparation overhead.

Advanced Features

Serializing and Deserializing Databases

You can capture an entire database state into a Buffer and restore it later, useful for backups or transferring in-memory databases.

// Serialize the main database to a Buffer
const snapshot = db.serialize(); // Uses "main" schema by default

// Restore into a new in-memory database
const restored = SQLite.Database.deserialize(snapshot);
const count = restored.query("SELECT COUNT(*) FROM users").get();
console.log("Restored row count:", count["COUNT(*)"]);

The serialize() method (lines 50-52) and static deserialize() method (lines 62-74) wrap the native SQLite serialize/deserialize API.

Using a Custom SQLite Binary

For applications requiring specific SQLite extensions or compile-time options, you can substitute the bundled library.

// Load a custom SQLite build (e.g., with specific extensions)
SQLite.Database.setCustomSQLite("/usr/local/lib/custom_sqlite3.so");
const customDb = new SQLite.Database("extended.db");

This static method is defined at lines 82-88 in the source. After setting a custom binary, you can load extensions using standard SQLite extension loading mechanisms.

Low-Level File Control

Access SQLite's fcntl interface for advanced database configuration like WAL mode tuning.

// Example: Enable WAL mode using fileControl
db.fileControl(
  SQLite.constants.SQLITE_FCNTL_PRAGMA,
  "journal_mode=WAL"
);

The fileControl() method (lines 90-98) forwards to the native SQL.fcntl implementation, with constants available in the constants export (lines 54-78).

Summary

• bun:sqlite provides zero-dependency SQLite integration in the Bun runtime via src/js/bun/sqlite.ts.
• The Database class handles connections, automatic query caching (default 20 statements), and transaction orchestration.
• The Statement class wraps prepared queries with methods like run(), get(), and iterate() that bind parameters safely.
• Transactions support deferred, immediate, and exclusive modes with automatic rollback on error.
• Serialization methods allow full database backup/restore via Buffer objects.
• Advanced users can swap the native SQLite library using setCustomSQLite() or access low-level controls via fileControl().

Frequently Asked Questions

How do I enable Write-Ahead Logging (WAL) mode in bun:sqlite?

Use the standard SQLite PRAGMA command through the run() method or the low-level fileControl() API. The simplest approach is db.run("PRAGMA journal_mode=WAL"), which persists the setting to the database file. According to the source code, fileControl() at lines 90-98 provides direct access to SQLite's fcntl interface for advanced locking configurations.

What is the difference between db.query() and db.prepare() in bun:sqlite?

db.query() returns a Statement instance that is automatically cached in the query cache (lines 66-95), making it ideal for one-off or frequently repeated queries. db.prepare() creates a new Statement without cache insertion, giving you explicit control over the statement lifecycle and requiring manual finalize() calls for cleanup. Both wrap the same underlying C++ JSSQLStatement implementation.

Can I use bun:sqlite with an existing SQLite database file from Python or Node.js?

Yes. The bun:sqlite module uses standard SQLite file formats and is compatible with databases created by other languages. Simply pass the file path to the Database constructor: new SQLite.Database("existing.db"). The constructor handles file creation flags and read/write permissions as implemented in lines 77-105 of src/js/bun/sqlite.ts.

How does the query cache work and can I configure its size?

The Database class maintains an LRU cache of prepared statements with a default maximum size of 20 entries, controlled by Database.MAX_QUERY_CACHE_SIZE. When you call query() with the same SQL string, the module returns the cached Statement instead of re-preparing it. You can modify MAX_QUERY_CACHE_SIZE before creating queries to adjust memory usage versus preparation overhead.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}