SQLite ist in Bun nativ integriert. Kein npm install, keine nativen Bindings, keine Kompilierung. Das bun:sqlite-Modul ist 3-6x schneller als Alternativen wie better-sqlite3.
Grundlagen
Eine Datenbank öffnen:
import { Database } from "bun:sqlite";
// Datei-Datenbank
const db = new Database("app.db");
// In-Memory für Tests
const memDb = new Database(":memory:");
// Readonly
const readonlyDb = new Database("app.db", { readonly: true });Queries ausführen
Einfache Queries:
// Alle Ergebnisse
const users = db.query("SELECT * FROM users").all();
// Ein Ergebnis
const user = db.query("SELECT * FROM users WHERE id = 1").get();
// Ausführen ohne Rückgabe
db.run("DELETE FROM sessions WHERE expires < datetime('now')");Mit Parametern:
// Positional Parameters
const user = db.query("SELECT * FROM users WHERE id = ?").get(42);
// Named Parameters
const user = db.query("SELECT * FROM users WHERE email = $email").get({
$email: "[email protected]",
});
// Mehrere Parameter
db.run(
"INSERT INTO users (name, email) VALUES (?, ?)",
"Alice",
"[email protected]"
);Prepared Statements
Für wiederholte Queries sind Prepared Statements schneller:
const findUser = db.prepare("SELECT * FROM users WHERE id = ?");
// Wiederverwendbar
const user1 = findUser.get(1);
const user2 = findUser.get(2);
const user3 = findUser.get(3);Das Statement wird einmal geparst und kann beliebig oft ausgeführt werden.
Type-Safety mit TypeScript
Generics für typisierte Ergebnisse:
interface User {
id: number;
name: string;
email: string;
created_at: string;
}
const findUser = db.prepare<User, [number]>(
"SELECT * FROM users WHERE id = ?"
);
const user = findUser.get(42);
// user ist vom Typ User | nullQuery-Builder Pattern:
class UserRepository {
private db: Database;
private findById = this.db.prepare<User, [number]>(
"SELECT * FROM users WHERE id = ?"
);
private findByEmail = this.db.prepare<User, [string]>(
"SELECT * FROM users WHERE email = ?"
);
constructor(db: Database) {
this.db = db;
}
getById(id: number): User | null {
return this.findById.get(id);
}
getByEmail(email: string): User | null {
return this.findByEmail.get(email);
}
}Transactions
Für atomare Operationen:
const insertUsers = db.transaction((users: { name: string; email: string }[]) => {
const insert = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
for (const user of users) {
insert.run(user.name, user.email);
}
return users.length;
});
// Alles oder nichts
const count = insertUsers([
{ name: "Alice", email: "[email protected]" },
{ name: "Bob", email: "[email protected]" },
]);Bei einem Fehler wird automatisch ein Rollback durchgeführt.
Verschachtelte Transactions:
const outer = db.transaction(() => {
db.run("INSERT INTO logs (message) VALUES ('start')");
const inner = db.transaction(() => {
db.run("INSERT INTO users (name) VALUES ('test')");
});
inner(); // Savepoint
db.run("INSERT INTO logs (message) VALUES ('end')");
});
outer();Datentypen
SQLite-Typen werden automatisch konvertiert:
| SQLite | JavaScript |
|---|---|
| INTEGER | number |
| REAL | number |
| TEXT | string |
| BLOB | Uint8Array |
| NULL | null |
BLOB-Handling:
// Schreiben
const data = new Uint8Array([1, 2, 3, 4]);
db.run("INSERT INTO files (content) VALUES (?)", data);
// Lesen
const row = db.query("SELECT content FROM files WHERE id = 1").get();
console.log(row.content); // Uint8ArrayJSON-Spalten:
interface Settings {
theme: string;
notifications: boolean;
}
// Als TEXT speichern
db.run(
"INSERT INTO users (settings) VALUES (?)",
JSON.stringify({ theme: "dark", notifications: true })
);
// Beim Lesen parsen
const row = db.query("SELECT settings FROM users WHERE id = 1").get();
const settings: Settings = JSON.parse(row.settings);Performance-Patterns
Batch-Inserts:
const insertMany = db.transaction((items: string[]) => {
const stmt = db.prepare("INSERT INTO items (name) VALUES (?)");
for (const item of items) {
stmt.run(item);
}
});
// 10.000 Inserts in einer Transaction
insertMany(Array.from({ length: 10000 }, (_, i) => `item-${i}`));WAL-Modus für Concurrency:
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA synchronous = NORMAL");WAL (Write-Ahead Logging) ermöglicht gleichzeitige Reads während eines Writes.
Indizes:
db.run("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)");API-Beispiel mit Hono
Tipp: Für einen umfassenden Einstieg in Hono siehe Ultra-performante Web-APIs mit Hono.
import { Hono } from "hono";
import { Database } from "bun:sqlite";
const db = new Database("app.db");
const app = new Hono();
// Prepared Statements
const getUsers = db.prepare("SELECT id, name, email FROM users LIMIT ? OFFSET ?");
const getUser = db.prepare("SELECT * FROM users WHERE id = ?");
const createUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING *");
app.get("/users", (c) => {
const limit = Number(c.req.query("limit")) || 10;
const offset = Number(c.req.query("offset")) || 0;
return c.json(getUsers.all(limit, offset));
});
app.get("/users/:id", (c) => {
const user = getUser.get(Number(c.req.param("id")));
if (!user) return c.json({ error: "Not found" }, 404);
return c.json(user);
});
app.post("/users", async (c) => {
const { name, email } = await c.req.json();
const user = createUser.get(name, email);
return c.json(user, 201);
});
export default app;Migrations
Ein einfaches Migrations-Pattern:
const migrations = [
`CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users(id),
title TEXT NOT NULL,
content TEXT,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
)`,
`CREATE INDEX IF NOT EXISTS idx_posts_user ON posts(user_id)`,
];
function migrate(db: Database) {
db.run(`CREATE TABLE IF NOT EXISTS _migrations (
id INTEGER PRIMARY KEY,
applied_at TEXT DEFAULT CURRENT_TIMESTAMP
)`);
const applied = db.query("SELECT id FROM _migrations").all().map(r => r.id);
for (let i = 0; i < migrations.length; i++) {
if (!applied.includes(i)) {
db.run(migrations[i]);
db.run("INSERT INTO _migrations (id) VALUES (?)", i);
console.log(`Applied migration ${i}`);
}
}
}
migrate(db);