From bun
Guides Bun's built-in SQLite driver usage: opening databases, running queries, prepared statements with positional/named params, and result methods like get/all/values.
How this skill is triggered — by the user, by Claude, or both
Slash command
/bun:bun-sqliteThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Bun has a built-in, high-performance SQLite driver via `bun:sqlite`.
Bun has a built-in, high-performance SQLite driver via bun:sqlite.
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", "[email protected]"]);
// Query data
const users = db.query("SELECT * FROM users").all();
console.log(users);
// Close
db.close();
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 });
// 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
// 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);
}
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
stmt.run("Bob", "[email protected]");
// Or as array
stmt.run(["Charlie", "[email protected]"]);
const stmt = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)");
stmt.run({ $name: "Dave", $email: "[email protected]" });
// Also works with : and @
const stmt2 = db.prepare("SELECT * FROM users WHERE name = :name");
stmt2.get({ name: "Dave" }); // Note: no colon in object key
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());
// 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: "[email protected]" },
{ name: "User2", email: "[email protected]" },
]);
// Transaction modes
const tx = db.transaction(() => {
db.run('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', '[email protected]']);
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
// 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"]);
// 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
// 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
import { Database, SQLiteError } from "bun:sqlite";
try {
db.run("INSERT INTO users (email) VALUES (?)", ["[email protected]"]);
} catch (error) {
if (error instanceof SQLiteError) {
console.error("SQLite error:", error.code, error.message);
// error.code: "SQLITE_CONSTRAINT_UNIQUE"
}
throw error;
}
// 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:"
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);
}
}
| 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 |
-- 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;
Load references/pragmas.md when:
Load references/fts.md when:
npx claudepluginhub secondsky/claude-skills --plugin bunManages SQLite databases in Bun using the built-in driver. Covers opening databases, queries, prepared statements, transactions, and best practices.
SQLite database implementation using LibSQL client and Drizzle ORM. This skill covers local development, Turso cloud, and Cloudflare D1 deployments.
Provides SQLite patterns for Python projects: WAL mode connections, context managers, async aiosqlite ops, migrations, gotchas, CLI. For local DB state, caching, concurrency.