SQL ServerSupported

Install @zmdb/mssql for T-SQL compilation, modeled migration DDL, structural execution and catalog introspection. Its optional mssql peer remains application-selected; the package does not open, close or configure the pool.

Database-selection workflow#

The six official database packages use the same selection workflow. The package reference owns current install and peer ranges; the SQL Server package README includes the standalone TypeScript setup and full capability table.

A hosted-service connection guide is a recipe using one of these owners or an explicitly supplied structural adapter. Protocol compatibility alone does not create another official package or transfer the recorded server qualification to that service.

import sql from 'mssql';
import { createQueryCompiler, trustedTable } from '@zmdb/sql';
import { mssql, mssqlDriver } from '@zmdb/mssql';

const pool = await sql.connect(process.env.DATABASE_URL!);
const driver = mssqlDriver(pool);
const query = createQueryCompiler(mssql).selectFrom(trustedTable('users')).where('email', '=', 'a@b.com').compile();

const rows = await driver.execute(query);

The compiler keeps parameters positional. The adapter maps array element zero to p1, element one to p2, and so on; mssql receives those names without the leading @.

SQL contract#

A paginated SQL Server select without .orderBy(...) is refused at compile(). The compiler does not invent ORDER BY (SELECT NULL), because that would make the query legal without making its pages reproducible.

returning() maps to the correct OUTPUT pseudo-table for insert, update and delete. SQL Server rejects OUTPUT without INTO when an enabled trigger exists for that DML action. zmdb cannot inspect target-table triggers, and OUTPUT … INTO would require a table variable and another statement, so triggered tables must use a hand-written path.

Upsert locking#

SQL Server upserts compile to MERGE with an explicit conflict target:

MERGE [users] WITH (HOLDLOCK) AS tgt
USING (VALUES (@p1, @p2)) AS src ([email], [role])
ON tgt.[email] = src.[email]
WHEN MATCHED THEN UPDATE SET [role] = src.[role]
WHEN NOT MATCHED THEN INSERT ([email], [role])
VALUES (src.[email], src.[role]);

HOLDLOCK closes the absent-key race between concurrent upserts by taking serializable range locks on the target. That correctness has a cost: hot-key workloads can block longer or deadlock. SQL Server error 1205 is classified as retryable metadata, but a transaction is retried only when the caller opts into the transaction retry policy. Keep external side effects out of a retrying callback.

Types and migrations#

All ten SqlType members have an explicit SQL Server mapping. varchar uses NVARCHAR(n) with Length<n> and NVARCHAR(MAX) without one. timestamp uses DATETIMEOFFSET(3), preserving the instant and JavaScript Date millisecond precision.

There is no uuid member in SqlType, so the dialect does not invent a UNIQUEIDENTIFIER mapping. Use Sql<'varchar'> & Length<36> for an application-generated GUID, or a custom migration when the native type is required.

Generated migrations cover table creation and removal, add/drop/alter column, named foreign keys, indexes including filtered indexes, sequences and persisted computed columns.

Refusals and boundaries#

Measured coverage#

The always-on suite covers SQL Server SQL/DDL expectations and refusals, a captured SQL Server 2022 catalog, named-parameter binding, transaction pinning, and the 2,000-parameter and 1205 metadata.

A mandatory CI job and packed external consumer run migrations, CRUD, transaction rollback, type round-trips, catalog introspection and clean drift against SQL Server 2022. The developer-only suite visibly skips without ZMDB_MSSQL_URL; required lanes make the connection mandatory.

---

See also: Query Compiler · Writing a Driver · Raw SQL