Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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

Was this page helpful?