SQL dialects

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 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 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 (Redis / NATS) closes the gap. See Multi-replica reactivity.

• Postgres — the reference. LISTEN/NOTIFY CDC, advisory locks, streaming replication.
• MySQL 8+ — 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+ — 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+ — TOP / OFFSET-FETCH instead of LIMIT, single-statement OUTPUT INSERTED/DELETED, native MERGE upsert, Change Tracking CDC, four upstream cluster patches.
• SQLite 3.38+ — single-process, in-memory workflow runner, in-process CDC bus.
• Turso (beta) — 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 (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.

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:

# 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

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

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:

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:

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 — framework emits VARCHAR(255)	yes — framework still emits VARCHAR(255) (engine parity)	yes — framework emits NVARCHAR(450) (indexable)	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:

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.