#SQL compiler API

@silt-db/compiler translates queries, expressions, updates, and pipelines into SQLite SQL. It opens no connections and imports no Node APIs or JavaScript document evaluator. Most applications should use the database API, which supplies persistence, capability checks, indexes, transactions, and decoding.

Use the compiler directly when integrating an existing SQL storage schema, implementing another adapter, or inspecting generated queries independently.

#Compile a query

import { compileFind, compileAggregate } from '@silt-db/compiler';

const find = compileFind(
  'orders',
  { paid: true },
  {
    sort: { amount: -1 },
    limit: 20,
    projection: { customerId: 1, amount: 1 },
  },
);

const aggregate = compileAggregate('orders', [
  { $match: { paid: true } },
  { $group: { _id: '$customerId', total: { $sum: '$amount' } } },
  { $sort: { total: -1 } },
]);

// Execute either result's sql with its params on a compatible SQLite connection.
// Each returned row contains JSON text in doc and an ordering column ord.

Both functions return { sql, params }. params is a named binding object; never substitute its values into SQL strings yourself. Returned documents are JSON text, so an adapter decodes row.doc with JSON.parse. SQL NULL represents a missing value internally, while the text null represents explicit JSON null.

#Public compilation functions

Skip/limit are nonnegative safe integers for compileFind; limit zero means unbounded. compileAggregate accepts a stage array and uses the pipeline contract, including positive $limit. Unlike a storage cursor, standalone aggregation compilation has no sort, skip, limit, or projection options: put those operations in its stages.

Aggregation variables bind JSON constants under names such as minimum; expressions reference them as $$minimum:

const query = compileAggregate(
  'orders',
  [{ $match: { $expr: { $gte: ['$amount', '$$minimum'] } } }],
  { variables: { minimum: 20 } },
);

#Map an existing schema

The default physical table is tableName(collection). A source relation needs JSON TEXT doc and stable numeric seq columns. The compiler carries seq into the pipeline as ord, preserving order through transformations.

const tables = { orders: 'app_orders', customers: 'app_customers' };

const query = compileAggregate(
  'orders',
  [
    {
      $lookup: {
        from: 'customers',
        localField: 'customerId',
        foreignField: '_id',
        as: 'customer',
      },
    },
  ],
  {
    resolveCollection(name) {
      const table = tables[name];
      if (!table) throw new Error(`Unknown collection: ${name}`);
      return table;
    },
  },
);

resolveCollection returns a physical table name, which the compiler quotes as an identifier. It does not accept arbitrary SQL. It is also called for foreign collections and nested pipelines. The standalone compiler does not create absent tables or substitute empty relations; a storage adapter must manage that behavior.

Resolvers describe trusted adapter state. Do not accept physical schema/index definitions or capability flags directly from an untrusted query. An incorrect declaration can produce an invalid or incomplete candidate relation.

#Compose SQL expressions

The package also exports building blocks for adapter authors:

The doc, sourceSQL, and internal variable arguments above are SQL expressions, not user values. These functions are lower-level than compileFind and must be composed by trusted application code. Bind values through Context.bind() or literal().

For example, composing an update expression alone does not provide selection, immutable-ID checks, atomicity, index maintenance, or result counts. The storage implementation adds those guarantees. Read Updates for its application-facing behavior.

Shared helpers include validateJSON, literal, pathValue, jsonPath, jsonEachValue, jsonNumber, equal, compare, groupKey, truthy, type, typeRank, scalar, boolean, and numeric. JSON comparisons and paths carry Mongo-specific semantics; replacing them with ordinary SQL equality or json_extract changes missing/null, object-order, numeric, and array behavior. The complete signatures live in the compiler type declarations.

#Index integration

normalizeIndex(collection, key, options) produces an IndexDefinition with name, key, namespace, table, and index. indexCatalogSQL(), createIndexSQL(), and dropIndexSQL() generate the supporting catalog/key-table/index/trigger SQL. Execute builds, backfills, metadata changes, and drops within appropriate transactions. Reusing a name must verify that its existing specification matches.

indexCandidate, lookupCandidate, and lookupPipelineCandidate create candidate predicates used by the query/pipeline compiler. Index candidates reduce the scan; exact SQL predicates remain responsible for semantic correctness. A custom adapter must keep key rows and document rows consistent in the same transaction. Core and ShareDB storage use distinct namespaces even when sharing a connection.

Application index examples and supported access patterns are in Indexes. Benchmark tradeoffs are in Performance.

#Errors and execution boundaries

SiltError has a code; UnsupportedOperatorError uses SILT_UNSUPPORTED, and ValidationError uses SILT_VALIDATION. Unsupported syntax is rejected during compilation. Some document-dependent errors occur only when SQLite evaluates the expression and surface as SQLite errors, including guarded malformed JSON errors. Exact MongoDB error codes, error ordering, server limits, and BSON types are outside the current contract.

All document gathering, filtering, joining, sorting, and aggregation remains in SQLite. General JSON comparisons can produce large SQL and quadratic ranking work; see pipeline execution costs. There is no JavaScript UDF or in-memory query fallback.