> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/zhcndoc/bun/llms.txt
> Use this file to discover all available pages before exploring further.

# SQLite

> Fast, embedded SQL database with bun:sqlite

Bun includes a high-performance SQLite driver built into the runtime. It's powered by SQLite3 and provides a simple, synchronous API.

## Quick Start

```ts theme={null}
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
  )
`);

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

## Opening a Database

### File Database

```ts theme={null}
const db = new Database("app.db");
```

### In-Memory Database

```ts theme={null}
const db = new Database(":memory:");
// or
const db = new Database();
```

### With Options

```ts theme={null}
const db = new Database("app.db", {
  readonly: false, // Allow writes (default)
  create: true, // Create if doesn't exist (default)
  readwrite: true, // Read-write mode (default)
  safeIntegers: false, // Return numbers as bigint for >52 bits
  strict: true, // Throw on missing parameters
});
```

### Using Database.open()

```ts theme={null}
const db = Database.open("app.db");
// Same as new Database("app.db")
```

## Executing Queries

### db.run()

Execute a query without returning results:

```ts theme={null}
const result = db.run(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  ["Bob", "bob@example.com"]
);

console.log(result.changes); // 1
console.log(result.lastInsertRowid); // 2
```

### db.query()

Prepare and cache a query:

```ts theme={null}
const query = db.query("SELECT * FROM users WHERE id = ?");

// Execute multiple times
const user1 = query.get(1);
const user2 = query.get(2);
```

### db.prepare()

Prepare a query without caching:

```ts theme={null}
const stmt = db.prepare("SELECT * FROM users WHERE name = ?");
const users = stmt.all("Alice");
```

## Querying Data

### Get Single Row

```ts theme={null}
const query = db.query("SELECT * FROM users WHERE id = ?");
const user = query.get(1);

console.log(user); // { id: 1, name: "Alice", email: "alice@example.com" }
```

### Get All Rows

```ts theme={null}
const query = db.query("SELECT * FROM users");
const users = query.all();

console.log(users); // [{ id: 1, ... }, { id: 2, ... }]
```

### Get Values as Arrays

```ts theme={null}
const query = db.query("SELECT name, email FROM users");
const rows = query.values();

console.log(rows); // [["Alice", "alice@example.com"], ["Bob", "bob@example.com"]]
```

### Iterate Rows

```ts theme={null}
const query = db.query("SELECT * FROM users");

for (const user of query.iterate()) {
  console.log(user.name);
}
```

## Parameter Binding

### Positional Parameters

```ts theme={null}
// ? placeholders
db.run("INSERT INTO users (name, email) VALUES (?, ?)", ["Alice", "alice@example.com"]);

// Multiple parameters
const query = db.query("SELECT * FROM users WHERE name = ? AND email = ?");
const user = query.get("Alice", "alice@example.com");
```

### Named Parameters

```ts theme={null}
// With strict mode disabled
const db = new Database("app.db", { strict: false });
db.run("INSERT INTO users (name, email) VALUES ($name, $email)", {
  $name: "Alice",
  $email: "alice@example.com",
});

// With strict mode enabled (default in newer versions)
const db = new Database("app.db", { strict: true });
db.run("INSERT INTO users (name, email) VALUES ($name, $email)", {
  name: "Alice", // No $ prefix needed
  email: "alice@example.com",
});
```

### Supported Types

| JavaScript Type | SQLite Type        |
| --------------- | ------------------ |
| `string`        | TEXT               |
| `number`        | INTEGER or DECIMAL |
| `boolean`       | INTEGER (1 or 0)   |
| `Uint8Array`    | BLOB               |
| `Buffer`        | BLOB               |
| `bigint`        | INTEGER            |
| `null`          | NULL               |

## Transactions

### Basic Transaction

```ts theme={null}
const insertUser = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

const insertMany = db.transaction((users) => {
  for (const user of users) {
    insertUser.run(user.name, user.email);
  }
});

// Execute transaction
insertMany([
  { name: "Alice", email: "alice@example.com" },
  { name: "Bob", email: "bob@example.com" },
]);
```

### Transaction Modes

```ts theme={null}
const insertMany = db.transaction((users) => {
  // ...
});

// Default (DEFERRED)
insertMany(users);

// IMMEDIATE
insertMany.immediate(users);

// EXCLUSIVE
insertMany.exclusive(users);

// DEFERRED
insertMany.deferred(users);
```

### Manual Transactions

```ts theme={null}
db.run("BEGIN");
try {
  db.run("INSERT INTO users (name) VALUES (?)", ["Alice"]);
  db.run("INSERT INTO users (name) VALUES (?)", ["Bob"]);
  db.run("COMMIT");
} catch (err) {
  db.run("ROLLBACK");
  throw err;
}
```

## Custom Classes

Return custom class instances:

```ts theme={null}
class User {
  id!: number;
  name!: string;
  email!: string;
  
  greet() {
    return `Hello, ${this.name}!`;
  }
}

const query = db.query("SELECT * FROM users").as(User);
const user = query.get();

console.log(user.greet()); // "Hello, Alice!"
```

## Column Metadata

```ts theme={null}
const query = db.query("SELECT id, name, age FROM users");

// Column names
console.log(query.columnNames); // ["id", "name", "age"]

// Number of parameters
console.log(query.paramsCount); // 0

// Execute first to get types
query.get();

// Column types from schema
console.log(query.declaredTypes); // ["INTEGER", "TEXT", "INTEGER"]

// Actual types from first row
console.log(query.columnTypes); // ["INTEGER", "TEXT", "INTEGER"]
```

## Database Methods

### Close Database

```ts theme={null}
db.close();

// Close and throw if in use
db.close(true);
```

### Check Transaction Status

```ts theme={null}
if (db.inTransaction) {
  console.log("In transaction");
}
```

### Serialize/Deserialize

```ts theme={null}
// Serialize to Buffer
const data = db.serialize();

// Deserialize from Buffer  
const db2 = Database.deserialize(data);

// Read-only deserialize
const db3 = Database.deserialize(data, true);

// With options
const db4 = Database.deserialize(data, {
  readonly: false,
  strict: true,
  safeIntegers: false,
});
```

### Load Extension

```ts theme={null}
// Set custom SQLite library (macOS only, must be done before opening)
Database.setCustomSQLite("/usr/local/lib/libsqlite3.dylib");

// Load extension
db.loadExtension("./my-extension.so", "entry_point");
```

### File Control

```ts theme={null}
import { constants } from "bun:sqlite";

// Control WAL persistence
db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0);
```

## Statement Methods

```ts theme={null}
const stmt = db.query("SELECT * FROM users WHERE id = ?");

// Get expanded SQL
console.log(stmt.toString()); // "SELECT * FROM users WHERE id = 1"

// Finalize statement
stmt.finalize();

// Auto-finalize with using
using stmt2 = db.query("SELECT * FROM users");
// Automatically finalized when out of scope
```

## Error Handling

```ts theme={null}
import { SQLiteError } from "bun:sqlite";

try {
  db.run("INVALID SQL");
} catch (err) {
  if (err instanceof SQLiteError) {
    console.error("SQLite error:", err.message);
    console.error("Error code:", err.code); // "SQLITE_ERROR"
    console.error("Error number:", err.errno); // 1
  }
}
```

## Using Disposables

```ts theme={null}
// Database auto-closes when out of scope
using db = new Database(":memory:");
db.run("CREATE TABLE test (id INTEGER)");
// Automatically closed here

// Statement auto-finalizes
using stmt = db.query("SELECT * FROM test");
stmt.all();
// Automatically finalized here
```

## Type Signatures

```ts theme={null}
class Database {
  constructor(filename?: string, options?: DatabaseOptions);
  static open(filename: string, options?: DatabaseOptions): Database;
  
  run<T extends SQLQueryBindings[]>(sql: string, ...params: T[]): Changes;
  query<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
    sql: string
  ): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;
  prepare<ReturnType, ParamsType extends SQLQueryBindings | SQLQueryBindings[]>(
    sql: string,
    params?: ParamsType
  ): Statement<ReturnType, ParamsType extends any[] ? ParamsType : [ParamsType]>;
  
  transaction<A extends any[], T>(
    fn: (...args: A) => T
  ): {
    (...args: A): T;
    deferred: (...args: A) => T;
    immediate: (...args: A) => T;
    exclusive: (...args: A) => T;
  };
  
  close(throwOnError?: boolean): void;
  serialize(name?: string): Buffer;
  static deserialize(data: Buffer | ArrayBuffer, options?: DatabaseOptions): Database;
  
  loadExtension(path: string, entryPoint?: string): void;
  fileControl(op: number, arg?: ArrayBufferView | number): number;
  
  readonly filename: string;
  readonly handle: number;
  readonly inTransaction: boolean;
  
  [Symbol.dispose](): void;
}

class Statement<ReturnType = unknown, ParamsType extends SQLQueryBindings[] = any[]> {
  all(...params: ParamsType): ReturnType[];
  get(...params: ParamsType): ReturnType | null;
  run(...params: ParamsType): Changes;
  values(...params: ParamsType): any[][];
  iterate(...params: ParamsType): IterableIterator<ReturnType>;
  
  as<T>(Class: new (...args: any[]) => T): Statement<T, ParamsType>;
  
  finalize(): void;
  toString(): string;
  
  readonly columnNames: string[];
  readonly paramsCount: number;
  readonly columnTypes: Array<"INTEGER" | "FLOAT" | "TEXT" | "BLOB" | "NULL" | null>;
  readonly declaredTypes: Array<string | null>;
  
  [Symbol.dispose](): void;
}

interface Changes {
  changes: number;
  lastInsertRowid: number | bigint;
}
```