roots/data API

Functions #

migrationChecksum #

function migrationChecksum(sql: string): string

The checksum stored for a migration: SHA-256 hex of its SQL with \n line endings.

Defined in runtime/data/migrate.lisp.

open #

function open(name: string, options?: OpenOptions): Promise<Database>

Open (or create) the database NAME: letters, digits, _ and -, at most 64 characters. Where it lives depends on the target (docs/data.md): a file in the app's data directory, OPFS in the browser, the app sandbox on phones. { memory: true } keeps it in memory.

Defined in runtime/data/index.lisp.

Constants #

migrations #

const migrations: readonly Migration[]

Defined in runtime/data/migrations.lisp.

Types #

Database #

class Database extends Session {
  readonly name: string;
  readonly driver: string;
  readonly persistent: boolean;
  readonly reason: PersistenceReason | null;
  readonly location: string;
  readonly closed: boolean;
  migrate(list?: readonly Migration[]): Promise<MigrateResult>;
  live<T = Row>(sql: string | (() => LiveSource), params?: Params): LiveQuery<T>;
  settled(): Promise<void>;
  onChange(listener: Listener): () => void;
  close(): Promise<void>;
}

An open SQLite database. Create it with open(name). Extends Session: its members are members of this class too.

database.name #

readonly name: string

database.driver #

readonly driver: string

database.persistent #

readonly persistent: boolean

database.reason #

readonly reason: PersistenceReason | null

Why the database is not persistent, or null when it is: "no-opfs" (the browser has no OPFS here), "another-tab" (another tab holds the files and cannot share them), "memory" (opened with { memory: true }, or by a driver that keeps nothing).

database.location #

readonly location: string

database.closed #

readonly closed: boolean

database.migrate() #

migrate(list?: readonly Migration[]): Promise<MigrateResult>

Apply pending migrations (default: the app's migrations/ folder, see migrations).

database.live() #

live<T = Row>(sql: string | (() => LiveSource), params?: Params): LiveQuery<T>

A LiveQuery: a reactive ref of the rows, re-run when a table it reads is written. SQL may be a function returning the SQL or [sql, params]: it is tracked like a computed, and when a value it read changes the query runs with the new text and parameters; the rows of before stay until the new result arrives. Created where an effect scope is current (a component's setup, effectScope().run), it stops with that scope; anywhere else it lives until stop() or db.close().

database.settled() #

settled(): Promise<void>

Resolves when the live queries show every write made so far: the statements called before it have run, and no live query is running or waiting to run again. After await table.update(…); await db.settled() the rows of every live query include the update. Inside db.transaction(async (tx) => …) it would wait for the transaction itself.

database.onChange() #

onChange(listener: Listener): () => void

Call LISTENER(tables) after each write (a transaction: once, on commit). "*" means any table (a schema change, a write roots/data could not analyse). Returns a remover.

database.close() #

close(): Promise<void>

Stop the live queries, wait for pending statements, close the connection.

Defined in runtime/data/index.lisp.

DataError #

class DataError extends RootsError {
  readonly sqlite: string | undefined;
  readonly sql: string | undefined;
}

A roots/data error. CODE is the D1xx code; SQLITE the SQLite result code name ("SQLITE_CONSTRAINT_UNIQUE") when the engine reported one; SQL the statement. Extends RootsError: its members are members of this class too.

dataError.sqlite #

readonly sqlite: string | undefined

dataError.sql #

readonly sql: string | undefined

Defined in runtime/data/common.lisp.

Driver #

interface Driver {
  readonly name: string;
  readonly persistent: boolean;
  readonly location: string;
  exec(sql: string): Promise<DriverExec>;
  run(sql: string, params: SqlValue[]): Promise<DriverRun>;
  query(sql: string, params: SqlValue[]): Promise<DriverRows>;
  close(): Promise<void>;
  reset?(): void;
  readonly reason?: string | null;
  notify?(tables: string[]): void;
  subscribe?(listener: (a: string[]) => void): () => void;
}

One open SQLite connection. Calls arrive one at a time (roots/data serializes them).

Defined in runtime/data/types.lisp.

DriverFactory #

type DriverFactory = (a: DriverOptions) => Promise<Driver>;

Defined in runtime/data/types.lisp.

DriverRows #

interface DriverRows {
  columns: string[];
  rows: SqlValue[][];
  tables?: string[];
}

Result rows as arrays, in the order of COLUMNS. TABLES: see DriverRun.

Defined in runtime/data/types.lisp.

FindOptions #

interface FindOptions {
  orderBy?: string;
  desc?: boolean;
  limit?: number;
}

Defined in runtime/data/types.lisp.

LiveQuery #

interface LiveQuery<T> extends ReadonlyRef<T[]> {
  readonly loading: boolean;
  readonly error: unknown;
  readonly ready: Promise<void>;
  readonly tables: readonly string[];
  refresh(): Promise<void>;
  stop(): void;
}

A reactive ref of the rows of a query: .value re-runs (and the ref changes) only when a table the query reads is written through this database.

Defined in runtime/data/index.lisp.

LiveSource #

type LiveSource = string | readonly [string] | readonly [string, Params] | null | undefined;

What the function of db.live(() => …) returns: the SQL, or [sql, params]; null or undefined for "no query now" (the rows are then empty).

Defined in runtime/data/index.lisp.

MigrateResult #

interface MigrateResult {
  readonly applied: number[];
  readonly version: number;
}

Defined in runtime/data/types.lisp.

Migration #

interface Migration {
  readonly version: number;
  readonly name: string;
  readonly sql: string;
}

Defined in runtime/data/types.lisp.

OpenOptions #

interface OpenOptions {
  memory?: boolean;
  driver?: DriverFactory;
  foreignKeys?: boolean;
  trustedSchema?: boolean;
  attach?: boolean;
  memoryFallback?: boolean;
}

memory: an in-memory database (gone when the app closes). driver: bring your own adapter (a DriverFactory). foreignKeys: false turns SQLite's foreign key checks off. trustedSchema: true lets views, triggers and defaults call functions that are not marked innocuous (PRAGMA trusted_schema). attach: true allows ATTACH, DETACH and VACUUM INTO (D117 otherwise). memoryFallback: false makes open() fail (D154) where the browser cannot persist the database, instead of keeping it in memory (Database.reason says why).

Defined in runtime/data/types.lisp.

Param #

type Param = SqlValue | boolean;

A value that can be bound to a ? parameter: booleans are stored as 1 and 0.

Defined in runtime/data/types.lisp.

Params #

type Params = readonly Param[];

Defined in runtime/data/types.lisp.

PersistenceReason #

type PersistenceReason = "memory" | "no-opfs" | "another-tab";

Why a database is not persistent: "no-opfs" (the browser context has no OPFS: private browsing, an insecure context, an old browser), "another-tab" (another tab holds the files and cannot share them), "memory" (it was opened with { memory: true }, or by a driver that keeps nothing).

Defined in runtime/data/types.lisp.

Query #

class Query<T = Row, P extends readonly Param[] = Param[]> {
  readonly sql: string;
  all(...params: P): Promise<T[]>;
  get(...params: P): Promise<T | undefined>;
  run(...params: P): Promise<RunResult>;
  live(...params: P): LiveQuery<T>;
}

A prepared-looking statement with typed parameters (the driver caches the real one).

query.sql #

readonly sql: string

query.all() #

all(...params: P): Promise<T[]>

query.get() #

get(...params: P): Promise<T | undefined>

query.run() #

run(...params: P): Promise<RunResult>

query.live() #

live(...params: P): LiveQuery<T>

Defined in runtime/data/index.lisp.

Row #

type Row = { [column: string]: SqlValue };

Defined in runtime/data/types.lisp.

RunResult #

interface RunResult {
  readonly changes: number;
  readonly lastInsertRowid: number;
}

Defined in runtime/data/types.lisp.

Session #

abstract class Session {
  run(sql: string, params?: Params): Promise<RunResult>;
  all<T = Row>(sql: string, params?: Params): Promise<T[]>;
  get<T = Row>(sql: string, params?: Params): Promise<T | undefined>;
  exec(sql: string): Promise<void>;
  transaction<R>(fn: (a: Transaction) => R | Promise<R>): Promise<R>;
  query<T = Row, P extends readonly Param[] = Param[]>(sql: string): Query<T, P>;
  table<T = Row, I = Partial<T>>(name: string): Table<T, I>;
}

Statements on a Database or inside a Transaction.

session.run() #

run(sql: string, params?: Params): Promise<RunResult>

Run one statement; { changes, lastInsertRowid }.

session.all() #

all<T = Row>(sql: string, params?: Params): Promise<T[]>

Every row of one statement, as objects.

session.get() #

get<T = Row>(sql: string, params?: Params): Promise<T | undefined>

The first row of one statement, or undefined.

session.exec() #

exec(sql: string): Promise<void>

Run one or more statements without parameters (schema scripts).

session.transaction() #

transaction<R>(fn: (a: Transaction) => R | Promise<R>): Promise<R>

Run FN(tx) in a transaction: committed when FN's promise resolves, rolled back when it rejects (the error is rethrown). Nested calls on tx use savepoints.

session.query() #

query<T = Row, P extends readonly Param[] = Param[]>(sql: string): Query<T, P>

A typed statement to run with different parameters: db.query<Row, [id: number]>(sql).

session.table() #

table<T = Row, I = Partial<T>>(name: string): Table<T, I>

Typed insert/update/delete/find for one table (identifiers checked and quoted).

Defined in runtime/data/index.lisp.

SqlValue #

type SqlValue = string | number | null | Uint8Array;

A value SQLite stores and returns: TEXT, INTEGER/REAL, NULL, BLOB.

Defined in runtime/data/types.lisp.

Table #

class Table<T = Row, I = Partial<T>> {
  readonly name: string;
  insert(values: I): Promise<RunResult>;
  update(where: Partial<T>, values: Partial<T>): Promise<RunResult>;
  delete(where: Partial<T>): Promise<RunResult>;
  find(where?: Partial<T>, options?: FindOptions): Promise<T[]>;
  get(where: Partial<T>, options?: FindOptions): Promise<T | undefined>;
  count(where?: Partial<T>): Promise<number>;
  live(
    where?: Partial<T> | (() => Partial<T> | undefined),
    options?: FindOptions,
  ): LiveQuery<T>;
}

CRUD for one table with checked, quoted identifiers and bound values: notes.insert({ title }), notes.update({ id }, { title }), notes.delete({ id }), notes.find({ pinned: 1 }, { orderBy: "id", desc: true, limit: 20 }), notes.get({ id }), notes.count()

table.name #

readonly name: string

table.insert() #

insert(values: I): Promise<RunResult>

table.update() #

update(where: Partial<T>, values: Partial<T>): Promise<RunResult>

table.delete() #

delete(where: Partial<T>): Promise<RunResult>

table.find() #

find(where?: Partial<T>, options?: FindOptions): Promise<T[]>

table.get() #

get(where: Partial<T>, options?: FindOptions): Promise<T | undefined>

table.count() #

count(where?: Partial<T>): Promise<number>

table.live() #

live(
  where?: Partial<T> | (() => Partial<T> | undefined),
  options?: FindOptions,
): LiveQuery<T>

The rows of find(where, options) as a LiveQuery. WHERE may be a function: the query then follows the values it reads, like db.live(() => …).

Defined in runtime/data/index.lisp.

Transaction #

class Transaction extends Session {
}

The tx of db.transaction(async (tx) => …). Extends Session: its members are members of this class too.

Defined in runtime/data/index.lisp.