# declarative-sqlite > declarative-sqlite is a TypeScript library for offline-first browser apps: SQLite (WebAssembly, OPFS) with a declarative schema, automatic additive migration, live queries, and a sync layer (cursor-based pull, column-level outbox, idempotent batched push). Install v3 with `npm install declarative-sqlite`. # declarative-sqlite declarative-sqlite is a TypeScript library for apps that must keep working without a network. It runs real SQLite in the browser (WebAssembly, stored in OPFS where available), keeps a local copy of server data, lets the user edit it offline, and syncs the edits back when the connection returns. :::note Version These docs cover **v3** (`npm install declarative-sqlite`), a full rewrite of the package. See [Upgrading from v2](./upgrading-from-v2.md) if you have an existing app. ::: ## What you get - **A declarative schema.** Describe tables in code. `Database.open` compares the schema with the database on disk and migrates it. Migrations only add tables, columns and indexes; they never drop anything. - **Live queries.** Write plain SQL, declare which tables (and which slice of them) it reads, and subscribe. The query re-runs only when a committed write touches what it declared, and it emits only when its rows actually changed. - **Sync.** Pull server rows page by page from a cursor, record local edits in an outbox, and push them in batches the server can apply idempotently. You supply two functions that talk to your API; the library never makes HTTP calls itself. - **React bindings** in `declarative-sqlite/react`: `useLiveQuery`, `useDraftField`, `useOutboxCounts`, `useSyncStatus`. ## The three kinds of state An offline-first app has three sources of truth for any value on screen. The library keeps them apart: | State | Where it lives | Who changes it | |---|---|---| | **Server truth** | Tables marked `.synced()` | Only the sync layer: pulls, and the local write done by `outbox.record` | | **Outbox** | The library's own `outbox` table | `sync.outbox.record(...)`; entries settle when the server answers | | **Draft** | Memory, keyed by table, row and column | An input the user is typing in (`useDraftField`) | When a row is read through a live query, pending outbox values are laid over the server value, and a column someone is typing in is held at the draft value. A pull that arrives in the meantime can't overwrite either one. ## When it fits - Browser apps, PWAs and web views that must work offline for long periods. - A backend you control, or can put an adapter in front of, that can serve rows by sequence number and accept column-level changes. The [server protocol](./server-protocol.md) page lists exactly what it must do. ## What it does not do - **Create or delete synced rows from the client.** The push format carries column changes to rows that already exist. New server rows and deletions arrive by pulling. Tables you don't sync (`db.tables.`) have full insert, update and delete. - **Merge conflicts.** The server decides: the last change to arrive wins, per column. Anything the server refuses comes back as a `rejected` outbox entry for the app to show. - **Talk to the network.** You write the transport, so you own auth, URLs and headers. - **Store files.** Keep blobs in your own storage and reference them from rows. ## Layout of the package ``` declarative-sqlite schema, migration, adapters, Database, live queries, sync declarative-sqlite/react SyncProvider and hooks (react is an optional peer dependency) ``` Next: [Getting started](./getting-started.md). # Getting started ## Install ```bash npm install declarative-sqlite ``` The package depends on `@sqlite.org/sqlite-wasm`, which it installs for you. `react` (18 or later) is an optional peer dependency, only needed for `declarative-sqlite/react`. ### Bundler setup (Vite) The SQLite WebAssembly build loads its `.wasm` file at runtime. With Vite, keep it out of dependency pre-bundling so that the file is found: ```ts title="vite.config.ts" export default defineConfig({ optimizeDeps: { exclude: ['@sqlite.org/sqlite-wasm'], }, }); ``` If you serve `sqlite3.wasm` from a folder of your own, pass that folder as `wasmDir` when opening storage (`openAdapter({ name, wasmDir: '/assets' })`). The OPFS backend used here doesn't need cross-origin isolation, so you don't have to set COOP/COEP headers for it. ## A local database The smallest useful program: one table, stored in the browser, read with a live query. ```ts import { SchemaBuilder, openAdapter, Database } from 'declarative-sqlite'; const schema = new SchemaBuilder(); schema.table('note', (t) => { t.text('body').notNull(''); t.date('created_at'); }); const { adapter, backend, warnings } = await openAdapter({ name: 'notes.db' }); warnings.forEach((w) => console.warn(w)); // e.g. "Storage is not persistent" const db = await Database.open({ schema: schema.build(), adapter }); await db.tables.note.insert({ system_id: crypto.randomUUID(), body: 'Hello', created_at: new Date().toISOString(), }); const notes = db.live<{ system_id: string; body: string }>({ sql: 'SELECT system_id, body FROM note ORDER BY created_at DESC', reads: [{ table: 'note' }], key: 'system_id', }); notes.subscribe((rows) => console.log(rows)); ``` Every table gets a `system_id` text column, which is its primary key. You supply the value when inserting; `crypto.randomUUID()` is a good default. ## Adding sync To keep a table in step with a server, mark it `.synced()` and create a sync runtime. The `transport` object is the only piece you write yourself; it calls your API (see [Sync](./sync.md) and [Server protocol](./server-protocol.md)). ```ts import { SchemaBuilder, openAdapter, Database, createSyncRuntime, type SyncTransport, } from 'declarative-sqlite'; const schema = new SchemaBuilder(); schema .table('task', (t) => { t.integer('project_id'); t.text('title'); t.real('hours'); }) .synced({ key: 'system_id', scope: ['project_id'] }); const { adapter } = await openAdapter({ name: 'app.db' }); const db = await Database.open({ schema: schema.build(), adapter }); const transport: SyncTransport = { pullRows: (req) => api.get('/sync/rows', req), push: (batch) => api.post('/sync/push', batch), }; const sync = await createSyncRuntime({ db, transport, deviceId: getInstallId() }); // Fetch the server's rows for one project. await sync.pull.pull('task', { project_id: 42 }); // Edit one. The local row changes immediately; the push goes out ~2 s later. await sync.outbox.record({ table: 'task', systemId: 'a3f1…', changes: { hours: 3.5 }, }); ``` When the app shuts down, close the runtime before the database: ```ts sync.close(); await db.close(); ``` ## Where to go next - [Schema](./schema.md): column types, keys, defaults and synced tables. - [Live queries](./live-queries.md): how invalidation and scopes work. - [Sync](./sync.md): pulling, the outbox, pushing and rejected changes. - [React](./react.md): the provider and hooks. # Schema A schema is built once, in code, with `SchemaBuilder`. `schema.build()` returns a frozen description that `Database.open` migrates the database to. ```ts import { SchemaBuilder } from 'declarative-sqlite'; const schema = new SchemaBuilder(); schema.table('setting', (t) => { t.text('name').notNull(''); t.text('value'); t.key('name').unique(); }); schema .table('task', (t) => { t.integer('project_id').notNull(0); t.text('title').notNull('').maxLength(200); t.real('hours'); t.date('due'); t.key('project_id').index(); }) .synced({ key: 'system_id', scope: ['project_id'] }); export const appSchema = schema.build(); ``` Table and column names are lowercase by convention. The sync layer uppercases them on the wire and lowercases what comes back. ## Column types | Builder | Stored as | Notes | |---|---|---| | `t.text(name)` | `TEXT` | | | `t.integer(name)` | `INTEGER` | Booleans are written as `1` / `0` | | `t.real(name)` | `REAL` | | | `t.date(name)` | `TEXT` | Store ISO 8601 strings | | `t.guid(name)` | `TEXT` | | | `t.blob(name)` | `BLOB` | `Uint8Array` values | Columns are nullable unless you call `.notNull(default)`. The default is required: it's the value existing rows get when a migration adds the column to a table that already holds data. It must match the column type (a number for `integer`/`real`, a string for text types, a `Uint8Array` for `blob`). `.maxLength(n)` is stored in the schema for your forms to read. Neither SQLite nor the library enforces it. ## Keys and indexes `t.key(...columns)` declares a key, then one of: - `.primary()`: the table's primary key. - `.unique(name?)`: a unique index. Named `uq__` if you don't pass a name. - `.index(name?)`: a plain index, the default. Named `idx_
_` if you don't pass a name. If you declare no primary key, the table's key column becomes the primary key: `system_id` for a plain table, or the `.synced()` key for a synced one. ## Columns added for you Every table you declare gets these columns unless you declare them yourself: | Column | Type | Meaning | |---|---|---| | `system_id` | `TEXT NOT NULL DEFAULT ''` | Row id. The primary key unless you declare another | | `system_removed` | `INTEGER NOT NULL DEFAULT 0` | Tombstone flag | | `sync_seq` | `INTEGER NOT NULL DEFAULT 0` | Synced tables only: the server sequence number of the row's last change | The library also adds two tables of its own to every schema, `outbox` and `sync_cursor`. Don't declare tables with those names; `SchemaBuilder` throws a `SchemaError` if you do. ## Synced tables `.synced({ key, scope })` marks a table as a copy of server data: - `key`: the column holding the server's row id. Almost always `system_id`. - `scope`: the columns a pull can filter on, such as `['project_id']`. One pull can filter on at most four of them. Use `[]` if you always pull the whole table. Both columns must be declared (or be `system_id`), or `build()` throws. A synced table is read-only through `db.tables`: its API has only `get`. To change it, record the change in the outbox with `sync.outbox.record`; see [Sync](./sync.md). ### Checking scope columns against the server If your server publishes which columns it accepts as scopes, you can check the schema at startup and fail early instead of on the first pull: ```ts import { validateScopes } from 'declarative-sqlite'; validateScopes(appSchema, { TASK: ['PROJECT_ID'] }); // throws ScopeError on a mismatch ``` ## Errors `SchemaError` is thrown while building, and names the table or column at fault: a table or column declared twice, a reserved table name, a `.notNull` default of the wrong type, or a synced key or scope column that isn't declared. # Storage The database runs on the official SQLite WebAssembly build. Where the bytes are kept is decided by an **adapter**. ## openAdapter `openAdapter` picks the best storage the browser can actually give you: ```ts import { openAdapter } from 'declarative-sqlite'; const { adapter, backend, warnings } = await openAdapter({ name: 'app.db' }); ``` It tries these in order: | Backend | When | Persists across reloads | |---|---|---| | `opfs` | The browser supports OPFS sync access handles: Chrome/Edge 108+, Firefox 111+, Safari 17+ | Yes | | `indexeddb` | OPFS is missing or fails to open (e.g. Safari 16) | Yes, with a caveat (below) | | `memory` | Neither works, e.g. storage is blocked in a private window | **No** | `backend` says where it landed. `warnings` explains every fallback. When the result is `memory`, it includes "Storage is not persistent", so show the user something, since everything they do will be lost on reload. ### Options | Option | Default | Meaning | |---|---|---| | `name` | required | Database file name | | `backend` | `'auto'` | Force `'opfs'`, `'indexeddb'` or `'memory'`. Skips detection, and throws instead of falling back | | `wasmDir` | – | Folder `sqlite3.wasm` is served from, if not the default location | | `opfsTimeoutMs` | `5000` | How long to wait for OPFS before falling back. Some browsers that report OPFS support hang on the first open | ## The IndexedDB caveat SQLite's WebAssembly build has no IndexedDB file system, so the IndexedDB adapter keeps the database in memory and saves a full copy of it to IndexedDB about 250 ms after writes stop. That means: - A crash or killed tab can lose writes from the last ~250 ms. - Each save copies the whole database, so it gets slower as the data grows. Call `flush()` when the page is being hidden to close that window: ```ts import { IndexedDbAdapter } from 'declarative-sqlite'; window.addEventListener('pagehide', () => { if (adapter instanceof IndexedDbAdapter) void adapter.flush(); }); ``` Prefer OPFS whenever it's available; `openAdapter` already does. ## Using an adapter directly The adapter classes are exported if you want to skip detection: ```ts import { OpfsAdapter, IndexedDbAdapter, MemoryAdapter } from 'declarative-sqlite'; const adapter = new OpfsAdapter('app.db'); const test = new MemoryAdapter(); // in-process SQLite, used for tests and Node ``` `MemoryAdapter` works in Node as well as the browser, which makes it the adapter to use in unit tests (see [Testing](./testing.md)). You can also write your own adapter, for example over a native SQLite bridge, by implementing the `SQLiteAdapter` interface: `open`, `close`, `exec`, `all`, `get`, `run`, `isOpen` and `export`. ## Exporting the database Every built-in adapter can serialise the whole database, which is handy for support tickets and debugging: ```ts const bytes: Uint8Array = await adapter.export(); ``` # Reading and writing ## Typing the database The library doesn't infer row types from the schema builder. Describe your rows once and pass them to `Database.open`, together with the names of your synced tables: ```ts import { Database } from 'declarative-sqlite'; interface Rows { setting: { system_id: string; name: string; value: string | null }; task: { system_id: string; project_id: number; title: string; hours: number | null }; } const db = await Database.open({ schema: appSchema, adapter }); db.tables.setting.insert(/* … */); // full CRUD db.tables.task.get(id); // read-only: task is synced ``` ## Queries Reads are plain SQL with positional `?` parameters: ```ts const tasks = await db.query( 'SELECT * FROM task WHERE project_id = ? ORDER BY title', [42], ); const one = await db.queryOne<{ n: number }>('SELECT COUNT(*) AS n FROM task'); ``` Parameter values can be strings, numbers, `null` or `Uint8Array`. Rows come back as plain objects keyed by column name. For data that should stay on screen and update itself, use a [live query](./live-queries.md) instead. ## db.tables Every table in the schema has an entry in `db.tables`, keyed by its row key (`system_id` unless the table is synced with a different key). For tables you own (not synced): ```ts const id = crypto.randomUUID(); await db.tables.setting.insert({ system_id: id, name: 'theme', value: 'dark' }); await db.tables.setting.update(id, { value: 'light' }); // returns rows changed await db.tables.setting.upsert({ system_id: id, name: 'theme', value: 'dark' }); await db.tables.setting.get(id); // row or undefined await db.tables.setting.delete(id); // returns rows changed ``` - You provide `system_id` when inserting. It isn't generated for you. - Keys that aren't columns in the schema are ignored. - `true` and `false` are stored as `1` and `0`; `undefined` as `NULL`. Synced tables expose only `get`. Their write methods don't exist on the object, so a stray `db.tables.task.update(...)` is a type error. Change synced rows with `sync.outbox.record(...)` ([Sync](./sync.md)). ## Transactions Group writes with `db.transaction`. Everything inside commits together or rolls back together: ```ts await db.transaction(async (tx) => { await db.tables.setting.upsert({ system_id: a, name: 'x', value: '1' }); await db.tables.setting.upsert({ system_id: b, name: 'y', value: '2' }); const row = await tx.queryOne('SELECT value FROM setting WHERE system_id = ?', [a]); }); ``` - Any write made while a transaction is open (through `db.tables`, `db.execute`, a nested `db.transaction`, or the sync layer) joins that transaction. - Transactions run one at a time. A second caller waits for the first to finish. - If the callback throws, everything rolls back and nothing is reported to live queries. - `tx.onCommit(fn)` runs `fn` only after the outermost transaction really commits. ## Raw statements `db.execute` runs any statement that returns no rows. Tell it which tables you wrote, so live queries on them re-run: ```ts await db.execute('DELETE FROM setting WHERE value IS NULL', [], { invalidates: ['setting'] }); ``` Inside a transaction, use `tx.execute` the same way, and call `tx.markWritten(table, rowKey, scope)` or `tx.markTableWritten(table)` to report what changed. :::warning Never write a synced table with raw SQL. The sync layer relies on being the only writer of those tables; writing them yourself breaks cursors and the outbox overlay. ::: ## Closing ```ts await db.close(); ``` `close` waits for queued writes to finish, closes every live query, and then closes the adapter. Any later call on the database throws `DatabaseError: Database is closed`. # Live queries A live query is a SQL query that keeps its result current. You declare what it reads; the library re-runs it after any committed write that could affect it and tells your subscribers when the rows changed. ```ts const query = db.live<{ system_id: string; title: string; hours: number | null }>({ sql: 'SELECT system_id, title, hours FROM task WHERE project_id = ? ORDER BY title', params: [42], reads: [{ table: 'task', scope: { project_id: 42 } }], key: 'system_id', }); const unsubscribe = query.subscribe((rows) => render(rows)); query.snapshot(); // the latest rows, synchronously query.hasLoaded; // false until the first run finishes await query.refresh(); // re-run now query.close(); // stop; call when the view goes away ``` ## The spec | Field | Meaning | |---|---| | `sql`, `params` | The query. Any `SELECT`, including joins | | `reads` | Every table the query reads, each with an optional `scope` | | `key` | The column that identifies a row across results, usually `system_id` | | `overlayTable` | Which synced table's pending edits and drafts to apply to the rows. Defaults to the first entry in `reads` | | `minInterval` | Minimum milliseconds between emissions. Rarely needed | ## When a query re-runs A query re-runs after a transaction commits if that transaction wrote one of the tables in `reads`, and: - the entry has no `scope`, or - a written row's scope values match the entry's `scope`, or - the writer couldn't say which rows it touched (for example `db.execute` with `invalidates`). So a query scoped to `{ project_id: 42 }` doesn't re-run when a pull writes rows for project 7. Scopes are matched on the synced table's scope columns; for tables without scope columns, every write to the table re-runs the query. **List every table the query reads**, including joined ones. A table missing from `reads` won't trigger a re-run when it changes. Other guarantees: - **One re-run per transaction.** A pull page of 500 rows is one transaction, so it causes one re-run, not 500. - **Emit only on change.** If the new result has the same rows with the same values in the same order, subscribers aren't called. - **Stable row objects.** Rows that didn't change keep the same object, so a React list keyed by id, or a `memo` component, skips them. - **Nothing half-done.** Writes that roll back never reach a live query. ## Loading versus empty `snapshot()` is `[]` both before the first run and after a first run that found nothing. Use `hasLoaded` to tell them apart: ```ts if (!query.hasLoaded) showSpinner(); else if (query.snapshot().length === 0) showEmptyState(); else render(query.snapshot()); ``` A subscriber that joins after the first run is called straight away with the current rows, even when they're empty. ## Synced tables: what you see When a sync runtime is running, rows from a synced table pass through two steps before a live query emits them: 1. **Outbox overlay.** A column with an unconfirmed edit shows the edited value, even if a pull has written a newer server value in the meantime. 2. **Draft hold.** A column the user is typing in shows the draft value. The overlay and hold apply to the table named by `overlayTable` (or the first `reads` entry). In a query that joins two synced tables, only that one table's columns get them. ## Closing queries Always close queries you no longer need. `db.close()` closes any that are still open. In React, `useLiveQuery` does this for you. # Migrations There are no migration files. Each time `Database.open` runs, it compares your schema with the database on disk and applies the difference, all in one transaction: the migration either completes or leaves the database as it was. ## What happens automatically | Change in your schema | What the migration does | |---|---| | New table | `CREATE TABLE` with its indexes | | New column | `ALTER TABLE … ADD COLUMN`. A `notNull` column gets its default in existing rows | | New or changed index / unique key | Creates it; an index with the same name but different columns or type is dropped and recreated | | Table or column removed from the schema | **Nothing.** Data stays; the plan reports it as extra | Migrations only ever add. An older build of your app that opens a newer database still works, and no user data is dropped because a line was deleted from the schema. ## Changes that rebuild a table SQLite can't change these in place: - a column's storage type (e.g. `text` to `integer`) - a column switching between nullable and `notNull` - the primary key Doing any of these requires copying the table into a new one. By default `Database.open` refuses and throws `MigrationBlockedError`, which lists the tables involved. To allow it: ```ts const db = await Database.open({ schema, adapter, allowRecreate: true }); ``` The rebuild copies every row, including columns the schema no longer declares. When a column becomes `notNull`, existing `NULL`s get the declared default. ## Options ```ts const db = await Database.open({ schema, adapter, migrate: 'auto', // 'auto' (default) | 'plan' | 'off' allowRecreate: false, onMigrationPlan: (plan) => { if (plan.hasOperations) console.info('Migrating', plan.operations.map((o) => o.description)); }, }); ``` - `auto`: migrate on open. - `plan`: work out the operations and call `onMigrationPlan`, but don't run anything. - `off`: skip migration and assume the database already matches. `onMigrationPlan` is called before anything runs. It's a good place to log what your users' databases are doing. ## Planning without opening ```ts import { planMigration } from 'declarative-sqlite'; await adapter.open(); const plan = await planMigration(adapter, schema); plan.diff.extraTables; // tables in the database but not the schema plan.diff.extraColumns; // same, for columns plan.operations; // [{ description, sql: [...] }] ``` # Sync The sync runtime keeps `.synced()` tables in step with your server. It pulls rows, records the user's edits in an outbox, pushes them, and makes sure that neither a pull nor a push ever throws away what the user did. ```ts import { createSyncRuntime } from 'declarative-sqlite'; const sync = await createSyncRuntime({ db, transport, deviceId }); ``` Create it once, right after `Database.open`. Call `sync.close()` before `db.close()`. ## The transport You provide the network part as two functions. What they call is up to you; [Server protocol](./server-protocol.md) describes what the answers must contain. ```ts import type { SyncTransport, RowsPage, PushResult } from 'declarative-sqlite'; // HttpError and authHeaders() stand in for your own code. const transport: SyncTransport = { async pullRows(req) { // req = { table: 'TASK', scope?: 'PROJECT_ID:42', after: 1200, limit?: 500 } const params = new URLSearchParams({ table: req.table, after: String(req.after) }); if (req.scope) params.set('scope', req.scope); if (req.limit) params.set('limit', String(req.limit)); const res = await fetch(`/sync/rows?${params}`, { headers: authHeaders() }); if (!res.ok) throw new HttpError(res.status); return (await res.json()) as RowsPage; }, async push(batch) { const res = await fetch('/sync/push', { method: 'POST', headers: { 'Content-Type': 'application/json', ...authHeaders() }, body: JSON.stringify(batch), }); if (!res.ok) throw new HttpError(res.status); return (await res.json()) as PushResult; }, }; ``` If `pullRows` throws, the pull rejects. If `push` throws, the batch is retried later (see [Pushing](#pushing)). ## Pulling ```ts const report = await sync.pull.pull('task', { project_id: 42 }); // { rows: 37, pages: 1, cursor: 1288 } ``` A pull fetches pages until the server says there are no more, and applies each page in one transaction. The runtime stores a **cursor** per table and scope: the highest sequence number seen so far. The next pull asks only for rows changed after it. `from` controls where a pull starts: | `from` | Starts at | Use it for | |---|---|---| | `'cursor'` (default) | The stored cursor | Normal incremental pulls | | `'window'` | 1000 below the cursor | Pulls triggered by a change notification (see [Ticks](#change-notifications-ticks)) | | `0` | The beginning | A manual full refresh of that scope | Pulling a row you already have is harmless: rows are upserted by id. A pulled row with `removed: true` is deleted locally. Server columns your schema doesn't declare are ignored, so the server can add columns before clients know about them. **A pull never overwrites the user's work:** - A column with an unconfirmed outbox entry keeps the local value. - A column someone is typing in is held. The server value is applied when the draft ends, if the user didn't change the field. - A deletion of a row someone is typing in waits until they're done. ## Recording changes Edits to synced rows go through the outbox: ```ts const groupId = await sync.outbox.record({ table: 'task', systemId: row.system_id, changes: { hours: 3.5, title: 'Install pump' }, }); ``` `record` writes the new values into the local row and adds one outbox entry per column, in a single transaction. Live queries show the new values straight away, and a push is scheduled. The columns in one `record` call form a **change group**: they're always sent in the same push batch. `record` throws `OutboxError`, and records nothing, when: - the table isn't synced (write it through `db.tables` instead), - the row doesn't exist locally, - a column isn't in the schema, - `changes` is empty, or has more than 500 columns. It throws `ValueTooLongError` when a value encodes to more than 4000 JSON characters. :::info Creating and deleting rows The outbox changes columns of rows that already exist. Creating a synced row or deleting one isn't part of the protocol: new and removed rows come from the server through a pull. ::: ## Pushing Pushes happen on their own: about 2 seconds after the last `record`, pending entries are sent in batches of up to 500 changes. A change group is never split across two batches. ```ts await sync.push.pushNow(); // push immediately, e.g. from a "Sync" button ``` Each outbox entry moves through these states: | Status | Meaning | |---|---| | `pending` | Recorded, not sent yet | | `sending` | In a batch waiting for an answer | | `applied` | The server applied it | | `noop` | The server already had that value | | `rejected` | The server refused it; `errorText` says why | The server's answer includes the current state of every row the batch touched, which is written locally. An answer row older than what a pull already brought in (a lower sequence number) is skipped. ### Network failures and retries If `push` throws, the batch goes back to `pending` and is retried **with the same batch id**, so a server that already applied it can just return its stored answer. Retries wait 5 s, then 30 s, then every 2 minutes. Tell the runtime when the connection comes back to retry at once: ```ts window.addEventListener('online', () => sync.push.notifyOnline()); ``` Some errors shouldn't be retried, such as a 400 from a malformed request. Pass `isTerminalError`, and the whole batch is marked `rejected` instead: ```ts const sync = await createSyncRuntime({ db, transport, deviceId, isTerminalError: (error) => error instanceof HttpError && error.status >= 400 && error.status < 500, }); ``` ### Rejected changes A rejected entry stays in the outbox until the user deals with it: ```ts sync.push.onRejected((entry) => toast(`${entry.columnName}: ${entry.errorText}`)); const rejected = await sync.outbox.entries({ status: 'rejected' }); await sync.outbox.retry(rejected[0].id); // back to pending, sent again await sync.outbox.discard(rejected[0].id); // drop it ``` After a rejection the local row still holds the value the user entered. It returns to the server's value when that row is next pulled with newer data. To fetch it now, pull the scope again with `{ from: 0 }`. ### Status ```ts sync.push.status(); // { online: true, sending: false, attempt: 0, nextRetryAt: null, lastError: null } const stop = sync.push.onStatusChange((status) => updateHeader(status)); const counts = await sync.outbox.counts(); // { pending, sending, rejected } ``` In React, use `useSyncStatus()` and `useOutboxCounts()`. ## Change notifications (ticks) If your server can notify clients when a table changes (WebSockets, SignalR, server-sent events), pass those notifications to the runtime: ```ts socket.on('TableChanged', (msg) => { sync.ticks.notify({ table: 'task', seq: msg.seq, scopes: msg.projectIds }); }); ``` Ticks are collected for about 1.5 seconds, then resolved together. For each table, every scope the app currently has open is pulled once, using `from: 'window'`. Scopes are skipped if their cursor is already at or past the tick's `seq`, or if the tick lists `scopes` and doesn't mention them. Tell the runtime which scopes are on screen: ```ts const unregister = sync.pull.registerScope('task', { project_id: 42 }); // when the view closes: unregister(); ``` `sync.ticks.flush()` resolves pending ticks immediately. ## Outbox history Settled entries (`applied`, `noop`) stay in the `outbox` table as history. On startup the runtime deletes settled entries older than `retentionDays` (default 30). Pass `retentionDays: 0` to turn that off and call `sync.outbox.purgeOlderThan(days)` yourself. Rejected entries are never purged automatically. ## Options | Option | Default | Meaning | |---|---|---| | `db` | required | The open `Database` | | `transport` | required | Your `SyncTransport` | | `deviceId` | required | Sent with every push; use a stable id per installation | | `debounceMs` | `2000` | Delay between the last `record` and the push | | `maxChangesPerBatch` | `500` | Changes per push batch (500 is also the maximum) | | `pullWindow` | `1000` | How far `from: 'window'` rewinds | | `pageLimit` | – | Page size sent to `pullRows`. Left out, the server picks | | `tickWindowMs` | `1500` | How long ticks are collected | | `retentionDays` | `30` | Settled outbox history to keep | | `isTerminalError` | every error retries | Which push errors mark a batch rejected instead of retrying | | `clock` | `() => new Date()` | Time source, useful in tests | ## What the runtime contains `createSyncRuntime` returns these services. Most apps only need the first four: | Property | Use | |---|---| | `pull` | `pull()`, `registerScope()` | | `outbox` | `record()`, `entries()`, `counts()`, `retry()`, `discard()`, `purgeOlderThan()` | | `push` | `pushNow()`, `notifyOnline()`, `status()`, `onStatusChange()`, `onRejected()` | | `ticks` | `notify()`, `flush()` | | `drafts` | The draft store behind `useDraftField`: `begin`, `set`, `end`, `endAll` | | `cursors` | Read or reset stored cursors: `get`, `all`, `reset` | | `overlay`, `applier` | Internals, exposed for advanced use and tests | # Server protocol The library never makes HTTP calls. Your `SyncTransport` does, and it must return data in the shapes below. This page is the contract a backend (or an adapter in front of one) has to meet. There are two operations: **pull rows** and **push changes**. ## Naming - Table and column names are **uppercase** on the wire (`TASK`, `PROJECT_ID`) and lowercase locally. The library converts in both directions. - Row ids are strings. They're stored in the synced table's key column (usually `system_id`). - Values are JSON scalars: string, number, boolean or `null`. ## Sequence numbers The server keeps one increasing counter, the **sequence number** (`seq`). Every time a row is created, changed or removed, it's stamped with the next value. The client stores the highest `seq` it has seen per table and scope, and asks for rows above it. Sequence numbers may be handed out in a different order than transactions commit, so a row can commit with a `seq` just below one the client has already seen. The client handles this by re-reading the last 1000 sequence numbers when a change notification arrives (`from: 'window'`). The server doesn't have to do anything special for this. ## Pull rows The library calls `transport.pullRows(request)`: ```ts interface PullRequest { table: string; // 'TASK' scope?: string; // 'PROJECT_ID:42' or 'AREA:N,PROJECT_ID:42' after: number; // return rows with seq > after limit?: number; // page size; absent means use the server default } ``` `scope` is a comma-separated list of `COLUMN:value` pairs: at most 4, sorted by column name, with no commas inside values. Without `scope`, return rows from the whole table. Return one page: ```ts interface RowsPage { table: string; // echo of request.table rows: RowDoc[]; // ordered by seq ascending, at most `limit` rows next: number; // the highest seq in this page, or `after` if the page is empty hasMore: boolean; // true if more rows exist above `next` } interface RowDoc { id: string; // the row id seq: number; // the row's current sequence number removed: boolean; // true if the row was deleted data: Record; // column values, uppercase keys; may be {} when removed } ``` The server must: 1. Return only rows with `seq > after` that match every scope pair. 2. Order them by `seq`, and set `next` to the last one's `seq`. 3. Include **deleted rows** as `removed: true` rather than leaving them out, or clients will never learn they're gone. The client keeps calling with `after = next` while `hasMore` is true (up to 100 pages per pull). Example: ```json { "table": "TASK", "rows": [ { "id": "a3f1…", "seq": 1287, "removed": false, "data": { "PROJECT_ID": 42, "TITLE": "Install pump", "HOURS": 3 } }, { "id": "9c02…", "seq": 1288, "removed": true, "data": {} } ], "next": 1288, "hasMore": false } ``` ## Push changes The library calls `transport.push(batch)`: ```ts interface PushBatch { batchId: string; // UUID, 36 characters deviceId: string; // the deviceId given to createSyncRuntime changes: PushChange[]; // 1 to 500 } interface PushChange { table: string; // 'TASK' id: string; // row id column: string; // 'HOURS' old: unknown; // the value the device had before the edit (informational) new: unknown; // the new value changedAt: string; // ISO timestamp of the edit (informational) } ``` Encoded as JSON, each `old` and `new` value is at most 4000 characters; the client refuses to record anything longer. Return: ```ts interface PushResult { batchId: string; results: Array<{ index: number; // position in batch.changes result: 'applied' | 'noop' | 'rejected'; error?: string | null; // shown to the user when rejected }>; rows: RowDoc[]; // current state of every row the batch changed, with new seq } ``` The server must: 1. **Apply changes in order.** When two changes target the same column, the last one to arrive wins, whichever device it came from. `old` is for your logs; don't use it to refuse a change. 2. **Answer every change.** Use `noop` if the column already had that value, `rejected` with an `error` if validation fails or the row doesn't exist. A change missing from `results` is sent again in a later batch. 3. **Be idempotent on `batchId`.** Store the answer. If the same `batchId` arrives again, return the stored answer without applying anything. The client re-sends a batch with the same id after a network error, when it can't know whether the first attempt went through. 4. **Bump `seq`** on every row it changes, and return those rows in `rows`. The client writes them locally but skips any row whose `seq` isn't newer than what it already has. A change group (all columns of one `record` call) always arrives in one batch. Applying a batch in one database transaction gives the user all-or-nothing edits per row. ### Errors - Throwing from `push` (network down, 5xx, timeout) means "no answer". The batch is retried with the same `batchId`. - If `isTerminalError(error)` returns true for what you threw, every change in the batch is marked `rejected` instead. Use it for errors a retry can't fix, like a 400. - Per-change failures belong in `results`, not in a thrown error. ## Change notifications (optional) To let clients pick up other devices' changes without polling, notify them when a table changes, carrying the table's newest `seq` and, if you can, the scope values that changed. The app passes each message to `sync.ticks.notify`: ```ts sync.ticks.notify({ table: 'task', seq: 1290, scopes: [42] }); ``` `table` here is the **local** (lowercase) name. `scopes` is a list of values; a scope the app has open is pulled if any of its values is in the list. ## Testing against the contract `FakeTransport`, exported from the package, is an in-memory server that follows this contract. Use it to test your app, and as a reference when building the real server. See [Testing](./testing.md). # React `declarative-sqlite/react` is a small layer over the core library. It holds no state of its own: queries, drafts and the outbox live in the library, so a component unmounting never loses anything. Requires React 18 or later. ## Setup Open the database and the sync runtime once, outside React, then provide them: ```tsx import { SyncProvider } from 'declarative-sqlite/react'; const db = await Database.open({ schema, adapter }); const sync = await createSyncRuntime({ db, transport, deviceId }); root.render( , ); ``` `SyncProvider` ends every open draft (saving what the user typed) when the page is hidden (`pagehide`, or `visibilitychange` to hidden), when the provider unmounts, and when `routeKey` changes. Pass something that changes on navigation, such as the router's pathname. `useDatabase()` and `useSyncRuntime()` return the two objects anywhere below the provider. ## useLiveQuery ```tsx import { useLiveQuery } from 'declarative-sqlite/react'; function TaskList({ projectId }: { projectId: number }) { const tasks = useLiveQuery({ sql: 'SELECT * FROM task WHERE project_id = ? ORDER BY title', params: [projectId], reads: [{ table: 'task', scope: { project_id: projectId } }], key: 'system_id', }); return
    {tasks.map((t) => )}
; } ``` The query is created when the component mounts, closed when it unmounts, and recreated only when the SQL, params, `reads` or `key` change. You can pass a new object literal on every render. Unchanged rows keep their identity, so `React.memo` on the row component works. To tell "loading" from "empty", use `useLiveQueryState`: ```tsx const { rows, hasLoaded } = useLiveQueryState(spec); if (!hasLoaded) return ; if (rows.length === 0) return

No tasks

; ``` ## useDraftField This is how you bind an input to a column of a synced row: ```tsx import { useDraftField } from 'declarative-sqlite/react'; function TitleInput({ task }: { task: Rows['task'] }) { const field = useDraftField('task', task.system_id, 'title', task.title); return ( ); } ``` - Pass the value from your live query as the last argument. - **Focus** starts a draft. Each **keystroke** updates it. While the draft is open, pulls can't change the field. - **Blur** or **Enter** ends it: a changed value is recorded in the outbox; an unchanged one is released, and any server value that arrived meanwhile is applied. - **Escape** reverts to the value at focus and releases the field. - `isDrafting` is true while editing; `isPending` is true while the column has an unconfirmed outbox entry. Don't keep a `useState` copy of the value next to it. The draft lives in the library so it survives the row re-rendering or unmounting (for example in a virtualised list). ### Non-text values Passed a change event, `onChange` stores `event.target.value`, which is a string. For a numeric column, convert it yourself, or the value is recorded as a string: ```tsx const field = useDraftField('task', task.system_id, 'hours', task.hours); field.onChange(e.target.value === '' ? null : Number(e.target.value))} onBlur={field.onBlur} onKeyDown={field.onKeyDown} /> ``` `onChange` also accepts the value directly, which suits checkboxes, selects and custom controls. ## useOutboxCounts and useSyncStatus ```tsx import { useOutboxCounts, useSyncStatus } from 'declarative-sqlite/react'; function SyncBadge() { const { pending, sending, rejected } = useOutboxCounts(); const status = useSyncStatus(); // { online, sending, attempt, nextRetryAt, lastError } if (!status.online) return Offline · {pending} waiting; if (rejected > 0) return {rejected} change(s) refused; return {pending + sending === 0 ? 'Saved' : 'Saving…'}; } ``` ## Registering visible scopes For [change notifications](./sync.md#change-notifications-ticks) to pull the right data, register the scopes a screen shows: ```tsx function ProjectScreen({ projectId }: { projectId: number }) { const sync = useSyncRuntime(); useEffect(() => { void sync.pull.pull('task', { project_id: projectId }); return sync.pull.registerScope('task', { project_id: projectId }); }, [sync, projectId]); // … } ``` # Testing Everything in the library runs in memory against a real SQLite engine, so sync behaviour can be tested with ordinary unit tests: no browser, no server. - `MemoryAdapter`: an in-memory SQLite database. Works in Node. - `FakeTransport`: a scripted server that follows the [server protocol](./server-protocol.md). ```ts import { describe, it, expect } from 'vitest'; import { Database, MemoryAdapter, FakeTransport, createSyncRuntime } from 'declarative-sqlite'; import { appSchema } from '../src/schema'; describe('task sync', () => { it('keeps the user edit when another device changes the same row', async () => { const db = await Database.open({ schema: appSchema, adapter: new MemoryAdapter() }); const server = new FakeTransport(); server.seed('TASK', [{ id: 'A', data: { PROJECT_ID: 42, TITLE: 'Pump', HOURS: 1 } }]); const sync = await createSyncRuntime({ db, transport: server, deviceId: 'test', debounceMs: 0 }); await sync.pull.pull('task', { project_id: 42 }); await sync.outbox.record({ table: 'task', systemId: 'A', changes: { hours: 5 } }); server.serverEdit('TASK', 'A', { TITLE: 'Pump (renamed)' }); await sync.pull.pull('task', { project_id: 42 }); const row = await db.tables.task.get('A'); expect(row).toMatchObject({ hours: 5, title: 'Pump (renamed)' }); await sync.push.pushNow(); expect(await sync.outbox.counts()).toMatchObject({ pending: 0, rejected: 0 }); sync.close(); await db.close(); }); }); ``` Table and column names passed to `FakeTransport` are the uppercase wire names. ## FakeTransport | Method | Simulates | |---|---| | `seed(table, rows)` | Rows that already exist on the server. Each gets the next `seq` | | `serverEdit(table, id, data)` | Another device changing a row | | `tombstone(table, id)` | A row deleted on the server | | `reject(table, column, error)` | The server refusing every change to that column | | `failNextPush(error?)` | A network failure on the next push | | `pushes`, `pulls` | Every batch and request received, for assertions | `new FakeTransport({ pageSize: 2 })` forces small pages to test paging. It behaves like a real server: changes apply in order with the last one winning, replaying a `batchId` returns the stored answer, and a change to a missing or deleted row is rejected. ## Tips - Set `debounceMs: 0` or call `sync.push.pushNow()` so tests don't wait for the push timer. - Pass `clock` to `createSyncRuntime` for deterministic timestamps. - Close the runtime and the database at the end of each test. # Upgrading from v2 v3 is a rewrite, not an incremental release. The schema builder keeps its shape; almost everything else is new. Plan the upgrade as a port. | v2 | v3 | |---|---| | `DeclarativeDatabase` | `Database.open({ schema, adapter })` | | `AdapterFactory` | `openAdapter({ name, wasmDir })` | | `SchemaBuilder` | Same fluent API. Drop `.lww()`, add `.synced({ key, scope })` to server tables | | `.lww()`, `__hlc` columns, `Hlc` | Gone. The server decides: last change to arrive wins | | `dirtyRowStore` | The built-in `outbox` table | | `db.update(...)` on a server table | `sync.outbox.record({ table, systemId, changes })` | | `db.stream(...)`, RxJS streams | `db.live(spec)` / `useLiveQuery(spec)` | | `bulkLoad(...)` | `sync.pull.pull(table, scope)` | | Push / debounced sync | Automatic after `record`; `sync.push.pushNow()` to force | | Realtime change handler | `sync.ticks.notify({ table, seq, scopes })` | | File management (`fileset`) | Removed from the package | ## Steps 1. Update the schema: remove `.lww()`, mark server-owned tables `.synced()`, and give every `notNull` column a default. 2. Replace database setup with `openAdapter` + `Database.open` + `createSyncRuntime`. 3. Write a `SyncTransport` for your API ([Server protocol](./server-protocol.md)). The server must provide sequence numbers and batch idempotency. 4. Replace writes to synced tables with `sync.outbox.record`. The type checker will point at every call site: synced tables have no write methods on `db.tables`. 5. Replace streams with live queries, and form inputs with `useDraftField`. ## Existing data v3 uses its own tables (`outbox`, `sync_cursor`) and the `sync_seq` column. The simplest safe upgrade is a fresh database file under a new name, filled by a full pull. Before switching, push any unsent v2 changes, or they'll be lost. ## Not in v3 - Creating or deleting server rows from the client. The push format carries column changes to existing rows only. - File storage. - Client-side conflict resolution (HLC / LWW). The full, app-specific migration notes are in [MIGRATION-v2-to-v3.md](https://github.com/graknol/declarative_sqlite/blob/main/packages/core/MIGRATION-v2-to-v3.md). # For AI agents These docs are published in forms meant for LLMs and coding agents: | URL | Contents | |---|---| | [`/llms.txt`](pathname:///llms.txt) | Index of every page, with one-line summaries and links to Markdown | | [`/llms-full.txt`](pathname:///llms-full.txt) | Every page as a single Markdown file | | `/docs/.md` | Any single page as raw Markdown, e.g. [`/docs/sync.md`](pathname:///docs/sync.md) | To give an agent the whole library in one go, point it at `https://declarative-sqlite.linden.no/llms-full.txt`. The package also ships TypeScript declarations (`dist/*.d.ts`). When these docs and the types disagree, the types are right. ## Rules for writing code with this library These are the mistakes that compile but break at runtime or corrupt sync state. Follow them in generated code. 1. **Install v3**: `npm install declarative-sqlite`. `latest` is v3; if you need the old API, pin a `2.x` version explicitly. 2. **Never write a `.synced()` table directly.** Not with `db.tables`, and not with `db.execute` or `tx.execute`. Use `sync.outbox.record({ table, systemId, changes })`. 3. **`outbox.record` only changes existing rows.** It can't create or delete synced rows; those come from the server by pulling. Don't write workarounds that insert into synced tables. 4. **Supply `system_id` on insert** into a plain table (`crypto.randomUUID()`). It isn't generated. 5. **Give every `.notNull()` a default of the column's type**: `t.integer('n').notNull(0)`, `t.text('s').notNull('')`. 6. **Declare every table a live query reads** in `reads`, joined tables included, and set `key` to the row id column (usually `system_id`). 7. **Close what you open**: `query.close()` for live queries you created, `sync.close()` before `await db.close()`. 8. **Create one database and one sync runtime per app**, outside React components, and hand them to ``. 9. **Use `useDraftField` for editable inputs bound to synced columns**, not `useState` plus `record`. Convert numbers in `onChange`; the event value is a string. 10. **Use lowercase names locally, uppercase on the wire.** `sync.pull.pull('task', { project_id: 42 })` and `sync.ticks.notify({ table: 'task', … })` take local names. `FakeTransport.seed('TASK', …)` and everything a `SyncTransport` sends or receives use wire names. 11. **Don't declare tables named `outbox` or `sync_cursor`, or recreate the `system_id`, `system_removed` or `sync_seq` columns** with other types. 12. **Throw from the transport on network failure.** Don't return an empty result: a thrown `push` is retried with the same batch id, while an invented answer is filed as the server's verdict. 13. **Test with `MemoryAdapter` and `FakeTransport`**, not mocks of the library. ## Minimal complete example ```ts import { SchemaBuilder, openAdapter, Database, createSyncRuntime, type SyncTransport } from 'declarative-sqlite'; interface Rows { task: { system_id: string; project_id: number; title: string; hours: number | null }; } const schema = new SchemaBuilder(); schema .table('task', (t) => { t.integer('project_id').notNull(0); t.text('title').notNull(''); t.real('hours'); }) .synced({ key: 'system_id', scope: ['project_id'] }); export async function start(transport: SyncTransport, deviceId: string) { const { adapter, warnings } = await openAdapter({ name: 'app.db' }); warnings.forEach((w) => console.warn(w)); const db = await Database.open({ schema: schema.build(), adapter }); const sync = await createSyncRuntime({ db, transport, deviceId }); await sync.pull.pull('task', { project_id: 42 }); const tasks = db.live({ sql: 'SELECT * FROM task WHERE project_id = ? ORDER BY title', params: [42], reads: [{ table: 'task', scope: { project_id: 42 } }], key: 'system_id', }); tasks.subscribe((rows) => console.log(rows)); const first = (await db.queryOne('SELECT * FROM task LIMIT 1'))!; await sync.outbox.record({ table: 'task', systemId: first.system_id, changes: { hours: 2 } }); return async () => { tasks.close(); sync.close(); await db.close(); }; } ```