Bun SQLite Database

Bun has a built-in high-performance SQLite driver without requiring additional dependencies. This chapter introduces how to use Bun SQLite.

Quick Start

Creating a Database

import { Database } from "bun:sqlite";

// Create/open database file
const db = new Database("mydb.sqlite");

// Create in-memory database
const memoryDb = new Database(":memory:");

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

Basic Operations

import { Database } from "bun:sqlite";

const db = new Database("users.db");

// Create table
db.run(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert data
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Zhang San", "zhangsan@example.com"]);

// Query data
const users = db.query("SELECT * FROM users").all();
console.log(users);

// Close database
db.close();

Query Methods

query() Method

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

// Create table
db.run("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)");

// Insert data
db.run("INSERT INTO products (name, price) VALUES (?, ?)", ["Apple", 5.5]);
db.run("INSERT INTO products (name, price) VALUES (?, ?)", ["Banana", 3.0]);

// Get all results
const allProducts = db.query("SELECT * FROM products").all();
console.log(allProducts);
// [{ id: 1, name: "Apple", price: 5.5 }, { id: 2, name: "Banana", price: 3.0 }]

// Get first result
const firstProduct = db.query("SELECT * FROM products WHERE id = ?").get(1);
console.log(firstProduct);
// { id: 1, name: "Apple", price: 5.5 }

// Get specific column values
const names = db.query("SELECT name FROM products").values();
console.log(names);
// [["Apple"], ["Banana"]]

run() Method

// Execute modification operations
const result = db.run(
  "INSERT INTO products (name, price) VALUES (?, ?)",
  ["Orange", 4.0]
);

console.log(result.changes);      // Number of affected rows: 1
console.log(result.lastInsertRowid); // Last inserted ID

Prepared Statements

Creating Statements

// Prepared statements (more efficient)
const insertStmt = db.prepare(
  "INSERT INTO users (name, email) VALUES ($name, $email)"
);

// Use multiple times
insertStmt.run({ $name: "Zhang San", $email: "zhangsan@example.com" });
insertStmt.run({ $name: "Li Si", $email: "lisi@example.com" });
insertStmt.run({ $name: "Wang Wu", $email: "wangwu@example.com" });

Parameter Binding

// Positional parameters (?)
const stmt1 = db.prepare("SELECT * FROM users WHERE id = ?");
stmt1.get(1);

// Named parameters ($name)
const stmt2 = db.prepare("SELECT * FROM users WHERE name = $name");
stmt2.get({ $name: "Zhang San" });

// Named parameters (:name)
const stmt3 = db.prepare("SELECT * FROM users WHERE name = :name");
stmt3.get({ name: "Zhang San" });

Statement Reuse

const selectByEmail = db.prepare(
  "SELECT * FROM users WHERE email = ?"
);

// Use same statement multiple times
const user1 = selectByEmail.get("zhangsan@example.com");
const user2 = selectByEmail.get("lisi@example.com");

// Release statement
selectByEmail.finalize();

Transactions

Basic Transactions

// Using transaction() method
const insertUsers = db.transaction((users: Array<{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;
});

// Execute transaction
const count = insertUsers([
  { name: "User1", email: "user1@example.com" },
  { name: "User2", email: "user2@example.com" },
  { name: "User3", email: "user3@example.com" },
]);

console.log(`Inserted ${count} records`);

Transaction Rollback

const transfer = db.transaction((from: number, to: number, amount: number) => {
  // Decrease sender balance
  const sender = db.query("SELECT balance FROM accounts WHERE id = ?").get(from);
  if (!sender || sender.balance < amount) {
    throw new Error("Insufficient balance");
  }
  
  db.run("UPDATE accounts SET balance = balance - ? WHERE id = ?", [amount, from]);
  db.run("UPDATE accounts SET balance = balance + ? WHERE id = ?", [amount, to]);
  
  return { from, to, amount };
});

try {
  const result = transfer(1, 2, 100);
  console.log("Transfer successful:", result);
} catch (error) {
  console.log("Transfer failed, rolled back:", error.message);
}

Nested Transactions (Savepoints)

const outer = db.transaction(() => {
  db.run("INSERT INTO logs (message) VALUES (?)", ["Outer transaction"]);
  
  const inner = db.transaction(() => {
    db.run("INSERT INTO logs (message) VALUES (?)", ["Inner transaction"]);
  });
  
  inner(); // Savepoint
});

outer();

Type Safety

Defining Interfaces

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

// Typed query
const users = db.query("SELECT * FROM users").all() as User[];

// Typed single query
const user = db.query("SELECT * FROM users WHERE id = ?").get(1) as User | null;

if (user) {
  console.log(user.name); // Has type hints
}

Using Generics

// Create typed query functions
function queryAll<T>(sql: string, params?: any[]): T[] {
  return db.query(sql).all(...(params || [])) as T[];
}

function queryOne<T>(sql: string, params?: any[]): T | null {
  return db.query(sql).get(...(params || [])) as T | null;
}

// Usage
const users = queryAll<User>("SELECT * FROM users");
const user = queryOne<User>("SELECT * FROM users WHERE id = ?", [1]);

Advanced Features

Custom Functions

// Register custom SQL function
db.function("upper_cn", (str: string) => {
  return str.toUpperCase();
});

// Use custom function
const result = db.query("SELECT upper_cn(name) as name FROM users").all();

Aggregate Functions

// Register aggregate function
db.aggregate("json_array_agg", {
  start: () => [],
  step: (acc: any[], value: any) => {
    acc.push(value);
    return acc;
  },
  finalize: (acc: any[]) => JSON.stringify(acc),
});

// Use aggregate function
const result = db.query("SELECT json_array_agg(name) FROM users").get();

WAL Mode

// Enable WAL mode (better concurrency performance)
const db = new Database("mydb.sqlite");
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA synchronous = NORMAL");

Foreign Key Constraints

// Enable foreign key constraints
db.run("PRAGMA foreign_keys = ON");

// Create table with foreign keys
db.run(`
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    product TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
  )
`);

Database Migration

Simple Migration

// migrations.ts
const migrations = [
  // Version 1
  `CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
  )`,
  
  // Version 2
  `ALTER TABLE users ADD COLUMN email TEXT`,
  
  // Version 3
  `CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`,
];

function migrate(db: Database) {
  // Create migrations table
  db.run(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      version INTEGER UNIQUE,
      applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);
  
  // Get current version
  const current = db.query("SELECT MAX(version) as version FROM migrations").get();
  const currentVersion = current?.version ?? 0;
  
  // Apply new migrations
  for (let i = currentVersion; i < migrations.length; i++) {
    console.log(`Applying migration ${i + 1}...`);
    db.run(migrations[i]);
    db.run("INSERT INTO migrations (version) VALUES (?)", [i + 1]);
  }
  
  console.log("Migration complete");
}

const db = new Database("app.db");
migrate(db);

Practical Application Examples

ORM-style Wrapper

// orm.ts
import { Database } from "bun:sqlite";

class Model<T extends Record<string, any>> {
  constructor(
    private db: Database,
    private table: string
  ) {}

  all(): T[] {
    return this.db.query(`SELECT * FROM ${this.table}`).all() as T[];
  }

  find(id: number): T | null {
    return this.db.query(`SELECT * FROM ${this.table} WHERE id = ?`).get(id) as T | null;
  }

  where(conditions: Partial<T>): T[] {
    const keys = Object.keys(conditions);
    const where = keys.map(k => `${k} = ?`).join(" AND ");
    const values = Object.values(conditions);
    
    return this.db.query(`SELECT * FROM ${this.table} WHERE ${where}`).all(...values) as T[];
  }

  create(data: Omit<T, "id">): T {
    const keys = Object.keys(data);
    const placeholders = keys.map(() => "?").join(", ");
    const values = Object.values(data);
    
    const result = this.db.run(
      `INSERT INTO ${this.table} (${keys.join(", ")}) VALUES (${placeholders})`,
      values
    );
    
    return this.find(Number(result.lastInsertRowid))!;
  }

  update(id: number, data: Partial<Omit<T, "id">>): T | null {
    const keys = Object.keys(data);
    const set = keys.map(k => `${k} = ?`).join(", ");
    const values = [...Object.values(data), id];
    
    this.db.run(`UPDATE ${this.table} SET ${set} WHERE id = ?`, values);
    return this.find(id);
  }

  delete(id: number): boolean {
    const result = this.db.run(`DELETE FROM ${this.table} WHERE id = ?`, [id]);
    return result.changes > 0;
  }
}

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

const db = new Database("app.db");
const users = new Model<User>(db, "users");

// CRUD operations
const newUser = users.create({ name: "Zhang San", email: "zhangsan@example.com" });
const allUsers = users.all();
const user = users.find(1);
const updated = users.update(1, { name: "Zhang Sanfeng" });
const deleted = users.delete(1);

API Server Example

// api-with-db.ts
import { Database } from "bun:sqlite";

const db = new Database("api.db");

// Initialize database
db.run(`
  CREATE TABLE IF NOT EXISTS todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    completed INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

interface Todo {
  id: number;
  title: string;
  completed: number;
  created_at: string;
}

const server = Bun.serve({
  port: 3000,
  
  async fetch(request) {
    const url = new URL(request.url);
    const method = request.method;
    
    // GET /api/todos
    if (url.pathname === "/api/todos" && method === "GET") {
      const todos = db.query("SELECT * FROM todos ORDER BY created_at DESC").all();
      return Response.json(todos);
    }
    
    // POST /api/todos
    if (url.pathname === "/api/todos" && method === "POST") {
      const body = await request.json();
      const result = db.run(
        "INSERT INTO todos (title) VALUES (?)",
        [body.title]
      );
      
      const todo = db.query("SELECT * FROM todos WHERE id = ?")
        .get(Number(result.lastInsertRowid));
      
      return Response.json(todo, { status: 201 });
    }
    
    // PUT /api/todos/:id
    const putMatch = url.pathname.match(/^\/api\/todos\/(\d+)$/);
    if (putMatch && method === "PUT") {
      const id = parseInt(putMatch[1]);
      const body = await request.json();
      
      db.run(
        "UPDATE todos SET title = ?, completed = ? WHERE id = ?",
        [body.title, body.completed ? 1 : 0, id]
      );
      
      const todo = db.query("SELECT * FROM todos WHERE id = ?").get(id);
      
      if (!todo) {
        return Response.json({ error: "Not found" }, { status: 404 });
      }
      
      return Response.json(todo);
    }
    
    // DELETE /api/todos/:id
    const deleteMatch = url.pathname.match(/^\/api\/todos\/(\d+)$/);
    if (deleteMatch && method === "DELETE") {
      const id = parseInt(deleteMatch[1]);
      const result = db.run("DELETE FROM todos WHERE id = ?", [id]);
      
      if (result.changes === 0) {
        return Response.json({ error: "Not found" }, { status: 404 });
      }
      
      return new Response(null, { status: 204 });
    }
    
    return Response.json({ error: "Not found" }, { status: 404 });
  },
});

console.log(`Server running at http://localhost:${server.port}`);

Performance Optimization

Batch Insertion

// Use transaction for batch insertion
const insertMany = db.transaction((items: any[]) => {
  const stmt = db.prepare("INSERT INTO items (name, value) VALUES (?, ?)");
  
  for (const item of items) {
    stmt.run(item.name, item.value);
  }
});

// Insert 1000 records at once
const items = Array.from({ length: 1000 }, (_, i) => ({
  name: `Item ${i}`,
  value: i * 10,
}));

insertMany(items);

Index Optimization

// Create indexes for common queries
db.run("CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)");
db.run("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)");

// Composite index
db.run("CREATE INDEX IF NOT EXISTS idx_orders_user_date ON orders(user_id, created_at)");

Summary

This chapter introduced:

• ✅ Creating and managing databases
• ✅ Querying and modifying data
• ✅ Prepared statements
• ✅ Transaction handling
• ✅ Type safety
• ✅ Advanced features and custom functions
• ✅ Practical application examples

Next Steps

Continue reading FFI to learn how to call native code.