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_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

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. Since @effect/sql-mssql 0.54.0 the driver's own default is the opposite — encrypt and verify the certificate — so the framework now passes encrypt: false explicitly for an unset connection: the modes above are the framework's contract, and a driver default changing under them must not turn "unset" into verify-full (a mode this dialect does not offer) and break every self-signed instance at boot.

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

#Request cancellation

Interrupting a SQL Server query or procedure cancels its Tedious request and waits for the server's completion acknowledgement before returning the connection to the pool. Transaction rollback follows that acknowledgement too. The next request can reuse the same connection; cancellation does not wait for the full query duration. SQL errors still propagate normally.

This requires the @effect/sql-mssql@0.54.0 patch shipped with the CLI. For a new SQL Server project, or when upgrading an existing one, run these commands at the pnpm workspace root:

voltro add mssql
pnpm install

A missing @voltro/sql-mssql dependency is pinned to the running CLI version, so installation also works in a standalone app without local framework packages. An existing driver dependency range is preserved.

The command installs both the driver patch and the cluster patch into patches/ and declares them in pnpm-workspace.yaml. Repeating it refreshes the shipped assets without duplicating entries. voltro update includes a migration note for SQL Server projects; installing framework packages alone cannot apply a workspace-local pnpm patch.

#Lossless wire parameters

Numeric parameters use INT for signed 32-bit integers, BIGINT for larger safe JavaScript integers, and FLOAT for other finite numbers. Fractions are not truncated and long millisecond durations are not limited to 32 bits on the wire. Integer parameters remain suitable for TOP, OFFSET and FETCH. NaN and infinities are rejected. Destination column constraints still apply; this does not add arbitrary decimal precision to JavaScript numbers.

Voltro binds JS Date values as DateTime2 and strings as NVarChar, matching the schema before SQL Server receives them. Explicit null also uses NVarChar so it can convert into non-bit columns. This covers row writes, predicates and raw SQL, not only .onUpdate() timestamps.

Binary parameters for bytes() columns accept Uint8Array and Node.js Buffer values and bind as VARBINARY. A subarray sends exactly its visible byte range, respecting its offset and length; an empty array sends empty binary data. This applies to row writes, predicates and raw SQL without requiring callers to convert a Uint8Array to a Buffer themselves.

Upstream defaults (DateTime / VarChar) can round milliseconds or replace Unicode characters before columns receive them; a later CAST cannot recover them. Actual-server tests cover all 1,000 millisecond values in a second, dates before 1753, Unicode and JSON keys. See SQL Server datetime rounding and Tedious parameter types.

#Integer column range changes

An integer() to bigint() migration preserves dependent defaults, CHECK constraints, UNIQUE constraints and ordinary row-store indexes, including their names and definitions. The applier removes and restores these dependencies inside the schema transaction; a failed alteration rolls back the changes. CHECK trust and enabled states and index key order, included columns, filters and supported options are retained.

Foreign keys, schema-bound expressions, explicit user statistics and unsupported indexes (including partitioned or compressed indexes) are refused before any dependency is removed. Use a reviewed file-based migration that restores the dependent definitions for those cases. Dependency preservation also covers an explicitly acknowledged bigint() to integer() narrowing; values outside the target range make the alteration fail and roll back. Other type changes do not receive this dependency handling. SQL Server refuses a declarative using expression instead of ignoring it; perform that transform in a reviewed file-based migration.

#No RETURNING — trigger-compatible OUTPUT … INTO

MssqlStore captures the row images produced by each write: insert, update, patchJson, delete, bulk writes and MERGE upserts. SQL Server rejects a bare OUTPUT result when the target has enabled triggers, including Timeline capture triggers. The store therefore uses OUTPUT … INTO and returns the captured images from a temporary table.

Before the write, the store reads column metadata. It then creates the temporary capture table, executes the DML, reads the captured images and drops the table in one SQL batch. Identity and computed values remain server-generated; rowversion images are captured as binary values. The temporary table is scoped to that driver request, including failure and cancellation paths. Cancellation waits for the in-flight driver request to finish before transactional cleanup; it does not return a connection while SQL is still running. This involves a metadata request plus the write batch; it is not a single network round trip.

-- Simplified insert example; the store derives the capture shape from the target.
CREATE TABLE #written (id nvarchar(100), title nvarchar(200), done bit);
INSERT INTO todos (id, title, done)
OUTPUT INSERTED.* INTO #written
VALUES (?, ?, ?);
SELECT * FROM #written;
DROP TABLE #written;

The returned values come from the write itself, not a later reread of the target table. Update/delete predicates are still evaluated by the DML statement. An update or delete with no matching key returns null or false. As with SQL Server's ordinary OUTPUT, images precede changes made by AFTER triggers. Existing transactions and bulk-write rollback remain intact.

#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.* INTO …:

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.* INTO #merge_output;

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.

The framework's own migration-ledger reads follow the same rule. Before this was fixed, voltro serve's schema check read the newest applied plan with LIMIT 1, treated the resulting syntax error as "no plan applied", and refused every production boot on SQL Server with live=<no _voltro_migration_plans row> — including the boot right after voltro db apply. voltro db plans, db drift and db restore-snapshot had the same spelling. A production voltro serve against a schema voltro dev or voltro db apply created now starts.

#Integer-literal LIMIT (same as MySQL)

The compiler inlines validated integer literals for TOP/OFFSET/FETCH NEXT values. Raw SQL can also bind integer parameters through Voltro's value-aware numeric wire codec.

#Text columns with a DEFAULT — NVARCHAR(450)

A plain text() column is NVARCHAR(MAX), which SQL Server cannot index. A text column that carries a literal default or a closed value set is therefore emitted bounded, at NVARCHAR(450) — under the 900-byte single-column key limit, so it stays indexable:

text().default('open')                 // → NVARCHAR(450) + a DEFAULT constraint
text().oneOf(['open', 'closed'])       // → NVARCHAR(450) + a CHECK constraint
text().nullable()                      // → NVARCHAR(MAX)

This holds for migrations as well as for CREATE TABLE: adding .default(…) to an existing text column retypes it to NVARCHAR(450) and then adds the default constraint, so the column has the same type whether the default was declared before or after the table existed. It is a narrowing — if a row already holds more than 450 characters, the ALTER COLUMN fails ("String or binary data would be truncated") and the migration stops there. Check first, and use text().maxLength(n).default(…) to choose a different width:

SELECT COUNT(*) FROM tickets WHERE LEN(status) > 450

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

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 Change Tracking events carry netChange: true. They may combine intermediate writes and provide current row images or delete keys only. Timeline records this provenance and refuses historical reconstruction across these events, including inserts whose post-image appears complete; the marker does not restrict reactive query refreshes.

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.

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

#Retained schema snapshots

Soft-dropping a SQL Server column or table uses the same sp_rename path as an explicit rename. Column CHECK constraints are rewritten for the retained name while preserving their enabled and trusted state. voltro db restore-snapshot <plan-id> uses the recorded snapshot identity to restore the original table and column names and their retained values; it does not guess ownership from a name suffix.

After the Change Tracking reader has stopped, a failure from an already in-flight poll is not reported as a retry or delivered to its error consumer. Poll failures while the reader is active remain reported.