#Database API

@silt-db/sqlite provides persistent collections with Mongo-shaped queries, updates, and aggregation. Start with Quick start for a small application. This reference describes initialization and the complete collection surface; accepted operators are listed in Compatibility.

#Choose an entry point

The root export selects Expo under browser and react-native conditions, Node under node, and the portable entry otherwise. Use explicit subpaths if your bundler has unusual export-condition settings. The Expo and portable import graphs contain no Node built-ins or Node addons. Optional peers are not installed automatically.

For a Node server, use better-sqlite3 with WAL when a native dependency fits your deployment:

import { openDatabase } from '@silt-db/sqlite/better-sqlite3';

const db = await openDatabase('./data.sqlite');
const notes = db.collection('notes');
await notes.insertOne({ _id: 'note-1', text: 'Saved in SQLite' });

Both Node adapters execute SQLite synchronously on the calling thread. The async API gives applications a common interface and schedules transactions; it does not move Node queries to worker threads.

#Open a database

await openDatabase(); // silt.sqlite
await openDatabase('app.sqlite');
await openDatabase('app.sqlite', { wal: true });
await openDatabase({ filename: 'app.sqlite', timeout: 5000 });
await openDatabase({ connection: nativeConnection });
await openDatabase({ driver: customDriver });

':memory:' creates an ephemeral test database on Node. openDatabase() always returns a promise for an AsyncDatabase. It probes for SQLite JSON functions/operators, materialized CTEs, math and window functions, and FULL JOIN, rejecting an incompatible engine. SQLite 3.39+ with those capabilities is required. The tested platform versions and their limits are recorded in Testing and Expo.

Owned writable Node connections use WAL and synchronous=FULL. The default configuration also enables foreign keys, requests file-backed temporary storage, and sets the busy timeout and page-cache target. wal: true on a supplied connection also requests synchronous=FULL. The actual journal mode still depends on the SQLite VFS; the validated Expo browser VFS used DELETE mode. A page-cache target is not a hard process memory cap.

#Native connections and ownership

import BetterSqlite3 from 'better-sqlite3';
import { openDatabase } from '@silt-db/sqlite/better-sqlite3';

const connection = new BetterSqlite3('existing.sqlite');
connection.pragma('journal_mode = WAL');
connection.pragma('synchronous = FULL');

const db = await openDatabase({ connection });
await db.collection('notes').insertOne({ _id: 'one', text: 'Hello' });
await db.close(); // Closes this wrapper; the caller's connection stays open.
connection.close(); // The caller owns the native connection.

openDatabase(connection) is also accepted by each platform entry. A supplied connection keeps its PRAGMAs unless you opt into configuration. Silt still creates collection tables and metadata as operations require them.

While Silt uses a handle, do not close it, issue raw statements, or run an independent transaction manager concurrently. Multiple Silt wrappers for one native handle share a queue. Node handles for the same file also share an in-process queue by normalized real path, so asynchronous transaction gaps cannot be blocked by another handle's synchronous busy waiting. Separate processes use SQLite's ordinary file locking; unrelated files have independent queues.

#Database methods and properties

Read Transactions before mixing asynchronous operations. Calling an outer database handle from inside its transaction callback queues behind that transaction; awaiting it would deadlock. Scoped handles expire after their callback finishes.

#Collection reads

Find options are sort, skip, limit, and projection. Sort directions are 1 or -1; skip and limit are nonnegative safe integers. A cursor limit of zero means no limit, unlike a pipeline $limit stage, which requires a positive value.

Aggregation options additionally accept variables: a map of JSON values referenced as $$name. Supplied cursor sort, skip, limit, and projection are appended after the pipeline, in that order. Array and object sort behavior has documented limits.

Read-only access treats a missing collection, including a missing lookup source, as an empty statement-local SQL relation. It creates no tables and checks again on each execution, so a collection handle can see data created later by another writer.

#Cursors

const cursor = db
  .collection('orders')
  .find({ paid: true })
  .sort({ amount: -1, _id: 1 })
  .limit(20)
  .project({ customerId: 1, amount: 1 });

for await (const order of cursor) {
  console.log(order);
}

const plan = await cursor.explain();
console.log(plan.sql, plan.params, plan.queryPlan);

Execution discovers all compiler-referenced collections, including foreign lookup sources, loads current index metadata, and recompiles. Therefore .compile() can precede an index-aware execution plan; use awaited .explain() for current details.

Iterator steps lease the connection individually, so a paused loop can await other work. Breaking iteration releases its statement. Better-sqlite3 rejects writes while its statement is active; finish the cursor before writing on that connection. Closing the database finalizes active iterators. Repeated iterations do not pin a shared snapshot.

Streaming avoids collecting the whole result in JavaScript. SQLite still constructs lookup arrays, facets, and grouped arrays; one result document can be large.

#Collection writes

All these methods return promises. Input documents are not mutated. A missing _id becomes a UUID string; _id accepts JSON scalars and valid embedded objects, rejects arrays and dollar-prefixed object keys, and is immutable. Strings give the most efficient ID path. See JSON storage for key handling and migrations.

Updates accept upsert and arrayFilters; replacement accepts only upsert. Refer to Updates for operators and pipeline forms. Multi-document insertion and updates are atomic across one Silt operation, which differs from MongoDB's ordinary batch behavior.

Core collections expose createIndex and collection drop, but do not currently expose individual listIndexes or dropIndex methods. The ShareDB adapter has those separate adapter APIs. Secondary indexes are nonunique; unsupported options such as unique: true, sparse, partial, text, or geospatial indexes are rejected.

#Write results

Updates and replacements return:

interface UpdateResult {
  acknowledged: true;
  matchedCount: number;
  modifiedCount: number;
  upsertedCount: number;
  upsertedId: JSONValue | null;
}

An upsert insertion has matchedCount: 0, modifiedCount: 0, upsertedCount: 1, and its inserted ID. An update without insertion has upsertedCount: 0 and upsertedId: null. modifiedCount counts final document differences; the update reference documents a narrow MongoDB pipeline-count difference.

#Custom SQLite drivers

Use the portable entry when the application already has an async SQLite abstraction:

import { openDatabase } from '@silt-db/sqlite/portable';

const db = await openDatabase({ driver, closeDriver: false });

The required driver contract is:

type BindValue = null | number | bigint | string | Uint8Array;
type BindParameters = BindValue[] | Record<string, BindValue>;
type Row = Record<string, unknown>;

interface SQLiteDriver {
  exec(sql: string): Promise<void>;
  get(sql: string, params?: BindParameters): Promise<Row | null | undefined>;
  all(sql: string, params?: BindParameters): Promise<Row[]>;
  run(
    sql: string,
    params?: BindParameters,
  ): Promise<{
    changes: number | bigint;
    lastInsertRowid: number | bigint;
  }>;
  iterate(sql: string, params?: BindParameters): AsyncIterable<Row>;
  transaction<T>(fn: (tx: SQLiteDriver) => Promise<T> | T): Promise<T>;
  close(): Promise<void>;
  nativeRegex?: boolean;
  capabilities?: Record<string, unknown>;
  connection?: unknown;
  ownsConnection?: boolean;
}

Bindings accept positional arrays or named objects, without mixing both in a statement. Missing parameters fail explicitly. Native adapters normalize parameter names and omit unused compiler bindings; values remain SQL bindings. Driver binding types include bigint and byte arrays for SQL plumbing, but stored documents still accept only the JSON data domain.

A custom driver must isolate transactions across asynchronous gaps, serialize concurrent access, support nested transaction scopes, and finalize statements when an iterator ends early. Silt does not wrap injected drivers in another transaction manager. The callback's tx driver must be used for every statement within its transaction.

Native helpers are available at /drivers/node, /drivers/better-sqlite3, and /drivers/expo, exporting adaptNodeConnection, adaptBetterSqlite3Connection, and adaptExpoConnection, respectively, plus corresponding create*Driver factories. Adapted handles default to caller ownership; ownsConnection: true explicitly transfers close responsibility to the adapter.

#Synchronous Node API

import { SQLiteDatabase } from '@silt-db/sqlite/node';

const db = new SQLiteDatabase(':memory:');
const notes = db.collection('notes');
notes.insertOne({ _id: 'one', revision: 0 });
db.transaction(() => {
  notes.updateOne({ _id: 'one' }, { $inc: { revision: 1 } });
});
for (const note of notes.find()) console.log(note);
db.close();

SQLiteDatabase is also exported as Database and as the Node default export. Its Collection and Cursor expose the same operations synchronously; cursor iteration uses for...of, and [Symbol.dispose]() closes the database. The constructor accepts a filename or node:sqlite DatabaseSync handle, with the same ownership/configuration principles.

Synchronous transaction callbacks take no scoped argument, must return synchronously, and use nested savepoints. Promises are rejected. The synchronous .driver supports async integrations such as ShareDB, but synchronous collection methods bypass async scheduling: do not interleave them with an active async transaction.