@rapiq/adapter-sql

Turns query AST nodes into parameterized SQL fragments. Database-agnostic — per-database behavior is injected as a small dialect option object, not a subclass. It is also the foundation the TypeORM adapter builds on.

npm install @rapiq/core @rapiq/adapter-sql

Dialects

A dialect is a handful of callbacks:

type DialectOptions = {
    escapeField: (input: string) => string,      // mysql: `field`, pg: "field", mssql: [field]
    paramPlaceholder: (index: number) => string, // pg: $1, mysql: ?
    regexp?: (field: string, placeholder: string, ignoreCase: boolean) => string,
    caseFold?: (input: string) => string,        // default: lower(input); mysql/mssql: identity
};

Presets ship for pg, mysql, sqlite, mssql and oracle:

import { mysql, pg } from '@rapiq/adapter-sql';

resolveDialect(name) maps a driver/connection type name (e.g. TypeORM's connection.options.type or a knex client name) to the matching preset — postgres, mariadb, better-sqlite3, oracledb, … — and returns undefined for unknown names:

import { resolveDialect } from '@rapiq/adapter-sql';

resolveDialect('mariadb'); // mysql preset

Dialects without regex support

The mssql and sqlite presets omit the regexp callback — SQL Server has no regex operator, and stock SQLite ships without a REGEXP function. On those dialects the contains / startsWith / endsWith filter operators (and their negations) fall back to LIKE ... ESCAPE '\' with wildcard escaping; only the regex filter operator is unavailable and throws a typed AdapterError (ErrorCode.FEATURE_UNSUPPORTED).

The root adapter

Each parameter has an adapter/visitor pair (FieldsAdapter/FieldsVisitor, SortAdapter/SortsVisitor, PaginationAdapter/PaginationVisitor, RelationsAdapter/RelationsVisitor) that collects the walked state — selected columns, order map, limit/offset, relation paths. A root Adapter bundles all five; execute(query) walks a whole Query into it and returns the accumulated clause fragments:

import { Adapter, pg } from '@rapiq/adapter-sql';

const adapter = new Adapter({ ...pg, rootAlias: 'user' });

const fragments = adapter.execute(query);
// {
//     columns: ['"user"."id"', '"user"."name"', '"r5_realm"."name"'],
//     where: '("user"."age" >= $1 and ...)',
//     params: [18, ...],
//     orderBy: ['"user"."age" DESC'],
//     limit: 25,
//     offset: 50,
//     relations: ['realm'],   // canonical paths, parents included
// }

Construct the Adapter per request — it accumulates per-call state; the shareable, long-lived part is the options object.

@rapiq/adapter-sql deliberately stops at fragments: composing the final SELECT statement — in particular FROM/JOIN conditions, which require knowledge of the table layout — is the job of the caller or a backend adapter. That's exactly what @rapiq/adapter-typeorm does for TypeORM.

Alias convention

Fragments reference joined columns through the exported buildRelationAlias(path) derivation. It length-prefixes every path segment (realm → r5_realm, role.realm → r4_role_5_realm), so role_realm and role.realm cannot collapse onto one alias. Use the same helper when rendering JOIN clauses from relations, or inject one convention through the relationAlias adapter option. Keep a custom derivation collision-free and within your database's identifier length limit.

Dotted paths & relations

A dotted field path (realm.name) references a joined relation by default: the prefix registers with the relations adapter and the fragment renders against the derived join alias. Backends where a dotted prefix is not necessarily a relation override isRelationPath(path) on the relations adapter (default: true) — segments only count as a relation path while the hook confirms them, and the remainder stays part of the column name, rendered against the parent alias (the root alias, or the last confirmed relation's join alias). The TypeORM adapter implements the hook via entity metadata so embedded column paths such as profile.firstName don't produce a bogus join.

Rendering filters standalone

The filters adapter accumulates conditions while a visitor walks the tree, then hands back SQL plus bound parameters:

import {
    FiltersAdapter, FiltersVisitor, RelationsAdapter, pg,
} from '@rapiq/adapter-sql';

const filters = new FiltersAdapter(new RelationsAdapter(), pg);

query.filters.accept(new FiltersVisitor(filters));

const [sql, params] = filters.getQueryAndParameters();
// sql:    ("name" ~* $1 and "age" >= $2)
// params: ['jo', 18]

Values are always bound as parameters — never interpolated into the SQL string.

Null semantics

A null filter value is rewritten to the SQL null predicates instead of being bound as a parameter (which would match nothing):

Filter	SQL
eq(field, null)	field IS NULL
ne(field, null)	field IS NOT NULL
in(field, [a, null])	(field IN (...) OR field IS NULL)
nin(field, [a, null])	(field NOT IN (...) AND field IS NOT NULL)

An empty list never matches: in(field, []) renders 1 = 0 and nin(field, []) renders 1 = 1 (instead of the invalid IN ()).

Negated operators are exact complements of their positive twins: a record that does not match eq(field, a) matches ne(field, a) — including records where the column is NULL. Since a bare SQL negation drops NULL rows under three-valued logic, negations render null-inclusively:

Filter	SQL
ne(field, a)	(field <> ? OR field IS NULL)
nin(field, [a, b])	(field NOT IN (...) OR field IS NULL)
notContains(field, a) (also notStartsWith / notEndsWith)	(field ~* ? OR field IS NULL)

String matching

The contains / startsWith / endsWith operators (and their negations) match their value literally on every dialect: regex metacharacters are escaped on regex-capable dialects, LIKE wildcards are escaped on the LIKE fallback. Only the regex operator interprets its RegExp or string value as a pattern. A JavaScript RegExp contributes its source and ignoreCase flag; a string is passed through unchanged so the selected database regex engine owns its syntax and validation.

The negations match rows whose column is NULL (complement law, see above) — on the LIKE fallback they render (field NOT LIKE ? ESCAPE '\' OR field IS NULL).

Case sensitivity

String equality (eq / ne / in / nin) matches case-insensitively by default. On dialects whose = is case-sensitive, both sides fold through the caseFold dialect callback — lower(field) = lower(?):

Filter	pg / sqlite / oracle	mysql / mssql
eq(field, 'a')	lower(field) = lower(?)	field = ?
in(field, ['a', 1])	lower(field) IN (lower(?), ?)	field IN (?, ?)

The mysql and mssql presets set caseFold to identity: their default collations (*_ci) already compare case-insensitively, and skipping lower() keeps plain indexes usable. Fields opted out via the caseSensitive visitor option render unfolded on every dialect:

adapter.execute(query, { visitor: { caseSensitive: ['id'] } });

On folding dialects, give hot string filter columns an expression index (CREATE INDEX ... ON "user" (lower(name))) — or opt them out.

Folding only happens for string filter values. Backends with column metadata can exempt whole columns by overriding isCaseFoldable(field) on the filters adapter (default: true) — the TypeORM adapter uses it to fold only string-typed columns.

ITSELF (element-level conditions)

The ITSELF marker — an elemMatch interior condition on the array element itself, produced e.g. by the mongo parser's element-level $elemMatch and $all — has no SQL rendering: elemMatch maps to a relation join, and a joined row is not a scalar column. Both @rapiq/adapter-sql and @rapiq/adapter-typeorm throw a typed AdapterError (ErrorCode.FEATURE_UNSUPPORTED). Dialect-level JSON-array support (json_each / unnest) may lift this later; evaluate such filters with @rapiq/adapter-memory in the meantime.

size (array length)

The size operator has no SQL rendering either — an array-length check needs per-dialect JSON-array support (json_array_length on Postgres/SQLite, JSON_LENGTH on MySQL, cardinality for Postgres arrays). Both @rapiq/adapter-sql and @rapiq/adapter-typeorm throw a typed AdapterError (ErrorCode.FEATURE_UNSUPPORTED); evaluate such filters with @rapiq/adapter-memory in the meantime.