L7 - Adapters
Translate VibORM's database-agnostic operations into dialect-specific SQL
Location: src/adapters/
Why This Layer Exists
Databases have different syntax for the same operations:
| Operation | PostgreSQL | MySQL | SQLite |
|---|---|---|---|
| Identifier quote | "column" |
`column` |
"column" |
| String concat | || |
CONCAT() |
|| |
| JSON aggregation | json_agg() |
JSON_ARRAYAGG() |
json_group_array() |
| Low-level upsert primitive | ON CONFLICT ... DO UPDATE |
ON DUPLICATE KEY UPDATE |
ON CONFLICT ... DO UPDATE |
| Boolean | true/false |
1/0 |
1/0 |
| RETURNING | RETURNING * |
Requires separate SELECT | RETURNING * |
The adapter pattern lets VibORM support multiple databases without scattering conditionals throughout the codebase.
MySQL’s low-level primitive reacts to any unique collision, so VibORM does not use it for public non-returning upserts. The query engine selects and locks the requested target, chooses the create/update branch, and refetches inside one transaction. This preserves the same target-specific public behavior as PostgreSQL and SQLite.
MySQL duplicate skipping likewise avoids INSERT IGNORE, which suppresses
unrelated failures. Top-level bulk creates execute row-level inserts inside one
transaction and recover only mapped duplicate-key errors; nested idempotent
inserts use a duplicate-key no-op update. Both paths surface foreign-key,
NOT NULL, conversion, truncation, and other integrity failures.
Adapter Interface
VibORM’s stock adapters share DatabaseAdapter (see
src/adapters/database-adapter.ts), a large dialect vocabulary organized into
namespaced groups of pure, composable SQL-fragment functions:
interface DatabaseAdapter {
raw: (sqlString: string) => Sql; // escape hatch (use sparingly)
identifiers: { escape, column, table, aliased }; // "name" vs `name`
literals: { value, null, ... }; // parameterized values
operators: { eq, neq, lt, gt, in, like, ... }; // comparison operators
expressions: { concat, coalesce, cast, ... }; // scalar expressions
aggregates: { count, sum, avg, min, max, ... }; // aggregate functions
json: { ... }; // JSON build/extract/agg
arrays: { ... }; // array ops (native or JSON)
orderBy: { ... }; // ORDER BY fragments
clauses: { ... }; // WHERE/LIMIT/... clauses
set: { ... }; // UPDATE SET fragments
filters: { ... }; // filter helpers
subqueries: { ... }; // correlated subqueries
assemble: { ... }; // final query assembly
cte: { ... }; // common table expressions
mutations: { ... }; // INSERT/UPDATE/DELETE/upsert
assertions: { ... }; // runtime SQL assertions
joins: { ... }; // JOIN fragments
setOperations: { ... }; // UNION and friends
vector: { ... }; // vector distance (pgvector, ...)
readonly geoPoint?: { // exact EPSG:4326 point SQL
value: (...args) => Sql;
longitude: (point) => Sql;
latitude: (point) => Sql;
equals: (point, expected) => Sql;
withinBounds: (point, bounds) => Sql;
withinPolygon?: (point, polygon) => Sql;
distance?: (left, right) => Sql;
};
result: { ... }; // result-shape hints
lastInsertId: () => Sql; // lastval()/LAST_INSERT_ID()/...
capabilities: DatabaseAdapterCapabilities; // see "Database Capabilities"
}
Final select assembly and atomic-batch reference lowering use one private
symbol-keyed adapter protocol. They are not part of viborm/adapters and cannot
be supplied as a public trust token.
External adapter customization extends PostgresAdapter, MySQLAdapter, or
SQLiteAdapter and overrides the public dialect vocabulary. A from-scratch
DatabaseAdapter implementation is not a supported V1 contract because those
private select and batch seams can only be inherited from a stock adapter.
Each group takes database-agnostic inputs and returns dialect-specific Sql
fragments. The query engine composes fragments, but it does not inspect their
rendered SQL to recover semantic facts.
If a later compiler fold needs to know a fact such as the target table, whether
an insert skips duplicates, or whether an identity is database-assigned, L6
carries that fact beside the Sql fragment. It does not recognize PostgreSQL,
MySQL, or SQLite tokens. Provider error codes and message recognition belong to
driver error mapping.
Available Adapters
| Adapter | Database |
|---|---|
PostgresAdapter |
PostgreSQL |
MySQLAdapter |
MySQL 8.0+ |
SQLiteAdapter |
SQLite 3.35+ |
Database Capabilities
The capabilities group (DatabaseAdapterCapabilities, in
src/adapters/adapter-capabilities.ts) lets the query engine branch on
database features instead of dialect names. The adapter sets the dialect’s
flags; a driver may set the ones its transport decides (the last six rows).
| Capability | PostgreSQL | MySQL | SQLite | What the engine does with it |
|---|---|---|---|---|
supportsReturning |
✅ | ❌ | ✅ | A write answers from its own RETURNING; without it, a locked re-read |
supportsCteWithMutations |
✅ | ❌ | ❌ | A write and the read of its answer in one WITH statement |
supportsFullOuterJoin |
✅ | ❌ | ❌ | The engine builds no full outer join; the adapter’s full-join helper refuses where false |
supportsLateralJoins |
✅ | ✅ | ❌ | A recursive projection reads its walk in a lateral derived table, re-run per row; SQLite runs the correlated CTE as written |
supportsVector |
with pgvector | ❌ | ❌ | Vector distance operators |
supportsUpsertWhere |
✅ | ❌ | ✅ | ON CONFLICT … WHERE |
supportsTargetedUpsert |
✅ | ❌ | ✅ | ON CONFLICT (cols) arbitrates on those columns only; MySQL’s ON DUPLICATE KEY fires on any unique, so it takes the select-then-write path. Also gates merging junction links into one INSERT |
supportsMutationTargetInSubquery |
✅ | ❌ | ✅ | False: relation filters in an UPDATE/DELETE are wrapped in a derived table (MySQL error 1093) |
supportsMutationRowLimit |
❌ | ✅ | ❌ | updateMany/deleteMany limit as a native LIMIT, else a key subquery |
supportsRowLocks |
✅ | ✅ | ❌ | SELECT … FOR UPDATE; SQLite locks the database, so lock-only reads are skipped |
locksMissingKeys |
❌ | ✅ | ❌ | InnoDB’s gap lock on a miss: a probe whose other arm inserts reads unlocked and is confirmed under lock once it finds a row |
joinsJunctionFilters |
"unbounded" |
❌ | ✅ | Many-to-many filters join the junction to the target (on PostgreSQL only in unbounded reads, counts and writes) |
boundsRecursionIterations |
❌ | ✅ | ❌ | A recursive filter keeps its walk in place (MySQL’s cte_max_recursion_depth) |
readsNodesByKey |
✅ | ❌ | ❌ | A recursive projection that states recurse.fewRows reads each node it reached by key, where PostgreSQL would hash the whole table to join it to the walk |
sharesRelationBodies |
every PostgreSQL driver | ❌ | D1 | A repeated relation-filter body is stated once in a WITH (sharedRelationBodies: false turns it off) |
relationsPerStatement |
option (Neki: 256) | — | D1: 256 | Over the bound, recursive walks become reaching sets |
compoundSelectTerms |
— | — | D1: 5 | Longer UNIONs nest in derived tables |
boundsExpressionDepth |
— | — | deepExpressions: false |
Relation filters read through derived tables SQLite flattens |
countsCascadedChanges |
— | — | D1 | The provider’s affected-row count includes rows a foreign key’s cascade changed, so updateMany/deleteMany counts and captured-mutation checks count RETURNING 1 rows instead |
When a capability is false the query engine picks a capability-specific path
(for example, correlated subqueries instead of lateral joins, or a
transactional upsert instead of ON CONFLICT ... WHERE).
Performance lists what each query type sends per database
as a result.
How Adapters Are Used
Query engine calls adapter methods instead of generating SQL directly:
// In query engine
const aggregated = adapter.json.agg(object);
// PostgreSQL adapter returns
sql`COALESCE(json_agg(${object}), '[]'::json)`
// MySQL adapter returns
sql`COALESCE(JSON_ARRAYAGG(${object}), JSON_ARRAY())`
// SQLite adapter returns (an empty aggregate is already `[]`)
sql`json_group_array(${object})`
The query engine knows the operation meaning and receives a composable fragment; the adapter alone owns its dialect spelling.
Connection to Other Layers
- L6 (Query Engine): Query engine delegates all SQL syntax to adapters
- L8 (Drivers): Adapters produce SQL that drivers execute
- L12 (Migrations): Migration drivers, not query adapters, generate database-specific DDL