bun-sqlite

name	bun-sqlite
user-invocable	false
description	Use when working with SQLite databases in Bun. Covers Bun's built-in SQLite driver, database operations, prepared statements, and transactions with high performance.
allowed-tools	["Read","Write","Edit","Bash","Grep","Glob"]

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", "[e-mail]"]);

// 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", "[e-mail]");
insertUser.run("Bob", "[e-mail]");

// Prepared query
const findUser = db.prepare("SELECT * FROM users WHERE email = ?");
const user = findUser.get("[e-mail]");

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: "[e-mail]" },
    { name: "Bob", email: "[e-mail]" },
  ]);
  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", "[e-mail]");

  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)  ;
  }

  (: ):  |  {
     stmt = ..();
     (stmt.(id)  ) || ;
  }

  (): [] {
     ..().()  [];
  }

  (: , : <>):  |  {
     stmt = ..();
     (stmt.(user., user., id)  ) || ;
  }

  (: ):  {
     stmt = ..();
     result = stmt.(id);
     result. > ;
  }

  () {
    ..();
  }
}


 users =  ();
 newUser = users.({ : , :  });
.(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) {
    ..(, [name]);
  }

  () {
     (.(name)) {
      .();
      ;
    }

     migration = ..( {
      ..(sql);
      .(name);
    });

    ();
    .();
  }

  () {
    ..();
  }
}


 migration =  ();

migration.(
  ,
  
);

migration.(
  ,
  
);

migration.();

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):  {
    . = n;
     ;
  }

  (: ):  {
    . = n;
     ;
  }

  (): T[] {
     sql = ;

     (.. > ) {
      sql += ;
    }

     (.) {
      sql += ;
    }

     (.) {
      sql += ;
    }

     stmt = ..(sql);
     stmt.(....)  T[];
  }

  (): T |  {
     sql = ;

     (.. > ) {
      sql += ;
    }

    sql += ;

     stmt = ..(sql);
     (stmt.(....)  T) || ;
  }
}


  {
  : ;
  : ;
  : ;
}

 db =  ();

 query =  <>(db, );
 users = query.(, ).().();
.(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", "[e-mail]"]);

// Good - Close when done
const db = new Database("mydb.sqlite");
try {
  db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[e-mail]"]);
} 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", "[e-mail]"]);
});
insert();

// Good - Direct execution
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[e-mail]"]);

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