Bun 08 ⚡ Bun’s Built-in SQLite Database API (bun:sqlite)

Bun ships with a native SQLite driver, and it is not a JavaScript wrapper around a WebAssembly module or a binding to a system library. The bun:sqlite module is implemented in Zig and linked directly into the runtime. This means there is no npm install sqlite3, no better-sqlite3 compilation step, and no native module incompatibility when the Bun version changes. The API is synchronous, modeled after better-sqlite3, and benchmarks show it is roughly three to six times faster than better-sqlite3 for read queries . For a runtime that already executes TypeScript and JSX natively, the database layer completes the picture: a single binary that can serve HTTP, run scripts, and query a persistent store without a dependency tree.

Key point: bun:sqlite is synchronous. Every query blocks the event loop until it completes. This is acceptable for local scripts, CLIs, and SQLite’s intended use case (embedded, single-process). It is not suitable for high-concurrency server workloads where multiple requests contend for the same database file, because SQLite serializes writes regardless of the driver.

---

Why a built-in SQLite driver matters

The native module problem. better-sqlite3 is a C++ addon. It must be compiled against the Node.js version that will load it. When Node.js releases a new major version, the addon must be rebuilt, and prebuilt binaries may not yet exist. Bun avoids this entirely because the SQLite driver is part of the binary. The bun:sqlite import works on every platform Bun supports, with no install step .

The synchronous advantage. SQLite is an embedded database. It reads and writes a file on disk. The overhead of a network round-trip does not exist, so the async/await ceremony that databases like PostgreSQL require is unnecessary. A synchronous API is simpler and faster for the workload SQLite is designed for. Bun exposes this directly rather than wrapping it in Promises that resolve immediately .

The performance claim. Bun’s benchmarks show three to six times faster reads than better-sqlite3 and eight to nine times faster than Deno’s SQLite wrapper . The speed comes from the direct Zig implementation and the avoidance of the Node.js C++ addon boundary. For CLI tools that query a local database on every invocation, the difference is measurable.

The type-mapping problem. JavaScript has no 64-bit integer type. Number is a double, limited to 53 bits of precision. SQLite supports signed 64-bit integers. When a database column contains a value larger than Number.MAX_SAFE_INTEGER, the default bun:sqlite behavior rounds it, silently corrupting the value. The safeIntegers: true option returns integers as BigInt and throws if a bound BigInt exceeds 64 bits . This is the correct setting for financial data, timestamps in nanoseconds, or any column that might hold a large ID.

The trade-off. Synchronous queries block the event loop. A SELECT that takes 50 milliseconds prevents any other request from being processed for 50 milliseconds. For a CLI or a local script, this is irrelevant. For an HTTP server handling concurrent requests, it is a bottleneck. The correct pattern is to keep the database queries fast (indexes, limited result sets) and to be aware that SQLite serializes writes regardless.

---

a. Opening a database and executing queries

The Database class is the entry point. It accepts a filename, :memory: for an in-memory database, or an empty string for a temporary database. The constructor options control read-only mode, creation behavior, strict parameter binding, and integer handling.

import { Database } from "bun:sqlite";

// File-based database
const db = new Database("mydb.sqlite");

// In-memory database
const memDb = new Database(":memory:");

// Read-only mode
const readDb = new Database("mydb.sqlite", { readonly: true });

// Strict mode: named parameters can be bound without the $ prefix
const strictDb = new Database("mydb.sqlite", { strict: true });

The db.run() method executes a query without returning rows. It is used for CREATE TABLE, INSERT, UPDATE, DELETE, and PRAGMA statements. The return value contains lastInsertRowid (the ID of the last inserted row) and changes (the number of rows affected) .

db.run("CREATE TABLE cats (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
const result = db.run("INSERT INTO cats (name, age) VALUES (?, ?)", ["Luna", 3]);
console.log(result.lastInsertRowid); // 1
console.log(result.changes); // 1

The db.query() method compiles the SQL statement and caches it. Calling query() again with the same SQL string returns the cached compiled statement, not a new compilation . The db.prepare() method compiles without caching, which is useful for dynamically generated SQL that will not be reused.

---

b. Statement methods: get, all, iterate, and values

A Statement is a compiled query. It can be executed multiple times with different parameter values. The method used determines the shape of the result.

const query = db.query("SELECT * FROM cats WHERE age > ?");

// Get the first row as an object (or null)
const first = query.get(2);
// { id: 2, name: "Salem", age: 5 }

// Get all rows as an array of objects
const all = query.all(2);
// [{ id: 2, name: "Salem", age: 5 }, ...]

// Iterate without loading all rows into memory
for (const row of query.iterate(2)) {
  console.log(row.name);
}

// Get rows as arrays instead of objects
const values = query.values(2);
// [[2, "Salem", 5], ...]

The iterate() method is the correct choice for large result sets. It uses a cursor and yields rows one at a time. A Statement has one cursor, and the most recent execution owns it. If the same statement is executed again inside an iteration loop, the loop throws. The fix is to use prepare() instead of query() for the inner query, or to finalize the statement before reusing it .

The .as(Class) method maps rows to instances of a class. The class prototype is assigned to the row object, so methods, getters, and setters work. The constructor is not called, and private fields are not accessible . This is a performance optimization, not a full instantiation.

class Cat {
  id: number;
  name: string;
  get isKitten() { return this.age < 1; }
}

const cats = db.query("SELECT * FROM cats").as(Cat).all();
console.log(cats[0].isKitten);

---

c. Parameters and transactions

Parameters are bound using positional (? or ?1), named ($name, :name, or @name) syntax. The binding object can include or omit the prefix; the prefix wins if both are present. With strict: true, the prefix is omitted in the binding object .

// Positional
db.query("SELECT * FROM cats WHERE name = ?").get("Luna");

// Named
db.query("SELECT * FROM cats WHERE name = $name").get({ $name: "Luna" });

// Named without prefix (strict mode)
db.query("SELECT * FROM cats WHERE name = $name").get({ name: "Luna" });

Transactions are created with db.transaction(). The method wraps a function and returns a new function. Calling the returned function begins a transaction, executes the wrapped function, and commits. If the wrapped function throws, the transaction is rolled back and the exception propagates .

const insertCat = db.prepare("INSERT INTO cats (name, age) VALUES ($name, $age)");

const insertCats = db.transaction((cats) => {
  for (const cat of cats) {
    insertCat.run(cat);
  }
  return cats.length;
});

const count = insertCats([
  { $name: "Keanu", $age: 2 },
  { $name: "Salem", $age: 5 },
]);

Nested transaction functions become savepoints. The outer transaction can roll back the entire set, or the inner savepoint can roll back only its portion . The transaction function has .deferred, .immediate, and .exclusive variants that control the SQLite BEGIN mode .

---

Complete Example Session

# ============================================
# PART 1: OPEN A DATABASE
# ============================================

import { Database } from "bun:sqlite";
const db = new Database("app.sqlite", { safeIntegers: true });

# ============================================
# PART 2: CREATE A TABLE
# ============================================

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

# ============================================
# PART 3: INSERT WITH PARAMETERS
# ============================================

const insert = db.prepare("INSERT INTO users (name, balance) VALUES ($name, $balance)");
insert.run({ $name: "Alice", $balance: 1000n });
insert.run({ $name: "Bob", $balance: 2500n });

# ============================================
# PART 4: QUERY WITH GET
# ============================================

const alice = db.query("SELECT * FROM users WHERE name = $name")
  .get({ $name: "Alice" });
// { id: 1, name: "Alice", balance: 1000n }

# ============================================
# PART 5: QUERY WITH ITERATE
# ============================================

const query = db.query("SELECT * FROM users");
for (const user of query.iterate()) {
  console.log(user.name);
}

# ============================================
# PART 6: TRANSACTION
# ============================================

const transfer = db.transaction((from, to, amount) => {
  db.run("UPDATE users SET balance = balance - ? WHERE name = ?", [amount, from]);
  db.run("UPDATE users SET balance = balance + ? WHERE name = ?", [amount, to]);
});

transfer("Alice", "Bob", 100n);

# ============================================
# PART 7: MAP TO CLASS
# ============================================

class User {
  id: number;
  name: string;
  balance: bigint;
  get isRich() { return this.balance > 2000n; }
}

const richUsers = db.query("SELECT * FROM users").as(User).all()
  .filter(u => u.isRich);

# ============================================
# PART 8: VALUES AS ARRAYS
# ============================================

const values = db.query("SELECT name, balance FROM users").values();
// [["Alice", 900n], ["Bob", 2600n]]

# ============================================
# PART 9: CLOSE DATABASE
# ============================================

db.close();

The nine parts trace the lifecycle: opening, table creation, parameterized insertion, single-row query, iteration, transaction, class mapping, array output, and cleanup.

---

Quick Reference

Database Options

Option	Effect
readonly: true	Open read-only
create: true	Create if not exists (default)
strict: true	Named parameters without prefix
safeIntegers: true	Return integers as BigInt

Statement Methods

Method	Returns
.get(...)	First row as object, or null
.all(...)	All rows as array of objects
.iterate(...)	Iterator for one-at-a-time processing
.values(...)	All rows as array of arrays
.run(...)	{ lastInsertRowid, changes }
.as(Class)	Rows as class instances
.finalize()	Free the statement

Parameter Binding

Syntax	Example
Positional	? or ?1
Named	$name, :name, @name
Strict mode	name (no prefix)

---

Best Practices

✅ Do This:

db.query("SELECT * FROM users WHERE id = $id").get({ $id: 1 });
<!-- Use named parameters for clarity -->               # ✅

const db = new Database("app.sqlite", { safeIntegers: true });
<!-- Use BigInt for 64-bit integers -->                # ✅

for (const row of query.iterate()) { ... }
<!-- Iterate for large result sets -->                 # ✅

❌ Don’t Do This:

# Assume Number handles 64-bit integers
<!-- Values above 2^53 are rounded -->                 # ❌

# Run the same query inside its own iterate loop
<!-- The cursor is overwritten; the loop throws -->    # ❌

# Use SQLite for high-concurrency writes
<!-- Synchronous; serializes writes -->                # ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Large integers corrupted	Default uses Number	safeIntegers: true
Iteration loop throws	Statement reused inside loop	Use prepare() for inner query
Parameters not binding	Wrong prefix or strict mode mismatch	Match the SQL prefix to the binding object
Database locked	Another process holds a write lock	Close other connections; enable WAL
Memory grows during large query	Using .all() instead of .iterate()	Use iterate()

---

Real-World Examples

1. In-Memory Database

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

2. Read-Only

const db = new Database("data.sqlite", { readonly: true });

3. Run DDL

db.run("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)");

4. Insert and Get ID

const result = db.run("INSERT INTO items (name) VALUES (?)", ["Box"]);
console.log(result.lastInsertRowid);

5. Get First Row

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

6. Iterate

for (const row of db.query("SELECT * FROM items").iterate()) { ... }

7. Transaction

const tx = db.transaction(() => { /* queries */ });
tx();

8. Safe Integers

const db = new Database("data.sqlite", { safeIntegers: true });

9. Map to Class

class Item { id: number; name: string; }
const items = db.query("SELECT * FROM items").as(Item).all();

10. Close

db.close();

---

Visual

Query Methods

┌──────────────────────────────────────────────┐
│  db.query("SELECT ...")                      │
└──────────────────┬───────────────────────────┘
                   │
                   ▼
┌──────────────────────────────────────────────┐
│  Statement                                   │
│  ├── .get()   → first row or null            │
│  ├── .all()   → array of objects             │
│  ├── .iterate() → iterator (one at a time)   │
│  ├── .values() → array of arrays             │
│  └── .run()   → { lastInsertRowid, changes } │
└──────────────────────────────────────────────┘

Transaction Flow

┌──────────────────────────────────────────────┐
│  const tx = db.transaction(fn)               │
└──────────────────┬───────────────────────────┘
                   │
                   ▼
┌──────────────────────────────────────────────┐
│  tx(args)                                    │
│  ├── BEGIN                                   │
│  ├── fn(args)                                │
│  │   ├── success → COMMIT                    │
│  │   └── throw → ROLLBACK → rethrow          │
│  └── return value from fn                    │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Import	import { Database } from "bun:sqlite"
Driver	Native Zig implementation
API style	Synchronous, better-sqlite3-inspired
Prepared statements	db.query() (cached) or db.prepare() (uncached)
Parameters	Positional (?) or named ($name)
64-bit integers	safeIntegers: true returns BigInt
Transactions	db.transaction(fn) with automatic commit/rollback
Large results	.iterate() for one-at-a-time processing
Performance	3–6x faster than better-sqlite3 reads

Key takeaways:

• bun:sqlite is built into the runtime. No installation, no native module compilation, no version compatibility issues. The import works on every platform Bun supports .
• The API is synchronous. Queries block until complete. This is correct for SQLite’s embedded use case, but it means concurrent HTTP requests contend for the same database connection .
• safeIntegers: true is required for 64-bit values. Without it, integers above 2^53 are rounded, silently corrupting the data .
• Transactions are automatic. db.transaction() wraps a function. The driver begins, commits on success, and rolls back on throw. Nested transactions become savepoints .
• .iterate() is for large result sets. It yields rows one at a time without loading the full result into memory. The same statement cannot be re-executed inside its own iteration loop .

Remember: bun:sqlite is the database layer of the all-in-one runtime. It is fast because it is native, simple because it is synchronous, and complete because it supports prepared statements, transactions, class mapping, and 64-bit integers. The Database class opens the connection, the Statement methods execute the queries, and the transaction wrapper handles atomicity. The database is a file. The driver is part of the binary. No dependency is required.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!