sqlite

123 notes in this subject, read out of this brain and free to use. This is page 1 of 3.

SQLite API

SQLite is available in Bun through the bun:sqlite built-in module.

sqlite loader behavior

The `sqlite` loader is accessed via import attribute `with { type: "sqlite" }`. In the runtime and bundler, SQLite databases can be directly imported. Bun loads the database with bun:sqlite. The sqlite loader is only supported when the target is bun. By default, the database is external to the bundle and Bun doesn't bundle the on-disk database file into the final output. Use the `embed` attribute to embed the database into the bundle: `import db from "./my.db" with { type: "sqlite", embed: "true" };`. With a standalone executable, Bun embeds the database into the single-file executable. Otherwise, the database to embed is copied into the outdir with a hashed filename.

node:sqlite fully implemented

node:sqlite is fully implemented in Bun. backup() runs synchronously and blocks the event loop for the duration of the copy (Node runs it on a worker thread). A Buffer/Uint8Array database path must be valid UTF-8 (Node passes the raw bytes through; Bun rejects non-UTF-8 with ERR_INVALID_ARG_VALUE). On macOS, Bun uses the system libsqlite3.dylib. loadExtension() requires a full SQLite build, and so do createSession()/applyChangeset() on older macOS releases. To use a full SQLite build, call require("bun:sqlite").Database.setCustomSQLite(path) before opening a database.

SQLite Statement.as() method for class mapping

Use `.as(Class)` to map query results to instances of a class. The class's methods, getters, and setters are available on each row. Example: `const query = db.query("SELECT title, year FROM movies").as(Movie); const movies = query.all();`. Bun does not call the class constructor or run default initializers, and private fields are not accessible—it assigns the class's prototype to the object (like `Object.create`) and sets database columns as properties.

SQLite Statement paramsCount property

A Statement instance has a `paramsCount` property (number) that indicates the number of parameters expected by the statement.

SQLite using statement for resource cleanup

The `using` statement automatically closes the database connection when the block exits. Example: `{ using db = new Database("mydb.sqlite"); using query = db.query("select 'Hello world' as message;"); console.log(query.get()); }`. The `using` statement calls `close(true)`.

SQLite Statement columnTypes property

A Statement instance has a `columnTypes` property that is an array of types based on actual values in the first row. Call `.get()` or `.all()` first to populate this property.

SQLite Database.close() method behavior

The `.close(throwOnError: boolean = false)` method closes a database connection. Calling `.close(false)` lets statements created with `.prepare()` keep working until finalized or garbage collected; calling `.close(true)` finalizes every outstanding statement immediately, releases the connection, and throws if SQLite reports an error. Statements created with `.query()` are finalized immediately either way. The method is safe to call multiple times with no effect after the first call, except `.close(true)` after `.close(false)` still finalizes remaining `.prepare()` statements. If a Database is garbage collected without being closed, Bun releases the connection once every statement created from it is finalized or collected.

SQLite module import and basic usage

Bun implements a native SQLite3 driver accessed via the `bun:sqlite` module. Import the `Database` class from `bun:sqlite` and create a database instance with `new Database(filename)`. The API is synchronous. Example: `import { Database } from "bun:sqlite"; const db = new Database(":memory:"); const query = db.query("select 'Hello world' as message;"); query.get();`

SQLite Statement declaredTypes property

A Statement instance has a `declaredTypes` property that is an array of types from the CREATE TABLE schema (string or null). Call `.get()` or `.all()` first to populate this property.

SQLite in-memory database creation

To create an in-memory SQLite database, pass ":memory:", an empty string "", or no argument to the Database constructor. All three approaches create the same in-memory database: `new Database(":memory:")`, `new Database()`, or `new Database("")`.

SQLite Statement.toString() method for debugging

Call `.toString()` on a Statement instance to print the expanded SQL query with the most recently bound parameter values. Useful for debugging. Internally calls `sqlite3_expanded_sql`.

SQLite database serialization and deserialization

Use `.serialize()` on a Database instance to serialize the database to a Uint8Array: `const contents = olddb.serialize();`. Use `Database.deserialize(contents)` to create a new database from serialized contents. Internally, `.serialize()` calls `sqlite3_serialize`.

SQLite Statement.iterate() method

Use `.iterate()` to execute a query and incrementally return results one row at a time. This is useful for large result sets that you want to process without loading all results into memory. You can also use the `@@iterator` protocol: `for (const row of query) { ... }` or `for (const row of query.iterate()) { ... }`.

SQLite parameter binding

Queries can contain parameters using numerical (`?1`, `?2`) or named syntax (`$param`, `:param`, `@param`). Bind values when executing the query by passing an object to `.all()`, `.get()`, `.run()`, or `.values()`. For named parameters with default settings, the binding object must include the prefix: `query.all({ $message: "Hello world" })`. For positional parameters, pass values directly: `query.all("hello", "goodbye")`. When `strict: true` is set on the Database, bind without prefixes: `query.all({ message: "Hello world" })`.

SQLite transaction variants

Transactions have three variants accessible as methods on the returned transaction function: `.deferred()` uses "BEGIN DEFERRED", `.immediate()` uses "BEGIN IMMEDIATE", and `.exclusive()` uses "BEGIN EXCLUSIVE". The default uses "BEGIN".

SQLite Statement.all() method

Use `.all(params)` to execute a query and get all results as an array of objects. Internally calls `sqlite3_reset` and repeatedly calls `sqlite3_step` until it returns `SQLITE_DONE`.

SQLite Statement.get() method

Use `.get(params)` to execute a query and get the first result as an object, or null if no rows are returned. Internally calls `sqlite3_reset` followed by `sqlite3_step`.

SQLite Statement.run() method

Use `.run(params)` to execute a query and get back an object with `{lastInsertRowid, changes}`. This is useful for schema-modifying queries like `CREATE TABLE` or bulk write operations. Internally calls `sqlite3_reset` and calls `sqlite3_step` once. The `lastInsertRowid` property is the ID of the last row inserted; the `changes` property is the number of rows affected.

SQLite Database constructor options

The Database constructor accepts a filename string and optional options object. Options are: `readonly` (boolean, open in read-only mode), `create` (boolean, create the database file if it doesn't exist), `readwrite` (boolean), `safeIntegers` (boolean, return integers as bigint for values larger than 2^53), `strict` (boolean, allow binding parameters without $ : @ prefixes and throw error on missing parameters).

SQLite Statement native property

A Statement instance has a `native` property that represents the native object representing the statement.

Enable SQLite WAL mode

To enable write-ahead log (WAL) mode, which dramatically improves performance with concurrent readers and a single writer, run this pragma at the beginning of your application: `db.run("PRAGMA journal_mode = WAL;");`

SQLite query caching with db.query()

The `db.query()` method prepares a SQL query and returns a cached Statement instance. The caching refers to the compiled prepared statement (SQL bytecode), not query results. When calling `db.query()` with the same SQL string multiple times, Bun returns the same cached Statement object instead of recompiling. The cache holds the most recently used `Database.MAX_QUERY_CACHE_SIZE` (default 20) SQL strings. Evicted statements keep working, but calling `db.query()` with the same string compiles a new one. It is safe to reuse a cached statement with different parameter values—parameters are bound fresh each time.

SQLite Statement columnNames property

A Statement instance has a `columnNames` property that is an array of the column names in the result set.

SQLite transactions with db.transaction()

Create a transaction with `db.transaction(callback)`, which returns a new function that wraps the callback. The wrapped function executes the callback atomically: either all queries succeed or none do. The transaction function passes its arguments through to the wrapped function and returns the wrapped function's return value. The driver automatically begins a transaction when you call the function and commits when it returns. If an exception is thrown, the driver rolls back the transaction and the exception propagates.

SQLite Statement.finalize() method

Use `.finalize()` to destroy a Statement and free associated resources. Once finalized, a Statement cannot be executed again. The garbage collector typically does this automatically, but explicit finalization may be useful in performance-sensitive applications.

SQLite strict mode behavior

When `strict: true` is set on the Database constructor, the driver throws an error if a parameter is missing from bindings, and allows binding parameter values without the $ : @ prefix. When `strict: false` (default), the $ : @ prefix is required in bindings and no error is thrown if a parameter is missing.

SQLite Statement.values() method

Use `.values(params)` to execute a query and get all results as an array of arrays instead of objects. Example: `query.values({ $message: "Hello world" })` returns `[["Hello world"]]`. Internally calls `sqlite3_reset` and repeatedly calls `sqlite3_step` until `SQLITE_DONE`.

SQLite WAL sidecar file cleanup

When using WAL mode with a file-based database, SQLite creates two sidecar files: a write-ahead log (`-wal`) and a shared-memory index (`-shm`). On macOS, Bun uses system-provided SQLite with persistent WAL enabled, so these files persist after `.close()`. On Linux and Windows, Bun statically links its own SQLite build which typically removes sidecar files after close when no other connections are open. To ensure cleanup on all platforms, disable WAL persistence and run a truncating checkpoint before closing: `db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0); db.run("PRAGMA wal_checkpoint(TRUNCATE);"); db.close();`

SQLite safeIntegers option for bigint handling

By default, `bun:sqlite` returns integers as `number` types. If you need to handle integers larger than 2^53, set `safeIntegers: true` when creating a Database instance. When `safeIntegers: true`, integers are returned as `bigint` types, and the driver validates that `bigint` values passed to `bun:sqlite` do not exceed 64 bits, throwing an error if they do. When `safeIntegers: false` (default), integers beyond 53 bits are rounded to the nearest representable `number`.

SQLite Statement execution methods

A Statement is a prepared query that can be executed multiple times. It is created with `.query()` or `.prepare()` on a Database instance. Execute with different methods: `.all(params)` returns an array of objects; `.get(params)` returns the first result as an object or null; `.run(params)` returns {lastInsertRowid, changes}; `.values(params)` returns an array of arrays; `.iterate()` or use `@@iterator` protocol for incremental iteration; `.as(Class)` maps results to class instances.

SQLite database ES module import with type attribute

You can load a SQLite database using an import attribute: `import db from "./mydb.sqlite" with { type: "sqlite" };`. This is equivalent to `import { Database } from "bun:sqlite"; const db = new Database("./mydb.sqlite");`

SQLite nested transactions become savepoints

You can call transaction functions from inside other transaction functions. When you do, the inner transaction becomes a savepoint rather than a new transaction.

SQLite Database.loadExtension() method

Call `.loadExtension(name)` on a Database instance to load a SQLite extension. On macOS, the system-provided SQLite doesn't support extensions by default. To use extensions on macOS, install a vanilla build of SQLite via Homebrew and call `Database.setCustomSQLite(path)` before creating any Database instances, passing the path to the SQLite `.dylib` file (e.g., `/opt/homebrew/Cellar/sqlite/<version>/libsqlite3.dylib`). On other operating systems, this is a no-op.

SQLite Database.fileControl() method

Call `.fileControl(cmd: number, value: any)` on a Database instance to use the advanced `sqlite3_file_control` API. The `value` parameter can be a number, TypedArray, undefined, or null.

SQLite type conversions

Data type conversions between JavaScript and SQLite: JavaScript `string` → SQLite `TEXT`, JavaScript `number` → SQLite `INTEGER` or `DECIMAL`, JavaScript `boolean` → SQLite `INTEGER` (1 or 0), JavaScript `Uint8Array` → SQLite `BLOB`, JavaScript `Buffer` → SQLite `BLOB`, JavaScript `bigint` → SQLite `INTEGER`, JavaScript `null` → SQLite `NULL`.

SQLite type definition reference

SQLite multi-query statements

Bun's SQLite driver supports multi-query statements in a single call to `database.run(query)`, such as executing `SELECT 1; SELECT 2;` together.

SQLite prepared statements with db.prepare()

Use `.prepare(sql: string)` on a Database instance to create a fresh Statement instance that is not cached. This is useful for dynamically generated SQL where you don't want to fill the query cache with one-off queries.

Bun SQLite performance comparison

The `bun:sqlite` module is roughly 3-6x faster than `better-sqlite3` and 8-9x faster than `deno.land/x/sqlite` for read queries. The module is inspired by `better-sqlite3` API. Benchmarks were performed against the Northwind Traders dataset.

sql.array helper for PostgreSQL arrays

Create PostgreSQL array literals with sql.array(): await sql`INSERT INTO tags (items) VALUES (${sql.array(["red", "blue", "green"])})`generates INSERT INTO tags (items) VALUES (ARRAY['red', 'blue', 'green']). The sql.array helper is PostgreSQL-only.

Dynamic columns in UPDATE with sql()

Use sql(object, ...columns) to pick which columns to update: await sql`UPDATE users SET ${sql(user, "name", "email")} WHERE id = ${user.id}`. If no columns are listed, Bun uses all keys on the object.

Conditional WHERE clauses in queries

Build queries with conditional clauses: const ageFilter = sql`AND age > ${minAge}`; await sql`SELECT * FROM users WHERE active = ${true} ${filterAge ? ageFilter : sql``}`. Use sql`` (empty template) for conditional parts that should be omitted.

Dynamic table names with sql() helper

Reference tables dynamically using the sql() helper to escape them: await sql`SELECT * FROM ${sql("users")}`. This safely handles table and schema names with proper escaping.

SQLite synchronous execution

SQLite executes queries synchronously, unlike PostgreSQL which uses asynchronous I/O. However, the API still returns Promises, so the interface is identical.

Bulk insert with array

Insert multiple rows at once by passing an array of objects: const users = [{ name: "Alice", email: "alice@example.com" }, { name: "Bob", email: "bob@example.com" }]; await sql`INSERT INTO users ${sql(users)}`. Bun expands this into an INSERT INTO ... VALUES ... statement.

Insert using sql() helper for objects

Use sql(object) helper for cleaner insert syntax: const userData = { name: "Alice", email: "alice@example.com" }; await sql`INSERT INTO users ${sql(userData)} RETURNING *`. Bun expands this to INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com').

Insert with RETURNING clause

Insert data and retrieve the inserted row: const [user] = await sql`INSERT INTO users (name, email) VALUES (${name}, ${email}) RETURNING *`. The RETURNING * clause returns the newly inserted row.

Query result format - .raw()

Call .raw() on a query to return rows as arrays of Buffer objects: await sql`SELECT * FROM users`.raw(). Use this for binary data or for performance.

Query result format - .values()

Call .values() on a query to return each row as an array of values in the same order as the columns: await sql`SELECT * FROM users`.values(). This is useful when a query returns duplicate column names, as objects would lose duplicates but arrays preserve all values by index.

Query result format - default objects

By default, SQL query results are returned as arrays of objects where each object represents a row with column names as keys.

SQL tagged template for queries

Execute SQL queries using tagged template literals: await sql`SELECT * FROM users WHERE active = ${true} LIMIT ${10}`. JavaScript values are passed directly and automatically escaped to prevent SQL injection.

Create SQLite connection

Create a SQLite connection with new SQL(":memory:") for in-memory database, new SQL("sqlite://myapp.db") for file-based, or new SQL({ adapter: "sqlite", filename: "./data/app.db" }). For simple filenames without a protocol like "myapp.db", you must specify { adapter: "sqlite" } to avoid ambiguity with PostgreSQL.

Pick columns to insert with sql(object, ...columns)

Use sql(object, "column1", "column2") to pick which columns to insert: await sql`INSERT INTO users ${sql(user, "name", "email")}`. Only the specified columns are inserted; other fields are ignored. Each specified column must be defined on the object.

Create MySQL connection with options object

Create a MySQL connection with new SQL({ adapter: "mysql", hostname: "localhost", port: 3306, database: "myapp", username: "dbuser", password: "secretpass" }). The adapter property is required when using an options object instead of a connection string.

Create MySQL connection with URL

Create a MySQL connection with new SQL("mysql://user:password@localhost:3306/database") or new SQL("mysql2://user:password@localhost:3306/database"). The mysql2 protocol is also supported for compatibility with the mysql2 npm package.

Create PostgreSQL connection

Create a PostgreSQL connection with new SQL("postgres://user:••••@localhost:5432/mydb") or new SQL("postgresql://..."). If no connection string is provided and DATABASE_URL environment variable points to PostgreSQL, the default sql instance uses PostgreSQL.

WHERE IN with dynamic values

Use sql([values]) for WHERE IN queries: await sql`SELECT * FROM users WHERE id IN ${sql([1, 2, 3])}`. You can also pass an array of objects with a column name: await sql`SELECT * FROM users WHERE id IN ${sql(users, "id")}`.

Import sql and SQL from bun

Import the sql tagged template and SQL class constructor from "bun". sql is a default PostgreSQL connection; SQL is the constructor for creating connections to any database type.

SQL client types - PostgreSQL, MySQL, SQLite

Bun provides native SQL bindings supporting PostgreSQL, MySQL, and SQLite through a unified Promise-based API. Queries are written as tagged template literals with support for connection pooling, transactions, and prepared statements.

Give your agent this brain