MSSQL 2019+

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

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_NAME

# 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.

#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).

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:

-- 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.)

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

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:

-- 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.59.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.59.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:

-- 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) — there is no newer @effect/cluster to bump to (0.59.0 is the latest), so the patch is how the fix reaches you.

#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.

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.

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

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

-- 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.59.0.patch — the four upstream patches (shipped in the CLI; voltro add mssql writes it into your project)