Bun Sqlite

import { Database } from "bun:sqlite";
// Create/open databaseconst db = new Database("mydb.sqlite");
// Create tabledb.run(`  CREATE TABLE IF NOT EXISTS users (    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT NOT NULL,    email TEXT UNIQUE  )`);
// Insert datadb.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "[email protected]"]);
// Query dataconst users = db.query("SELECT * FROM users").all();console.log(users);
// Closedb.close();

import { Database } from "bun:sqlite";
// File-based databaseconst db = new Database("data.sqlite");
// In-memory databaseconst memDb = new Database(":memory:");
// Read-only modeconst 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 infoconst result = db.run("DELETE FROM items WHERE id > ?", [10]);console.log(result.changes); // Rows affectedconsole.log(result.lastInsertRowid); // Last inserted ID

// Create prepared statementconst stmt = db.prepare("SELECT * FROM users WHERE id = ?");
// Get single rowconst user = stmt.get(1);
// Get all rowsconst allUsers = db.prepare("SELECT * FROM users").all();
// Get values as arrayconst values = db.prepare("SELECT name, email FROM users").values();// [[name1, email1], [name2, email2], ...]
// Iterate with for...ofconst 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 arraystmt.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 nullconst first = stmt.get(true);
// .all() - All rows as arrayconst 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 efficiencyfor (const row of stmt.iterate(true)) {  processRow(row);}
// .run() - Execute without returning datadb.prepare("DELETE FROM cache WHERE expires < ?").run(Date.now());

// Simple transactionconst 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 modesconst 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 writetx.immediate();  // Lock immediately on transaction starttx.exclusive();  // Exclusive lock, blocks all other connections

// WAL mode for better concurrent performancedb.run("PRAGMA journal_mode = WAL");
// Bulk insert with transactionconst 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 integersconst 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 Uint8Arraydb.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 infoconst 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 databasedb.close();
// Check if openconsole.log(db.inTransaction); // Is in transaction
// Serialize to bufferconst buffer = db.serialize();await Bun.write("backup.sqlite", buffer);
// Load from bufferconst data = await Bun.file("backup.sqlite").arrayBuffer();const restored = Database.deserialize(data);
// Filenameconsole.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);  }}