Microsoft SQL Server

Microsoft SQL Server 2017 and up, over the pure-JavaScript mssql driver. 2017 is the floor because STRING_AGG is the newest T-SQL the dialect emits; everything else is 2016 or older.

Connect

npm install uql-orm mssql

import { MsSqlQuerierPool } from 'uql-orm/mssql';

export const pool = new MsSqlQuerierPool({
  server: 'localhost',
  database: 'app',
  user: 'app',
  password: process.env.DB_PASSWORD,
  options: { encrypt: true, trustServerCertificate: false },
});

The first argument is mssql’s own config verbatim. Sizing, lifetime and shutdown are the same on every driver: see Pool.

Snapshot isolation

Set it, once, per database:

MSSQL

ALTER DATABASE app SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SQL Server’s default READ COMMITTED takes shared read locks where PostgreSQL and MySQL use MVCC, so a concurrent ORM workload deadlocks on patterns that never deadlock elsewhere. This is the fix, and UQL’s own test database runs with it on.

What differs from the other engines

• Strings are NVARCHAR, always. VARCHAR is a codepage type that silently drops anything outside it, and UQL never exposes the choice.
• String comparison follows the database collation, case-insensitive by default, as on MySQL. $regex matches case-sensitively regardless.
• $regex needs SQL Server 2025 at database compatibility level 170, where REGEXP_LIKE exists. Below that the server rejects the statement itself.
• $text is refused. CONTAINS needs a full-text catalogue, which UQL does not create.
• Vector search is exact, through VECTOR_DISTANCE on SQL Server 2025 with a declared dimensions. Its DiskANN index is still a preview feature, so every distance is computed rather than read from an index.
• Two cascade paths to one table are refused by the server (error 1785), and so is RESTRICT, which T-SQL lacks. The default action, NO ACTION, meets neither.
• expr.uuidv7() throws. NEWSEQUENTIALID() is an ordered v4 GUID with no readable timestamp, so it is not served in place of a v7.

Generated ids

OUTPUT INSERTED reports one id per row, so insertMany returns them exact, with no inference from a header as on MySQL. Writing an explicit value into an identity column works too: UQL wraps that insert in SET IDENTITY_INSERT, which the engine otherwise refuses.

Migrations

uql-migrate reads the schema back through INFORMATION_SCHEMA and the sys catalogue views, and diffs it like any other engine. A column’s default, CHECK and UNIQUE are constraints the server names itself, so dropping the column drops them first, and a retype puts the default back. Renames go through sp_rename. The one thing it will not do is create a table or index with IF NOT EXISTS, which T-SQL has no form of.