SQLite built-in module: bun:sqlite
Bun provides SQLite support through the built-in module bun:sqlite.
bun apis/sqlite
60 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.
sqlite loader for SQLite databases
The `sqlite` loader handles SQLite databases using `import db from "./my.db" with { type: "sqlite" };`. This is supported in the runtime and bundler when the target is bun. By default, the database is external to the bundle and the on-disk database file is not bundled. Use `import db from "./my.db" with { type: "sqlite", embed: "true" };` to embed the database into the bundle. When using a standalone executable, the database is embedded into the single-file executable. Otherwise, the database is copied into the outdir with a hashed filename.
Database constructor with filename
Create a new Database instance by passing a filename string: new Database('mydb.sqlite'). This opens or creates a SQLite database file at the specified path.
Database constructor for in-memory database
To create an in-memory database, pass ':memory:', an empty string '', or no arguments to the Database constructor: new Database(':memory:'), new Database(''), or new Database(). All three are equivalent.
Database constructor options
The Database constructor accepts an optional second parameter that can be a number or an options object with properties: readonly (boolean), create (boolean), readwrite (boolean), safeIntegers (boolean), and strict (boolean).
Database readonly mode
Open a database in read-only mode by passing { readonly: true } as the second argument to the Database constructor: new Database('mydb.sqlite', { readonly: true }).
Database create option
To create the database file if it doesn't exist, pass { create: true } as the second argument to the Database constructor: new Database('mydb.sqlite', { create: true }).
Database strict mode
By default, bun:sqlite requires binding parameters to include the $, :, or @ prefix and does not throw an error if a parameter is missing. Set strict: true in the Database constructor to throw an error when a parameter is missing and to allow binding without a prefix.
Load database via ES module import with attribute
Load a database file using an import attribute: import db from './mydb.sqlite' with { type: 'sqlite' }. This is equivalent to new Database('./mydb.sqlite').
Database.close() method
The Database class has a close(throwOnError: boolean = false) method. Calling close(false) closes the connection but lets statements created with .prepare() keep working until finalized. Calling close(true) finalizes every outstanding statement, releases the connection immediately, and throws if SQLite reports an error. close() is safe to call multiple times but has no effect after the first call.
Database statements owned by Database
Statements created with .query() are owned by the Database and are finalized immediately when the database closes. Statements created with .prepare() are not owned by the Database, and the underlying connection is released once the last outstanding .prepare() statement is finalized.
Database using statement
Use the 'using' statement to automatically close a database connection when the block exits: using db = new Database('mydb.sqlite'). The 'using' statement calls close(true).
Database.serialize() method
Call serialize() on a Database instance to serialize the database to a Uint8Array in memory. This calls sqlite3_serialize internally: const contents = db.serialize().
Database.deserialize() static method
Call the static method Database.deserialize(contents) to deserialize a database from a Uint8Array that was created with serialize(). This reconstructs a Database instance from the serialized bytes.
db.query() method prepares and caches statements
Call db.query(sql) to prepare a SQL query and get back a Statement instance. The query is not executed. The compiled prepared statement (SQL bytecode) is cached on the Database instance. When query() is called with the same SQL string multiple times, Bun returns the same cached Statement object instead of recompiling. The cache holds Database.MAX_QUERY_CACHE_SIZE (default 20) most recently used SQL strings.
db.prepare() method creates uncached statements
Call db.prepare(sql) to create a fresh Statement instance that is not cached. Use prepare() instead of query() when dynamically generating SQL and you don't want to fill the query cache.
Statement.all() returns array of objects
Call all() on a Statement to run the query and get back all results as an array of objects. Parameters can be passed as an object or individual arguments depending on the parameter style.
Statement.get() returns first result as object
Call get() on a Statement to run the query and get back the first result as an object. If the query returns no rows, null is returned. Parameters can be passed as an object or individual arguments.
Statement.run() returns execution metadata
Call run() on a Statement to run a query and get back an object with properties: lastInsertRowid (the ID of the last row inserted) and changes (the number of rows affected). This is useful for schema-modifying queries or bulk writes.
Statement.values() returns array of arrays
Call values() on a Statement to run the query and get back all results as an array of arrays, where each inner array represents one row with column values.
Statement.as(Class) maps results to class instances
Call as(Class) on a Statement to map query results to instances of a class. The class constructor is not called, default initializers are not run, and private fields are not accessible. The class prototype is assigned to the object so its methods, getters, and setters work. Database columns are set as properties on the class instance.
Statement.iterate() for incremental result processing
Call iterate() on a Statement to incrementally return results one row at a time. This is useful for large result sets to avoid loading all results into memory at once. The Statement also supports the @@iterator protocol, so you can use it directly in a for...of loop.
Statement.finalize() destroys statement
Call finalize() on a Statement to destroy the statement and free associated resources. Once finalized, the statement cannot be executed again. Typically the garbage collector handles this, but explicit finalization may be useful in performance-sensitive applications.
Statement.toString() prints expanded SQL
Call toString() on a Statement to print the expanded SQL query with parameters replaced by their most recently bound values. This is useful for debugging.
Statement properties: columnNames, columnTypes, declaredTypes, paramsCount, native
A Statement instance has read-only properties: columnNames (array of column names), columnTypes (types based on actual values in first row, set after get()/all()), declaredTypes (types from CREATE TABLE schema, set after get()/all()), paramsCount (number of parameters expected), and native (the native object representing the statement).
Named parameters in queries
Queries can use named parameters with $, :, or @ prefixes: db.query('SELECT $param1, :param2, @param3'). Bind values by passing an object with the prefixed names as keys.
Numbered positional parameters in queries
Queries can use numbered positional parameters: db.query('SELECT ?1, ?2'). Bind values by passing arguments in order: query.all('value1', 'value2').
Bind values without prefixes with strict mode
When strict: true is set in the Database constructor, you can bind values to named parameters without the $, :, or @ prefixes. For example: db.query('SELECT $message'); query.all({ message: 'Hello' });
SQLite safeIntegers option for bigint handling
Set safeIntegers: true in the Database constructor to handle integers larger than 2^53. When true, bun:sqlite returns integers as bigint types and validates that bigint values do not exceed 64 bits. When false (default), integers are returned as number types and bits beyond 53 are truncated.
safeIntegers: true returns integers as bigint
When safeIntegers: true is set on a Database, query results return integers as bigint types instead of number types, allowing safe handling of 64-bit integers.
safeIntegers: true validates bigint bounds
When safeIntegers: true is set, bun:sqlite throws an error if a bigint value in a bound parameter exceeds 64 bits: 'BigInt value is out of range'.
db.transaction() creates atomic transactions
Call db.transaction(function) to create a transaction. It returns a new function that wraps the provided function. When called, the wrapper executes the wrapped function atomically: either all queries succeed or none do. If an exception is thrown, the transaction is rolled back.
Transaction function variants: deferred, immediate, exclusive
A transaction returned by db.transaction() has three variants: transactionFn.deferred() uses 'BEGIN DEFERRED', transactionFn.immediate() uses 'BEGIN IMMEDIATE', and transactionFn.exclusive() uses 'BEGIN EXCLUSIVE'. The default is 'BEGIN'.
Nested transactions become savepoints
Transaction functions can be called from inside other transaction functions. When nesting, the inner transaction becomes a SQLite savepoint rather than a new transaction.
db.loadExtension() loads SQLite extensions
Call db.loadExtension(name) on a Database instance to load a SQLite extension.
SQLite extensions not supported on default macOS
By default, macOS ships with Apple's proprietary build of SQLite which does not support extensions. To use extensions on macOS, install a vanilla build of SQLite (e.g., via Homebrew) and call Database.setCustomSQLite(path) before creating any Database instances, passing the path to the SQLite .dylib file.
Database.setCustomSQLite() for custom SQLite build
Call the static method Database.setCustomSQLite(path) to point bun:sqlite to a custom SQLite build. Pass the path to the SQLite .dylib file (not the executable). On non-macOS systems this is a no-op. Must be called before creating any Database instances.
db.fileControl() for sqlite3_file_control API
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.
WAL mode dramatically improves performance
SQLite's write-ahead log (WAL) mode dramatically improves performance, especially with many concurrent readers and a single writer. Enable WAL mode by running: db.run('PRAGMA journal_mode = WAL;')
WAL mode creates sidecar files
When using WAL mode with a file-based database, SQLite creates two sidecar files: a write-ahead log file (-wal) and a shared-memory index file (-shm). In WAL mode, writes are written directly to the WAL file and later integrated into the main database file.
WAL sidecar file cleanup on macOS vs Linux/Windows
On macOS, Bun uses Apple's system SQLite which has persistent WAL enabled, so -wal and -shm files persist after close(). On Linux and Windows, Bun statically links its own SQLite build which follows upstream defaults, and sidecar files are typically removed after close when no other connections are open.
Disable WAL persistence and cleanup sidecar files
To ensure sidecar files are cleaned up on all platforms, disable persistent WAL and run a truncating checkpoint before closing: db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0); db.run('PRAGMA wal_checkpoint(TRUNCATE);'); db.close();
db.run() method for direct query execution
Call db.run(sql, params?) directly on a Database instance to execute a SQL query without creating a Statement. Returns an object with lastInsertRowid and changes properties.
db.exec is an alias for db.run()
The Database class has an exec property that is an alias for the run method. Both db.run() and db.exec() execute SQL queries directly.
JavaScript to SQLite type mapping
JavaScript types map to SQLite types as follows: string -> TEXT, number -> INTEGER or DECIMAL, boolean -> INTEGER (1 or 0), Uint8Array -> BLOB, Buffer -> BLOB, bigint -> INTEGER, null -> NULL.
SQLQueryBindings type definition
The SQLQueryBindings type encompasses the values that can be bound to parameters: string, bigint, TypedArray, number, boolean, null, or a Record with these same types as values.
Multi-query statements with database.run()
bun:sqlite supports executing multi-query statements (multiple SELECT, INSERT, etc. separated by semicolons) in a single call to database.run(). For example: db.run('SELECT 1; SELECT 2;')
BLOB data becomes Uint8Array
SQLite BLOB columns are automatically converted to Uint8Array when returned from queries, providing native JavaScript typed array support for binary data.
bun:sqlite performance benchmark
bun:sqlite is 3-6x faster than better-sqlite3 and 8-9x faster than deno.land/x/sqlite for read queries when benchmarked against the Northwind Traders dataset. The benchmarks were run on an M1 MacBook Pro with 64GB RAM running macOS 12.3.1.
bun:sqlite query caching behavior
It is safe to reuse a cached Statement returned from db.query() with different parameter values. Parameters are bound fresh each time the statement is executed (get(), all(), run(), or values() is called).
Garbage collection of finalized statements
If a Database is garbage collected without being closed, the connection is released once every statement created from it has also been finalized or garbage collected.
Statement.as() example with Movie class
Example: class Movie { title: string; year: number; get isMarvel() { return this.title.includes('Marvel'); } } const query = db.query('SELECT title, year FROM movies').as(Movie); const movies = query.all(); console.log(movies[0].isMarvel); // Methods and getters work on result instances.
Statement.iterate() example for large datasets
Example: const query = db.query('SELECT * FROM foo'); for (const row of query.iterate()) { console.log(row); } Or use the @@iterator protocol: for (const row of query) { console.log(row); }
Database transaction example with insertCats
Example: const insertCat = db.prepare('INSERT INTO cats (name) VALUES ($name)'); const insertCats = db.transaction(cats => { for (const cat of cats) insertCat.run(cat); return cats.length; }); const count = insertCats([{ $name: 'Keanu' }, { $name: 'Salem' }]); console.log(`Inserted ${count} cats`);
Database.serialize() and deserialize() example
Example: const olddb = new Database('mydb.sqlite'); const contents = olddb.serialize(); // => Uint8Array const newdb = Database.deserialize(contents); This serializes and deserializes a database to/from memory using sqlite3_serialize internally.
WAL mode with sidecar cleanup example
Example: import { Database, constants } from 'bun:sqlite'; const db = new Database('mydb.sqlite'); db.run('PRAGMA journal_mode = WAL;'); db.fileControl(constants.SQLITE_FCNTL_PERSIST_WAL, 0); db.run('PRAGMA wal_checkpoint(TRUNCATE);'); db.close(); This ensures WAL sidecar files are cleaned up on all platforms.
safeIntegers: true bigint handling example
Example: const db = new Database(':memory:', { safeIntegers: true }); const query = db.query(`SELECT ${BigInt(Number.MAX_SAFE_INTEGER) + 102n} as max_int`); const result = query.get(); console.log(result.max_int); // Output: 9007199254741093n
Strict mode with parameter binding example
Example: const strict = new Database(':memory:', { strict: true }); const query = strict.query('SELECT $message;').all({ messag: 'Hello world' }); // Throws error because of typo. Without strict mode, does not throw error.
Statement.toString() debugging example
Example: const query = db.query('SELECT $param;'); console.log(query.toString()); // 'SELECT NULL' query.run(42); console.log(query.toString()); // 'SELECT 42' query.run(365); console.log(query.toString()); // 'SELECT 365'
bun:sqlite module import
Import the SQLite driver from the built-in module 'bun:sqlite'. The module exports a Database class that can be instantiated to open or create SQLite databases.