Bookclerk SQL contract v1
This document is the normative definition of sqlContractVersion = 1.
BookclerkSQL is a host-owned portable subset, not “SQLite everywhere.”
Adapter admission is “passes Bookclerk SQL v1 conformance,” not “looks like
SQLite or PostgreSQL.” Guests speak only this grammar. Adapters alone lower
to SQLite, PostgreSQL 16+, or Cloudflare D1. Machine-readable vectors live under
crates/bookclerk-db-exec/testdata/sql_v1/
and are consumed by bookclerk-db-exec lowering tests and library conformance.
- Contract versions are monotonic supersets: every statement form, type, and semantic guarantee in version N remains valid in version N+1.
- Guests advertise the highest version they implement as
sqlContractVersion. - Hosts require
sqlContractVersion >= SQL_CONTRACT_VERSION(currently1). - A non-superset redesign needs a new contract identity and a plugin ABI
break (
apiVersion/plugin.tomlapi_version). Do not increment this scalar and keep>=if N+1 would drop or change a guarantee of N.
Guest SQL is admitted in this order:
- SQL-v1 grammar — positive/versioned Bookclerk SQL (not a SQLite dialect).
- Typed expressions — the host builds exactly one resolved typed proof
(
ResolvedStatement) per canonical statement, bound to the SHA-256 of that exact SQL string (after host semantic desugars). Authorization, schema companions, result expectations, and adapter lowering consume that proof; none re-resolve names or types. Proofs travel on host→adapterAdapterDatabaseSession.execute(AdapterExecuteRequest.proofs).GuestDatabase.executestays untrustedExecuteRequestwithout proofs. The proof is typed against a durable binding catalog (bookclerk_sql_catalog/bookclerk_sql_schema) plus in-requestCREATE/DROP. Unknown tables/columns fail closed. Empty catalog means “no plugin tables,” not “unknown → NULL.”host_authoritativeskips authorization only; typing and TEXT lowering still run (from the canonical host schema or an equivalent already-resolved proof). New atomic batches snapshot catalog and physical types in autocommit beforeBEGIN, so SQLite does not hold a reserved lock across paged catalog loads. On a SQLite file the SQLite adapter remembers that autocommit snapshot and reuses it while one small key read (the file,PRAGMA schema_version, the catalog row count and thebookclerk_sql_schemarows) is unchanged, so a batch does not reload the catalog and reparse every table; any DDL, by any connection, changes the key. Nested typedexecuteon an already-open transaction still snapshots inside that transaction, never from the memo.SchemaAction::Createstores the fullCreateTableSchema(ordered columns, SQL-v1 types, nullability, identity, PK/UNIQUE/defaults/CHECK/FKs) plus fingerprint and no-op. Catalog companions and Postgres identity objects consume that schema; they do not reparse canonical CREATE text. Host catalog INSERT/DELETE DML is built with SeaQuery (SqliteQueryBuilder) from the proof. Adapter-private catalogs are reserved names; guests cannot grant themselvessqlite_master/information_schemaprobes. - Binding scope/security — isolated schema, reserved-name, and ref checks
using
proof.physical_accessesandproof.functions(no second alias walk) when a type environment is present (binding-owned and library guests with catalog types). A projection wildcard (SELECT *,alias.*,RETURNING *) records each physical column plus"*"on that table; column-scoped grants reject the wildcard the same way the lexical authorizer does.column: Noneis table presence only (FROM / DML destination), not a star. Library guests without a type env still use lexicalparse_guest_sql_refsso SQLite-only helpers remain authorizable without pretending they are portable SQL-v1 types. - Capability/limit validation — binds, result selection, negotiated caps.
- Canonical
ExecuteRequest— host wrap (receipts) without engine identity. Receipt wrappers get their own proofs bound to the wrapper SQL. - Adapter-edge lowering — at execute, against the isolated physical DB,
reading TEXT collation sites from the proof (never
ident_is_text).
Hosts emit canonical Bookclerk SQL. Host semantic desugars (not adapter
rewrites) make unspecified ORDER BY explicit (NULLS FIRST/LAST, SQLite
default) and wrap / % divisors as NULLIF(x, 0). Adapters lower to the
engine at execute time. Domain planners must not branch on engine identity.
- DML and queries —
bookclerk-db-exec::lower_canonical_sql_typed(placeholders,IFNULL,json_extract,INSERT OR IGNORE, 2+-argmin/max,json_valid,round/sum/avgwire types,LIKE→GLOB, proof-directedCOLLATE "C"and INTEGER overflow). Default NULLS are not adapter rewrites. - DDL types and identity — adapter execution edge
(
realize_host_ddl/ binding DDL helpers). The generic DML pass does not classify statements as DDL. INSERT OR IGNORE … SELECT/WITH— adapters wrap the row source asSELECT * FROM (<source>) AS bookclerk_src WHERE truebeforeON CONFLICT DO NOTHINGso SQLite/D1 UPSERT is unambiguous.VALUESis not wrapped.bookclerk_srcis reserved like everybookclerk_-prefixed identifier.
- Positional binds use bare
?only (1-based order of appearance in code spans). $n,?NNN, and other engine-specific bind spellings are outside SQL v1 and are rejected for every guest before adapter dispatch.- Placeholders inside string/identifier literals, line/block comments, and PostgreSQL dollar-quotes are not binds (adapters must preserve them).
Supported top-level forms for guest-authorized and host-authored plans (fail-closed recursive-descent grammar, not a denylist):
| Form | Notes |
|---|---|
WITH CTEs + SELECT … |
CTE bodies are SELECT only (no nested DML). DISTINCT is admitted; DISTINCT ON is not. Mixed aggregate + non-aggregate select lists are SQLite-shaped and not in this contract; keep aggregates in their own statement (or GROUP BY). |
SELECT clauses |
FROM / JOIN (INNER / LEFT / CROSS; not RIGHT / FULL), WHERE / GROUP BY / HAVING / ORDER BY [ASC|DESC] [NULLS FIRST|LAST] / LIMIT / OFFSET, UNION [ALL] (not EXCEPT / INTERSECT). |
INSERT INTO … / INSERT OR IGNORE INTO … |
Optional RETURNING |
UPDATE … / DELETE … |
Optional RETURNING |
| Schema DDL | Host-only for library and session guests. GuestSqlPolicy::binding_owned admits idempotent CREATE/DROP TABLE/INDEX IF [NOT] EXISTS on a physically separate plugin database. Binding helpers are this contract (not a wider SQLite dialect). Postgres adapters lower canonical types (INTEGER PRIMARY KEY AUTOINCREMENT → BIGINT PRIMARY KEY plus a transactional identity trigger, BLOB → BYTEA, case-insensitive) at their execute edge. |
Expression operators (closed): = <> != < > <= >=, AND OR NOT,
IS [NOT] NULL, IN, BETWEEN, LIKE (not ILIKE), \|\| + - * / %,
CASE, CAST(x AS INTEGER\|REAL\|TEXT\|BLOB\|BOOLEAN), policy-allowed function
calls, . qualification, ?, literals. Rejected for every guest: ::,
ILIKE, SIMILAR, OVER, FILTER, GLOB / MATCH / REGEXP. COLLATE is rejected except COLLATE NOCASE on an ORDER BY key or a CREATE INDEX key (the library title-order fold).
INSERT OR IGNORE is canonical and means unique / primary-key conflict
only. NOT NULL and CHECK still abort on every backend. Adapters lower
the form to INSERT … ON CONFLICT DO NOTHING (conflict clause before
RETURNING) for SQLite, D1, and Postgres — SQLite OR IGNORE would otherwise
swallow NOT NULL. When the source is SELECT/WITH, the wrap above is
required. REPLACE INTO, INSERT OR REPLACE / OR ABORT /
OR FAIL / OR ROLLBACK are outside SQL v1 and are rejected before adapter
dispatch.
Binding CREATE TABLE / CREATE INDEX types are INTEGER, REAL, TEXT,
BLOB, and BOOLEAN only. AUTOINCREMENT is valid once, immediately
after PRIMARY KEY, and only when the column type is INTEGER (INTEGER PRIMARY KEY AUTOINCREMENT). INTEGER AUTOINCREMENT, REAL AUTOINCREMENT,
and duplicate AUTOINCREMENT are rejected. BOOL is not an alias. Tails
such as STRICT, WITHOUT ROWID, JSONB, NUMERIC(p,s), VARCHAR(n),
CASCADE / RESTRICT on DROP, and backend-specific index clauses
(USING, INCLUDE, and column COLLATE other than COLLATE NOCASE on an index key) are rejected. Adapter lowering of
type/identity tokens and RETURNING is case-insensitive in code spans (the
host write-gate constant is still matched exactly).
Identity after INTEGER PRIMARY KEY AUTOINCREMENT is SQLite-equivalent:
after an explicit id insert, the next omit-id is max(committed id)+1 and
deleted ids are not reused. Explicit identity values are admitted. Postgres
adapters do not use BIGSERIAL/setval (those do not roll back with the
ExecuteRequest). They keep a transactional BIGINT high-water row in
adapter-private bookclerk_identity and a BEFORE INSERT trigger generated
from the parsed identity column name. DROP TABLE deletes that table's
catalog and identity rows in the same atomic request and drops
bc_if_<digest>(); it does not emit DROP TRIGGER … ON after the
relation is gone. Recreate starts at 1.
SQLite/D1 keep native AUTOINCREMENT. Guests cannot name bookclerk_identity
or bookclerk_sql_catalog.
- Identifiers are canonical unquoted names:
[A-Za-z_][A-Za-z0-9_]*, ≤ 63 bytes after case fold (PostgreSQLNAMEDATALEN-1). 63 accepted; 64 rejected. Quoted / mixed-case object names ("Foo",`Foo`,[Foo]) are rejected. Adapter-private Postgres identity objects use hash-based names with separate prefixes:bc_if_<digest>(function) andbc_it_<digest>(trigger), 16 hex digits of SHA-256 of the folded table name, always under 63 bytes. Two canonical names that would collide under Postgres 63-byte truncation produce distinct generated object names. - Unquoted names fold to lowercase in authorization (
guest_sql); adapters must not reinterpret authorization.CREATE TABLE FooandSELECT … FROM fooname the same object on every admitted backend.
Wire (DbValue) |
Canonical SQL expectation |
|---|---|
null(expectedType) |
SQL NULL with typed metadata |
bool |
Canonical SQL BOOLEAN (not INTEGER 0/1 storage leaking onto the wire) |
int64 |
Exact 64-bit integer (not JSON number) |
float64 |
IEEE float |
text |
UTF-8 text: Unicode scalar values excluding U+0000 |
bytes |
Opaque byte string (NULs allowed) |
Unknown Cap'n union members fail closed as unsupported.
BookclerkSQL TEXT is Unicode scalar values on the UTF-8 wire, excluding
U+0000. UTF-8 is the encoding, not a smaller repertoire. The host rejects NUL
in parser literals, DbValue::Text binds/results, and catalog TEXT cells
before adapter dispatch, including SQLite. BYTES/BLOB may contain
0x00. PostgreSQL adapters require server_encoding / client_encoding
UTF8 (or a strict UTF-8 equivalent) at connect; SQL_ASCII / LATINn fail
closed.
Host admission enforces advertised DbCapabilities before dispatch. Adapters
must not over-advertise a physical engine limit they cannot hide. A guest
reads the caps its session negotiated with GuestDatabase.limits (the
binding's limits() in every SDK) and sizes requests and reads before
sending; exceeding one fails with the typed limitExceeded error, never a
truncated result.
| Field | Meaning |
|---|---|
maxBinds / maxStatements / maxPayloadBytes |
Binds, statements, and canonical SQL+binds per statement |
maxResultRows / maxResultBytes / maxCellBytes |
Result envelope (fail closed, never truncate) |
maxRequestBytes / maxAtomicResultBytes |
Whole encoded request / reply |
maxStreamedResultBytes / maxStreamedResultRows |
Whole reply and rows per statement when the result is read as a stream (executeStreamed); 0 on both means the adapter does not stream. When set, each is at least its inline counterpart and at most the host maximum (64 MiB, 250 000 rows), since the host holds the reply while it reassembles it |
maxFunctionArgs |
Physical vararg ceiling after adapter hiding. Counts the arguments of one function call; the members of an IN list, a VALUES row, a column list or a subquery are not arguments. Hosts require at least 32 (the portable json_object maximum) |
maxSchemaColumns |
Columns per CREATE TABLE / result width |
maxPatternBytes |
UTF-8 bytes of a LIKE pattern value (literals and TEXT binds). Sqlite-family GLOB expansion is 3× for [ * ?; D1 advertises 16 so expanded GLOB stays ≤ 50 |
AdapterDatabaseSession.executeStreamed and GuestDatabase.executeStreamed
run the same atomic batch as execute but answer it as a sequence of
ExecuteStreamFrame messages instead of one ExecuteReply, so a result
may grow to maxStreamedResultBytes / maxStreamedResultRows instead of
the inline caps. The batch still commits (or rolls back) whole before the
first frame is sent; streaming changes how the result crosses the wire,
not its atomicity.
Each frame is one single-segment Cap'n Proto message, at most 256 KiB
unless it carries a single larger row. The sequence is head
(operationId, statement count), then per statement a statement frame
(index, columns, rowsAffected) followed by zero or more rows frames,
then end with the timing, or err with a PluginError at any point.
The reply is inline bytes when the frames fit in one scalar, else a
ByteSource. A receiver decodes frame by frame, holds the raw bytes to
maxStreamedResultBytes plus framing slack, and fails closed with
limitExceeded (maxStreamedResultRows / maxStreamedResultBytes) as
soon as a budget is passed, so an oversized result never lands whole.
The whole-reply budget (maxAtomicResultBytes, or maxStreamedResultBytes
for a streamed batch) is also enforced while the batch runs, not only on the
encoded reply: an adapter holds every statement to the smaller of
maxResultBytes and what is left of the whole-reply budget, so a batch of
individually admissible results fails with limitExceeded
(maxAtomicResultBytes) at the statement that would exceed it, and the
remaining statements never run or occupy memory.
The host reads every standalone library and binding batch through
executeStreamed when the adapter streams, and the SDKs read a guest's
all() and iterate() the same way over the granted channel's
POST /db/execute-streamed (see
Working within database limits). An adapter that
does not stream answers both methods with unsupported. An isolate
(workerd) adapter's streamed reply travels inline through the launcher, so
its frames must fit one scalar: the SDK clamps such an adapter's advertised
maxStreamedResultBytes to 256 KiB so the host's budget is one the
adapter can hold to before it commits, and frames past one scalar come
back as limitExceeded (maxScalarBytes). First-party sqlite and
Postgres advertise 64 MiB and 250 000 rows; D1 advertises 8 MiB and
50 000 rows, and reads its HTTP responses under a body ceiling sized for
the budget of the attempt.
A guest batch reaches an adapter wrapped in a replay receipt: a prune
DELETE and a prior-receipt SELECT come first, every guest DML write
is gated on the receipt being absent, and a stub INSERT claims the
receipt last (AdapterExecuteRequest.receipt gives the guest statement
count as guestLen and the guest request's requestHash as
guestHash). Guest DDL carries no receipt predicate: the host admits only
idempotent DDL (CREATE/DROP … IF [NOT] EXISTS), and the SDK helpers
skip all remaining guest work, DDL included, once the prior-receipt
SELECT shows a completed or conflicting receipt. The adapter stores the
guest reply on that receipt before COMMIT, so a retry with the same
operationId after a lost reply replays the recorded result instead of
running the writes again.
Isolate adapters get this from the SDKs instead of writing it themselves:
- TypeScript:
executeAdapterBatchandexecuteAdapterBatchStreamedfrom@bookclerk/plugin-sdk/workerd. - Python:
execute_adapter_batchandexecute_adapter_batch_streamedfrombookclerk_plugin_sdk.db_value.
The adapter supplies a begin that opens a transaction with run,
commit and rollback. The helper runs the wrapped statements, skips the
guest work a completed prior receipt already covers, runs the finalize
UPDATE that stores the reply (or the read-only marker for a batch of
SELECTs), and commits. When the prior-receipt SELECT found nothing
but the stub claimed nothing either, another attempt committed the
receipt in between; the helper rolls back with unavailable so the
host's retry replays that receipt. The receipt records the measured
timing, which a replay answers with. A result cell the host would refuse (a non-finite
float64 or TEXT with U+0000) rolls the batch back with invalidParams.
The streamed variant takes the streamed budget the adapter advertises
(executeStreamLimits(caps) / execute_stream_limits(caps)). It encodes
the frames inside the transaction, stops with limitExceeded
(maxScalarBytes) as soon as they pass one scalar, decodes them under
that budget the way the host does, and holds the encoded ExecuteReply to
the byte budget itself (the decoder allows framing slack past it). Any
failure rolls back, so a reply the host would refuse never commits. A write whose reply is over the
64 KiB replay payload also rolls back, with internal. The SDK tests run both helpers on SQLite against a
receipt-wrap vector the host generates
(crates/bookclerk-library/fixtures/guest_receipt/isolate_adapter.json).
json_object even arity 2–32 is portable (16 key/value pairs). That matches
D1’s physical maxFunctionArgs. Sqlite-family adapters must not nest
json_patch to admit more pairs: JSON Merge Patch deletes null values and
changes duplicate-key behavior. Scalar min/max / coalesce are already
≤ 32 in the helper table. Postgres json_build_object does not need nesting
and is cast to TEXT ((json_build_object(…)::text)) except when a nested
json_object is a constructor value (that call stays json so it nests as
an object). Unproven LIKE pattern expressions fail closed when maxPatternBytes is
finite. Do not use PRAGMA case_sensitive_like.
- Typed null carries
expectedTypefor empty-result metadata and decode. - Adapters must not collapse distinct Cap'n types through JSON number/array shims on the granted Cap'n path.
CASTmatrix (proven only): same-typeCAST;INTEGER↔REALwhen the conversion is proven; typedNULLto any admitted type. Rejected:TEXT→INTEGER/BOOLEAN/BLOB(includingCAST('1' AS INTEGER)),BLOB→TEXT/INTEGER,BOOLEAN↔numeric, and any untested coercion. Store the value in the destination type instead.- Unification (same rules on every backend):
IFNULL/COALESCE/NULLIF/CASE/UNIONarms, comparisons, scalarmin/max, arithmetic (INTEGER+REAL→REAL).sum(x)requiresINTEGER.avg/roundrequire numeric.lower/upper/LIKErequireTEXT. - BOOLEAN contexts (
BOOLEAN | NULLonly):WHERE/HAVING/JOIN … ON/DELETE/UPDATEWHERE, searchedCASE WHEN <pred>,NOT/AND/ORoperands, andCHECK (…)on bindingCREATE TABLE.SELECT 1 WHERE 1is rejected. SimpleCASE expr WHEN vunifiesvwithexpr. - Assignment sites:
INSERTdestination list is the explicit column list or catalog declaration order.VALUESandSELECT/WITHmust match arity and unify with each dest column.UPDATE … SET col = rhsuses the same dest-typing rule. Guest v1 does not admitON CONFLICT … DO UPDATE SET(canonical form isINSERT OR IGNORE→ adapterON CONFLICT DO NOTHING). - Operators:
+-*/are INTEGER/REAL/NULL (result REAL if any REAL).%is INTEGER/NULL only (REAL is not portable).||/LIKEare TEXT/NULL. Comparisons unify both sides. - Helpers (typed arguments):
replace(TEXT, TEXT, TEXT) → TEXT;substr(TEXT, INTEGER [, INTEGER]) → TEXT;round(numeric [, INTEGER]) → REAL;lower/upper/trimTEXT-ish;lengthTEXT|BLOB;json_extractTEXT path;sumINTEGER-only. Unknown helper names are grammar-denied; typecheck does notOk(NULL)for unknown calls. - Derived names / CTEs: visible FROM keys are
AS alias(or the table name).WITH c(x, y) AS (…)honors explicit CTE output names. Recursive CTEs infer the anchor first, publish those concrete types into self-scope, then typecheck recursive arms and unify every arm with the anchor. Anonymous or duplicate derived columns are rejected when they must be addressable (SELECT 1in a CTE later qualified,AS (a, a), expression columns withoutAS). Top-levelSELECTlists may omitAS(cells are positional). Postgres labels those items?column?; the adapter uniquifies colliding labels. Explicit duplicate aliases (SELECT x, x) still fail closed. Correlated subqueries resolve qualified aliases against enclosing FROM maps (nestedEXISTSmay namei.user_idafterFROM portal_identities i). - Schema fingerprint:
CREATE TABLE IF NOT EXISTSis keyed on a structured schema IR (column order/names/types, identity, DEFAULTs, and admitted constraints — not normalized CREATE text). Catalog has the table and the fingerprint matches → no-op (no catalog/identity companions). Fingerprint differs → reject. Catalog lacks the table → INSERT catalog/ schema rows (noON CONFLICT DO UPDATE); identity companions only on this path. Physical reconciliation on SQLite, D1, and PostgreSQL fail-closes: catalog absent + physical exists (do not adopt orphans), catalog present + physical missing, or catalog vs physical column IR disagree. D1 uses the samesqlite_masterfingerprint path as SQLite. Catalog load is fail-closed (Result): RPC/adapter/shape errors areErr; missing catalog objects areOk(empty). Rows page by keyset(table_name, ordinal)atmin(negotiated maxResultRows, FIRST_PARTY_MAX_RESULT_ROWS)with the binding cancel and deadline forwarded. Malformed cells / unknownsql_typefail closed. - Runtime semantics (portable):
/and%by zero yield NULL. The host rewrites the divisor asNULLIF(x, 0)unless it is already structurallyNULLIF(<expression>, 0). ANULLIFwhose second argument is not0(for exampleNULLIF(0, 1), which evaluates to0) still gets an outer guard. Adapters must not repeat this rewrite. INTEGER+-*andabsin DML/query SQL that would overflow i64 yield NULL (proof-directedCASEwraps; guests must not depend on wraparound or a backend error). Overflow wrapping is not applied toCREATE TABLECHECKexpressions; INTEGER+-*absinCHECKis rejected at admission.roundhalfway rounds away from zero (round(1.5)=2,round(2.5)=3) via PostgresNUMERIC.substris 1-based; portable vectors use start ≥ 1 and length ≥ 0. Negative/NULLsubstrindexes are outside the portable claim. Postgressubstr(text, int, int)does not accept bigint, and a bound INTEGER parameter is bigint, so lowering clamps the start and length into theint4range and then casts them toINTEGER. A length above 2³¹−1 still means the rest of the string. The argument is evaluated once, and NULL stays NULL. SQLite keepssubstrunchanged.lengthof TEXT is characters;replace/lower/absof values in range follow engine defaults locked by shared vectors. Host dispatch-latency SQL may calljulianday(TEXT) → REAL; Postgres lowering rewrites that exact expression toEXTRACT(EPOCH …). Guests cannot calljulianday(not in the binding helper allowlist). DEFAULTmust match the declared column type: integer literals forINTEGER, numeric forREAL, strings forTEXT,X'hex'forBLOB(kept in v1; Postgres DDL lowering rewritesDEFAULT X'deadbeef'toDEFAULT decode('deadbeef', 'hex')afterBLOB→BYTEA),TRUE/FALSEforBOOLEAN(not1). ParenthesizedDEFAULT (…)is rejected unless the inner expression is a matrix-legalCAST.- Admitted helper signatures already land in the universal
DbValuedomain (round→Float64, integersum/count→Int64,avg→Float64); callers are not required toCASTfor those shapes. Other computed numerics that would be engineNUMERICshould stillCASTtoINTEGERorREAL. - Conformance vectors compare typed
DbValuecells (Int64is notFloat64(n)orText("n")). SQLite expression columns may still beDbType::Unspecified; Postgres OID mapping should beInt64/Text.
| Canonical | Arity | Wire | Meaning |
|---|---|---|---|
IFNULL(a, b) |
2 | (inputs) | First non-null |
json_extract(col, '$.path') |
2 | text | JSON path extract |
json_object(k, v, …) |
even 2–32 | text | Object constructor (key/value pairs). Portable maximum is 16 pairs (32 arguments), matching D1 maxFunctionArgs. Adapters must not emulate larger calls with json_patch. |
json_valid(expr) |
1 | int64 0/1 | Predicate (= 0 / = 1) |
min(a, b, …) / max(a, b, …) |
2+ | (inputs) | Scalar least/greatest. Any NULL argument yields NULL (NULL-poison). One-arg MIN/MAX stay aggregates. |
round(x) / round(x, n) |
1–2 | float64 | Round; Postgres CASTs through NUMERIC onto DOUBLE PRECISION. |
avg(x) |
1 | float64 | Average |
sum(x) |
1 | int64 | Integer aggregate (REAL-sum portability is not claimed) |
count(*) / count(x) |
1 | int64 | Count |
Wrong arity is rejected before dispatch, including IFNULL/COALESCE/NULLIF
(2 / 2+ / 2), abs/sum/avg/count/length/lower/upper/json_valid
(1), and even-arity json_object (2–32 arguments, 16 key/value pairs). Postgres lowering
maps IFNULL / json_* / 2+-arg min/max to COALESCE, jsonb operators,
(json_build_object(…)::text) (nested json_object values stay json so they
nest as objects), IS [NOT] JSON, and LEAST/GREATEST with an explicit
NULL guard (CASE WHEN a IS NULL OR b IS NULL … THEN NULL ELSE LEAST/GREATEST(…) END). SQLite/D1 execute those canonical forms directly after the unique-conflict
INSERT OR IGNORE rewrite. MAX(attempt_count, 1) in host plans is the same
2-arg scalar form.
Plugin-owned database bindings (GuestSqlPolicy::binding_owned) authorize
these helpers plus portable scalars (abs, cast, coalesce,
length, lower, nullif, replace, substr,
trim, upper). hex is not portable (PostgreSQL has no hex()); it remains
available to library guests. SQLite-only names (strftime, typeof, group_concat,
iif, instr, quote, total, date, datetime, time, json_array)
are denied so a binding cannot depend on a dialect the contract does not
guarantee. See Isolated plugin database bindings.
Default NULL ordering matches SQLite: NULL sorts as smallest (ASC NULLS FIRST, DESC NULLS LAST). The host desugars omitted NULLS into explicit
clauses before typecheck. Explicit NULLS FIRST / NULLS LAST is admitted
and left unchanged. Adapters must not repeat this rewrite.
Host library pages order titles with COLLATE NOCASE (ASCII case-fold, then code points). That spelling is admitted on ORDER BY and CREATE INDEX keys. Any other collation name is rejected. Postgres lowering rewrites the fold to lower(ident COLLATE "C") only in those key lists. A WHERE predicate that still contains COLLATE NOCASE is left unchanged.
Postgres CREATE INDEX keys each receive NULLS FIRST, matching the host ORDER BY desugar (NULL sorts as smallest). The check is the keyword NULLS, so an identifier such as nulls_flag still receives the clause. CREATE INDEX IF NOT EXISTS does not alter an index that already exists, so a database created before this lowering can keep a different NULL order until that index is rebuilt.
lower and upper of a TEXT column, a string literal, or a direct ? argument whose bind is TEXT or untyped are COLLATE "C" sites. A nested call (lower(substr(body, ?, ?))) and a parenthesized subquery do not collate their placeholders. An INTEGER, REAL, BLOB, or BOOLEAN bind is not a collate site, so Postgres does not see COLLATE on bigint. Postgres lower uses the argument collation's LC_CTYPE; without "C", a UTF-8 database locale folds É to é, which SQLite does not. A direct TEXT placeholder lowers to lower(($1 COLLATE "C")). The books expression index lowers to lower((uuid COLLATE "C")) on Postgres so the predicate matches the index. IF NOT EXISTS leaves an already-created lower(uuid) index unchanged until it is rebuilt. SQLite keeps lower(uuid).
A TEXT column compared with = to a placeholder, or with IN to a list of placeholders only (uuid IN (?, ?)), stays bare so the btree collation matches the UNIQUE(uuid) index. A literal in that comparison or list is still collated. Postgres DDL (lower_canonical_ddl_to_postgres and binding schema batches) fails closed when a CREATE INDEX COLLATE NOCASE tie-break or a lower/upper column argument cannot be typed from the catalog or an earlier CREATE TABLE in the same batch. An untyped key is not emitted as a different index. Query entry points (lower_canonical_sql, lower_canonical_to_postgres) stay infallible: an untyped index key sent through them remains bare.
TEXT ORDER BY is binary / code-point order. Postgres lowering applies
COLLATE "C" only at TEXT expression spans recorded on the statement proof
(comparisons, scalar min/max, ORDER BY keys, LIKE / NOT LIKE,
lower/upper). INTEGER/REAL/BOOLEAN/BLOB expressions never receive
COLLATE "C". json_extract path string arguments are omitted from the
proof (so the path stays a bare literal for helper lowering); a comparison
or ORDER BY on a '$.…' string still receives "C". Host-authored
ORDER BY TEXT uses the same annotations. SQLite/D1 need no COLLATE rewrite
(BINARY default).
The conformance suite uses ASCII and non-ASCII vectors. Locale-sensitive
Unicode order is outside v1.
Canonical LIKE is case-sensitive. Grammar keeps LIKE and still
rejects ILIKE and ESCAPE. SQLite/D1 adapters rewrite LIKE/NOT LIKE to
GLOB with bind-safe replace (escape [ * ?, then %→* _→?).
Do not use PRAGMA case_sensitive_like. Guests cannot write GLOB.
rowsAffected: mandatory (affectedRowscapability). Reports rows changed by DML;INSERT OR IGNORE/ON CONFLICT DO NOTHINGthat insert zero report0.RETURNING: mandatory (returningcapability). Column order and names come from engine metadata; empty results still expose column metadata.- Transactions: one
ExecuteRequestis one atomic batch. Failure before commit rolls back all statements in the request. - Errors: stable categories (
conflict,deadline,invalidParams,internal, …). Ambiguous commit / lost response uses durable receipt replay of the originalExecuteReply. A batch that only reads stores a receipt without its rows and re-runs the reads on replay, so a read-only result is bounded by the result budget it was read under, not by the 64 KiB replay payload. A request or result over a negotiated cap islimitExceeded: its message islimit exceeded: <cap> is <n>; used <m>and itsdetailsJSON carries{ "limit", "cap", "used" }, so callers branch on the cap name instead of matching text. Every emitter (adapter result capping, host admission, reply validation, the SDK request-size check) uses that one shape. - Bounds:
maxResultRows,maxResultBytes,maxAtomicResultBytes,maxRequestBytesfail closed before commit / dispatch; a streamed read fails closed atmaxStreamedResultRows/maxStreamedResultBytesinstead.maxLoweredStatementBytes(0unspecified) is compared to a proof-aware sqlite-family lowering upper bound afterResolvedStatementexists. D1 advertises100000for that ceiling;maxPayloadBytesremains a proven canonical cap so mechanical-only lowering of an admitted statement stays within 100 KiB.
A new SQL adapter is admitted when it:
- Negotiates
DbCapabilitieswithsqlContractVersion >= 1and required semantic flags/limits (not by matching a plugin id or diagnosticengine). - Executes the sql_v1 corpus (lowering + shared behavioral vectors) without host library changes keyed on adapter identity.
- Keeps
sqlFamily/dialectonDbBootstrap(host-private bootstrap metadata) only — not on typedDbCapabilities.DbCapabilities@17ispluginDatabases,@18–@20are numeric caps,@21ismaxLoweredStatementBytes, and@22/@23areconsistentBackupRead/atomicUnitRestore. Reports any diagnosticenginestring onDbBootstrap; the host always emits canonical?SQL. Architecture lint forbids planner reads of bootstrap fields and host calls to physical lowering helpers.
Source: docs/plugins/sql-contract/v1.md at main · View as Markdown