Compiles a trilean predicate tree into a parameterised SQL WHERE fragment, so a rule stored as data can be evaluated by the database over a whole table instead of in process, one subject at a time.
pnpm add trilean-sqltrilean is a peer concern rather than an implementation detail: this package takes its PredicateNode type as input and declares it as a dependency, so the tree you compile is the same tree you evaluate.
trilean evaluates a predicate to definite(true), definite(false), or indeterminate — the third value meaning the tree could not be judged, usually because a reference resolved to nothing. Given a table of subjects and a rule to apply to all of them, the obvious approach is to fetch every row and evaluate the tree once per row. That reads the whole table to discard most of it.
The alternative is to let the database apply the rule. It already has a three-valued logic: TRUE, FALSE, and NULL meaning unknown, with AND, OR and NOT following Kleene's strong tables — the same tables trilean's own connectives implement — and a comparison against NULL yielding NULL rather than a verdict. A WHERE clause keeps only the rows whose condition came out TRUE, so a row whose condition was unknown is dropped exactly as a subject the evaluator declines to judge is not accepted.
So the third value is not reimplemented on top of SQL. It is the same third value, and the compiler's job is to translate the tree faithfully enough that it stays that way.
import { compilePredicateNode } from "trilean-sql";
import type { PredicateNode } from "trilean";
const rule: PredicateNode = {
kind: "allOf",
operands: [
{
kind: "compare",
op: "gte",
left: { kind: "reference", key: "age" },
right: { kind: "numberLiteral", value: 18 },
},
{
kind: "textCompare",
op: "matches",
left: { kind: "reference", key: "email" },
right: { kind: "textLiteral", value: "@example[.]com$" },
},
],
};
const columns = {
age: { column: "age", paramType: "number" },
email: { column: "email", paramType: "text" },
} as const;
const { sql, params } = compilePredicateNode(rule, {
dialect: "postgres",
// `matches` is refused by default -- see "Regular expressions" -- and opted into here because
// "@example[.]com$" uses nothing outside the syntax portable between ECMAScript and PostgreSQL.
postgresRegexpPushdown: true,
columnFor: (key) => {
const binding = columns[key as keyof typeof columns];
if (binding === undefined) throw new Error(`no column for ${key}`);
return binding;
},
});
// sql: (("age" >= $1::double precision) AND ("email" ~ $2::text))
// params: [18, "@example[.]com$"]
await client.query(`SELECT id FROM members WHERE ${sql}`, params);The fragment is a self-contained boolean expression with no WHERE keyword of its own, so it also drops into a HAVING, a CHECK, or a partial index predicate.
Returns { sql, params }. Throws rather than approximating; see Refusal.
options.dialect is "postgres" or "sqlite". It is required rather than defaulted so that a caller states the engine it is compiling for, instead of inheriting whichever one this package happened to implement first. See Dialects for what differs between them; the tree, the refusals, and the three-valued guarantee do not.
options.columnFor(referenceKey) maps a reference node's key onto { column, paramType? }. Only string keys reach it — trilean permits any JSON value as a key, and a non-string one is refused before the call. Throwing from columnFor is how you reject a key you have no column for; the exception propagates out unchanged rather than being wrapped. It is memoised for the duration of one compilation, so it is called once per distinct key however many times that key appears.
column may be qualified with dots ("members.age"); each segment is emitted as its own double-quoted identifier. Quoting is unconditional, which is what makes an arbitrary column name safe — an embedded quote is doubled, so a name carrying "; DROP TABLE ... becomes one inert identifier that simply does not exist. It also means the name is taken literally rather than case-folded, so return the column's real, case-exact name.
paramType declares the column's value kind: "text", "number", "boolean", or "timestamp" (trilean's instant). It is optional and worth supplying — it does not change the emitted SQL, but it is the only thing that lets the compiler detect the operand-kind mismatches described under Refusal. Without it, a comparison the database would coerce into a definite answer where trilean returns indeterminate compiles silently.
options.postgresRegexpPushdown, false by default, governs whether matches/notMatches may compile to PostgreSQL's own ~/!~ at all. See Regular expressions for why the default refuses them.
options.collectionFor(collectionKey) maps a some/every/fold node's collection key onto { table, join, columnFor } — see Collections. Optional: a tree that never uses one of those kinds never calls it, and leaving it unset is fully backward compatible with every version before it existed.
Returns { kind, path, reason } for the first node the compiler will not translate, or undefined if the whole tree is pushable. compilePredicateNode runs it first and throws on any result, so call it yourself only to choose between pushdown and in-process evaluation without provoking an exception:
const blocker = findUnpushableNodeKind(rule, options);
const rows = blocker
? await evaluateEveryRowInProcess(rule)
: await queryWith(compilePredicateNode(rule, options));Passing options widens the check: without them the walk is purely structural; with them it also applies the operand-kind rules that depend on each column's declared paramType.
UnsupportedNodeError (carrying nodeKind, path, reason), InvalidColumnError (carrying referenceKey, column) and InvalidCollectionTableError (carrying collectionKey, table), all extending TrileanSqlError.
| Node | PostgreSQL | SQLite |
|---|---|---|
and, or, not |
AND, OR, NOT |
same |
allOf, anyOf |
n-ary AND, OR; empty operands become each connective's identity, TRUE and FALSE, matching the evaluator's own fold |
same |
compare |
>, >=, <, <=, =, <> |
same |
textCompare |
=, <>; matches/notMatches only once postgresRegexpPushdown opts in, otherwise refused (see Regular expressions); ~/!~ against a translated pattern for portableMatches/portableNotMatches, no opt-in needed |
=, <>; REGEXP/NOT REGEXP for matches/notMatches; GLOB/NOT GLOB against a translated pattern for portableMatches/portableNotMatches |
memberOf |
IN / NOT IN, one parameter per candidate |
same |
exists |
IS NOT NULL |
same |
some, every |
a correlated scalar subquery over collectionFor's table, combining each participating row's own vote with MAX/CASE to match the evaluator's OR/AND-fold absorption exactly (see Collections); refused unless collectionFor maps the collection key |
same |
fold (max/min) |
the same correlated subquery, aggregating each participating item's own projected value with MAX/MIN, NULL the moment any participating item (or its filter) is indeterminate (see Collections); refused unless collectionFor maps the collection key. fold (reduce) is always refused — see Refusal |
same |
reference |
the mapped column, as a quoted identifier | same |
textLiteral, numberLiteral, booleanLiteral, instantLiteral |
a bind parameter, cast to text, double precision, boolean, timestamptz |
a bare ?, uncast |
Every literal in the tree becomes a parameter. Nothing but structure, operators and quoted identifiers is ever written into the returned sql, so a literal's content cannot alter the statement. Identifier quoting is the same in both: each dot-separated segment double-quoted with any embedded quote doubled, which is ANSI-standard and not a per-dialect rule.
PostgreSQL placeholders are always cast. That is not decoration: PostgreSQL rejects $1 < $2 outright because it cannot determine either parameter's type, and casting each placeholder to the type its own literal kind implies is what makes a fragment's meaning independent of how a particular driver decided to infer an untyped parameter. timestamptz rather than timestamp, because trilean's instant is an ISO-8601 string that may carry an offset and parsing one as a naive timestamp would silently discard it. SQLite placeholders carry neither a number nor a cast, because there is nothing to write: parameters bind by the order they appear, and there is no type to annotate. It answers a comparison between two bare ? from the bound values themselves.
An empty memberOf candidate list is worth a note, because IN () is a syntax error and the two constants it is tempting to fold to are both wrong. An empty in is false and an empty notIn is true only once the operand itself is known, and both stay unknown while it is NULL. The compiled forms — (x IS NULL AND NULL::boolean) and (x IS NOT NULL OR NULL::boolean), and the same two without the cast under SQLite, which has no boolean type to annotate — reproduce that exactly, which a bare FALSE/TRUE would not, most visibly under a surrounding NOT.
some, every, and fold (max/min) range over a collection the caller's resolvers supply — in trilean itself an opaque key resolved to an arbitrary in-memory list at evaluation time, with no assumption it is this query's row set at all. This package can push one down only when the caller states that the collection is a correlated table: options.collectionFor(collectionKey) returns
interface SqlCollectionBinding {
table: string; // dot-qualified, quoted exactly like a mapped column's `column`
join: string; // raw boolean SQL relating one row of `table` to the outer row
columnFor: (referenceKey: string) => SqlColumnBinding; // resolves a reference *inside* item/filter
}join is caller-authored rather than assembled from a join-column pair, because only the caller knows the real join shape — a single foreign key, a composite key, or an entity-attribute-value table's foreign key plus a literal discriminator ("attrs"."nodeId" = "graph_nodes"."id" AND "attrs"."attrName" = 'voltageLevel'). columnFor is a second, independent mapping from the outer one: it resolves a reference inside the collection's own item/filter, exactly as trilean's own evaluator re-points its EvaluationContext at the collection item before evaluating either. A tree that never uses some, every, or fold(max|min) never calls collectionFor — leaving it unset is fully backward compatible. A tree that does use one of those kinds without it set is refused with UnsupportedNodeError, the same refused outcome those kinds had before this option existed.
some/every compile to a correlated scalar subquery: SELECT filter, item FROM table WHERE join, filtered down to the participating rows (filter IS NULL OR filter, dropping only the rows filter excluded outright), then combined with MAX/CASE to match the evaluator's own OR/AND-fold absorption exactly — some is true the moment any participating row's item is true, regardless of another row's indeterminacy; indeterminate only once no row voted true and at least one participated indeterminately (an indeterminate filter, or an indeterminate item); false once neither, including an empty participating set (no rows, or every row filtered out), which is some's own identity. every is the mirror image: false the moment any participating row's item is definitely false, indeterminate only once neither that nor "every row voted true" holds, and true — including on an empty participating set — otherwise.
fold(max|min) compiles the same correlated subquery over the projected value instead of a predicate, and aggregates with MAX/MIN directly: NULL (indeterminate) the moment any participating row's filter or projected value is itself indeterminate — matching the evaluator's firstFilterIndeterminate/per-item short-circuit exactly, which has no absorbing value the way some/every's OR/AND does — and the aggregate otherwise, including NULL from an empty participating set, matching the evaluator's own domain-error indeterminate for a fold with nothing to seed a running extremum from. fold(reduce) is always refused, with or without collectionFor set: it threads an arbitrary combine expression through the collection in a caller-chosen order, which has no general SQL translation.
A fold(max|min) whose projected item is statically known to be text or boolean is refused, the same way a compare against one of those kinds is (see Refusal): trilean's own ordering (compareValues) refuses to order either, for exactly one item it would happily return definite, but the moment a second item participates it goes indeterminate — a divergence undetectable from a fixed pair of operands the way compare's is, since a collection's real cardinality is only known at query time, so it is refused unconditionally rather than only when two or more rows are proven to participate.
The dialects differ in three places, and nowhere else. matches/notMatches compile to each engine's own regular-expression operator; placeholders are $N::type under PostgreSQL and a bare ? under SQLite; and the bare NULL in an empty memberOf carries a ::boolean annotation only where there is a boolean type to annotate. Everything else the compiler emits — the connectives, the six comparison operators, =/<>, IN/NOT IN, IS NOT NULL, quoted identifiers — is ANSI-standard and identical.
const { sql, params } = compilePredicateNode(rule, { dialect: "sqlite", columnFor });
// sql: (("age" >= ?) AND ("email" REGEXP ?))
// params: [18, "@example[.]com$"]What compiles, what is refused, and the row-for-row agreement with evaluatePredicate are the same in both. The refusals in particular are not PostgreSQL caution carried over untested: SQLite's type affinity produces the same silent definite answers where trilean returns wrong-type, and test/integration/sqlite.test.ts measures each one against a real connection. A TEXT-affinity column compared to the number 5 is compared as text, so '9' > 5 is true there while '10' > 5 is not; a boolean is an integer, so gt on one answers by integer ordering; and 'abc' > 5, with no column involved at all, answers true rather than erroring.
Two things a SQLite caller has to supply that a PostgreSQL caller does not, both of which fail loudly rather than silently:
- A
REGEXPfunction, if the tree usesmatchesornotMatches— unless you setsqliteRegexpAvailable: false, in which case those two are refused at compile time instead. See Regular expressions. - Booleans bound as
0/1. SQLite has no boolean type, and drivers do not agree on whether a JS boolean is bindable at all — better-sqlite3 rejects one outright ("SQLite3 can only bind numbers, strings, bigints, buffers, and null").paramscarries the tree's own literals unchanged in every dialect, so converting them is the binding caller's job:params.map((v) => (typeof v === "boolean" ? Number(v) : v)).
A dialect this version does not implement is refused by name, from compilePredicateNode and findUnpushableNodeKind alike, with UnknownDialectError. SqlDialect is a closed union so TypeScript source cannot reach that, but a dialect read from configuration and asserted into the union at the boundary can, and reporting such a tree as pushable would promise a compilation that cannot happen.
Instants are the one place to be deliberate rather than merely careful. SQLite has no timestamp type either, so an instantLiteral is compared as text against whatever text the column holds. Offset-bearing ISO-8601 in a single common offset (2020-01-01T00:00:00Z) sorts chronologically as a string and compares correctly; mixed offsets, or a format that is not ISO-8601, do not. This is the SQLite counterpart of the PostgreSQL session-time-zone caveat under Refusal, and the same advice resolves both.
The compiler never degrades. There is no best-effort fragment, no silently dropped conjunct, no approximation that answers differently from the evaluator. Either the whole tree compiles to SQL that agrees with evaluatePredicate row for row, or UnsupportedNodeError is thrown and the caller evaluates in process instead.
The check is an allow-list walk rather than a deny-list, so a node kind added to trilean after this version was written is refused by default instead of falling through to whatever branch happened to be last.
That guarantee is about the tree's structure, and it has one remaining limit that is about the content of a string operand rather than any node's kind, and is not reachable by a walk over kinds: an instantLiteral is parsed by PostgreSQL rather than by Date, so one carrying no UTC offset is read in the database session's time zone where trilean reads it in the Node process's, and one PostgreSQL cannot parse at all raises a query error at execution time rather than this exception (trilean answers indeterminate for the same string). Pass instants as offset-bearing ISO-8601, which both read identically.
A matches/notMatches pattern would be the same kind of limit — matched by the server in PostgreSQL's own regular-expression language, not ECMAScript's — which is why it is refused rather than pushed down by default; see Regular expressions. Setting postgresRegexpPushdown: true opts back into that limit deliberately, for patterns already known to be portable.
Every refusal below applies to both dialects. What changes with the dialect is the reason text, which names the mechanism that actually applies to the engine you are compiling for — a findUnpushableNodeKind call given no options has no dialect to read and describes PostgreSQL, the one these refusals were first derived against.
Kinds this version does not translate. some, every, fold(max|min): refused unless options.collectionFor maps the collection key onto a correlated table — see Collections. fold(reduce): always refused, collectionFor or not — it threads an arbitrary combine expression through the collection in a caller-chosen order, which has no general SQL translation. lookup, call, delegate, treeReference: each is resolved by something the database has no access to — the caller's resolvers, its function registry, an external system. conditional: not implemented here. accumulator: only meaningful inside a reduce fold. arithmetic and negate: these carry and combine units, and pushing them down would drop that dimensional analysis without saying so. durationLiteral: trilean compares two durations in whichever unit each carries, and a column holds no unit to carry. complexLiteral: neither engine has a complex type.
Shapes refused despite a supported kind. A reference whose key is not a string, since there is nothing to map. A reference or numberLiteral carrying a unit: a unit on a reference asserts that the resolved value carries the same one, and a column has no unit for that assertion to be checked against. A numberLiteral of NaN, which trilean compares with === — under which NaN equals nothing including itself — and neither engine reproduces, for opposite reasons. PostgreSQL defines NaN as equal to itself and greater than every other double, so NaN = NaN selects every row there and none here. SQLite has no NaN at all and a driver binding one substitutes SQL NULL, so the same comparison is indeterminate there and matches nothing — which looks like agreement until you negate it, at which point trilean's definite true matches every row and SQLite's NULL still matches none. Infinities are not refused alongside it; every engine here orders them identically.
Operand pairings the engines would answer differently. These are the reason to declare paramType, and they are only detectable where it is declared:
- A
compareagainst text. trilean returnswrong-typeand directs you totextCompare. PostgreSQL orders the operand by collation and answers definitely; SQLite applies the text operand's own affinity to the other side and compares lexicographically, which is worse rather than better —'9' > 5is true and'10' > 5is not. - An ordering
compare(gt/gte/lt/lte) against a boolean. trilean has no order for booleans; PostgreSQL ordersfalsebeforetrue, and SQLite orders the integers 0 and 1 it stores them as. - A
textCompareagainst a non-text operand, which trilean treats aswrong-type. - Any comparison whose operands are of different declared kinds — a number against an instant, say. trilean calls that
wrong-type; both engines may coerce one to the other and answer definitely. - A
fold(max|min)'s projected item against text or boolean. trilean has no order for either (compareValuesreturnswrong-typethe moment a second item is compared);MAX/MINhappily orders text lexicographically and booleans as0/1the moment two or more rows participate. Unlike the other pairings above, this one is refused unconditionally rather than only when a fixed pair of operands proves the mismatch, because a collection's real cardinality is only known at query time.
Left undeclared, these compile, and the divergence is real but invisible. That is the whole argument for supplying paramType.
Under PostgreSQL, matches and notMatches can compile to ~ and !~, so the pattern is matched by the server rather than in process — but only once postgresRegexpPushdown: true is passed in SqlCompileOptions. Left at its default (false, or simply omitted), both are refused by findUnpushableNodeKind/compilePredicateNode and the caller evaluates them in process instead, exactly like any other unpushable node.
This is the one node kind refused by default for a reason that is not structural: trilean's evaluator matches a pattern as an ECMAScript RegExp, and PostgreSQL's ~/!~ match under PostgreSQL's own regular-expression dialect (POSIX Advanced Regular Expressions, "ARE"). The two are close — literals, character classes, basic quantifiers, alternation, non-greedy quantifiers, numbered backreferences, and even lookahead/lookbehind are portable — but they are separate implementations, and measuring them directly (PostgreSQL's own docs against MDN's RegExp reference, each point checked against a real PostgreSQL engine via PGlite rather than trusted from documentation alone) turns up real divergence in both directions:
- Silent divergence — the compiled fragment executes cleanly under both engines and still answers a different row set for the same pattern and data, with no error anywhere to catch it.
.matches a newline under PostgreSQL's default (newline-insensitive) mode; ECMAScript's.does not unless the pattern carries thesflag, which trilean's evaluator never sets (new RegExp(pattern), no flags) — soa.bmatches"a\nb"under PostgreSQL and not under trilean.\w/\d/\s(and their negations) are locale-dependent under PostgreSQL — in a UTF8 database,\wmatches an accented letter likeé— while ECMAScript's are ASCII-only by default (nouflag) — so^\w+$matches"café"under PostgreSQL and not under trilean. Neither is a syntax difference a validator could reject; the identical pattern text is valid and meaningful in both languages, just with different meaning. - Loud divergence — the pattern compiles clean here and fails only when PostgreSQL executes the query. Named capture groups (
(?<name>...)) and named backreferences (\k<name>) are ECMAScript syntax with no ARE equivalent (invalid regular expression: quantifier operand invalidfrom PostgreSQL, since ARE reads(?<as the start of a lookbehind and then finds no valid lookbehind body). Unicode property escapes (\p{...}/\P{...}) are likewise ECMAScript-only (invalid regular expression: invalid escape \ sequence). Character class subtraction ([a-z-[aeiou]], from other regex dialects, not valid ECMAScript either but sometimes reached for) also fails under ARE.
Given the silent class exists at all, a validated "portable subset" of pattern syntax cannot close this gap on its own: the offending constructs here are not unusual syntax to ban, they are two of the most ordinary regex idioms there are (. and \w), and the difference is in what the database's locale and newline-handling settings mean, not in what characters appear in the pattern text. That is why this compiles to in-process evaluation by default rather than to a syntax-restricted pushdown — and why opting in with postgresRegexpPushdown: true is a deliberate, whole-compilation decision rather than a per-pattern one: it is only safe once every pattern reaching this option is known, by the caller, to avoid both the syntax PostgreSQL cannot parse at all and the constructs (. against data that may contain newlines, \w/\d/\s against data that may contain non-ASCII text) whose meaning quietly differs.
Sources: PostgreSQL's own pattern-matching documentation (POSIX ARE support, its embedded-option flags, and its own comparison against XQuery's regular expressions); MDN's regular expressions reference for ECMAScript's semantics; the divergence points above were each executed against a real PostgreSQL engine (@electric-sql/pglite) and Node's own RegExp, not inferred from documentation alone.
Under SQLite there is no built-in regular-expression support at all. REGEXP is reserved syntax for a regexp(pattern, value) function the connection has to register itself — X REGEXP Y is exactly regexp(Y, X), pattern first — and the dialect emits REGEXP / NOT REGEXP for the same two operators. An unregistered one is a query error, no such function: REGEXP, rather than a fragment that quietly matches nothing, so this is a documented environment requirement in the same class as the caveats above and not a hole in the compile-time guarantee. The upside is that the pattern is then matched by your own RegExp, so the ECMAScript-versus-server-dialect divergence above does not arise.
With better-sqlite3:
db.function("regexp", (pattern, text) =>
typeof pattern !== "string" || typeof text !== "string"
? null
: new RegExp(pattern).test(text)
? 1
: 0,
);Both details are load-bearing rather than stylistic. Returning null for a NULL argument is what keeps the third value intact: SQLite does not propagate NULL through a user function on its own, so one answering 0 for a NULL value would make NOT REGEXP answer TRUE for a row whose value is unknown — the two-valued collapse this package exists to avoid. Returning 1/0 rather than a JS boolean is what better-sqlite3 accepts; a boolean is rejected from a user function ("returned an invalid value") for the same reason it is rejected as a bound parameter.
Some SQLite-wire-compatible targets have no way to register a function at all — Cloudflare D1's Workers Binding API is the motivating case, with no hook for it in its API and cloudflare/workers-sdk#2802 still open. Against a target like that, the registration above is simply not possible, and compiling matches/notMatches to REGEXP/NOT REGEXP anyway produces SQL that always fails at query execution with no such function: REGEXP rather than failing at compile time the way every other unpushable shape does. Set sqliteRegexpAvailable: false in SqlCompileOptions for a target like this, and the SQLite dialect refuses matches/notMatches with UnsupportedNodeError — and findUnpushableNodeKind reports them unpushable — at compile time instead, so the caller falls back to in-process evaluation the same way it would for any other unpushable node. It defaults to true, so a caller with a registered function (better-sqlite3, say) sees no change. It has no effect under the postgres dialect, which never needs a registered function in the first place.
portableMatches/portableNotMatches below need neither flag: D1 is exactly the target GLOB was chosen for, since it needs no function registration at all, and PostgreSQL's own translation is proven equivalent by this package's integration tests rather than passed through and hoped to be portable, so there is no divergence for postgresRegexpPushdown to gate.
matches/notMatches above carry a real cost: PostgreSQL's advanced regular expressions and ECMAScript's are close but not identical languages, and SQLite has no regular-expression support to push down to at all, REGEXP registration notwithstanding. trilean-regex exists to close both gaps with a small grammar that is a true regular language — no backreferences, no lookaround — and portableMatches/portableNotMatches are the two textCompare operators that use it instead of native regex syntax.
The pattern (textCompare's right operand) must be a compile-time literal — a bare textLiteral, never a column reference or a computed expression. This is a structural requirement, not a stylistic preference: translating a pattern into a dialect's own syntax happens once, in JavaScript, while compilePredicateNode runs, and there is nothing to translate if the pattern is only known per row at query time. A non-literal pattern is refused by the guard with a reason naming this directly, the same as any other unpushable shape.
PostgreSQL translates the parsed pattern into PostgreSQL's own Advanced Regular Expression syntax and binds it to ~/!~ — the same operators matches/notMatches already use, but now matched against a pattern PostgreSQL itself defines the syntax of, rather than one written for ECMAScript and hoped to be close enough. Every construct in trilean-regex's grammar translates structurally (confirmed against a real PostgreSQL 17 server in test/integration/postgres.test.ts, including ., whose default newline-matching behaviour agrees with trilean-regex's own despite reading otherwise from PostgreSQL's prose documentation alone). The one genuine limit is PostgreSQL's own: a bounded repetition ({n}/{n,m}) whose bound exceeds 255 has no PostgreSQL-native equivalent and is refused.
SQLite translates the pattern into a GLOB wildcard pattern and binds it to GLOB/NOT GLOB — a core SQLite feature needing no function registration, which is the whole reason a portable grammar is worth having for this dialect at all. GLOB's wildcard vocabulary is much narrower than a regular language, though, so only a reachable subset translates: literal runs, . as ?, a star of . as *, an anchor at the very start/end of the whole pattern (translated into a leading/trailing * standing in for "the rest, unconstrained," reproducing portableMatches's own substring-search default), and a character class that is neither negated nor contains ]/^/-/[ as a member (SQLite's own documentation does not confirm an escape mechanism for those inside a bracket expression, so this compiler does not guess at one). Alternation, bounded/optional/one-or-more repetition, and anything using an unsafe class member are refused, falling back to in-process evaluation. LIKE is never used as a target even for the wildcard shapes it could express: it is case-insensitive for ASCII by default, while trilean-regex matching is always case-sensitive; GLOB matches that exactly.
const { sql, params } = compilePredicateNode(
{
kind: "textCompare",
op: "portableMatches",
left: { kind: "reference", key: "code" },
right: { kind: "textLiteral", value: "^ENA-[0-9].*$" },
},
{ dialect: "sqlite", columnFor },
);
// sql: ("code" GLOB ?)
// params: ["ENA-[0-9]*"]The unit suite asserts compiled SQL text and parameter arrays per node kind. It cannot, on its own, establish anything about three-valued behaviour: ("age" > $1) is only indeterminate-preserving because of what PostgreSQL's planner does with a NULL age, which is a fact about PostgreSQL rather than about the string.
So the integration suite (pnpm test:integration) runs the same fixture against a real engine, once per implementation: PostgreSQL in an ephemeral container, PGlite in process, and SQLite in memory through better-sqlite3. Each seeds a table whose rows carry real NULLs, and for every case executes the compiled fragment as a WHERE clause and evaluates the same tree through trilean's own evaluatePredicate once per row, asserting the two agree on which rows match and which do not. Agreement on absence matters as much as on presence: the case that distinguishes three-valued logic from two-valued is the row that appears in neither a predicate nor its negation.
postgres.test.ts uses a real PostgreSQL server started as an ephemeral container, and needs a working Docker daemon. pglite.test.ts uses PGlite, which is PostgreSQL itself compiled to WebAssembly and run in process rather than a reimplementation of it, and needs nothing beyond Node — so the parity claim above is measurable on any machine, Docker or not, and PGlite is a verified target of the postgres dialect rather than an assumed one. sqlite.test.ts likewise needs nothing beyond Node, and also measures the divergences the guard's refusals exist to prevent — the NaN-to-NULL binding substitution, the lexicographic text comparison, the integer boolean ordering — rather than only asserting that each refusal fires, since inheriting PostgreSQL's refusal set would be worth nothing if SQLite happened to agree with trilean where PostgreSQL does not.
MIT — see LICENSE.