js/database

type DatabaseDriver = 'sqlite' | 'postgres'

Identifier for the concrete engine behind a connection.

Database.open() selects the driver from the connection target and exposes the choice as DatabaseConnection.driver, letting portable code branch into dialect-specific SQL only where it must.

type DbValue = null | undefined | bigint | number | string | boolean | Uint8Array | Date

Portable values accepted for parameter binding and returned in result rows.

null and undefined bind as SQL NULL. bigint and integral number values bind as integers, other numbers as floating point, strings as text, and Uint8Array as BLOB/bytea. Date is serialized to an ISO-8601 string by the Postgres engine; SQLite has no native date binding, so convert dates to strings or numbers explicitly in portable code.

type DbParams = DbValue | Record<string, DbValue>

One argument to statement execution: a positional value or a record of named parameters.

Positional values fill ? (or $n) placeholders in order. A record binds named placeholders — write :name in the SQL and key the record by the bare name. Prefer either positional values or a single named record per call: SQLite rejects mixing a named record with positional placeholders, while the Postgres placeholder rewrite tolerates the combination.

type DbRow = Record<string, DbValue>

A single result row: column names mapped to portable values.

Value representation follows the engine's type mapping — for example SQLite integers arrive as number by default (or bigint when the connection was opened with the engine's safeIntegers option).

type SqlDialect = DatabaseDriver

Placeholder dialect used when rendering a SqlFragment to text.

Identical to DatabaseDriver: 'postgres' renders $1, $2, … placeholders while 'sqlite' renders ?.

type SqlInput = string | SqlFragment

Query forms accepted by exec() and prepare(): a raw SQL string or a SqlFragment built with the sql tag.

interface QueryResult {

Outcome of a statement executed with DatabaseStatement.run().

changes is always present; the other fields depend on the engine that produced the result.

Properties

interface DatabaseStatement {

Prepared statement handle shared by both engines.

Statements come from DatabaseConnection.prepare() and may be executed repeatedly with different parameters. Each execution method accepts positional values or a single record of named parameters (see DbParams). When the statement was prepared from a SqlFragment, the fragment's interpolated values are bound first and call-time parameters follow them.

import { Database } from 'fino:database';

await using db = await Database.open(':memory:');
await db.exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');

const insert = db.prepare('INSERT INTO users (name) VALUES (?)');
await insert.run('Ada');
await insert.run('Grace');
insert.finalize();

const byId = db.prepare('SELECT * FROM users WHERE id = :id');
console.log(await byId.get({ id: 1 }));

for await (const row of db.prepare('SELECT name FROM users').iterate()) {
  console.log(row.name);
}

Methods

interface DatabaseConnection {

Engine-agnostic connection contract returned by Database.open().

Both the SQLite and Postgres engines are wrapped in this shape: exec() for fire-and-forget statements, prepare() for reusable parameterized statements, transaction() for atomic groups, and close() (aliased as Symbol.asyncDispose) for teardown. Anything beyond this portable core lives on the concrete engine classes.

import { Database, sql } from 'fino:database';

await using db = await Database.open(process.env.DATABASE_URL ?? ':memory:');
await db.exec('CREATE TABLE IF NOT EXISTS accounts (id INTEGER PRIMARY KEY, balance REAL)');

await db.transaction(async () => {
  await db.prepare(sql`UPDATE accounts SET balance = balance - ${25} WHERE id = ${1}`).run();
  await db.prepare(sql`UPDATE accounts SET balance = balance + ${25} WHERE id = ${2}`).run();
});

console.log(db.driver, await db.prepare('SELECT * FROM accounts').all());

Readonly Properties

Methods

interface SqlFragment {

Portable SQL fragment produced by the sql tag.

A fragment keeps the SQL text and its interpolated values separate so the values can be bound as statement parameters instead of being spliced into the query string. Fragments compose: interpolating one fragment inside another splices its text and renumbers its value slots.

Detection is structural — anything with kind: 'sql', a values array, and a text function is treated as a fragment — so fragments do not need to come from this module's own sql tag to compose with it.

import { sql, type SqlFragment } from 'fino:database';

const where: SqlFragment = sql`WHERE id = ${42}`;
const query = sql`SELECT * FROM users ${where}`;
console.log(query.text('postgres')); // SELECT * FROM users WHERE id = $1
console.log(query.text('sqlite'));   // SELECT * FROM users WHERE id = ?
console.log(query.values);           // [42]

Readonly Properties

Methods

interface SqlTag {

Shape of the sql template tag: callable as a tag, with helpers for the positions placeholders cannot express.

import { sql, type SqlTag } from 'fino:database';

const tag: SqlTag = sql;
const fragment = tag`SELECT ${1} AS answer`;
console.log(fragment.values); // [1]

Methods

const sql: SqlTag

Template tag for building parameterized, dialect-portable SQL.

Interpolated values never enter the SQL text: each ${value} becomes a placeholder and the value travels in SqlFragment.values for statement binding, which makes value positions safe from SQL injection. Interpolating another fragment splices it inline with its placeholders renumbered, so queries can be assembled from reusable pieces.

The helpers cover what placeholders cannot: sql.identifier() validates and quotes table or column names, sql.join() expands lists (for example IN (...) membership tests), and sql.raw() inserts trusted SQL text verbatim.

import { Database, sql } from 'fino:database';

await using db = await Database.open(':memory:');
const table = sql.identifier('users');
const names = ['Ada', 'Grace'];
const query = sql`SELECT * FROM ${table} WHERE name IN (${sql.join(names)})`;
// postgres: SELECT * FROM "users" WHERE name IN ($1, $2)
// sqlite:   SELECT * FROM "users" WHERE name IN (?, ?)
const rows = await db.prepare(query).all();
console.log(rows);

class Database {

Entry point that opens a connection on whichever engine matches the target.

This is the portable front door of the module: hand it a connection string (typically from configuration such as DATABASE_URL) and it returns a DatabaseConnection backed by the right engine. Code that needs engine-specific features should open PostgresDatabase or sqlite.Database directly instead.

import { Database } from 'fino:database';

await using pg = await Database.open('postgres://app@localhost/app');
await using file = await Database.open('./data/app.db');
await using mem = await Database.open(':memory:');
console.log(pg.driver, file.driver, mem.driver); // postgres sqlite sqlite

Static Methods