website·Indexed 31 days ago
https://declarative-sqlite.linden.no/A TypeScript library for offline-first browser apps that runs SQLite (WebAssembly, OPFS) with a declarative schema. It features live queries, automatic migrations, a sync runtime for server synchronization, and a dedicated React layer via declarative-sqlite/react.
declarative-sqlite does not use migration files. Instead, Database.open compares the current schema with the database on disk and applies differences in a single transaction.
Automatic behaviors include:
CREATE TABLE including indexes.ALTER TABLE … ADD COLUMN. If the column is notNull, existing rows receive the default value.When the server notifies the client of a change (via WebSockets, SSE, etc.), pass the notification to the runtime. The runtime will pull the affected scopes using from: 'window'.
To optimize, register which scopes are currently active/on-screen so the runtime knows which ones to pull.
// Pass server notification to runtime
socket.on('TableChanged', (msg) => {
sync.ticks.notify({ table: 'task', seq: msg.seq, scopes: msg.projectIds });
});
// Register an active scope
const unregister = sync.pull.registerScope('task', { project_id: 42 });
// Call unregister() when the view closes
// Force immediate resolution of pending ticks
sync.ticks.flush();The openAdapter function automatically selects the best available storage backend based on browser support. It attempts to use backends in the following priority order:
The returned backend string indicates which storage was selected, and warnings provides details on any fallbacks that occurred.
import { openAdapter } from 'declarative-sqlite';
const { adapter, backend, warnings } = await openAdapter({ name: 'app.db' });MemoryAdapter for an in-memory SQLite database (Node.js) and FakeTransport to simulate a server following the server protocol.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 () => {
// 1. Setup in-memory DB and fake server
const db = await Database.open({ schema: appSchema, adapter: new MemoryAdapter() });
const server = new FakeTransport();
// Seed server with initial data (use uppercase wire names for tables/columns)
server.seed('TASK', [{ id: 'A', data: { PROJECT_ID: 42, TITLE: 'Pump', HOURS: 1 } }]);
// 2. Initialize sync runtime
const sync = await createSyncRuntime({ db, transport: server, deviceId: 'test', debounceMs: 0 });
await sync.pull.pull('task', { project_id: 42 });
// 3. Simulate local change and remote server edit
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 });
// 4. Assert results
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 });
// 5. Cleanup
sync.close();
await db.close();
});
});Follow these steps to port your application to v3:
.lww(), mark server-owned tables with .synced(), and ensure every notNull column has a default value.openAdapter, Database.open, and createSyncRuntime.SyncTransport for your API. The server must support sequence numbers and batch idempotency.sync.outbox.record. Note that synced tables no longer have write methods on db.tables.useDraftField for form inputs.npm install declarative-sqliteTo avoid runtime errors and sync state corruption, follow these constraints:
npm install declarative-sqlite for v3. Pin to 2.x if the old API is required..synced() table directly via db.tables or execute. Use sync.outbox.record({ table, systemId, changes }) to modify existing rows. Note that outbox.record cannot create or delete rows; these must come from the server via pulling.system_id (e.g., crypto.randomUUID()) when inserting into plain tables, as it is not auto-generated..notNull() column must have a default value matching its type (e.g., t.integer('n').notNull(0) or t.text('s').notNull('')).reads configuration of a live query, declare every table being read (including joined tables) and set the key to the row ID column (typically system_id).query.close() for live queries and sync.close() before calling await db.close().<SyncProvider>.useDraftField for editable inputs bound to synced columns instead of useState + record. Convert string event values to numbers during onChange.sync.pull.pull('task', ...)). Use uppercase names for wire/transport operations (e.g., FakeTransport.seed('TASK', ...)).outbox or sync_cursor. Do not redefine system_id, system_removed, or sync_seq columns with different types.push operations are retried with the same batch ID.MemoryAdapter and FakeTransport instead of mocking the library.Edits to synced rows must be recorded via the outbox. This writes new values to the local row and creates outbox entries in one transaction. Columns provided in a single record call form a change group and are always pushed in the same batch.
Constraints:
changes cannot be empty or exceed 500 columns.ValueTooLongError).Note: Creating or deleting rows is not handled by the outbox; these operations must come from the server via a pull.
const groupId = await sync.outbox.record({
table: 'task',
systemId: row.system_id,
changes: { hours: 3.5, title: 'Install pump' },
});planMigration function. This allows you to identify extra tables or columns and see the SQL operations that would be performed.import { planMigration } from 'declarative-sqlite';
await adapter.open();
const plan = await planMigration(adapter, schema);
console.log(plan.diff.extraTables); // tables in the database but not the schema
console.log(plan.diff.extraColumns); // columns in the database but not the schema
console.log(plan.operations); // Array of { description, sql: [...] }declarative-sqlite/react, open the database and sync runtime outside of the React component tree and provide them via the SyncProvider. The SyncProvider manages the lifecycle of open drafts, automatically saving them when the page is hidden, the provider unmounts, or the routeKey changes. Use useDatabase() and useSyncRuntime() hooks to access these objects in child components.import { SyncProvider } from 'declarative-sqlite/react';
// Initialize outside React
const db = await Database.open<Rows, 'task'>({ schema, adapter });
const sync = await createSyncRuntime({ db, transport, deviceId });
root.render(
<SyncProvider db={db} sync={sync} routeKey={location.pathname}>
<App />
</SyncProvider>,
);The server must maintain a single increasing counter called the sequence number (seq).
seq value.seq greater than the highest one it has already seen for a specific table and scope.from: 'window'). The server does not need special logic to handle this; it simply fulfills the request for rows above the provided after value.