#SQLite engine internals

@silt-db/sqlite is the engine beneath the MongoDB-compatible application API. This page is for maintainers, adapter authors, and applications that need custom SQLite configuration. Ordinary application code should start with MongoClient.

The lower-level collection API is intentionally separate: it has reusable cursors, callback-scoped transaction handles, and atomic batches. These are not the Mongo driver contracts. The shared query semantics 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/server';

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

The /server entry executes SQL in workers and verifies WAL support. Ordinary Node entries use one connection on the calling thread. See server configuration for pool options, ownership, memory costs, and cursor limits.

#Open a database

These alternatives use the ordinary Node entry. Use the corresponding Expo or better-sqlite3 entry when supplying that platform's native connection.

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

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 the ordinary Node, better-sqlite3, and Expo entries. The server worker entry owns its connections and does not accept native handles. 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

Use the scoped transaction handle for every operation inside a callback. 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.

#Transaction handles

await db.transaction(async (tx) => {
  await tx.collection('notes').updateOne({ _id: 'one' }, { $set: { saved: true } });
  await tx.sql.run('INSERT INTO audit (event) VALUES (?)', ['note saved']);
});

The example assumes the application has created its audit table. The transaction owns the connection through every await; unrelated operations wait. Use tx for collections and SQL within it. Nested tx.transaction() calls use savepoints. Callback failure rolls back; scoped handles expire after completion. The Mongo facade translates its session API into these engine transactions.

#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.

Buffered reads finish the statement before returning. Single-connection iterators execute the query once into SQLite TEMP storage, then fetch bounded pages with no live source statement between application awaits. Server iterators lease a separate WAL reader and keep one snapshot; idle pooled cursors expire. Writes can run between yielded rows. Early exit, errors, and database closure release resources, including cursors on a borrowed driver. Repeated core iterations execute new queries. See server cursor resources.

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.

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.

#Managed SQL

db.sql.get/all/run/exec use the writer queue and preserve bound SQLite values, including Uint8Array BLOBs. db.sql.iterate provides safe single-query iteration. db.sql.read.get/all/iterate accepts one SELECT, SELECT CTE, or VALUES query and may use a pooled reader; result columns should have explicit aliases.

db.sql.transaction(async sql => ...) passes a scoped facade and supports nesting. For combined document and SQL work, use db.transaction(async tx => ...) with tx.collection() and tx.sql. All scoped reads, including tx.sql.read, use that transaction. Manual transaction-control SQL is rejected. The facade checks closure and read-only access and does not expose an unmanaged native connection.

cursorBatchSize sets the portable spool page size (default 128, from 1 to 65,536). Storage stays in SQLite; toArray() still allocates its requested result. See the server guide for worker settings and examples.

#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.