# database.seedsDialects

> Six SQL backends, one schema DSL. Decision matrix, configuration, boot-log shape, and the cross-dialect feature parity table the framework hides for you.



---

<!-- source: en/database/seeds.md -->
## Seeds

_defineSeed — idempotent data fixtures for reference data and demo data, with fingerprint-based re-run detection and lifecycle hooks._

A **seed** answers two questions: *where does my reference data live* (countries, plan tiers, feature flags) and *where does my demo data live* (a populated dashboard on first boot). A `*.seed.ts` file default-exports `defineSeed({...})`; the framework discovers it like any other primitive and runs it **idempotently**.

```ts
// apps/api/seeds/plans.seed.ts
import { defineSeed } from '@voltro/database'

export default defineSeed({
  id:        'plans',
  name:      'Subscription plan tiers',
  lifecycle: 'boot',
  steps: ({ step }) => [
    step('upsert plans', async ({ upsertByUnique }) => {
      for (const p of [
        { id: 'free', name: 'Free',  priceCents: 0 },
        { id: 'pro',  name: 'Pro',   priceCents: 2900 },
      ]) {
        await upsertByUnique('plans', { id: p.id }, p)
      }
    }),
  ],
})
```

## Idempotency is the whole point

Seeds are meant to be safe to re-run. The `upsertByUnique(table, matchFields, fullRow)` helper does "row matching `matchFields` exists? update it : insert it", returning `{ row, created }`. Use stable string ids (not random tokens) so subsequent runs match the same rows instead of duplicating.

The runner also fingerprints each seed by hashing its source. On a `boot`-lifecycle seed it only re-runs when the fingerprint changes — so an unchanged seed doesn't re-execute on every `voltro dev` restart. The record lives in `_voltro_seeds`, one row per seed. If the runner cannot write it, it says so at **warn** level and names the seed — a ledger that silently fails to record looks exactly like a working one whose seeds all changed, so it is not something to find out from a debug stream.

Two things the skip deliberately does **not** do. A **failed** run is recorded as failed and never satisfies the skip, so a broken seed retries on the next boot instead of disabling itself permanently. And if the ledger cannot be read at all — unmigrated database, memory store, missing table — every boot seed **runs**: re-doing idempotent work costs time, whereas skipping data restoration on a database we could not inspect costs data.

Override the fingerprint when the seed depends on external state (env vars) that should force a re-run:

```ts
fingerprint: ({ src }) => `${src}:${process.env.SEED_VERSION ?? ''}`,
```

### What a step's `ctx.store` can do

`query` (full descriptor — `order` / `take` / `skip` / `projection`), `insert`,
**`insertIgnore`**, `update`, `delete`, plus the `upsertByUnique` helper.

Reach for `insertIgnore` when restoring a snapshot: it is one statement per row
and leaves an existing row alone. `upsertByUnique` costs a read per row and
*overwrites* what it finds, which is wrong whenever the live row is newer than
the snapshot.

```ts
await ctx.store.insertIgnore('ai_models', row, { conflictColumns: ['id'] })
```

**Reads are unscoped and include soft-deleted rows** — by construction, not by
flag. A seed runs at boot with no request, so there is no subject to scope to
and nothing applies the `deletedAt IS NULL` predicate. That is why there is no
`.unscoped()` / `.withDeleted()` to reach for: a seed already sees the whole
table. If you want one tenant's rows, say so in your own predicate.

## Lifecycles

| `lifecycle` | Runs… | Requires |
|---|---|---|
| `boot` | On every `voltro dev` boot, **only if the fingerprint changed** | — |
| `manual` | Only via `voltro db seed --id <name>` or the dashboard | — |
| `onTenantCreate` | Every time a tenant namespace is provisioned, **into that namespace** | a store with namespace isolation |
| `onSchemaChange` | After a schema apply that touched a watched table (`voltro dev`'s boot auto-migrate, `voltro db apply`, `voltro migrate`) | `watchedTables` |
| `cron` | On the cron expression, through the coordinated scheduler — one firing fleet-wide | `cron` |

### `onTenantCreate`

Fires from `provisionTenantNamespace`, after the namespace DDL lands and before
provisioning resolves — so a caller that awaits it gets a tenant whose tables
**and** reference data exist, or an error. Three properties worth knowing:

- **Steps run scoped to the new namespace**, never the shared tables. A store
  without namespace isolation (sqlite, a single-schema deployment) makes the run
  **refuse** rather than fall back — the fallback would write one tenant's
  fixture into every tenant's data. On a single-namespace deployment, declare
  the seed `lifecycle: 'boot'` instead.
- **No fingerprint skip.** A new namespace has none of the data, whatever
  another tenant's run recorded. The ledger row is keyed `<seedId>@<namespace>`,
  so N tenants produce N rows in `_voltro_seeds`.
- **A failure fails provisioning.** Unlike a boot seed, which logs and lets the
  server come up, a failed tenant-create seed rejects the provisioning call — a
  tenant whose namespace exists and whose data does not, reported as success, is
  worse than a loud error. Re-run provisioning (it is idempotent) or
  `voltro db seed --id <name>` once the cause is fixed.

### `onSchemaChange`

Fires from the migration applier, so it covers every path that applies a schema:
`voltro dev`'s boot auto-migrate, `voltro db apply` (bare and `--plan`) and
`voltro migrate`. Every seed whose `watchedTables` intersect the tables the apply
actually changed runs once, after the apply.

Three properties worth knowing:

- **Strictly post-apply.** It runs after the audit row is written, so on a
  transactional dialect the DDL has already committed and the seed talks to its
  own `DataStore` rather than the migration's connection.
- **It cannot fail the migration.** The schema landed; a fixture that throws is
  logged and recorded in `_voltro_seeds`. A successful apply is never reported as
  failed because reference data did not load.
- **`voltro serve` does not run it** — for the same reason it does not run boot
  seeds. Serve never applies a schema, so there is no schema change for it to
  react to; the pre-deploy `voltro db apply` is where it happens.

> This seam was **declared and never called** until 0.34.0: the hook installed,
> the seeds were discovered, listed and ledgered, and nothing ran. If you
> declared an `onSchemaChange` seed before that release, expect it to fire on
> your next apply.

### `cron`

`cron` seeds are projected into real schedules — the same coordinated scheduler
`*.cron.tsx` uses, so one firing happens fleet-wide instead of one per replica.
Both `voltro dev` and `voltro serve` register them at boot and log the schedule
names they created.

The schedule is named `seed:<id>`, which is also the name its runs appear under
in `_voltro_schedule_runs` and in the dashboard — distinct from a `*.cron.tsx`
namespace, so a schedule and a seed may share an id without colliding.

A cron firing runs the seed **unconditionally**. The fingerprint skip that makes
a `boot` seed cheap does not apply here: the trigger is the clock, not a change
in the source.

```ts
defineSeed({
  id: 'refresh-search-index',
  name: 'Rebuild search index nightly',
  lifecycle: 'cron',
  cron: '0 3 * * *',              // required for lifecycle: 'cron'
  timezone: 'Europe/Berlin',      // optional; defaults to an explicit 'UTC'
  steps: ({ step }) => [ /* … */ ],
})
```

`timezone` is defaulted rather than required (unlike `defineSchedule`'s, where
it is mandatory) — reference data rarely cares about a local wall clock. The
default is an explicit `'UTC'`, never the container's clock.

`defineSeed` validates at definition time: `cron` lifecycle without a `cron` field throws, `onSchemaChange` without `watchedTables` throws, and a seed with zero steps throws. The cron EXPRESSION is validated at boot, so a typo fails the boot naming the seed rather than never firing.

> **An app with any `*.seed.ts` gets the two schedule ledger tables**
> (`_voltro_schedule_runs`, `_voltro_schedule_claims`), even without a cron seed.
> Whether a seed is a cron seed is a field INSIDE the file, and `voltro migrate`
> never imports your modules — so the table set is decided from the FILENAME, and
> it has to be decided identically by `voltro dev`, `voltro serve`, `voltro db
> apply` and `voltro migrate` or the schema fingerprint diverges. Two empty ledger
> tables is the price of that agreement.

## Steps

`steps` is a factory `({ step }) => SeedStep[]`. Each step is a named async function; the runner executes them as a workflow, so a long seed is observable and resumable. The step context gives you:

- `store` — the typed `DataStore` (`query` / `insert` / `update` / `delete`).
- `upsertByUnique(table, matchFields, fullRow)` — the idempotent upsert above.
- `progress(done, total)` — emit progress for a long-running step (wired through the workflow's progress stream).

```ts
steps: ({ step }) => [
  step('import countries', async ({ upsertByUnique, progress }) => {
    const rows = COUNTRIES
    let i = 0
    for (const c of rows) {
      await upsertByUnique('countries', { iso: c.iso }, c)
      progress(++i, rows.length)
    }
  }),
]
```

## Running seeds

```bash
voltro db seed                    # run all boot-lifecycle seeds (forced, ignores fingerprint)
voltro db seed --id plans         # run one seed by id
voltro db seed --lifecycle manual # run all seeds of a lifecycle
voltro db seed --store memory     # explicit opt-in: in-memory smoke run
```

> `voltro db seed` runs against the **same store the app runs on** — resolution is `DB_DIALECT` env → `STORE` env → `app.config.ts`'s `store` → `postgres`. Rows persist to the real database. Seeding to memory by default was a silent data-loss footgun (rows written, process exits, nothing persists), so it's no longer the default — pass `--store memory` for the rare smoke-test case where you genuinely want an in-memory run.

## Reference data vs demo data

- **Reference data** (must exist in every environment): `lifecycle: 'boot'`, idempotent via `upsertByUnique`. Safe in production.
- **Demo data** (populate a fresh dashboard): `lifecycle: 'boot'` in dev, or `lifecycle: 'manual'` so it's operator-triggered and never auto-runs in prod.
- **Per-tenant starter data** (a new tenant's default categories, roles, settings): `lifecycle: 'onTenantCreate'`, which runs scoped to the tenant's own namespace as it is provisioned.

See [migrations](/docs/database/migrations) for schema changes — seeds populate data, migrations shape the tables.



---

<!-- source: en/database/dialects/index.md -->
## SQL dialects

_Six SQL backends, one schema DSL. Decision matrix, configuration, boot-log shape, and the cross-dialect feature parity table the framework hides for you._

**Reactivity is dialect-independent.** Every table is reactive by default on all
six backends — you write nothing to get it, and `.nonReactive()` turns it off
for one table everywhere. What differs is only the TRANSPORT that carries a
change between instances: Postgres uses LISTEN/NOTIFY, MySQL and MariaDB read
the binlog, MSSQL uses Change Tracking, and SQLite / Turso have no native one —
[`@voltro/plugin-broadcast`](../multi-replica) closes that gap.

Voltro runs on six SQL backends. The application code — schema, queries, workflows, subscriptions, the reactive engine — is written ONCE and compiles down to the dialect-native idiom at runtime. Selecting a dialect is a single environment variable.

```
postgres  ·  mysql 8+  ·  mariadb 10.6+  ·  mssql 2019+  ·  sqlite 3.38+  ·  turso (beta)
```

This index covers the cross-cutting bits: decision matrix, configuration, boot log, and the parity table. Each dialect has its own page below for the runtime-specific surface (driver quirks, framework workarounds, performance notes).

> **Hosting providers** — running on a managed database (Supabase, Neon, Vercel Postgres, Railway, Render, Fly.io, AWS RDS, DigitalOcean, Timescale, CockroachDB, PlanetScale, Azure SQL)? See [Database hosting](../providers) for per-provider connection strings, pooling, and — above all — how to enable CDC on each one.

> **Multi-replica reactivity** — behind a load balancer, how does a write on one replica reach clients on another? Postgres / MariaDB fan out natively; for every other dialect, [`@voltro/plugin-broadcast`](../multi-replica) (Redis / NATS) closes the gap. See [Multi-replica reactivity](../multi-replica).

- [Postgres](./postgres) — the reference. LISTEN/NOTIFY CDC, advisory locks, streaming replication.
- [MySQL 8+](./mysql) — INSERT-then-SELECT instead of RETURNING, `LAST_INSERT_ID()` recovery for AUTO_INCREMENT ids, binlog CDC (cross-instance reactivity, at parity with mariadb), mysql2 driver quirks.
- [MariaDB 10.6+](./mariadb) — same wire driver as MySQL but UPDATE-RETURNING gap + ROW_NUMBER eager-load + binlog CDC (cross-instance reactivity, one shared reader with mysql).
- [MSSQL 2019+](./mssql) — TOP / OFFSET-FETCH instead of LIMIT, single-statement OUTPUT INSERTED/DELETED, native MERGE upsert, Change Tracking CDC, four upstream cluster patches.
- [SQLite 3.38+](./sqlite) — single-process, in-memory workflow runner, in-process CDC bus.
- [Turso (beta)](./turso) — the Rust rewrite of SQLite with MVCC concurrent writes (`BEGIN CONCURRENT` over a connection pool). SQLite-compatible; single-node; no generated columns / FTS; no Alpine/musl prebuilts.

## Decision matrix

Pick a dialect by intersection of need + constraint.

| Need                                    | Pick     | Why |
|-----------------------------------------|----------|-----|
| Sub-second reactive UIs at scale        | postgres / mariadb / mssql | Native `LISTEN/NOTIFY` (postgres), ROW-format binlog CDC (mariadb), or Change Tracking (mssql, polled) deliver cross-instance change events with zero extra infra. mysql / sqlite have no native fan-out — add [`@voltro/plugin-broadcast`](../multi-replica) (Redis / NATS) for cross-instance reactivity, or they degrade to single-instance. |
| Multi-instance horizontal scaling       | postgres / mysql 8+ / mariadb 10.6+ / mssql | Cluster workflow runners need a coordinatable lock primitive (advisory locks, GET_LOCK, sp_getapplock). SQLite is single-process by design. |
| MySQL-shop procurement                  | mysql 8.0.18+ | Mysql 8.0.0–8.0.17 rejects parameterized `LIMIT ?` — framework auto-inlines integers but other tooling may break. |
| MariaDB-shop procurement                | mariadb 10.6+ | Same wire driver as mysql. The framework's dialect dispatcher branches on a few quirks (`UPDATE … RETURNING`, JSON-agg LATERAL gap). |
| Enterprise SQL Server                   | mssql 2019+ | 2016/2017 work but lose query-optimizer shortcuts for `OFFSET … FETCH NEXT`. |
| Single-binary distribution (dev tool, embedded device, CLI) | sqlite | No cluster scale-out: workflows run with `runnerStorage: 'memory'`. Durable replay within ONE process. |
| Single node that needs CONCURRENT writes (beta) | turso | The Rust SQLite rewrite: a connection pool + MVCC `BEGIN CONCURRENT` runs concurrent writers instead of sqlite's single-writer lock. Beta; no generated columns / FTS; glibc-only binaries. |
| Zero infrastructure for smoke tests     | `store: 'memory'` | In-process map. Resets on restart. |

## Configuration

Two sources, in priority order: env > `app.config.ts`.

```sh
DB_DIALECT=postgres   # default. Maps to @effect/sql-pg.
DB_DIALECT=mysql      # @effect/sql-mysql2, variant='mysql'.
DB_DIALECT=mariadb    # @effect/sql-mysql2, variant='mariadb'.
DB_DIALECT=mssql      # @effect/sql-mssql.
DB_DIALECT=sqlite     # @effect/sql-sqlite-node. DB_URL=file:./db.sqlite or :memory:
DB_DIALECT=turso      # @voltro/sql-turso (Rust rewrite, MVCC). DB_URL=file:./db.turso or :memory:; DB_MAX_CONNECTIONS = pool size. BETA.
DB_DIALECT=memory     # in-process DataStore, no SQL.
```

Connection details come from the **same `DB_*` vars for every dialect** — there are no per-dialect `MYSQL_*` / `MARIADB_*` / `MSSQL_*` vars:

```sh
# One URL…
DB_URL=mysql://app:app@db.internal:3306/app

# …or discrete fields (any dialect)
DB_HOST=… DB_PORT=… DB_USER=… DB_PASSWORD=… DB_DATABASE=…

# Pool size (any dialect)
DB_MAX_CONNECTIONS=10

# SQLite / Turso take a file URL
DB_URL=file:./db.sqlite     # or `:memory:` for ephemeral
```

`PG_HOST` / `PG_PORT` / `PG_USER` / `PG_PASSWORD` / `PG_DATABASE` / `PG_MAX_CONNECTIONS` are accepted as postgres-flavoured aliases for the same fields; `DB_SCHEMA` (postgres `search_path`) and `PG_SSL` are postgres-only knobs.

The `app.config.ts` `store:` field stays as the dev-friendly shortcut (`store: 'postgres'`, `store: 'memory'`) — env always wins.

### Query timeout — bound a runaway query

```sh
DB_STATEMENT_TIMEOUT_MS=30000   # cancel any single query after 30s
```

A missing index or an accidental cartesian join can run for minutes, and while it does it **pins a pooled connection**. Enough of them under load and the pool is exhausted — every other request now waits on a connection that will never free, and the whole app stalls. `DB_STATEMENT_TIMEOUT_MS` puts a ceiling on it: a query that outlasts the deadline is cancelled, its connection returns to the pool, and the caller gets a normal error instead of a hang.

It applies to the **runtime query path only**. Migrations (`voltro db apply`) run legitimately long statements — backfills, index builds — and are **never** cancelled by it. (Caveat: `voltro dev`'s boot auto-migrate shares the app connection, so a very slow dev migration under a low timeout would trip it — raise the value, or run `voltro db apply` first.)

**Wired for postgres today** (the default dialect), where it maps to the server-side `statement_timeout` — a real, server-enforced cancel (SQLSTATE `57014`), not a client-side disconnect that leaves the query running. Other dialects accept the variable but currently ignore it, and the reasons are honest rather than incidental: `@effect/sql-mssql` exposes only a connection-establishment timeout, not a per-request one; MySQL/MariaDB's `max_execution_time` bounds `SELECT`s only (writes stay unbounded), which would be a misleading half-guarantee; and SQLite is in-process with a single connection, so there is no pool to protect. Unset (or any non-postgres dialect) = no timeout.

### Acquire timeout — bound the wait for a free connection

`DB_STATEMENT_TIMEOUT_MS` above bounds a query the **server** is running. Nothing bounded a query the client had not sent yet: a request arriving when every pooled connection is busy waited — with no error, no retry and no log line — until something else finished. Those are the two halves of "a request is stuck", and only one of them was covered.

Every dialect that can bound an acquire now does, **by default**:

| Dialect | Default | What is bounded |
|---|---|---|
| postgres | 10 s | the whole acquire — waiting in the pool's queue *and* connecting |
| mysql / mariadb | 10 s connect · 100 queued waiters | connecting; the wait is bounded by queue **length**, see below |
| mssql | 10 s | connecting (and the boot probe) |
| sqlite / turso | — | one in-process connection, no pool to exhaust |

An exhausted pool now fails with an error that **names the pool** — `Failed to acquire connection` — at the moment the pool is the cause, instead of surfacing as unexplained latency somewhere with no connection information in it.

Move it with `DB_ACQUIRE_TIMEOUT_MS`, in milliseconds. `0` restores the driver's unbounded wait; a negative or non-numeric value falls back to the 10 s default rather than to no bound at all:

```sh
DB_ACQUIRE_TIMEOUT_MS=3000     # 0 = the driver's unbounded wait
DB_ACQUIRE_QUEUE_LIMIT=2000    # mysql / mariadb only, OPT-IN — read the hazard below
```

Both are read on every command that opens a pool — `voltro dev`, `voltro serve`, `voltro migrate` and every `voltro db …` subcommand alike — so the variable means one thing across your deployment. (`DB_STATEMENT_TIMEOUT_MS` above is deliberately runtime-only: a migration runs legitimately long *statements*. An acquire bound fires when no connection is free at all, which a migration has no more reason to wait forever for than a request does.)

That "every command" is literal, and it is worth stating because it has not always been true. Every pool the CLI opens gets its configuration from **one** resolver, so a knob cannot be honoured by one command and ignored by the next. The only two things the command changes are the two named here: `DB_STATEMENT_TIMEOUT_MS` (runtime only) and `DB_DIRECT_URL` / `DB_MIGRATE_URL` (migration only). Everything else — pool size, `DB_SCHEMA`, TLS (`PG_SSL`), the acquire bounds — resolves identically everywhere, including in the two places that open a bare postgres client rather than a pool (the web process's ISR cache and its CDC listener).

The same value is `ConnectionConfig.acquireTimeoutMs` when you build a layer yourself:

```typescript
import { postgresDialect } from '@voltro/sql-postgres'

const layer = postgresDialect.makeSqlLayer({
  url: process.env.DB_URL!,
  acquireTimeoutMs: 3_000,   // 0 = the driver's unbounded wait
})
```

**mysql/mariadb cannot express a time bound on the waiting half, and the length bound that exists is opt-in.** `mysql2`'s pool queues the waiting caller with no timer at all, so there is nothing to set. The one bound the driver has is a LENGTH — `ConnectionConfig.acquireQueueLimit` / `DB_ACQUIRE_QUEUE_LIMIT`, mysql2's `queueLimit`. **The framework does not set it for you**, and the reason is worth understanding before you do:

A time bound self-throttles. The acquire fails only once the wait has elapsed, so nothing can retry it faster than the timeout. A length bound is free: past the limit mysql2 rejects the acquire *synchronously*, so any caller that retries a failed acquire without a delay retries in the same tick — forever. The event loop is never reached again, which means no timer fires and the queue whose depth caused the rejection can never drain. The symptom is a process pinned at 100% CPU with no error, no log line, and an idle database.

That caller is not hypothetical: the workflow cluster releases shards one statement per shard (300 by default) and retries a failed release immediately. So set `DB_ACQUIRE_QUEUE_LIMIT` only if you know nothing in your process retries an acquire without backoff, and set it well above the peak concurrency of anything that might. Unset — the default — you get mysql2's unbounded queue: callers wait rather than fail. mssql's pool exposes neither knob, so only establishment is bounded there — stated rather than papered over, for the same reason `DB_STATEMENT_TIMEOUT_MS` is left unwired on the dialects that cannot enforce it honestly.

## Local development — bring up all five

The framework ships a docker-compose at `voltro/test/docker-compose.yml` that brings up postgres + mysql + mariadb + mssql on distinct ports so per-dialect tests can run side-by-side and the dev fixture never clashes with your starter postgres on `:5432`:

```sh
cd voltro
docker compose -f test/docker-compose.yml up -d --wait
```

| Service       | Host port |
|---------------|-----------|
| postgres-test | `:55432`  |
| mysql-test    | `:33060`  |
| mariadb-test  | `:33061`  |
| mssql-test    | `:11433`  (database `voltro_test`) |

SQLite needs no container — point `DB_URL=:memory:` for an ephemeral in-process DB.

## Boot log

`voltro dev` and `voltro start` print a one-line summary of the resolved dialect + its enabled capabilities so `voltro logs --tail 50` answers "what's running" without grepping source:

```
[voltro:dev] sql dialect resolved: postgres — CDC: LISTEN/NOTIFY, RETURNING: native
[voltro:dev] read replicas: 0 configured (DB_REPLICA_URLS empty) — all queries → primary
[voltro:dev] workflow engine: cluster-sql, dialect=postgres
```

For sqlite:

```
[voltro:dev] sql dialect resolved: sqlite — CDC: in-process bus, RETURNING: native
[voltro:dev] workflow engine: in-process durable (storage=memory) — no horizontal scale-out
```

## Feature parity matrix

What the framework hides for you vs what's worth knowing. Per-dialect pages drill into each row.

| Capability                                | postgres        | mysql 8+        | mariadb 10.6+   | mssql 2019+      | sqlite 3.38+       | turso (beta)       |
|-------------------------------------------|-----------------|-----------------|-----------------|------------------|---------------------|--------------------|
| CDC (change feed)                         | LISTEN/NOTIFY | binlog CDC (ROW) | binlog CDC (ROW) | Change Tracking (polled) | in-process bus | in-process bus |
| Concurrent writers (one node)             | yes (MVCC)      | yes (InnoDB)    | yes (InnoDB)    | yes              | **no** — single-writer lock | **yes** — MVCC `BEGIN CONCURRENT` |
| Workflow cluster runners                  | advisory_lock   | GET_LOCK        | GET_LOCK        | sp_getapplock     | **n/a** — single process | **n/a** — single process |
| Read-replica routing                      | streaming repl  | GTID repl       | GTID repl       | Always-On AG     | **n/a** — single writer | **n/a** — single node |
| `RETURNING *` on INSERT                   | yes             | no (INSERT then SELECT) | yes (10.5+) | OUTPUT INSERTED.*| yes | yes |
| `RETURNING *` on UPDATE                   | yes             | no              | **NO** (any version) | OUTPUT INSERTED.*| yes | yes |
| `RETURNING *` on DELETE                   | yes             | no              | yes (10.0+)     | OUTPUT DELETED.* | yes | yes |
| Parameterized `LIMIT ?`                   | yes             | no — integer-literal inlined | yes | no — integer-literal inlined | yes | yes |
| `LIMIT N OFFSET N` syntax                 | yes             | yes             | yes             | no — `OFFSET … ROWS FETCH NEXT … ROWS ONLY` | yes | yes |
| DEFAULT on TEXT columns                   | yes             | **NO** — auto-uses VARCHAR(255) | yes | yes (NVARCHAR(MAX)) | yes | yes |
| Native JSON column type                   | JSONB           | JSON            | JSON            | NVARCHAR(MAX)    | TEXT                | TEXT               |
| JSON columns returned as objects          | yes             | yes             | yes             | **no — strings** — framework auto-parses | **no — strings** — framework auto-parses | **no — strings** — framework auto-parses |
| Booleans                                  | proper booleans | 0/1 (TINYINT)   | 0/1             | BIT (proper bool) | 0/1 (INTEGER)     | 0/1 (INTEGER)      |
| Auto-incrementing numeric id              | BIGSERIAL       | AUTO_INCREMENT  | AUTO_INCREMENT  | IDENTITY         | INTEGER PK AUTOINCREMENT | INTEGER PK — no AUTOINCREMENT |
| Identifier quoting                        | `"name"`        | `` `name` ``    | `` `name` ``    | `[name]`         | `"name"`            | `"name"`            |

## Dialect-aware code

You almost never need to write dialect-branching SQL — the schema DSL is portable. When you reach for `sql.unsafe()` or hand-written queries, branch via `sql.onDialectOrElse`:

```typescript
import { SqlClient } from '@effect/sql'

const fetchTopN = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient
  return yield* sql.onDialectOrElse({
    pg: () => sql<{ id: string }>`SELECT id FROM users LIMIT 10`,
    mysql:    () => sql<{ id: string }>`SELECT id FROM users LIMIT 10`,  // mariadb rides this branch
    mssql:    () => sql<{ id: string }>`SELECT TOP 10 id FROM users`,
    orElse:   () => sql<{ id: string }>`SELECT id FROM users LIMIT 10`,
  })
})
```

The `orElse` branch catches sqlite + future dialects.

## Migration between dialects

Switching `DB_DIALECT` is a deploy-time decision, not a runtime one. Migrating data from postgres to mysql (or vice versa) is a separate problem the framework doesn't solve — use `pg_dump` / `mysqldump` + a transform script. The schema you wrote against the framework's DSL re-applies cleanly on the new dialect (`voltro migrate` re-emits in the new idiom); production data does not.

## Where the dialect lives in the codebase

- `voltro/packages/database/src/dialect.ts` — `SqlDialect` interface
- `voltro/packages/database/src/jsonEagerCompiler.ts` — per-dialect JSON-agg emission
- `voltro/packages/database/src/sqlCompiler.ts` — dialect-aware SELECT compile (TOP / LIMIT / FETCH NEXT)
- `voltro/packages/database/src/migrate.ts` — DDL emitter, identifier quoting, column-type mapping
- `voltro/packages/database/src/rowDecoder.ts` — read-path coercion for booleans / JSON / dates
- `voltro/packages/sql-postgres` / `sql-mysql` / `sql-mssql` / `sql-sqlite` — per-dialect store implementations + retry filters + CDC sources
- `voltro/packages/cli/src/dev.ts` `buildStore()` — dialect resolution from env

Each per-dialect package exports a `SqlDialect` value (`postgresDialect`, `mysqlDialect`, `mariadbDialect`, `mssqlDialect`, `sqliteDialect`). The CLI dynamically imports just the one the deployment needs — postgres-only deploys don't pull mysql / mssql into the bundle.



---

<!-- source: en/database/dialects/postgres.md -->
## Postgres

_The reference dialect. LISTEN/NOTIFY for low-latency CDC, advisory locks for cluster workflow runners, streaming replication for read replicas, JSONB for native JSON columns._

Postgres is the framework's reference dialect — every reactive feature was prototyped against it and the others were brought to parity. If you have no procurement constraint, this is the dialect that gives you the lowest latency + highest feature density without workarounds.

## Why it's the default

- **LISTEN/NOTIFY** delivers change events with very low latency end-to-end. mariadb gets cross-instance CDC too (tailing the ROW-format binlog); mysql/mssql are inline-only (the writing instance emits its own deltas — single-instance reactivity); sqlite uses an in-process bus.
- **Advisory locks** (`pg_advisory_lock(key)`) give `@effect/cluster`'s `SqlRunnerStorage` a coordinatable primitive for shard ownership. Workflow runners migrate cleanly on instance loss; no manual recovery needed.
- **Streaming replication** is the basis of the framework's read-replica adapter — LSN positions track replica freshness for the per-subject RYW (read-your-writes) policy.
- **JSONB** stores `json()` columns natively; the driver parses on read, the framework never sees serialized strings.
- **`BIGSERIAL` / `GENERATED ALWAYS AS IDENTITY`** for numeric auto-increment without surprises.
- **`TIMESTAMPTZ`** keeps timezone offset on the wire; the framework's Date columns round-trip without ambiguity.

## Configuration

```sh
DB_DIALECT=postgres
PG_HOST=…  PG_PORT=…  PG_USER=…  PG_PASSWORD=…  PG_DATABASE=…

# Recommended for any deployment that uses CDC, replicas, or BOTH:
# `wal_level=logical` is required for logical-replication slots.
# `max_replication_slots ≥ 4` so the framework can open one per
# replica + leave headroom for ad-hoc pg_recvlogical sessions.
```

`wal_level=logical` is required only for logical-replication consumers (the CDC replication adapter opens a slot). The LISTEN/NOTIFY reactivity path doesn't need WAL at all — it works on the default `wal_level`. If you enable replication and the slot can't open, the driver error surfaces the misconfiguration.

## Driver: `@effect/sql-pg`

The framework wraps `pg` (`node-postgres`) via Effect's typed connection layer. Key settings the framework defaults sensibly + you can override:

| Env var               | Default | What it does |
|-----------------------|---------|--------------|
| `PG_HOST`             | `localhost` | hostname |
| `PG_PORT`             | `5432`  | port |
| `PG_USER`             | `app`   | role |
| `PG_PASSWORD`         | `app`   | password (use a secret manager in prod) |
| `PG_DATABASE`         | `app`   | database name |
| `PG_MAX_CONNECTIONS`  | `10`    | pool ceiling. Tune up for high concurrency; CPU-bound workloads rarely benefit past ~2× cores. |
| `PG_SSL`              | unset   | `'require'` (TLS without certificate verification — what production deployments behind RDS / Cloud SQL / Supabase want) / `'disable'` (force plaintext). Any other value — including libpq's `'prefer'`, which node-postgres cannot express (the driver has no TLS-then-plaintext fallback) — fails at boot instead of silently downgrading to plaintext. A `?sslmode=require\|disable` query on `DB_URL` works too; an explicit `PG_SSL` wins when both are set. |

`PG_SSL` is read by **every** command that opens a connection — `voltro dev`, `voltro serve`, `voltro start`, `voltro migrate` and every `voltro db …` subcommand — so TLS is not something one command negotiates and the next one skips.

## CDC — LISTEN/NOTIFY

Every table gets an AFTER trigger that emits a `framework_changes` notification on each insert / update / delete. The dispatcher LISTENs on that channel once per process and fans out to subscribers in-memory.

**Every table gets this by default.** Reactivity is what the framework is for,
so you write nothing to opt in — the trigger above is installed for every table
unless you opt it out.

`.nonReactive()` turns reactivity **off** for a table — not "off across
instances". It emits no change events at all: no local subscriber fires and no
cross-instance transport carries it. That holds on every dialect, including
sqlite and turso, because the guard sits at each store's emit. On postgres it
additionally drops the trigger and `REPLICA IDENTITY FULL`; on mysql/mariadb and
mssql it drops the table from the reader's filter.

The write itself is unaffected — this is about notification, never persistence.

Worth using for a genuinely hot table nobody subscribes to: an append-only event
log, a metrics sink. `REPLICA IDENTITY FULL` widens every UPDATE/DELETE in the
WAL and the trigger fires on every write, so that is a real saving.

Do **not** use it on a table a query reads — that subscription will never fire.
`voltro dev` and `voltro serve` warn at boot when that combination exists.

Latency: very low on a local-network postgres (it varies with network and load). The framework instruments this — `voltro traces` shows the notification → dispatcher → subscriber waterfall.

Trade-offs of the LISTEN/NOTIFY path:

- ✅ Sub-frame latency (60fps UIs feel real-time).
- ✅ Zero polling load even on idle tables.
- ❌ Requires a long-lived connection per process. Connection-pooled deploys must use a sidecar listener or pgbouncer in session mode.
- ❌ NOTIFY payloads are capped at 8000 bytes. The trigger sends the full row images (`row_to_json(OLD)`/`row_to_json(NEW)`) so subscribers get the pre/post values directly; a wide row that would exceed the cap falls back to a key-only notification. See the delivery guarantee below — the fallback is not free, and it is not the same for a subscription as it is for a tap.

Set `CDC=0` to disable + force the inline-emit path (single-process only, no cross-process fan-out). Useful for tests + single-binary deploys.

### Oversized rows — what is guaranteed

A row whose JSON image exceeds ~8000 bytes (a document, a large `json()` column,
an embedded array) cannot travel in a NOTIFY payload. The trigger keeps the
change and the **primary key**, drops the images, and the CDC consumer re-reads
the row from the database before the event reaches anything. What each consumer
gets:

| Change | What is delivered | `event.oversized` |
|---|---|---|
| insert / update | the row **re-read from the database** | `'rehydrated'` |
| delete | the **primary key only** — a tombstone | `'tombstone'` |
| key missing, re-read failed, or the row is already gone | both images null | `'unrecovered'` |

Read the marker before you treat an image as a snapshot. Three limits are real
and cannot be engineered away:

- **A re-read returns the row as it is NOW.** If a second write lands between
  the change and the re-read, this event carries the newer state — and the
  second change delivers it again. The stream is convergent, not
  point-in-time. Postgres keeps no copy of an image the transport dropped.
- **`old` is null on an oversized update, and pk-only on an oversized delete.**
  There is nowhere to read a pre-image from. A tombstone is enough to REMOVE the
  row from a search index, an analytics mirror or a CDC stream; it is not a
  record of what the row contained, and `@voltro/plugin-row-history` writes
  `data: null` for one rather than a fabricated empty snapshot.
- **`'unrecovered'` means the content is gone.** No retry can bring it back —
  it was never delivered. Subscriptions are unaffected (they re-query); taps
  miss that row until the next write to it or a re-seed.

Every fallback is counted as `voltro_cdc_oversized_total{outcome=…}` (scrapeable
via `@voltro/plugin-prometheus` at `/metrics`, or `GET
/_voltro/inspect/metrics`), the first one per table is logged at `warn`, and
every `unrecovered` one is logged at `error`. **Alert on
`outcome="unrecovered"`** — a non-zero rate means the deployment is losing
changes for its taps.

Tunables (options on the postgres store, or environment):

| Env | Default | Meaning |
|---|---|---|
| `VOLTRO_CDC_REHYDRATE_TIMEOUT_MS` | `5000` | total budget for recovering one oversized change. The LISTEN consumer is serial, so this also bounds how long one oversized row can hold up the change stream. |
| `VOLTRO_CDC_REHYDRATE_RETRIES`    | `2`    | re-reads after the first attempt, inside that budget. |

The re-read is issued against the schema the write landed in, so it is correct
under namespace (schema-per-tenant) isolation.

**This lives in the database, so it has to be applied.** The trigger function is
DDL: a database created before this shipped still carries the old body, which
drops the key and makes every oversized change `'unrecovered'`. `voltro db
apply` replaces it (`voltro dev` reports it at boot as trigger drift, and the
`error` log line names the same remedy).

## Workflow cluster

`@effect/cluster`'s `SqlRunnerStorage` uses `pg_advisory_lock(key)` to claim shard ownership. The framework wires this transparently — set `DB_DIALECT=postgres` + provide a SqlClient layer and `workflowEngineLayer({ runnerStorage: 'sql' })` does the rest.

Shard re-assignment on runner death: postgres releases advisory locks on session close, so a crashed runner's shards become acquirable by survivors automatically. Typical takeover time: 5–15s depending on heartbeat interval.

## Read replicas

The framework's `ReplicatedDataStore` uses `pg_last_wal_replay_lsn()` to measure replica freshness against the primary's `pg_current_wal_lsn()`. Per-subject RYW (read-your-writes) waits until the replica catches up to the primary's LSN at write time, or falls back to the primary if `RYW_POLICY=fallback`.

Enable with:

```sh
DB_REPLICA_URLS=postgresql://app:app@replica-1:5432/app,postgresql://app:app@replica-2:5432/app
RYW_POLICY=fallback   # default. 'wait' is the alternative.
RYW_TTL_MS=30000      # how long a write keeps its subject on the primary.
```

Without `DB_REPLICA_URLS` the wrapper isn't instantiated — zero overhead, every query goes to primary.

## JSON columns

`json()` columns emit `JSONB` in DDL (binary stored format, indexable via GIN, comparison/membership operators native). The driver auto-parses on read; the framework never sees strings.

If you need raw JSON (text) storage for some reason — preserving formatting, embedding non-canonical UTF-8 — drop down to `unsafe()` and emit `JSON` explicitly. Rare.

## Identifier quoting

`"users"` — double quotes. The framework emits these for every identifier when needed; user-written SQL that hand-quotes column names needs to use double quotes for portability.

## Migration emitter

`voltro migrate` against postgres uses the standard DDL idiom:

- `CREATE TABLE … IF NOT EXISTS`
- `ALTER TABLE … ADD COLUMN … IF NOT EXISTS`
- `CREATE INDEX … IF NOT EXISTS`
- FK constraints with explicit `ON DELETE` / `ON UPDATE` clauses
- `BIGSERIAL PRIMARY KEY` for numeric id columns

The `_voltro_migrations` ledger is a regular table with a unique constraint on `(id, hash)` for idempotency — re-running the same migration is a no-op.

## Known caveats

- **Long-running transactions hold ROW EXCLUSIVE locks**. The framework's `transactional()` wrapper auto-retries on serialization failures (`40001`) and deadlocks (`40P01`); if you build custom long-running flows, expect contention.
- **`pg_listen_notify` has a per-connection capacity**. The framework uses ONE dedicated LISTEN connection per process — never multiplexes through the pool.
- **`pg_advisory_lock` keys are 8-byte ints**. The framework hashes shard-id strings to int64; collisions are astronomically improbable but theoretically possible.

## Where it lives

- `voltro/packages/sql-postgres/src/store.ts` — `PostgresDataStore` implementation + LISTEN/NOTIFY consumer
- `voltro/packages/sql-postgres/src/retry.ts` — `isRetryablePgFailure` (40001 / 40P01)
- `voltro/packages/sql-postgres/src/replicationAdapter.ts` — LSN compare for RYW
- `voltro/packages/database/src/migrate.ts` — postgres DDL emission (the orElse branch)



---

<!-- source: en/database/dialects/mysql.md -->
## MySQL 8+

_Wide procurement footprint, binlog CDC for cross-instance reactivity (at parity with mariadb), no RETURNING (INSERT-then-SELECT fallback), AUTO_INCREMENT id recovery via LAST_INSERT_ID, parameterized LIMIT rejected (auto-inlined integer literals), DEFAULT on TEXT forbidden in strict mode._

MySQL 8+ is the second-most-shipped dialect because of procurement: enterprises that standardized on MySQL want to stay there. The framework provides full feature parity with postgres — including **binlog CDC for cross-instance reactivity** (mysql and mariadb share one binlog reader) — plus a handful of driver quirks worked around transparently.

## Target version

**MySQL 8.0.18 or later.** Two reasons:

1. `LIMIT ?` as a prepared-statement parameter was rejected with `ER_WRONG_ARGUMENTS` in 8.0.0–8.0.17. The framework inlines integer literals (validated as non-negative) via `sql.unsafe(String(n))` to work around this, but other tooling you connect (BI tools, ORMs) may break.
2. JSON_OBJECT + JSON_ARRAYAGG semantics stabilized in 8.0.14. The framework's JSON-agg eager-load compiler depends on these.

MySQL 5.7 is **not** supported — the JSON eager loads would fail.

## Configuration

```sh
DB_DIALECT=mysql
DB_URL=mysql://app:app@db.internal:3306/app
# …or discrete fields — the same DB_* vars every dialect uses
# (there are no MYSQL_* vars):
DB_HOST=…  DB_PORT=…  DB_USER=…  DB_PASSWORD=…  DB_DATABASE=…

# Strict mode is REQUIRED. Most modern MySQL deploys have it on by
# default; verify with `SELECT @@sql_mode;` — should contain
# `STRICT_TRANS_TABLES,STRICT_ALL_TABLES`. The framework's DDL
# emitter relies on strict-mode validation to surface schema bugs
# at migration time.
```

## TLS — honoured, or refused

Ask for TLS in the connection URL:

```sh
DB_URL=mysql://app:app@db.internal:3306/app?sslmode=require   # TLS, certificate not verified
DB_URL=mysql://app:app@db.internal:3306/app?ssl=true          # the provider-style alias — same thing
DB_URL=mysql://app:app@db.internal:3306/app?sslmode=disable   # explicit plaintext
```

`require` maps to mysql2's `{ rejectUnauthorized: false }` — the connection is **encrypted, the server certificate is not authenticated**. That is the mode managed providers expect, and it is the same meaning `?sslmode=require` has on the postgres dialect.

**Anything else fails at boot**, on purpose: `prefer`, `allow`, `verify-ca`, `verify-full`, a CA-profile name, `?ssl=yes`. mysql2 either sends an SSLRequest or it does not, so `prefer` is not expressible; and a verification mode cannot round-trip through the cross-dialect boolean `ssl` field, so honouring the word would mean quietly giving you something weaker than it names. A boot failure is the correct answer to a TLS request that cannot be satisfied.

> **This changed.** Before this release the mysql/mariadb dialect had **no TLS path at all** — `?ssl=true` was parsed off the URL and discarded, and the connection went out in plaintext with no warning. If your URL carries `?ssl=` or `?sslmode=`, that request is now real: verify your server accepts TLS before deploying.

## Driver: `@effect/sql-mysql2`

Wraps the `mysql2` driver (Node.js mysql client). The framework's `MysqlStore` opens a connection pool sized via `DB_MAX_CONNECTIONS` (default 10), with a 10 s bound on **establishing** a connection. Waiting for a busy one is **not** bounded: mysql2 has no timer for that phase, and the length ceiling it does offer (`ConnectionConfig.acquireQueueLimit`) is opt-in because mysql2 rejects past the limit synchronously — which turns any zero-delay acquire retry into an event-loop-starving spin. See the [acquire-timeout table](../dialects) for the full reasoning before you set it.

The driver returns BOOLEAN as `0|1` (TINYINT(1) is MySQL's underlying type). The framework's `decodeRowsFromSchema` post-processor converts back to `true|false` for any column the schema registry declares as `boolean()`. Without this, every reactive subscription opening on a BOOLEAN column would see `1` instead of `true` and fail framework Schema validators.

## No `RETURNING` — INSERT then SELECT

MySQL 8.x does not support `RETURNING *` on any DML statement. The framework's mysql store handles this with deterministic IDs + a follow-up SELECT:

```typescript
// What you write:
await ctx.store.insert('todos', { title: 'hello', done: false })

// What the framework does on mysql (internally — `generateId` takes the
// table's RESOLVED scheme, not a bare table name):
const id = generateId(resolveIdScheme(undefined, 'todos'), 'todos')   // TypeID auto-injection
await sql`INSERT INTO todos (id, title, done) VALUES (${id}, ${title}, ${done})`
const [row] = await sql`SELECT * FROM todos WHERE id = ${id} LIMIT 1`
return row
```

This works because the framework's mutation middleware generates IDs client-side BEFORE the insert via the schema registry's TypeID / ULID / Snowflake generator. The follow-up SELECT is `O(1)` against the PK. Performance impact: one extra round-trip per write — meaningful for very high-throughput workloads but invisible for typical app traffic.

For UPDATE: UPDATE then SELECT by the known PK. For DELETE: SELECT before delete (to capture the row for the ChangeEvent) then DELETE.

### AUTO_INCREMENT ids — `LAST_INSERT_ID()` recovery

The write-then-SELECT above relies on knowing the PK before the insert — which the framework's client-side TypeID / ULID / Snowflake auto-injection guarantees on every framework path. The escape hatch is a **hand-rolled numeric `AUTO_INCREMENT` primary key**: a table you created yourself with `id INT PRIMARY KEY AUTO_INCREMENT`, inserted WITHOUT a client-side `id`. The store recovers the DB-generated id via `LAST_INSERT_ID()`:

```typescript
// A table with a numeric AUTO_INCREMENT PK (no framework id() column):
//   CREATE TABLE counters (id INT PRIMARY KEY AUTO_INCREMENT, label VARCHAR(64))

const row = await ctx.store.insert('counters', { label: 'hits' })
row.id            // ← the DB-generated id, recovered and returned

const rows = await ctx.store.insertMany('counters', [
  { label: 'a' }, { label: 'b' }, { label: 'c' },
])
rows.map((r) => r.id)   // ← the full generated range, in input order
```

`LAST_INSERT_ID()` is connection-scoped, so the INSERT, the id read, and the post-image SELECT are pinned to one connection (a short transaction when you're not already inside one). For `insertMany`, `LAST_INSERT_ID()` returns the FIRST generated id and the engine allocates the rest consecutively, so the store re-selects the `[first, first+n-1]` range. This works identically on mysql and mariadb. (If the PK is not actually `AUTO_INCREMENT`, the insert throws a clear error rather than returning a bogus id.)

## CDC — binlog (ROW format)

MySQL has no `LISTEN/NOTIFY`, but MySQL 8's ROW-format binary log is a real out-of-band CDC source — the SAME binlog the framework already tails for mariadb. The framework tails the primary's binlog via the `@vlasky/zongji` replication client, so **every replica tails the binlog itself and a write on any instance surfaces on every instance's `onChange`** (true cross-instance reactivity in a multi-replica deploy). This is at parity with mariadb: binlog CDC is **one path** with per-variant detection, not two implementations.

**Every table is reactive by default** — nothing to opt into. `.nonReactive()`
turns it off for one table on *every* dialect: no subscriber fires, locally or
across instances. The write still happens; only the notification is suppressed.
See [the schema overview](/docs/database/overview).

### How it works

1. With `CDC=1` (the default for any SQL dialect) on `mysql`, the store starts a binlog reader on a **separate replication connection** — distinct from the SQL pool.
2. The reader subscribes to `WriteRows` / `UpdateRows` / `DeleteRows` events and turns each into a `ChangeEvent { table, op, old, new }`. UPDATE events carry both the BEFORE and AFTER row images (needs `binlog_row_image=FULL`), so `old` is richer than inline mode can produce.
3. In CDC mode the binlog reader is the **sole** emitter — the write path stays silent, so each write surfaces exactly once per instance, delivered by that instance's own reader.
4. Framework-internal `_voltro_`-prefixed tables (and any table not in the reactive set) are skipped by the reader.

### Requirements

The reader fails fast at boot if these aren't met:

- **`binlog_format=ROW`** — statement/mixed formats don't carry per-row images.
- **`binlog_row_image=FULL`** — needed for complete UPDATE/DELETE before-images (a non-FULL image logs a warning; before-images may be partial).
- **`log_bin=ON`.** MySQL 8.0+ enables this **by default** (unlike mariadb, which needs an explicit `--log-bin`), so most MySQL 8 deploys already have a binlog.
- A DB user with **`REPLICATION SLAVE, REPLICATION CLIENT`**.
- **A UNIQUE `server_id` per reader.** Duplicate `server_id`s silently break binlog streams. The framework derives one per pod from `POD_NAME` / `HOSTNAME` (falling back to the PID in dev).
- The **`@vlasky/zongji`** package. It ships as an `optionalDependency` of `@voltro/sql-mysql`; if it's absent, a `cdc` request throws a clear "install `@vlasky/zongji`" error rather than silently degrading.

### mysql-vs-mariadb difference: binlog-end query

The one engine divergence the store handles for you: **MySQL 8.4 removed `SHOW MASTER STATUS`** in favour of `SHOW BINARY LOG STATUS`. The framework resolves the current binlog end with the right statement per variant (`SHOW BINARY LOG STATUS` on mysql, `SHOW MASTER STATUS` on mariadb), falling back to the other spelling for mysql < 8.4. You don't wire anything for this — it's picked from the `variant` flag.

### Resume offsets

Each replica persists its progress in the **`_voltro_cdc_offsets`** table — one row per replica (`PK = replicaId`), holding the last binlog **`(file, position)`** it fully processed. On boot the reader resumes from that point; events between crash and resume replay and self-heal via the dispatcher's per-subscribe re-query. (Resume is by binlog file + position, not by GTID.)

If the persisted offset has been purged (`err 1236`) or rejected after a failover, the reader jumps to the current binlog end and signals a resync so dependent subscriptions re-query rather than missing the gap.

### Boot summary

```
[voltro:dev] sql dialect resolved: mysql — CDC: binlog CDC (ROW), RETURNING: INSERT/UPDATE/DELETE then SELECT (no RETURNING)
[voltro:dev] mysql binlog CDC enabled { replicaId: 'pod-0', serverId: 1234567, reactiveTables: 42 }
[voltro:dev] cdc: binlog reader attached { serverId: 1234567, from: 'current-end' }
```

Set `CDC=0` to fall back to inline-emit (single-process only, no binlog dependency) — useful for tests and single-binary deploys.

## Parameterized LIMIT — auto-inlined

`mysql2`'s prepared-statement layer rejects `LIMIT ?` with the cryptic error `ER_WRONG_ARGUMENTS`. The framework's `compileSelect` inlines integer literals across every dialect to dodge this:

```sql
-- What the framework emits:
SELECT * FROM users WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 100

-- Not what would fail:
SELECT * FROM users WHERE tenant_id = ? ORDER BY created_at DESC LIMIT ?
```

The `100` is a literal `sql.unsafe(String(100))`. The framework validates non-negative integer before emitting → zero injection surface.

User-written `unsafe()` SQL on mysql should follow the same pattern: inline integer literals via `sql.literal(String(n))` rather than `${n}` parameter binding.

## DEFAULT on TEXT columns — VARCHAR(255) fallback

MySQL (strict mode) rejects DEFAULT values on TEXT/BLOB columns with `BLOB, TEXT, GEOMETRY or JSON column 'col' can't have a default value`. MariaDB allows this; MySQL does not.

The framework's DDL emitter detects the case and switches to `VARCHAR(255)`:

```typescript
text().default('json')                     // → VARCHAR(255) DEFAULT 'json'
text().oneOf(['a', 'b', 'c']).default('a') // → VARCHAR(255) DEFAULT 'a' CHECK (col IN ('a','b','c'))
text().nullable()                          // → TEXT (unchanged — no default to trip up)
```

VARCHAR(255) is the framework's heuristic — enough for typical enum-like values, short status strings, format identifiers. If you need longer defaulted text, declare the column as `text().nullable()` + handle the missing-default case in application code, OR drop down to `unsafe()`.

## JSON columns

`json()` columns emit `JSON` (mysql's native binary JSON type since 5.7+). The driver auto-parses on read; same shape as postgres. No coercion overhead.

JSON_VALID is enforced by mysql — you can't insert non-JSON strings into a JSON column. Test fixtures that bypass the framework's validation will hit this.

## Identifier quoting

`` `users` `` — backticks. Standard MySQL. The framework's compiler emits these for every identifier; user-written `unsafe()` SQL should match.

## Workflow cluster

`@effect/cluster`'s `SqlRunnerStorage` mysql branch uses `GET_LOCK(name, timeout)` for shard claims and `ON DUPLICATE KEY UPDATE` for runner upserts. Same end-to-end behaviour as postgres; takeover on runner death takes ~5–15s.

## Read replicas

The framework's mysql replication adapter reads GTID positions on both sides (`@@global.gtid_executed` on mysql) — but the catch-up **comparison is a stub today**: `compare()` always answers `'behind'`. Under the default `RYW_POLICY=fallback` that's fine (fallback pins on RYW-position presence and never calls `compare()`); under `RYW_POLICY=wait`, a session with a pending RYW position always falls back to the primary instead of ever seeing a caught-up replica. A real `GTID_SUBSET()` round-trip is a tracked follow-up.

```sh
DB_REPLICA_URLS=mysql://app:app@replica-1:3306/app
RYW_POLICY=fallback
```

Set `gtid_mode=ON` + `enforce_gtid_consistency=ON` on every node in the topology — without GTIDs, the replication adapter can't track positions and the RYW guarantee silently degrades.

## Known caveats

- **Mysql 8.4.x raised the default `auth_plugin` to `caching_sha2_password`**. The `mysql2` driver supports it but some older Node.js builds need the `RSA-OAEP` opt-in. If you hit `ERR_OSSL_UNSUPPORTED`, create the app's database user with `mysql_native_password` on the server (there is no framework env var for the auth plugin).
- **DATETIME(6) for sub-second precision**. The framework emits this for `timestamp()` columns; lower-precision DATETIME drops microseconds and your audit logs lose ordering on fast inserts.
- **`utf8mb4_unicode_ci` collation is required** for the framework's text columns to compare correctly. `utf8` (legacy 3-byte) breaks emoji + non-BMP chars; charset checks fire at first insert.

## Where it lives

- `voltro/packages/sql-mysql/src/store.ts` — `MysqlStore` with `variant: 'mysql'`
- `voltro/packages/sql-mysql/src/retry.ts` — `isRetryableMysqlFailure` (1213 / 1205 deadlock + lock wait timeout)
- `voltro/packages/sql-mysql/src/replicationAdapter.ts` — GTID capture/probe for RYW (catch-up `compare()` is a stub)
- `voltro/packages/database/src/migrate.ts` — mysql DDL branch (line 59)
- `voltro/packages/database/src/sqlCompiler.ts` — integer-literal LIMIT inlining



---

<!-- source: en/database/dialects/mariadb.md -->
## MariaDB 10.6+

_Wire-compatible with MySQL but diverges on UPDATE-RETURNING (doesn't exist), correlated derived tables (rejected), and JSON_ARRAYAGG ORDER BY (MariaDB-only extension). Framework dispatches all three at the dialect-tag level._

MariaDB shares MySQL's wire protocol and the `@effect/sql-mysql2` driver — but the SQL surface diverges enough that the framework keeps it as a separate dialect tag (`DB_DIALECT=mariadb`). This page enumerates the differences the framework handles for you, plus the ones that bite when you reach for hand-written SQL.

## Target version

**MariaDB 10.6 or later.** Two reasons:

1. JSON_OBJECT + JSON_ARRAYAGG were added in 10.5, but `JSON_ARRAYAGG(... ORDER BY ...)` (a MariaDB-only extension the framework's eager-load compiler uses) is stable from 10.5+. ROW_NUMBER + window functions stabilized earlier (10.2+).
2. Native `INSERT … RETURNING` is in 10.5+, `DELETE … RETURNING` is in 10.0+. The framework relies on both.

10.5.x works in principle; 10.6+ gives you the long-term-support windowing semantics the framework tests against.

## Configuration

```sh
DB_DIALECT=mariadb
DB_URL=mysql://app:app@db.internal:3306/app
# …or discrete fields — the same DB_* vars every dialect uses
# (there are no MARIADB_* vars):
DB_HOST=…  DB_PORT=…  DB_USER=…  DB_PASSWORD=…  DB_DATABASE=…

# Strict mode is recommended. MariaDB defaults to ON in modern
# versions but is less aggressive about it than mysql. Verify:
# `SELECT @@sql_mode;` should contain STRICT_TRANS_TABLES.
```

## Driver: `@effect/sql-mysql2` (variant='mariadb')

Same driver as MySQL — MariaDB is wire-compatible. The framework keys behaviour on the `variant` flag passed at store construction:

```typescript
makeMysqlStore({ sqlLayer, variant: 'mariadb', changeStrategy: 'cdc', cdcConfig })
```

The variant flows through to per-operation getters (`supportsInsertReturning`, `supportsDeleteReturning`, `supportsUpdateReturning`) and to the JSON-agg compiler's dialect branch.

TLS and the pool bounds are shared with MySQL, driver-for-driver: `?sslmode=require` / `?ssl=true` encrypts (certificate not verified), an unsupported mode fails at boot rather than falling back to plaintext, and the acquire wait is bounded by queue length because mysql2 has no time-based bound for it. See [MySQL → TLS](./mysql) and the [acquire-timeout table](../dialects).

## `UPDATE … RETURNING` does NOT exist — anywhere

Despite MariaDB's broad RETURNING support — `INSERT … RETURNING *` since 10.5, `DELETE … RETURNING *` since 10.0 — there is **no UPDATE … RETURNING in any MariaDB version**. The framework's first attempt at supportsReturning treated the whole RETURNING family as one flag and emitted `UPDATE … RETURNING *` on mariadb, which fails with a parse error.

The split:

| Operation | MariaDB native? | Framework path |
|-----------|------------------|----------------|
| INSERT    | yes (10.5+)      | `INSERT … RETURNING *` |
| DELETE    | yes (10.0+)      | `DELETE … RETURNING *` |
| UPDATE    | **no, never**    | UPDATE then SELECT by PK (same as mysql) |

The mysql store's class internally exposes three getters (`supportsInsertReturning`, `supportsDeleteReturning`, `supportsUpdateReturning`). MariaDB gets `true` on the first two and `false` on the third. User code that calls `ctx.store.update(...)` returns the row from the follow-up SELECT — same interface, same return shape, just one extra round-trip.

## Correlated subqueries in non-LATERAL derived tables — rejected

MariaDB refuses correlated outer references inside non-LATERAL derived tables. The classic MySQL eager-load pattern:

```sql
-- Works in MySQL 8.0.14+ (auto-promoted to LATERAL).
-- Works in postgres.
-- FAILS in MariaDB: Unknown column 't1.id' in 'WHERE'.
SELECT t1.id,
  (SELECT JSON_ARRAYAGG(JSON_OBJECT('title', x.title))
   FROM (SELECT * FROM posts WHERE author_id = t1.id LIMIT 5) AS x
  ) AS posts
FROM users t1;
```

And MariaDB does NOT accept the `LATERAL` keyword as a workaround either.

### The framework's workaround: window function pattern

For `many()` and `manyToMany()` branches with `limit` / `offset` / `orderBy`, the framework emits a `ROW_NUMBER() OVER (PARTITION BY fk ORDER BY …)` pattern. The ranking happens in a derived table with NO outer reference, and the correlation lives in the wrapping subquery's WHERE clause where MariaDB accepts it:

```sql
-- What the framework emits on MariaDB for users.with({ posts: { limit: 5 } }):
SELECT JSON_OBJECT(
  'id', t1.id,
  'posts', COALESCE((
    SELECT JSON_ARRAYAGG(JSON_OBJECT(
      'id', ranked.id, 'title', ranked.title
    ) ORDER BY ranked.rn)
    FROM (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY author_id ORDER BY created_at DESC) AS rn
      FROM posts
    ) ranked
    WHERE ranked.author_id = t1.id AND ranked.rn <= 5
  ), JSON_ARRAY())
) AS __row
FROM users t1;
```

For the no-pagination case (no `limit`, no `offset`), the framework emits a direct correlated subquery with `JSON_ARRAYAGG(… ORDER BY col)` — the MariaDB-only extension — for cleaner SQL.

Performance cost: `ROW_NUMBER` materializes ranking across the whole table on every invocation. For tables in the millions this hurts; MariaDB users running into the cliff should add FK indices and consider limit-less queries against the walker path. For typical app-tier sizes (10k–100k rows) the optimizer prunes by partition + the cost stays linear with per-parent row count.

## `JSON_ARRAYAGG(… ORDER BY …)` — MariaDB-only

This extension lets you order rows inside a JSON aggregate without a derived-table wrap:

```sql
SELECT JSON_ARRAYAGG(x.title ORDER BY x.title)  -- MariaDB ✓ / MySQL ✗
FROM posts x WHERE author_id = ?;
```

MySQL 8 rejects `ORDER BY` inside `JSON_ARRAYAGG` with a parse error. MariaDB has supported it since 10.5.

The framework uses it for the no-pagination case of `many()` / `manyToMany()` on MariaDB — cleaner SQL than the ROW_NUMBER pattern when you don't need per-parent limits.

## CDC — binlog (ROW format)

MariaDB has no `LISTEN/NOTIFY`, but it gets a real out-of-band CDC source: the framework tails the primary's **ROW-format binary log** via the `@vlasky/zongji` replication client. This is the MariaDB equivalent of postgres's `LISTEN/NOTIFY` — **every replica tails the binlog itself, so a write on any instance surfaces on every instance's `onChange`** (true cross-instance reactivity in a multi-replica deploy).

**Every table is reactive by default** — nothing to opt into. `.nonReactive()`
turns it off for one table on *every* dialect: no subscriber fires, locally or
across instances. The write still happens; only the notification is suppressed.
See [the schema overview](/docs/database/overview).

This is **one binlog-CDC path shared with `mysql`** — mysql-8 and mariadb speak the same ROW binlog to the reader, so both get cross-instance CDC. The only engine difference is the binlog-end query (mariadb `SHOW MASTER STATUS`; mysql 8.4 `SHOW BINARY LOG STATUS`), which the store picks per variant.

### How it works

1. With `CDC=1` (the default for any SQL dialect) on `mariadb`, the store starts a binlog reader on a **separate replication connection** — distinct from the SQL pool.
2. The reader subscribes to `WriteRows` / `UpdateRows` / `DeleteRows` events and turns each into a `ChangeEvent { table, op, old, new }`. UPDATE events carry both the BEFORE and AFTER row images (needs `binlog_row_image=FULL`), so `old` is richer than inline mode can produce.
3. In CDC mode the binlog reader is the **sole** emitter — the write path stays silent, so each write surfaces exactly once per instance, delivered by that instance's own reader.
4. Framework-internal `_voltro_`-prefixed tables (and any table not in the reactive set) are skipped by the reader.

### Requirements

The reader fails fast at boot if these aren't met:

- **`binlog_format=ROW`** — statement/mixed formats don't carry per-row images.
- **`binlog_row_image=FULL`** — needed for complete UPDATE/DELETE before-images (a non-FULL image logs a warning; before-images may be partial).
- **`log_bin=ON`.**
- A DB user with **`REPLICATION SLAVE, REPLICATION CLIENT`**.
- **A UNIQUE `server_id` per reader.** Duplicate `server_id`s silently break binlog streams — two readers with the same id collide. The framework derives one per pod from `POD_NAME` / `HOSTNAME` (falling back to the PID in dev).
- The **`@vlasky/zongji`** package. It ships as an `optionalDependency` of `@voltro/sql-mysql`; if it's absent, a `cdc` request throws a clear "install `@vlasky/zongji`" error rather than silently degrading.

### Resume offsets

Each replica persists its progress in the **`_voltro_cdc_offsets`** table — one row per replica (`PK = replicaId`), holding the last binlog **`(file, position)`** it fully processed. On boot the reader resumes from that point; events between crash and resume replay and self-heal via the dispatcher's per-subscribe re-query. (Resume is by binlog file + position, not by GTID — failover relies on the re-query self-heal, not GTID portability.)

If the persisted offset has been purged (`err 1236`) or rejected after a failover, the reader jumps to the current binlog end and signals a resync so dependent subscriptions re-query rather than missing the gap.

### A table the reader cannot decode

`@vlasky/zongji` reads each event's column layout from the binlog's `Table_map` (fixed on disk) and compares it to a fresh `information_schema` fetch. A mismatch throws:

```
Table app.sessions schema changed between binlog event and metadata fetch:
  the event has 9 columns, fetched metadata has 8
```

The usual cause on MariaDB is a **UNIQUE constraint on an unbounded text column**. MariaDB can only back that with a **HASH long-unique index**, and that index adds a hidden `DB_ROW_HASH_n` column to the InnoDB row — present in the binlog row image, absent from `information_schema.COLUMNS`. So the counts can never agree, and every write to that table trips it. Nothing is broken; the table is shaped that way.

The reader finds such tables when CDC starts, reports each once, and **excludes** it — so there is no reconnect loop. Cross-instance change events for that table are lost; own-node reactivity is unaffected, because writes still emit inline.

**How long that verdict lasts.** It is a reading of the schema at the moment the reader attached, and applying the remedy does not lift it by itself. `voltro dev` builds its store before it migrates — kv, cross-replica broadcast and the analytics mirror all need one — so on a boot whose own migration bounds the column, the finding is a second older than the fix. That boot therefore **re-checks the exclusion set once all its schema work is done and re-admits the table in the same run**:

```
cdc: table 'sessions' looks undecodable … Not a verdict yet; the definitive line follows.
auto-migrate: applied 74 op(s) in 1157ms
cdc: table 'sessions' re-admitted to binlog capture — the hidden-column condition is gone.
```

The re-check runs in both directions: schema work that *creates* an unbounded unique on a captured table excludes it there and then, rather than after the reader has failed on three writes.

`voltro serve` has no equivalent step and needs none — it builds its store *after* every schema step and never applies DDL itself, so its reading at attach is already the final one. Everywhere else the exclusion holds until the process restarts.

**The remedy is to bound the column:**

```ts
tokenHash: text().maxLength(64).unique()   // VARCHAR(64) → ordinary B-tree index
```

`ALTER TABLE … FORCE` does **not** help. The rebuild recreates the index and therefore recreates the hidden column — measured before and after: same column count both times. If you already tried it, that was not your mistake.

A bounded unique is worth having anyway: it is also what keeps the key inside the index-size limits on every dialect.

The other cause of the same message is a **backlog event that predates a migration** — the reader was down while a table changed. That one is unreplayable but transient: the reader skips to the current binlog end, signals a resync, and recovers. One warning, then it is over.

### Boot summary

```
[voltro:dev] sql dialect resolved: mariadb — CDC: binlog CDC (ROW), RETURNING: native (INSERT/DELETE); UPDATE then SELECT
[voltro:dev] mariadb binlog CDC enabled { replicaId: 'pod-0', serverId: 1234567, reactiveTables: 42 }
[voltro:dev] cdc: binlog reader attached { serverId: 1234567, from: 'current-end' }
```

Set `CDC=0` to fall back to inline-emit (single-process only, no binlog dependency) — useful for tests and single-binary deploys.

## Workflow cluster

`@effect/cluster`'s mysql branch (`GET_LOCK` + `ON DUPLICATE KEY UPDATE`) works on MariaDB. The framework dispatches it via the same `variant: 'mariadb'` flag the store carries.

## Read replicas

MariaDB's GTID format differs from MySQL's: `0-1-100` (domain-server-sequence) vs `aaaaaaaa-...:1-100` (UUID-based). The framework's replication adapter probes the right variable per variant — `@@global.gtid_current_pos` on mariadb, `@@global.gtid_executed` on mysql. As on mysql, the catch-up **comparison is a stub today** (`compare()` always answers `'behind'`): the default `RYW_POLICY=fallback` never calls it, but `RYW_POLICY=wait` always routes RYW reads to the primary. A real GTID-subset round-trip is a tracked follow-up.

## Identifier quoting

`` `name` `` — backticks. Same as MySQL.

## Known caveats

- **`mariadb` schema package is wire-compatible with `mysql`**. If you migrate from MySQL → MariaDB, the framework re-emits DDL cleanly via `applySchema(..., 'mariadb')`. Production data round-trips through `mysqldump` without translation.
- **`sql_mode=NO_BACKSLASH_ESCAPES`** is sometimes set on MariaDB deploys. The framework's identifier escaping handles it, but user-written `unsafe()` strings that hand-escape backslashes may produce wrong output. Leave that mode off if you can.
- **Sequence-based ID columns**. MariaDB has true CREATE SEQUENCE; the framework doesn't use it (TypeID / ULID / Snowflake are client-side). If you reach for sequences for legacy reasons, they're outside the framework's auto-injection path.
- **Hand-rolled `AUTO_INCREMENT` primary keys** work the same as on mysql: an `insert` / `insertMany` with no client-side `id` recovers the DB-generated id via `LAST_INSERT_ID()` (connection-pinned; `insertMany` recovers the whole consecutive range). See the [mysql page](/docs/database/dialects/mysql#auto_increment-ids--last_insert_id-recovery) for the worked example — the recovery path is identical on both engines.

## Where it lives

- `voltro/packages/sql-mysql/src/index.ts` — exports `mariadbDialect`
- `voltro/packages/sql-mysql/src/store.ts` — `supportsInsertReturning` / `supportsDeleteReturning` / `supportsUpdateReturning` getters branch on `variant`
- `voltro/packages/database/src/jsonEagerCompiler.ts` — `mariadbManySubquery` / `mariadbManyToManySubquery` ROW_NUMBER window-function pattern
- `voltro/packages/database/src/migrate.ts` — mariadb shares the mysql DDL branch (text-DEFAULT stays as TEXT — mariadb allows it)
- `voltro/packages/sql-mysql/src/binlogCdc.ts` — ROW-format binlog reader (`@vlasky/zongji`), per-pod `server_id`, file/position resume + self-heal on purge/failover
- `voltro/packages/sql-mysql/src/cdcOffsetsTable.ts` — `_voltro_cdc_offsets` per-replica binlog `(file, position)` checkpoint



---

<!-- source: en/database/dialects/mssql.md -->
## MSSQL 2019+

_TOP / OFFSET-FETCH instead of LIMIT, single-statement OUTPUT INSERTED/DELETED instead of RETURNING, IDENTITY id recovery, native MERGE upserts, NVARCHAR(MAX) for JSON columns, and Change Tracking CDC for cross-instance reactivity. Four upstream cluster patches the framework carries._

MSSQL is the most divergent dialect the framework supports. T-SQL deviates from ANSI SQL in places the schema DSL hides — but when you reach for hand-written SQL the differences surface. This page documents what the framework does for you + what to know when you bypass it.

## Target version

**SQL Server 2019 or later.** Three reasons:

1. JSON support is mature: `FOR JSON PATH`, `JSON_QUERY`, `JSON_VALUE`, `ISJSON` all stable. 2016/2017 had FOR JSON PATH but `JSON_QUERY` semantics were narrower.
2. `OFFSET … FETCH NEXT` optimizer plans stabilized around 2019 — earlier versions could plan-thrash on paginated queries with complex predicates.
3. Always-On Availability Groups ship the `sys.dm_hadr_database_replica_states` DMV columns (`end_of_log_lsn`, `last_hardened_lsn`) the framework's RYW adapter reads. Earlier versions exposed equivalent data through other DMVs but the framework doesn't fallback to them.

SQL Server 2016 / 2017 work for the basic workflow path; some performance characteristics will be different.

## Configuration

```sh
DB_DIALECT=mssql
DB_URL=mssql://sa:<password>@localhost:11433/voltro_test
# or discrete fields: DB_DIALECT + DB_HOST / DB_PORT / DB_USER / DB_PASSWORD / DB_DATABASE

# Local Docker dev fixture defaults:
#   container: mcr.microsoft.com/mssql/server:2022-latest
#   port:      11433 (mapped from 1433 to avoid the system default)
#   db:        voltro_test (created by an init sidecar)
#   user:      sa / Voltro_test_99
```

The framework's docker-compose at `voltro/test/docker-compose.yml` brings up MSSQL on `:11433` with an `mssql-init` sidecar that runs `CREATE DATABASE voltro_test` once the server is healthy. mssql doesn't have a `docker-entrypoint-initdb.d` equivalent, so the framework synthesizes one.

## TLS — honoured, or refused

```sh
DB_URL=mssql://sa:<password>@sql.internal:1433/app?sslmode=require   # encrypt, don't verify the certificate
DB_URL=mssql://sa:<password>@sql.internal:1433/app?encrypt=1         # tedious' own spelling — same thing
DB_URL=mssql://sa:<password>@sql.internal:1433/app?sslmode=disable   # explicit plaintext
```

`require` maps to tedious `encrypt: true` + `trustServerCertificate: true` — encrypted, server certificate not authenticated, which is what lets a Developer-edition instance's self-signed certificate work. Unsupported modes (`prefer`, `verify-ca`, `verify-full`, `?encrypt=yes`) **fail at boot** rather than connecting with something weaker than they name.

> **This changed.** `@effect/sql-mssql` defaults `encrypt` to **false** (it overrides tedious' own `true`), and the framework never read the `ssl` config or the URL query — so every mssql session was plaintext, while a hard-coded `trustServerCertificate: true` made the configuration look TLS-aware. Unset still means plaintext (unchanged); a TLS request in the URL is now real.

## Driver: `@effect/sql-mssql`

Wraps `tedious` (the Node.js TDS driver). The framework's `MssqlStore` opens a connection pool sized via `DB_MAX_CONNECTIONS` (default 10), with a 10 s bound on establishing a connection (`ConnectionConfig.acquireTimeoutMs`). Waiting for a BUSY pooled connection is not bounded here — the client's pool exposes no such knob; see the [acquire-timeout table](../dialects) for what each dialect can enforce.

Tedious returns BIT as proper boolean (✓) and NVARCHAR as string. JSON columns are NVARCHAR(MAX) under the hood — driver returns the raw string. The framework's `decodeRowsFromSchema` JSON.parses any column declared as `json()` in the schema registry; without this, every reactive subscription reading a JSON column would see strings and fail framework Schema validators.

## No `RETURNING` — single-statement `OUTPUT INSERTED.*` / `OUTPUT DELETED.*`

MSSQL has the OUTPUT clause for DML, and the framework's `MssqlStore` uses it for **every** write — insert, update, patchJson, delete, `updateMany`, `deleteMany`, and the MERGE upsert — so each returns its post-image (or pre-image, for delete) in ONE round-trip, matching postgres's `RETURNING` behaviour with no follow-up SELECT:

```sql
-- What MSSQL uses (all single-statement):
INSERT INTO todos (id, title, done) OUTPUT INSERTED.* VALUES (?, ?, ?)
UPDATE todos SET done = ? OUTPUT INSERTED.* WHERE id = ?
DELETE FROM todos OUTPUT DELETED.* WHERE id = ?
```

The `OUTPUT` clause sits in the T-SQL-required position — between the column list and `VALUES` for INSERT, between `SET` and `WHERE` for UPDATE, between the table and `WHERE` for DELETE. `@effect/sql`'s insert/update helpers carry a `.returning('*')`, and the mssql compiler lowers it to `OUTPUT INSERTED.*` in exactly that spot, so there is no write-then-SELECT fallback anywhere. An empty OUTPUT result is also the existence check: an UPDATE/DELETE by an id that matches no row returns `null` / `false`.

### IDENTITY id recovery

Because the write path returns `INSERTED.*`, a raw `IDENTITY(1,1)` primary-key table inserts **without a client-side id** and the server-generated id comes back in the returned row — no `SCOPE_IDENTITY()` round-trip needed. (Framework entities use client-side TypeIDs by default, so this matters only for hand-declared IDENTITY tables.)

```ts
// ident table: id INT IDENTITY(1,1) PRIMARY KEY, title NVARCHAR(200)
const row = await store.insert('ident', { title: 'auto' })
row.id // → the generated INT, e.g. 1
```

### Native MERGE upsert

`upsert` compiles to a single-statement `MERGE … WITH (HOLDLOCK) … OUTPUT $action, INSERTED.*`:

```sql
MERGE todos WITH (HOLDLOCK) AS tgt
USING (VALUES (?, ?, ?)) AS src (id, title, done)
ON tgt.id = src.id
WHEN MATCHED THEN UPDATE SET tgt.title = src.title, tgt.done = src.done
WHEN NOT MATCHED THEN INSERT (id, title, done) VALUES (src.id, src.title, src.done)
OUTPUT $action AS __action, INSERTED.*;
```

`WITH (HOLDLOCK)` takes a range lock on the match key so a concurrent upsert on the same conflict key serialises behind it (closing the MERGE insert/update race + the Halloween window). `$action` distinguishes the insert vs update branch, so the emitted `ChangeEvent` carries the right `op`. The **function-form** `update` (compute the patch from the conflicting row) can't be expressed in a MERGE `WHEN MATCHED` clause, so it alone keeps the read-then-compute path.

Caveat: MSSQL's OUTPUT clause forbids subqueries (Msg 10705). `MERGE … OUTPUT (SELECT … FROM …) AS X` fails to parse — the framework's MERGE uses only `$action` + `INSERTED.*`, never a subquery. `@effect/cluster`'s upsert path hits the subquery limit → see "Upstream cluster patches" below.

## No `LIMIT / OFFSET` — TOP N / OFFSET … FETCH NEXT

MSSQL has two pagination idioms:

```sql
-- Take-only: SELECT TOP N goes before the projection list.
SELECT TOP 10 id, name FROM users ORDER BY created_at DESC

-- Skip + take: OFFSET … FETCH NEXT goes after the ORDER BY.
-- Requires ORDER BY (mssql refuses FETCH NEXT without one).
SELECT id, name FROM users
ORDER BY created_at DESC
OFFSET 100 ROWS FETCH NEXT 20 ROWS ONLY
```

The framework's `compileSelect` emits whichever fits the descriptor:

- `take=N, skip=undefined` → `TOP N` prefix.
- `take=N, skip=M` (or `take=undefined, skip=M`) → `OFFSET M ROWS FETCH NEXT N ROWS ONLY` suffix.

When the caller didn't supply an `orderBy` but did set `skip` (uncommon but legal), the framework adds `ORDER BY (SELECT NULL)` as a parser-pacifier — the planner treats it as "any order" with no actual sorting cost.

## Integer-literal LIMIT (same as MySQL)

The compiler inlines integer literals for TOP/OFFSET/FETCH NEXT values rather than parameter binding. Same rationale as MySQL — tedious has bind-as-INT issues with large or unexpected-typed numeric params.

## JSON columns — NVARCHAR(MAX) + auto-parse

`json()` columns emit `NVARCHAR(MAX)` in DDL — mssql has no native JSON type pre-2025. Validation goes through `ISJSON(col) = 1` CHECK constraints; serialization is application-side.

The framework's `decodeRowsFromSchema` JSON.parses these on read so application code always sees objects. Writes go through `JSON.stringify` before bind — the mutation middleware handles this transparently for any `json()` column.

If you write hand-rolled queries that read JSON columns through `unsafe()`, you'll get strings. `JSON.parse` them yourself or pre-shape them through `JSON_VALUE(col, '$.field')` in SQL.

## Identifier quoting

`[users]` — square brackets. Standard MSSQL. The framework's compiler emits these for every identifier; double quotes work too if `QUOTED_IDENTIFIER ON` is set (which it is by default in modern MSSQL).

## Workflow cluster

> **If you run durable cluster workflows on mssql, run `voltro add mssql`.** A pnpm
> patch lives in your workspace config, NOT in a published npm tarball — so a plain
> `pnpm install` of the framework can't carry it. `voltro add mssql` ships the
> `.patch` file (it's bundled in the CLI) into your `patches/` and adds the
> `patchedDependencies` entry to your `pnpm-workspace.yaml`; the next `pnpm install`
> then applies it. Idempotent, and a no-op on every other dialect. Without it,
> workflow message/runner storage misbehaves on SQL Server.

`@effect/cluster`'s mssql branch uses `sp_getapplock` for shard claims and `MERGE … WHEN NOT MATCHED THEN INSERT … OUTPUT INSERTED` for runner upserts. Four bugs in `@effect/cluster@0.60.0` mssql code paths fail under the framework's workflow stack; the framework carries a `pnpm patch` (shipped in the CLI at `packages/cli/templates/patches/@effect__cluster@0.60.0.patch`, written into your project by `voltro add mssql`):

### Patch 1 — SqlRunnerStorage shard-lock MERGE alias

The shard-lock acquireShards SQL wraps its VALUES list in an extra SELECT:

```sql
-- Original (illegal in MSSQL):
USING (SELECT * FROM (VALUES (...)) ) AS source (shard_id, address, acquired_at)

-- Patched (drops the wrap):
USING (VALUES (...)) AS source (shard_id, address, acquired_at)
```

The inner `(VALUES ...)` derived table doesn't get its own alias, and the outer SELECT can't introduce a column-name list. Flattening fixes it.

### Patch 2 — SqlMessageStorage `FOR UPDATE` in non-cursor SELECT

`SELECT … ORDER BY … FOR UPDATE` is illegal in MSSQL outside a `DECLARE CURSOR`. Postgres/MySQL use it for row-locking; MSSQL needs `WITH (UPDLOCK, ROWLOCK)` table hints. The patch dispatches `mssql` to an empty literal (same path SQLite takes), accepting the race-window tradeoff the upstream cluster already accepts for SQLite.

### Patch 3 — SqlMessageStorage `insertEnvelope` MERGE-with-OUTPUT subqueries

MSSQL forbids subqueries inside OUTPUT clauses (Msg 10705). The original `insertEnvelope` MERGE used `CASE WHEN inserted.id IS NULL THEN (SELECT …) END` in the OUTPUT list. The patch restructures: `MERGE … OUTPUT inserted.id;` then a conditional follow-up SELECT joining the replies table. Same control flow as the mysql branch.

### Patch 4 — `envelopeToRow` BigInt for `deliver_at`

The cluster's `deliver_at` column is BIGINT (storing millisecond epoch). Tedious binds JS `number` parameters as INT — which overflows for any post-2001 timestamp. The patch casts `deliver_at` to `BigInt` once at the top of `envelopeToRow` so all three message-kind switch arms emit `bigint | null`; other dialects accept bigint fine.

All four patches are dialect-keyed (touch only the `mssql:` branch of `sql.onDialectOrElse`) so postgres / mysql / sqlite paths are bit-identical to upstream. The framework carries them and ships them to your project via `voltro add mssql` (see the note at the top of this section) — the patch is how the fix reaches you. Upstream still carries these bugs on the current release — the framework re-verifies that on every bump (`git apply --check` against the new tarball) and re-keys the patch, because a `patchedDependencies` key is version-exact and a stale one fails the install.

## Read replicas — Always-On Availability Groups

The framework's mssql replication adapter reads `end_of_log_lsn` from `sys.dm_hadr_database_replica_states` to measure replica freshness against the primary's `last_hardened_lsn`. The LSN triplet is parsed and compared lexicographically.

```sh
DB_REPLICA_URLS=mssql://app:app@replica-1:1433/voltro_app
RYW_POLICY=fallback
```

Requires the deployment to use Always-On AGs (the modern HA story since SQL Server 2012). The framework does NOT support Log Shipping or Database Mirroring — they have different position tracking. If you're on those, stick with primary-only routing.

## CDC — Change Tracking (cross-instance reactivity)

SQL Server ships **Change Tracking** (CT) — a lightweight, built-in change source available on every edition (unlike the heavier Change Data Capture feature). The framework's mssql store uses it as an out-of-band CDC reader so a write on ANY instance surfaces on EVERY instance's `onChange` — the mssql equivalent of postgres `LISTEN/NOTIFY` or mariadb binlog CDC.

**Every table is reactive by default** — nothing to opt into. `.nonReactive()`
turns it off for one table on *every* dialect: no subscriber fires, locally or
across instances. The write still happens; only the notification is suppressed.
See [the schema overview](/docs/database/overview).

Enable it and set `changeStrategy: 'cdc'` (the default when `CDC` is not `0`):

```sh
DB_DIALECT=mssql
CDC=1   # default; set CDC=0 for single-instance inline emit
```

```sql
-- One-time, at the database level (the store enables per-table CT itself):
ALTER DATABASE voltro_test
  SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
```

```
[voltro:dev] sql dialect resolved: mssql — CDC: Change Tracking (polled), RETURNING: OUTPUT INSERTED/DELETED
```

How it works: a polling reader tails each reactive table via `CHANGETABLE(CHANGES <t>, @sinceVersion)`, advancing a database-wide `CHANGE_TRACKING_CURRENT_VERSION()` cursor that it checkpoints per-replica into `_voltro_cdc_offsets` (so a restart resumes where it left off). Rows are joined back to the base table for the current image; the CT op (`I`/`U`/`D`) maps to `insert`/`update`/`delete`. If auto-cleanup outruns a replica's cursor (`CHANGE_TRACKING_MIN_VALID_VERSION` passes it), the reader jumps to the current version and the dispatcher's per-subscribe re-query self-heals.

Two honest limitations of Change Tracking, both documented so subscribers don't assume more than CT gives:

- **Net-change, not a change log.** Between two polls, an insert→update→delete of one row collapses to a single delete, and an insert→update to a single insert carrying the latest image. The framework surfaces the net effect per poll cycle. Reactive queries re-read on any change, so a collapsed intermediate never produces a wrong result — but an exactly-once consumer must not assume it sees every intermediate op.
- **No before-image.** CT reports the primary key + op, not the prior row. So an update's `ChangeEvent.old` is `null` (the `new` image carries the current row), and a delete's `old` carries only the id. Mariadb binlog CDC (`binlog_row_image=FULL`) gives a full before-image; CT does not.

For a full before-image or per-op fidelity, use **mariadb** (binlog CDC). For zero-infra single-instance reactivity, set `CDC=0` (inline emit — the writing instance emits its own deltas, never cross-instance).

## Known caveats

- **`tedious` connections close abruptly on AAD-only auth refresh**. Use SQL auth (sa / app role) for app-tier deploys; reserve AAD for admin access.
- **`NVARCHAR(MAX)` columns can't be in indexes** with `INCLUDE` clauses pre-2017. The framework's index emitter doesn't try; you get an error at migration time if you compose `.index([jsonCol])`.
- **`DATETIME2(6)` is the framework default for timestamps**; older DATETIME drops sub-second precision and serialization on the wire differs.
- **`MERGE` has subtle race conditions** documented by Microsoft. The framework's `upsert` uses `MERGE … WITH (HOLDLOCK)` to dodge them; if you write hand-rolled MERGE, copy the HOLDLOCK hint.

## Where it lives

- `voltro/packages/sql-mssql/src/store.ts` — `MssqlStore` with single-statement OUTPUT INSERTED/DELETED, native MERGE upsert, IDENTITY id recovery, Change Tracking CDC consumer
- `voltro/packages/sql-mssql/src/changeTrackingCdc.ts` — the polled Change Tracking reader (`CHANGETABLE` / `CHANGE_TRACKING_CURRENT_VERSION`)
- `voltro/packages/sql-mssql/src/cdcOffsetsTable.ts` — `_voltro_cdc_offsets` per-replica CT resume checkpoint
- `voltro/packages/sql-mssql/src/retry.ts` — `isRetryableMssqlFailure` (1205 deadlock)
- `voltro/packages/sql-mssql/src/replicationAdapter.ts` — `end_of_log_lsn` / `last_hardened_lsn` compare for RYW
- `voltro/packages/database/src/sqlCompiler.ts` — TOP / FETCH NEXT dispatch
- `voltro/packages/database/src/migrate.ts` — mssql DDL branch (line 72)
- `packages/cli/templates/patches/@effect__cluster@0.60.0.patch` — the four upstream patches (shipped in the CLI; `voltro add mssql` writes it into your project)



---

<!-- source: en/database/dialects/sqlite.md -->
## SQLite 3.38+

_Single-process by design. Workflows run with in-memory runner storage. Read replicas n/a. In-process EventEmitter CDC bus. TEXT storage for JSON/timestamps/dates with framework auto-coercion on read._

SQLite is the framework's single-process dialect — Dev environments, embedded apps, CLIs, edge-deployed single-tenant tools. The architectural trade-offs are different from the server dialects: no cluster scale-out, no read replicas, sub-millisecond CDC. The framework documents these explicitly because users frequently underestimate the inherent single-process constraint.

## Target version

**SQLite 3.38 or later.** Two reasons:

1. `json_object` + `json_group_array` (the framework's eager-load idiom on sqlite) stabilized in 3.38. 3.37.x supports them but with subtle ordering quirks.
2. `RETURNING *` on DML was added in 3.35. The framework relies on it.

`better-sqlite3` (the bundled driver) ships modern SQLite versions — version targeting is more about your build environment than the runtime.

## Configuration

```sh
DB_DIALECT=sqlite

# File backend:
DB_URL=file:./db.sqlite       # relative path (resolved against cwd)
DB_URL=file:/abs/path/db.sqlite

# Ephemeral in-memory backend:
DB_URL=:memory:
```

No host, no port, no credentials. SQLite is process-local.

## Driver: `@effect/sql-sqlite-node` (better-sqlite3)

Synchronous driver under the hood — the framework wraps every call in `Effect.tryPromise` so the API surface stays Promise-based. Performance is excellent for single-process workloads (no inter-process IPC, no network round-trips).

The driver returns INTEGER for BOOLEAN columns (sqlite stores them as 0/1) and TEXT for everything else (no native DATE / TIMESTAMP / JSON types). The framework's `decodeRowsFromSchema` post-processor handles all three:

- `boolean()` columns: 0/1 → false/true
- `json()` columns: TEXT → object (JSON.parse)
- `timestamp()` / `date()` columns: ISO string → Date

On write, `coerceForSqlite` converts Date → ISO string and boolean → 0/1 before binding.

## No cluster — `runnerStorage: 'memory'`

SQLite is single-process by definition. `@effect/cluster`'s `SqlRunnerStorage` requires a coordinatable lock primitive (advisory locks, GET_LOCK, sp_getapplock) — SQLite has none.

The framework boots the workflow engine with `runnerStorage: 'memory'` when the dialect resolves to sqlite. What this means:

- Workflows run durably **within ONE process**. Crash + restart resumes from the persisted journal.
- Multi-replica deployments are **not possible**. The cluster's `getRunners()` returns only the local instance; there's no shard re-assignment because there's no second runner to assign to.
- Boot log surfaces this:

  ```
  [voltro:dev] workflow engine: cluster-memory, dialect=sqlite — single-process durable replay, no horizontal scaling
  ```

For SQLite use cases this constraint is usually intentional — a CLI tool that runs workflows during a single command execution, a desktop app where the whole framework lives in the same process. If you're considering multi-replica scale-out you've outgrown SQLite; switch to postgres / mysql / mariadb / mssql.

## No read replicas

SQLite is single-writer by definition. Setting `DB_REPLICA_URLS=…` is a no-op with a warning:

```
[voltro:dev] read replicas: not applicable (sqlite is single-process); ignoring DB_REPLICA_URLS
```

## CDC — in-process EventEmitter

Sqlite has no LISTEN/NOTIFY equivalent and no trigger-based fan-out is needed (everything runs in one process). The framework's `SqliteStore` uses an in-process Node `EventEmitter` (composed as a private field, not subclassed):

**Every table is reactive by default** — nothing to opt into. `.nonReactive()`
turns it off for one table on *every* dialect: no subscriber fires, locally or
across instances. The write still happens; only the notification is suppressed.
See [the schema overview](/docs/database/overview).

- Insert/update/delete emit `'change'` events synchronously to the dispatcher.
- The dispatcher's `onChange` callback is registered against the emitter — no polling, no triggers, no log table.

Latency: sub-millisecond. Bounded by Node's event-loop tick.

This is the FASTEST CDC path the framework offers. The tradeoff is the inherent single-process constraint — there's no cross-process or cross-machine fan-out to worry about.

## JSON columns — TEXT with auto-coerce

`json()` columns map to TEXT in DDL. SQLite's optional `json` extension validates content via the `JSON1` functions but enforces no type — TEXT is what you get on the wire.

The framework's `decodeRowsFromSchema` JSON.parses any column declared as `json()` in the schema registry. Writes go through `JSON.stringify` in the mutation middleware. Application code sees objects on both sides.

## Identifier quoting

`"name"` — double quotes. Same as postgres.

## Migration emitter

`voltro migrate` against sqlite emits:

- `CREATE TABLE IF NOT EXISTS …`
- `CREATE INDEX IF NOT EXISTS …`
- FK constraints via `REFERENCES … ON DELETE CASCADE/RESTRICT/SET NULL` (sqlite supports these since `PRAGMA foreign_keys = ON`, which the framework sets at connect)
- `INTEGER PRIMARY KEY AUTOINCREMENT` for numeric ids
- `TEXT` for ids, text, timestamp, date, json, references
- `INTEGER` for booleans
- `BLOB` for vectors

The `_voltro_migrations` ledger is a regular table keyed by migration id — re-running the same migration is a no-op. WAL mode is enabled at connect (`PRAGMA journal_mode = WAL`) for better concurrency and crash safety.

## File vs `:memory:`

- `:memory:` — ephemeral, lives in the process's address space, dies on exit. Use for tests + smoke fixtures.
- `file:./db.sqlite` — durable, lives at the filesystem path. The framework auto-creates the file on first write. WAL mode means you'll see `db.sqlite-wal` + `db.sqlite-shm` sidecars; that's expected.

When you copy or back up a sqlite file, capture all THREE files together (the WAL contains uncommitted-to-main writes). Or run `PRAGMA wal_checkpoint(FULL)` first to fold the WAL back into the main file.

## Known caveats

- **`PRAGMA foreign_keys` is OFF by default**. The framework turns it on at every connect; if you open the database via another tool (sqlite3 CLI, DBeaver) and run mutations, you bypass FK enforcement.
- **Single-writer**. SQLite serializes writes — a long-running write blocks every other write on the same database file. WAL mode helps readers (they don't block) but doesn't help writers.
- **Date arithmetic is string-based**. `timestamp()` columns store ISO-8601 text; comparing two timestamps is lexical (which works because ISO-8601 sorts correctly) but date math requires the framework's higher-level API, not raw SQL.
- **No native DECIMAL**. The framework doesn't ship a decimal type yet — `integer()` and `number()` (floating-point) are it. Money values: integer cents.

## Where it lives

- `voltro/packages/sql-sqlite/src/store.ts` — `SqliteStore` with EventEmitter CDC + `coerceForSqlite` on write
- `voltro/packages/sql-sqlite/src/retry.ts` — `isRetryableSqliteFailure` (SQLITE_BUSY / SQLITE_LOCKED)
- `voltro/packages/database/src/migrate.ts` — sqlite DDL branch (line 46)
- `voltro/packages/database/src/jsonEagerCompiler.ts` — `compileSqliteEntry` using `json_object` / `json_group_array`
- `voltro/packages/database/src/rowDecoder.ts` — read-path JSON / boolean / Date coercion against schema
- `voltro/packages/workflow/src/clusterLayer.ts` — `runnerStorage: 'memory'` branch for sqlite



---

<!-- source: en/database/dialects/turso.md -->
## Turso (beta)

_The Rust rewrite of SQLite (@tursodatabase/database) with MVCC concurrent writes via BEGIN CONCURRENT — OR remote Turso Cloud (libsql://) with embedded-replica sync via @libsql/client. Routed by URL scheme. SQLite-compatible SQL. Single-node local, beta. No generated columns, no FTS; numeric ids drop AUTOINCREMENT; no Alpine/musl or Intel-mac prebuilts on the local engine._

Turso is the **Rust rewrite of SQLite** (`@tursodatabase/database`, formerly Limbo). It speaks SQLite's SQL dialect, file format, and a better-sqlite3-shaped driver — so the framework reuses the entire SQLite-family store and SQL compiler. The ONE thing it adds over the `sqlite` dialect is the reason to pick it: **MVCC concurrent writes** via `BEGIN CONCURRENT`. Where `sqlite` serializes every write through a single connection, `turso` runs a connection POOL where multiple transactions commit concurrently; a write-write conflict is detected and the framework retries it transparently.

The `turso` dialect has **two live backends, routed by the connection URL scheme** — not two versions of the same thing, but a real config choice:

- **Local Rust engine** (`file:` / `:memory:`) — the embedded `@tursodatabase/database` engine described above: pooled, single-process, MVCC concurrent writes. **beta** (`0.x`).
- **Remote Turso Cloud** (`libsql://` / `https://` / `wss://`) — a connection to a hosted Turso Cloud database over the official `@libsql/client`, with an auth token. Also supports an **embedded replica**: a local `file:` copy that periodically syncs from a remote primary (local-latency reads, writes forwarded to the primary).

Treat the local engine as "SQLite for a single node that needs real write concurrency", not a production-hardened engine; treat the remote/replica backend as "your app runs against managed Turso Cloud". For multi-instance scale-out with sub-second local reactivity use postgres / mysql / mariadb / mssql; for a rock-stable single-process embed use `sqlite`.

## Configuration

The URL scheme picks the backend — local engine vs remote Turso Cloud vs embedded replica:

```sh
DB_DIALECT=turso

# ── Local Rust engine ─────────────────────────────────────────
DB_URL=file:./db.turso        # relative path (resolved against cwd)
DB_URL=file:/abs/path/db.turso
DB_URL=:memory:               # ephemeral, pool forced to size 1 (see below)
DB_MAX_CONNECTIONS=8          # pool size = the MVCC write-concurrency knob. Default 4.

# ── Remote Turso Cloud ────────────────────────────────────────
DB_URL=libsql://your-db.turso.io   # or https:// / wss://
DB_AUTH_TOKEN=<token>              # from `turso db tokens create <db>` — never logged/baked

# ── Embedded replica (local file that syncs from a remote primary) ─
DB_URL=file:./replica.db           # the LOCAL replica file
DB_SYNC_URL=libsql://your-db.turso.io   # the remote primary it syncs FROM
DB_AUTH_TOKEN=<token>
DB_SYNC_INTERVAL=30                # optional: pull-sync every N seconds (else sync on boot only)
```

The scheme routes the connection: `file:` / `:memory:` → the **local** Rust engine; `libsql://` / `https://` / `wss://` → **remote** Turso Cloud (needs `DB_AUTH_TOKEN`); a `file:` URL **paired with `DB_SYNC_URL`** → an **embedded replica**. So the same `file:` scheme means "local engine" without a sync URL and "libsql embedded replica" with one. A remote URL without an auth token, or a `DB_SYNC_URL` without one, **fails loud at boot** with an actionable message. The token comes from `DB_AUTH_TOKEN` (alias `TURSO_AUTH_TOKEN`) — it is never logged or baked into the build.

## Remote Turso Cloud + embedded replicas

The remote backend connects through the official `@libsql/client`:

- **Remote Turso Cloud** (`libsql://` / `https://` / `wss://`): every statement runs against your hosted Turso database; `ctx.store.transactional(...)` opens an interactive libsql `transaction('write')`. Turso Cloud serialises writes server-side (there's no client-side `BEGIN CONCURRENT` here — that's the local engine's mechanism), and a transient busy/conflict is retried by the same transaction-retry the local engine uses.
- **Embedded replica** (`file:` + `DB_SYNC_URL`): the client keeps a **local copy** of the database that reads with local latency and syncs from the remote primary. The framework does an initial `client.sync()` at boot so the first reads see a warm replica; if `DB_SYNC_INTERVAL` is set, the driver keeps pulling on that cadence. Writes are forwarded to the primary. Optional `DB_READ_YOUR_WRITES` (default on) makes a local read after a write wait until the replica has caught up; `DB_ENCRYPTION_KEY` encrypts the local replica file at rest.

Everything above this seam is identical to the local engine — the same reused SQLite-family `DataStore`, the same schema/query/mutation code. Only the transport differs.

> The local Rust engine and the remote/replica backend are a deliberate config choice, not a versioned split. Pick `file:`/`:memory:` for an embedded single-process DB with MVCC write concurrency; pick `libsql://` (± an embedded replica) to run against managed Turso Cloud.

## MVCC is mandatory — `journal_mode=experimental_mvcc`

`BEGIN CONCURRENT` is only accepted when MVCC is enabled, so every pooled connection runs `PRAGMA journal_mode=experimental_mvcc` at open. This is not optional for the dialect — it's what the whole thing is for. The store's DML transactions run as `BEGIN CONCURRENT`, so every `ctx.store.transactional(...)` (i.e. every mutation) opens a concurrent transaction. DDL keeps plain `BEGIN` (Turso rejects DDL inside a concurrent transaction), so the `@effect/sql` client defaults to `BEGIN` and the store upgrades its own transactions per-fiber — migrations (including the workflow engine's) still work.

### Concurrent writes + automatic retry

Two mutations that touch the same row run on two different pooled connections, each with its own MVCC snapshot. The loser of the commit race gets a `Write-write conflict`; the framework's transaction retry (exponential backoff, a few attempts) replays the whole transaction body on a fresh snapshot that now sees the winner's commit. Your handler code is unchanged — write a normal transactional mutation and the concurrency + retry happen underneath.

Size the pool (`DB_MAX_CONNECTIONS`) to the concurrent-mutation count you expect. `:memory:` is per-connection (each connection is a private database), so the pool is **forced to size 1** there — `:memory:` is for tests, not for exercising concurrency.

## Driver: `@tursodatabase/database` (native NAPI)

Async driver (`connect()` → `prepare()` → `run`/`get`/`all`), wrapped in `Effect`. The framework's `decodeRowsFromSchema` post-processor handles the same SQLite read quirks as the `sqlite` dialect:

- `boolean()` columns: 0/1 → false/true
- `json()` columns: TEXT → object (JSON.parse)
- `timestamp()` / `date()` columns: ISO string → Date

On write, the same coercion converts Date → ISO string and boolean → 0/1 before binding.

**Prebuilt binaries ship for `linux-x64-gnu`, `linux-arm64-gnu`, `win32-x64-msvc`, `darwin-arm64` only — there is NO Alpine/musl or Intel-mac binary.** A Node Docker image on Alpine will fail to install the driver; use a glibc base image (`node:22-bookworm-slim`, etc.). This applies to the **local** engine only — the remote / embedded-replica backend goes through `@libsql/client` (pure JS + its own bindings) and is not bound by the local engine's prebuilt matrix.

## No cluster — `runnerStorage: 'memory'`

Like `sqlite`, Turso is single-node here. The workflow engine boots with `runnerStorage: 'memory'`: workflows run durably within ONE process (crash + restart resumes from the journal), multi-replica scale-out is not possible, and `DB_REPLICA_URLS` is a no-op with a warning. (The pool gives concurrent *writes* on one node — it does NOT give horizontal scale-out.)

## CDC — in-process EventEmitter

Same as `sqlite`: an in-process Node `EventEmitter` fans insert/update/delete events to the dispatcher. Sub-millisecond, single-process. No `LISTEN/NOTIFY`, no triggers.

**Every table is reactive by default** — nothing to opt into. `.nonReactive()`
turns it off for one table on *every* dialect: no subscriber fires, locally or
across instances. The write still happens; only the notification is suppressed.
See [the schema overview](/docs/database/overview).

## Not supported on Turso (beta gaps)

These are SQLite features the `sqlite` dialect has but Turso's MVCC mode does not. The framework fails LOUD at migrate rather than emit DDL the engine rejects:

- **Generated columns** (`.generatedAs(...)`). Turso MVCC rejects STORED/VIRTUAL generated columns — migrate throws a clear error. Drop the column or use `sqlite` / `postgres`.
- **Full-text search** (`.fullTextIndex(...)`). MVCC has no virtual tables (FTS5) — migrate throws. Use `sqlite` / `postgres` for FTS.
- **`AUTOINCREMENT`.** Numeric ids (`id({ scheme: 'numeric' })`) emit plain `INTEGER PRIMARY KEY` (still auto-allocating, just without the no-reuse-of-deleted-ids guarantee). The default TypeID/ULID schemes are unaffected.

Everything else — `RETURNING`, `ON CONFLICT` upserts, `json_object`/`json_group_array` eager-loads, PRAGMA introspection, partial + expression indexes, CHECK constraints, namespace ATTACH (multi-tenant physical isolation) — works identically to `sqlite`.

## DDL runs in autocommit

Turso rejects DDL inside a `BEGIN CONCURRENT` transaction ("DDL statements require an exclusive transaction"). So `voltro migrate` / auto-migrate runs each DDL statement in **autocommit** on turso (sequential, idempotent `CREATE … IF NOT EXISTS`). A partial failure simply recovers on the next boot; DML transactions still use `BEGIN CONCURRENT`.

## Identifier quoting

`"name"` — double quotes. Same as postgres + sqlite.

## File vs `:memory:`

- `:memory:` — ephemeral, per-connection, pool clamped to 1. Tests + smoke fixtures.
- `file:./db.turso` — durable. Pool of N connections share the one file; MVCC coordinates concurrent writers.

## Where it lives

- `voltro/packages/sql-turso/src/sqlLayer.ts` — the URL-scheme ROUTER: `connectionFromConfig` parses a `ConnectionConfig` (+ token/sync env) into a `local` (Rust engine) or `remote` (libsql) connection and builds the matching layer
- `voltro/packages/sql-turso/src/sqlClient.ts` — the LOCAL pooled `@effect/sql` client: async connection, per-connection prepare cache, `Pool` acquirers, `beginTransaction: 'BEGIN'` (DDL-safe default; the store upgrades its own DML transactions to `BEGIN CONCURRENT` per-fiber via `ConcurrentTransaction`), MVCC pragma, `:memory:` size-1 clamp
- `voltro/packages/sql-turso/src/libsqlClient.ts` — the REMOTE `@effect/sql` client over `@libsql/client`: autocommit `client.execute`, an interactive `client.transaction('write')` for `transactional()` (with BEGIN as a no-op and COMMIT/ROLLBACK mapped onto the tx object), and the embedded-replica sync (`syncUrl` + initial `client.sync()`). The auth token is config/env-sourced and never logged
- `voltro/packages/sql-turso/src/retry.ts` — `isTursoRetryableFailure` (matches the `"Write-write conflict"` / busy MESSAGE; the local engine tags every error with a generic code, so the retry identity is the message)
- `voltro/packages/sql-turso/src/index.ts` — `tursoDialect` (`id: 'turso'`); reuses `makeSqliteStore` from `@voltro/sql-sqlite` with the routed client + retry predicate
- `voltro/packages/sql-sqlite/src/store.ts` — the shared SQLite-family store (driver + retry predicate + span name are injected)
- `voltro/packages/database/src/migrate.ts` — turso DDL branch (numeric-id without AUTOINCREMENT; generated-column + FTS guards; autocommit DDL)