---
title: L7 - Adapters
description: Translate VibORM's database-agnostic operations into dialect-specific SQL
icon: plug
---

**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:

```typescript
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 `UNION`s 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](/docs/internals/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:

```typescript
// 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
