sqlite

fs?: FileSystem

FileSystem provider used by Fino's SQLite VFS.

Defaults to a new DiskFileSystem. Supplying a custom provider lets SQLite read and write through virtual filesystems — a database file, its journal, and its WAL all flow through this provider rather than direct disk I/O.

import { Database } from 'fino:database/sqlite';
import { DiskFileSystem } from 'fino:file';

const db = await Database.open('/data/app.db', { fs: new DiskFileSystem() });
await db.close();

readonly?: boolean

Open the database in read-only mode.

The default is false, which opens read-write and creates the database if needed. Read-only connections reject writes at SQLite level and fail when the file does not exist.

safeIntegers?: boolean

Return INTEGER columns as bigint when true.

The default is true. Set to false to coerce INTEGER results to JavaScript number, accepting precision loss for values outside the safe integer range. This does not affect lastInsertRowid, which is always a bigint.

constructor(db: Database, sql: string, safeIntegers: boolean)

Create a statement wrapper over sql for an open connection.

Application code should normally call Database.prepare() instead of this constructor so the statement inherits the database's integer mapping and is tracked for cleanup at close(). safeIntegers controls whether INTEGER columns are returned as bigint. Compilation remains lazy until the first execution method is called.

import { Database, Statement } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
const stmt = new Statement(db, 'SELECT 1 AS value', true);
console.log(await stmt.get());
stmt.finalize();
await db.close();

run(...params: SqlValue[]): Promise<{ changes: number; lastInsertRowid: bigint; }>

Execute the statement and return write metadata.

Parameters may be positional values or one named-parameter object. The statement is reset after execution. Parameter counts are validated before binding. SQL errors reject with the database error message. Result rows, if any, are discarded; the resolved object carries the connection's change count and last insert rowid.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.exec('CREATE TABLE t (name TEXT)');
const result = await db.prepare('INSERT INTO t VALUES (?)').run('hello');
console.log(result.changes, result.lastInsertRowid);
await db.close();

get(...params: SqlValue[]): Promise<Record<string, SqlValue> | undefined>

Execute and return the first row.

Returns undefined when the query produces no rows. Column names are used as object keys. Parameter counts are validated before binding. The statement is reset before returning or throwing.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
const row = await db.prepare('SELECT 42 AS answer').get();
console.log(row?.answer);
await db.close();

all(...params: SqlValue[]): Promise<Record<string, SqlValue>[]>

Execute and return all rows.

This buffers every result row in memory. Use iterate() for large result sets. Parameter counts are validated before binding. The statement is reset before returning or throwing, and rows resolve in result order.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
const rows = await db.prepare('SELECT 1 AS n UNION ALL SELECT 2').all();
console.log(rows.length);
await db.close();

async *iterate(...params: SqlValue[]): AsyncGenerator<Record<string, SqlValue>>

Async-iterate rows one at a time.

The statement remains active for the duration of iteration and is reset in a finally block when iteration finishes, throws, or is abandoned early. Parameter counts are validated before binding. Each step is serialized on the connection individually, so other operations on the same connection may interleave between rows.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
for await (const row of db.prepare('SELECT 1 AS n').iterate()) {
  console.log(row.n);
}
await db.close();

finalize(): void

Finalize and free this statement.

Calling finalize() more than once is allowed. Any later execution method throws Statement is finalized.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
const stmt = db.prepare('SELECT 1');
stmt.finalize();
await db.close();

get ptr(): ArrayBuffer

Internal sqlite3 pointer for statement helpers.

This getter exposes the native pointer wrapper used by this module. It is public for Statement integration but is not needed by normal application code. The value is a Fino pointer buffer containing the sqlite3* address and becomes invalid after close().

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
console.log(db.ptr.byteLength);
await db.close();

get vectorsAvailable(): boolean

Whether the current sqlite build supports extension loading and sqlite-vec was found. Probed lazily on first access.

The probe tries FINO_SQLITE_VEC_PATH first when present, then common platform paths, and returns true only when sqlite-vec actually loaded. A failed probe caches false. Access may enable extension loading on the connection.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
console.log(db.vectorsAvailable);
await db.close();

get changes(): number

Number of rows changed by the most recent DML statement.

This mirrors sqlite3_changes() for the connection. It is meaningful after INSERT, UPDATE, DELETE, and similar statements. It throws only if SQLite bindings are unavailable.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.exec('CREATE TABLE t (value TEXT)');
await db.prepare('INSERT INTO t VALUES (?)').run('x');
console.log(db.changes);
await db.close();

get lastInsertRowid(): bigint

Row ID of the most recent INSERT.

This mirrors sqlite3_last_insert_rowid() and returns a bigint regardless of safeIntegers, because rowids may exceed JavaScript's safe integer range.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.exec('CREATE TABLE t (id INTEGER PRIMARY KEY)');
await db.prepare('INSERT INTO t DEFAULT VALUES').run();
console.log(db.lastInsertRowid);
await db.close();

static async open(path: string, opts: DatabaseOptions = {}): Promise<Database>

Open a database at path. Use ':memory:' for an in-memory database. Pass { fs } to route I/O through a custom FileSystem provider.

By default, the database opens read-write and is created if missing. { readonly: true } opens read-only. Each connection registers a private Fino VFS name so file operations go through the chosen filesystem provider. Throws when SQLite is unavailable, open fails, or VFS registration fails.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:', { safeIntegers: true });
await db.close();

exec(sql: string): Promise<void>

Execute one or more SQL statements with no result rows.

This uses sqlite3_exec() and is best for schema setup, pragmas, and simple SQL batches. Use prepare() for parameter binding or reading result rows. Treat this as a trusted-SQL-only API: never concatenate untrusted user input into exec() strings. Throws on SQL errors or when the database is closed.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.exec('CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)');
await db.close();

prepare(sql: string): Statement

Compile a SQL statement and return a reusable Statement. Compilation is lazy — it happens on the first .run/.get/.all/.iterate call.

Throws immediately if the database is closed. SQL syntax errors are thrown later when the statement first compiles.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
const stmt = db.prepare('SELECT ? AS value');
console.log(await stmt.get(123));
stmt.finalize();
await db.close();

async transaction<T>(fn: () => Promise<T>): Promise<T>

Run fn inside a BEGIN/COMMIT transaction. Rolls back on throw.

The transaction starts with BEGIN, commits if fn resolves, and attempts ROLLBACK if fn throws, then rethrows fn's error. The resolved value is whatever fn returned. Nested transaction behavior depends on SQLite and the SQL executed by fn; this helper does not create savepoints.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.exec('CREATE TABLE t (value TEXT)');
await db.transaction(async () => {
  await db.prepare('INSERT INTO t VALUES (?)').run('ok');
});
await db.close();

loadExtension(path: string, entryPoint?: string): void

Load a SQLite extension from path. Requires a SQLite build with extension loading enabled (e.g. Homebrew sqlite on macOS).

Loading an extension executes native code inside the current process. Only load trusted extension libraries from trusted paths.

The optional entryPoint symbol is passed through to sqlite3_load_extension. Throws when an absolute path does not exist, with SQLite's extension error message when loading fails, and if the database is closed.

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
db.loadExtension('/usr/local/lib/sqlite-vec.dylib');
await db.close();

async close(): Promise<void>

Close the database and unregister the VFS.

Calling close() more than once is allowed. The close runs after any in-flight queued operations, and any statements created by this connection are finalized before the native database handle is closed. Closing also removes the automatic Realm-shutdown cleanup registered by open().

import { Database } from 'fino:database/sqlite';

const db = await Database.open(':memory:');
await db.close();