jutsu-bunbun-sqlite

Bun SQLite

Use this skill when working with SQLite databases using Bun's built-in, high-performance SQLite driver.

Key Concepts

Opening a Database

Bun includes a native SQLite driver:

import { Database } from "bun:sqlite";

// Open or create database
const db = new Database("mydb.sqlite");

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

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

Basic Queries

Execute SQL queries:

import { Database } from "bun:sqlite";

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 NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// 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 database
db.close();

Prepared Statements

Use prepared statements for better performance:

import { Database } from "bun:sqlite";

const db = new Database("mydb.sqlite");

// Prepare statement
const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Execute multiple times
insertUser.run("Alice", "alice@example.com");
insertUser.run("Bob", "bob@example.com");

// Prepared query
const findUser = db.prepare("SELECT * FROM users WHERE email = ?");
const user = findUser.get("alice@example.com");

console.log(user);

Best Practices

Use Prepared Statements

Prepared statements are faster and prevent SQL injection:

// Good - Prepared statement
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

// Bad - String interpolation (SQL injection risk)
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

Transactions

Use transactions for atomic operations:

import { Database } from "bun:sqlite";

const db = new Database("mydb.sqlite");

// Transaction with automatic rollback on error
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);
  }
});

try {
  insertUsers([
    { name: "Alice", email: "alice@example.com" },
    { name: "Bob", email: "bob@example.com" },
  ]);
  console.log("All users inserted");
} catch (error) {
  console.error("Transaction failed:", error);
}

Query Methods

Different methods for different use cases:

const db = new Database("mydb.sqlite");

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

// .get() - Get first row
const firstUser = db.query("SELECT * FROM users").get();

// .values() - Get array of arrays
const userValues = db.query("SELECT name, email FROM users").values();

// .run() - Execute without returning rows
db.run("DELETE FROM users WHERE id = ?", [userId]);

Error Handling

Properly handle database errors:

import { Database } from "bun:sqlite";

try {
  const db = new Database("mydb.sqlite");

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

  db.close();
} catch (error) {
  if (error instanceof Error) {
    console.error("Database error:", error.message);
  }
}

Common Patterns

CRUD Operations

import { Database } from "bun:sqlite";

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

class UserRepository {
  private db: Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.createTable();
  }

  private createTable() {
    this.db.run(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  create(user: User): User {
    const stmt = this.db.prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING *");
    return stmt.get(user.name, user.email) as User;
  }

  findById(id: number): User | null {
    const stmt = this.db.prepare("SELECT * FROM users WHERE id = ?");
    return (stmt.get(id) as User) || null;
  }

  findAll(): User[] {
    return this.db.query("SELECT * FROM users").all() as User[];
  }

  update(id: number, user: Partial<User>): User | null {
    const stmt = this.db.prepare(`
      UPDATE users
      SET name = COALESCE(?, name), email = COALESCE(?, email)
      WHERE id = ?
      RETURNING *
    `);
    return (stmt.get(user.name, user.email, id) as User) || null;
  }

  delete(id: number): boolean {
    const stmt = this.db.prepare("DELETE FROM users WHERE id = ?");
    const result = stmt.run(id);
    return result.changes > 0;
  }

  close() {
    this.db.close();
  }
}

// Usage
const users = new UserRepository("mydb.sqlite");
const newUser = users.create({ name: "Alice", email: "alice@example.com" });
console.log(newUser);

Bulk Inserts with Transaction

import { Database } from "bun:sqlite";

const db = new Database("mydb.sqlite");

const bulkInsert = db.transaction((items: Array<{ name: string; email: string }>) => {
  const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

  for (const item of items) {
    stmt.run(item.name, item.email);
  }
});

// Insert 1000 users atomically
const users = Array.from({ length: 1000 }, (_, i) => ({
  name: `User ${i}`,
  email: `user${i}@example.com`,
}));

bulkInsert(users);

Migrations

import { Database } from "bun:sqlite";

class DatabaseMigration {
  private db: Database;

  constructor(dbPath: string) {
    this.db = new Database(dbPath);
    this.initMigrationTable();
  }

  private initMigrationTable() {
    this.db.run(`
      CREATE TABLE IF NOT EXISTS migrations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  private hasRun(name: string): boolean {
    const stmt = this.db.prepare("SELECT COUNT(*) as count FROM migrations WHERE name = ?");
    const result = stmt.get(name) as { count: number };
    return result.count > 0;
  }

  private recordMigration(name: string) {
    this.db.run("INSERT INTO migrations (name) VALUES (?)", [name]);
  }

  migrate(name: string, sql: string) {
    if (this.hasRun(name)) {
      console.log(`Migration ${name} already applied`);
      return;
    }

    const migration = this.db.transaction(() => {
      this.db.run(sql);
      this.recordMigration(name);
    });

    migration();
    console.log(`Migration ${name} applied successfully`);
  }

  close() {
    this.db.close();
  }
}

// Usage
const migration = new DatabaseMigration("mydb.sqlite");

migration.migrate(
  "001_create_users",
  `
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
  )
`
);

migration.migrate(
  "002_add_timestamps",
  `
  ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP
`
);

migration.close();

Query Builder Pattern

import { Database } from "bun:sqlite";

class QueryBuilder<T> {
  private db: Database;
  private tableName: string;
  private whereClause: string[] = [];
  private whereValues: any[] = [];
  private limitValue?: number;
  private offsetValue?: number;

  constructor(db: Database, tableName: string) {
    this.db = db;
    this.tableName = tableName;
  }

  where(column: string, value: any): this {
    this.whereClause.push(`${column} = ?`);
    this.whereValues.push(value);
    return this;
  }

  limit(n: number): this {
    this.limitValue = n;
    return this;
  }

  offset(n: number): this {
    this.offsetValue = n;
    return this;
  }

  getAll(): T[] {
    let sql = `SELECT * FROM ${this.tableName}`;

    if (this.whereClause.length > 0) {
      sql += ` WHERE ${this.whereClause.join(" AND ")}`;
    }

    if (this.limitValue) {
      sql += ` LIMIT ${this.limitValue}`;
    }

    if (this.offsetValue) {
      sql += ` OFFSET ${this.offsetValue}`;
    }

    const stmt = this.db.prepare(sql);
    return stmt.all(...this.whereValues) as T[];
  }

  getOne(): T | null {
    let sql = `SELECT * FROM ${this.tableName}`;

    if (this.whereClause.length > 0) {
      sql += ` WHERE ${this.whereClause.join(" AND ")}`;
    }

    sql += " LIMIT 1";

    const stmt = this.db.prepare(sql);
    return (stmt.get(...this.whereValues) as T) || null;
  }
}

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

const db = new Database("mydb.sqlite");

const query = new QueryBuilder<User>(db, "users");
const users = query.where("name", "Alice").limit(10).getAll();
console.log(users);

Anti-Patterns

Don't Use String Interpolation

// Bad - SQL injection vulnerability
const userId = "1 OR 1=1";
const user = db.query(`SELECT * FROM users WHERE id = ${userId}`).get();

// Good - Use prepared statements
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = stmt.get(userId);

Don't Forget to Close Database

// Bad - Database remains open
const db = new Database("mydb.sqlite");
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

// Good - Close when done
const db = new Database("mydb.sqlite");
try {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
} finally {
  db.close();
}

Don't Use Transactions for Single Operations

// Bad - Unnecessary transaction
const insert = db.transaction(() => {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);
});
insert();

// Good - Direct execution
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

Don't Reparse Queries

// Bad - Reparsing query each iteration
for (let i = 0; i < 1000; i++) {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", [`User ${i}`, `user${i}@example.com`]);
}

// Good - Prepare once, execute many times
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
for (let i = 0; i < 1000; i++) {
  stmt.run(`User ${i}`, `user${i}@example.com`);
}

Related Skills

• bun-runtime: Core Bun runtime features and file I/O
• bun-testing: Testing database operations
• bun-bundler: Bundling applications with SQLite