bun-sqlite

# Bun SQLite

Bun has a built-in, high-performance SQLite driver via `bun:sqlite`.

## Quick Start

```typescript
import { Database } from "bun:sqlite";

// Create/open database
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
  )
`);

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

## Opening Databases

```typescript
import { Database } from "bun:sqlite";

// File-based database
const db = new Database("data.sqlite");

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

// Read-only mode
const 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 });
```

## Running Queries

### Direct Execution

```typescript
// 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 info
const result = db.run("DELETE FROM items WHERE id > ?", [10]);
console.log(result.changes); // Rows affected
console.log(result.lastInsertRowid); // Last inserted ID
```

### Prepared Statements (Recommended)

```typescript
// Create prepared statement
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");

// Get single row
const user = stmt.get(1);

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

// Get values as array
const values = db.prepare("SELECT name, email FROM users").values();
// [[name1, email1], [name2, email2], ...]

// Iterate with for...of
const iter = db.prepare("SELECT * FROM users");
for (const user of iter.iterate()) {
  console.log(user);
}
```

## Parameters

### Positional Parameters

```typescript
const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
stmt.run("Bob", "bob@example.com");

// Or as array
stmt.run(["Charlie", "charlie@example.com"]);
```

### Named Parameters

```typescript
const stmt = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)");
stmt.run({ $name: "Dave", $email: "dave@example.com" });

// Also works with : and @
const stmt2 = db.prepare("SELECT * FROM users WHERE name = :name");
stmt2.get({ name: "Dave" }); // Note: no colon in object key
```

## Query Methods

```typescript
const stmt = db.prepare("SELECT * FROM users WHERE active = ?");

// .get() - First row or null
const first = stmt.get(true);

// .all() - All rows as array
const 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 efficiency
for (const row of stmt.iterate(true)) {
  processRow(row);
}

// .run() - Execute without returning data
db.prepare("DELETE FROM cache WHERE expires < ?").run(Date.now());
```

## Transactions

```typescript
// Simple transaction
const 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: "user1@example.com" },
  { name: "User2", email: "user2@example.com" },
]);

// Transaction modes
const tx = db.transaction(() => {
  db.run('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', 'alice@example.com']);
  db.run('UPDATE accounts SET balance = balance - 100 WHERE user_id = ?', [1]);
});

tx.deferred();   // Default: defer lock until first write
tx.immediate();  // Lock immediately on transaction start
tx.exclusive();  // Exclusive lock, blocks all other connections
```

## Batch Operations

```typescript
// WAL mode for better concurrent performance
db.run("PRAGMA journal_mode = WAL");

// Bulk insert with transaction
const 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"]);
```

## Column Types

```typescript
// SQLite types map to JavaScript
/*
  SQLite      JavaScript
  ------      ----------
  INTEGER     number | bigint
  REAL        number
  TEXT        string
  BLOB        Uint8Array
  NULL        null
*/

// Handle BigInt for large integers
const 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 Uint8Array
db.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
```

## Column Definitions

```typescript
// Get column info
const 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
```

## Error Handling

```typescript
import { Database, SQLiteError } from "bun:sqlite";

try {
  db.run("INSERT INTO users (email) VALUES (?)", ["duplicate@example.com"]);
} catch (error) {
  if (error instanceof SQLiteError) {
    console.error("SQLite error:", error.code, error.message);
    // error.code: "SQLITE_CONSTRAINT_UNIQUE"
  }
  throw error;
}
```

## Database Management

```typescript
// Close database
db.close();

// Check if open
console.log(db.inTransaction); // Is in transaction

// Ser