Bun SQLite

Bun has a built-in, high-performance SQLite driver via bun:sqlite .

Quick Start

import { Database } from "bun:sqlite";

// Create/open database 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);

// Close db.close();

Opening Databases

import { Database } from "bun:sqlite";

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

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

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

// Create if not exists (default) const createDb = new Database("new.sqlite", { create: true });

// Strict mode (recommended) const strictDb = new Database("strict.sqlite", { strict: true });

Running Queries

Direct Execution

// Run (for INSERT, UPDATE, DELETE, DDL) db.run("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)"); db.run("INSERT INTO items (name) VALUES (?)", ["Item 1"]); db.run("DELETE FROM items WHERE id = ?", [1]);

// Get changes info const result = db.run("DELETE FROM items WHERE id > ?", [10]); console.log(result.changes); // Rows affected console.log(result.lastInsertRowid); // Last inserted ID

Prepared Statements (Recommended)

// Create prepared statement const stmt = db.prepare("SELECT * FROM users WHERE id = ?");

// Get single row const user = stmt.get(1);

// Get all rows const allUsers = db.prepare("SELECT * FROM users").all();

// Get values as array const values = db.prepare("SELECT name, email FROM users").values(); // [[name1, email1], [name2, email2], ...]

// Iterate with for...of const iter = db.prepare("SELECT * FROM users"); for (const user of iter.iterate()) { console.log(user); }

Parameters

Positional Parameters

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

// Or as array stmt.run(["Charlie", "charlie@example.com"]);

Named Parameters

const stmt = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)"); stmt.run({ $name: "Dave", $email: "dave@example.com" });

// Also works with : and @ const stmt2 = db.prepare("SELECT * FROM users WHERE name = :name"); stmt2.get({ name: "Dave" }); // Note: no colon in object key

Query Methods

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

// .get() - First row or null const first = stmt.get(true);

// .all() - All rows as array const all = stmt.all(true);

// .values() - Rows as arrays (not objects) const values = stmt.values(true); // [[1, "Alice", true], [2, "Bob", true]]

// .iterate() - Iterator for memory efficiency for (const row of stmt.iterate(true)) { processRow(row); }

// .run() - Execute without returning data db.prepare("DELETE FROM cache WHERE expires < ?").run(Date.now());

Transactions

// Simple transaction const insertMany = db.transaction((users: { name: string; email: string }[]) => { const insert = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)"); for (const user of users) { insert.run(user); } return users.length; });

const count = insertMany([ { name: "User1", email: "user1@example.com" }, { name: "User2", email: "user2@example.com" }, ]);

// Transaction modes const tx = db.transaction(() => { db.run('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', 'alice@example.com']); db.run('UPDATE accounts SET balance = balance - 100 WHERE user_id = ?', [1]); });

tx.deferred(); // Default: defer lock until first write tx.immediate(); // Lock immediately on transaction start tx.exclusive(); // Exclusive lock, blocks all other connections

Batch Operations

// WAL mode for better concurrent performance db.run("PRAGMA journal_mode = WAL");

// Bulk insert with transaction const insertBulk = db.transaction((items: string[]) => { const stmt = db.prepare("INSERT INTO items (name) VALUES (?)"); for (const item of items) { stmt.run(item); } });

insertBulk(["A", "B", "C", "D", "E"]);

Column Types

// SQLite types map to JavaScript /* SQLite JavaScript

INTEGER number | bigint REAL number TEXT string BLOB Uint8Array NULL null */

// Handle BigInt for large integers const bigStmt = db.prepare("SELECT COUNT(*) as count FROM users"); const result = bigStmt.get(); // result.count may be bigint if > Number.MAX_SAFE_INTEGER

// Store/retrieve Uint8Array db.run("INSERT INTO files (data) VALUES (?)", [new Uint8Array([1, 2, 3])]); const file = db.prepare("SELECT data FROM files WHERE id = ?").get(1); // file.data is Uint8Array

Column Definitions

// Get column info const stmt = db.prepare("SELECT * FROM users"); const columns = stmt.columnNames; // ["id", "name", "email"]

// Type annotations (Bun extension) const typedStmt = db.prepare<{ id: number; name: string }, [number]>( "SELECT id, name FROM users WHERE id = ?" ); const user = typedStmt.get(1); // user is typed as { id: number; name: string } | null

Error Handling

import { Database, SQLiteError } from "bun:sqlite";

try { db.run("INSERT INTO users (email) VALUES (?)", ["duplicate@example.com"]); } catch (error) { if (error instanceof SQLiteError) { console.error("SQLite error:", error.code, error.message); // error.code: "SQLITE_CONSTRAINT_UNIQUE" } throw error; }

Database Management

// Close database db.close();

// Check if open console.log(db.inTransaction); // Is in transaction

// Serialize to buffer const buffer = db.serialize(); await Bun.write("backup.sqlite", buffer);

// Load from buffer const data = await Bun.file("backup.sqlite").arrayBuffer(); const restored = Database.deserialize(data);

// Filename console.log(db.filename); // Path or ":memory:"

Common Patterns

Repository Pattern

import { Database } from "bun:sqlite";

interface User { id: number; name: string; email: string; }

class UserRepository { private db: Database; private stmts: { findById: ReturnType<Database["prepare"]>; findAll: ReturnType<Database["prepare"]>; create: ReturnType<Database["prepare"]>; update: ReturnType<Database["prepare"]>; delete: ReturnType<Database["prepare"]>; };

constructor(db: Database) { this.db = db; this.stmts = { findById: db.prepare("SELECT * FROM users WHERE id = ?"), findAll: db.prepare("SELECT * FROM users"), create: db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)"), update: db.prepare("UPDATE users SET name = $name, email = $email WHERE id = $id"), delete: db.prepare("DELETE FROM users WHERE id = ?"), }; }

findById(id: number): User | null { return this.stmts.findById.get(id) as User | null; }

findAll(): User[] { return this.stmts.findAll.all() as User[]; }

create(user: Omit<User, "id">): number { const result = this.stmts.create.run(user); return Number(result.lastInsertRowid); } }

Common Errors

Error Cause Fix

SQLITE_CONSTRAINT

Constraint violation Check UNIQUE/FK constraints

SQLITE_BUSY

Database locked Use WAL mode, add retry logic

no such table

Table doesn't exist Run CREATE TABLE first

database is locked

Concurrent access Enable WAL mode

Performance Tips

-- Enable WAL mode (better concurrency) PRAGMA journal_mode = WAL;

-- Faster writes (less durable) PRAGMA synchronous = NORMAL;

-- Increase cache size PRAGMA cache_size = 10000;

-- Enable foreign keys PRAGMA foreign_keys = ON;

When to Load References

Load references/pragmas.md when:

• Performance tuning

• Journal modes

• Memory configuration

Load references/fts.md when:

• Full-text search

• FTS5 configuration