SQL dialects

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.

#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 — 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:

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.