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

# SQLite

> Use the built-in bun:sqlite module for fast, synchronous SQLite3 access with prepared statements, transactions, and WAL mode.

Bun natively implements a high-performance [SQLite3](https://www.sqlite.org/) driver. Import it from the built-in `bun:sqlite` module — no installation required.

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

const db = new Database(":memory:");
const query = db.query("select 'Hello world' as message;");
query.get(); // { message: "Hello world" }
```

The API is synchronous and modeled after [better-sqlite3](https://github.com/JoshuaWise/better-sqlite3). `bun:sqlite` is roughly 3–6x faster than `better-sqlite3` for read queries.

**Features:**

* Prepared statements (cached and uncached)
* Named and positional parameters
* Transactions with savepoints
* `BLOB` → `Uint8Array` type conversion
* Map rows to class instances with `.as(Class)`
* `bigint` support for 64-bit integers
* WAL mode for better write concurrency
* Multi-statement queries via `db.run()`

***

## Opening a database

<CodeGroup>
  ```typescript File database theme={null}
  import { Database } from "bun:sqlite";

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

  ```typescript In-memory database theme={null}
  import { Database } from "bun:sqlite";

  const db = new Database(":memory:");
  // also valid:
  // new Database()
  // new Database("")
  ```

  ```typescript Read-only theme={null}
  import { Database } from "bun:sqlite";

  const db = new Database("mydb.sqlite", { readonly: true });
  ```

  ```typescript Create if missing theme={null}
  import { Database } from "bun:sqlite";

  const db = new Database("mydb.sqlite", { create: true });
  ```
</CodeGroup>

### Import attribute

Load a database directly via an import statement:

```typescript theme={null}
import db from "./mydb.sqlite" with { type: "sqlite" };

const user = db.query("SELECT * FROM users LIMIT 1").get();
```

### Strict mode

By default, named parameters require a `$`, `:`, or `@` prefix. With `strict: true`, you can bind values without prefixes, and missing parameters throw an error:

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

const db = new Database(":memory:", { strict: true });

const query = db.query("SELECT $name;");
query.all({ name: "Alice" }); // no $ prefix needed
```

### Closing a database

```typescript theme={null}
db.close();        // allow pending queries to finish
db.close(true);    // throw if queries are pending
```

`close(false)` is called automatically when the database is garbage collected.

### Using the `using` statement

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

{
  using db = new Database("mydb.sqlite");
  using query = db.query("SELECT 'hello' as msg;");
  console.log(query.get()); // { msg: "hello" }
} // db and query are automatically closed here
```

***

## Queries and statements

### `db.query()` — cached prepared statements

`db.query()` compiles and caches the SQL statement. Calling it again with the same SQL string returns the cached compiled form, not a new compilation.

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

query.get({ $id: 1 }); // first row matching id=1
query.all({ $id: 2 }); // all rows matching id=2
```

<Note>
  Caching applies to the **compiled prepared statement**, not the query results. The same cached statement can be reused safely with different parameter values.
</Note>

### `db.prepare()` — uncached prepared statements

Use `db.prepare()` when you want a fresh `Statement` that is not stored in the cache (e.g., for dynamically generated SQL):

```typescript theme={null}
const stmt = db.prepare("SELECT * FROM logs WHERE level = ?");
stmt.all("error");
```

### `db.run()` — execute without reading results

Use `db.run()` for `INSERT`, `UPDATE`, `DELETE`, or DDL statements where you don't need rows back:

```typescript theme={null}
db.run("CREATE TABLE cats (id INTEGER PRIMARY KEY, name TEXT)");
db.run("INSERT INTO cats (name) VALUES (?)", ["Luna"]);
```

Returns `{ lastInsertRowid: number, changes: number }`.

***

## Statement methods

A `Statement` is a compiled query that can be executed multiple times with different parameter values.

### Binding parameters

Named parameters use `$`, `:`, or `@` prefixes (or no prefix when `strict: true`):

```typescript theme={null}
const q = db.query("SELECT * FROM users WHERE id = $id AND role = :role");
q.get({ $id: 1, ":role": "admin" });
```

Positional parameters use `?` or `?N`:

```typescript theme={null}
const q = db.query("SELECT ?1, ?2");
q.all("hello", "world");
// [{ "?1": "hello", "?2": "world" }]
```

### `.get()` — fetch first row

```typescript theme={null}
const user = db.query("SELECT * FROM users WHERE id = $id").get({ $id: 1 });
// { id: 1, name: "Alice" } or undefined
```

### `.all()` — fetch all rows

```typescript theme={null}
const users = db.query("SELECT * FROM users").all();
// [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]
```

### `.run()` — execute and get metadata

```typescript theme={null}
const result = db.query("INSERT INTO cats (name) VALUES ($name)").run({ $name: "Salem" });
// { lastInsertRowid: 3, changes: 1 }
```

### `.values()` — fetch rows as arrays

```typescript theme={null}
const rows = db.query("SELECT name, age FROM users").values();
// [["Alice", 30], ["Bob", 25]]
```

### `.iterate()` — stream rows one at a time

Useful for large result sets that shouldn't be fully loaded into memory:

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

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

### `.as(Class)` — map rows to class instances

```typescript theme={null}
class Movie {
  title: string;
  year: number;

  get isRecent() {
    return this.year >= 2020;
  }
}

const query = db.query("SELECT title, year FROM movies").as(Movie);
const movies = query.all();

console.log(movies[0].isRecent); // true or false
```

<Note>
  The class constructor is not called. Columns are assigned directly to the object and the class prototype is attached. Use this for methods and getters, not constructor initialization.
</Note>

### `.toString()` — inspect expanded SQL

```typescript theme={null}
const query = db.query("SELECT $param;");
query.run(42);
console.log(query.toString()); // "SELECT 42"
```

### `.finalize()` — free resources

```typescript theme={null}
query.finalize(); // destroy statement and release memory
```

***

## Transactions

Wrap multiple queries in an atomic transaction using `db.transaction()`:

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

const db = new Database(":memory:");
db.run("CREATE TABLE cats (id INTEGER PRIMARY KEY, name TEXT)");

const insert = db.prepare("INSERT INTO cats (name) VALUES ($name)");

const insertMany = db.transaction((cats: { $name: string }[]) => {
  for (const cat of cats) {
    insert.run(cat);
  }
  return cats.length;
});

const count = insertMany([
  { $name: "Keanu" },
  { $name: "Salem" },
  { $name: "Crookshanks" },
]);

console.log(`Inserted ${count} cats`);
```

If an exception is thrown inside the transaction function, the transaction is automatically rolled back.

### Transaction modes

```typescript theme={null}
insertMany(cats);              // BEGIN
insertMany.deferred(cats);     // BEGIN DEFERRED
insertMany.immediate(cats);    // BEGIN IMMEDIATE
insertMany.exclusive(cats);    // BEGIN EXCLUSIVE
```

### Nested transactions (savepoints)

Calling one transaction function from inside another creates a [savepoint](https://www.sqlite.org/lang_savepoint.html):

```typescript theme={null}
const insertCats = db.transaction((cats) => {
  for (const cat of cats) insert.run(cat);
});

const adopt = db.transaction((cats) => {
  expenses.run("adoption fees", 20);
  insertCats(cats); // nested — becomes a savepoint
});
```

***

## WAL mode

[Write-ahead log mode](https://www.sqlite.org/wal.html) significantly improves write performance and allows concurrent readers. Enable it at startup:

```typescript theme={null}
db.run("PRAGMA journal_mode = WAL;");
```

### WAL sidecar file cleanup

WAL mode creates `-wal` and `-shm` sidecar files. On macOS (which uses Apple's SQLite), these persist after `db.close()`. To clean them up reliably on all platforms:

```typescript theme={null}
import { Database, constants } from "bun:sqlite";

const db = new Database("mydb.sqlite");
db.run("PRAGMA journal_mode = WAL;");

// ... use the database ...

db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0);
db.run("PRAGMA wal_checkpoint(TRUNCATE);");
db.close();
// only mydb.sqlite remains
```

***

## Integer handling

SQLite supports 64-bit signed integers, but JavaScript numbers only safely represent up to 53 bits. By default, `bun:sqlite` returns integers as `number`. Enable `safeIntegers` to use `bigint` for large values:

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

const db = new Database(":memory:", { safeIntegers: true });

const query = db.query(`SELECT ${BigInt(Number.MAX_SAFE_INTEGER) + 102n} as big_id`);
const result = query.get();

console.log(result.big_id); // 9007199254741093n (bigint)
```

With `safeIntegers: true`, passing a `bigint` that exceeds 64 bits throws a `RangeError`.

***

## Serialization

Serialize and deserialize the entire database to/from a `Uint8Array`:

```typescript theme={null}
const original = new Database("mydb.sqlite");
const bytes = original.serialize(); // Uint8Array

const copy = Database.deserialize(bytes); // new in-memory DB with same data
```

***

## Loading extensions

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

const db = new Database();
db.loadExtension("myext");
```

<Note>
  On macOS, Apple's system SQLite does not support extensions. Install a vanilla SQLite via Homebrew and point `bun:sqlite` to it:

  ```bash theme={null}
  brew install sqlite
  ```

  ```typescript theme={null}
  Database.setCustomSQLite("/opt/homebrew/Cellar/sqlite/<version>/lib/libsqlite3.dylib");
  ```
</Note>

***

## Type mapping

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

***

## API reference

```typescript theme={null}
class Database {
  constructor(
    filename?: string,
    options?: {
      readonly?: boolean;
      create?: boolean;
      readwrite?: boolean;
      safeIntegers?: boolean;
      strict?: boolean;
    },
  );

  query<ReturnType, ParamsType>(sql: string): Statement<ReturnType, ParamsType>;
  prepare<ReturnType, ParamsType>(sql: string): Statement<ReturnType, ParamsType>;
  run(
    sql: string,
    params?: SQLQueryBindings,
  ): { lastInsertRowid: number; changes: number };

  transaction(
    fn: (...args: any[]) => void,
  ): CallableFunction & {
    deferred: (...args: any[]) => void;
    immediate: (...args: any[]) => void;
    exclusive: (...args: any[]) => void;
  };

  serialize(): Uint8Array;
  static deserialize(data: Uint8Array): Database;

  fileControl(cmd: number, value: any): void;
  loadExtension(name: string): void;
  close(throwOnError?: boolean): void;
}

class Statement<ReturnType, ParamsType> {
  all(...params: ParamsType[]): ReturnType[];
  get(...params: ParamsType[]): ReturnType | undefined;
  run(...params: ParamsType[]): { lastInsertRowid: number; changes: number };
  values(...params: ParamsType[]): unknown[][];
  iterate(...params: ParamsType[]): IterableIterator<ReturnType>;
  as<T>(Class: new (...args: any[]) => T): Statement<T, ParamsType>;
  finalize(): void;
  toString(): string;

  columnNames: string[];
  paramsCount: number;
}
```