Microsoft SQL Server Dialect

Overview

The mssql dialect targets Microsoft SQL Server (and Azure SQL) through the
mssql Node.js package, which uses the
pure-JavaScript tedious driver under the hood. It generates T-SQL: bracketed
[identifiers], @pN bind parameters, OUTPUT clauses in place of Postgres's
RETURNING, IDENTITY columns instead of SERIAL, and OFFSET/FETCH
pagination.

Property	Value
Dialect name	`mssql`
Extends	nothing — implements `Dialect` directly
Driver	`mssql`
Cloud SDK	none
Default port	1433
Local runtime	`prorm db up --dialect mssql` (`mcr.microsoft.com/mssql/server:2022-CU14-ubuntu-22.04`); `docker-compose.test.yml`

Status: Experimental. The core CRUD/DDL surface is implemented and
parameterized, but several advanced areas are stubbed or partial (see
Known limitations). Treat this dialect as unverified
against a live server until you have exercised it end-to-end.

Quick start

Install

$ npm install ts-prorm-orm mssql

$ pnpm add ts-prorm-orm mssql

$ yarn add ts-prorm-orm mssql

$ bun add ts-prorm-orm mssql

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'mssql',
  host: 'localhost',
  database: 'app',
  username: 'sa',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    encrypt: true,
    trustServerCertificate: true, // local development only
  },
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'mssql',
  url: 'mssql://sa:secret@localhost:1433/app',
  dialectOptions: {
    encrypt: true,
    trustServerCertificate: true, // local development only
  },
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

MERGE statement

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Transaction with a savepoint

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

Skip locked rows (`READPAST`)

A worker that claims rows from a queue table should pass over rows another transaction holds rather than wait for them. lock with skipLocked inside a transaction:

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

Type mapping for the portable descriptors: STRING → NVARCHAR(255), auto-increment INTEGER → INT, BOOLEAN → BIT, DATE → DATETIME2, UUID → UNIQUEIDENTIFIER, JSON → NVARCHAR(MAX). The type comparison lines the major engines up side by side.

Connection

The constructor defaults are host: 'localhost', port: 1433,
database: 'master', username: 'sa', password: ''. Because encrypt
defaults to true, connecting to a local server with a self-signed certificate
relies on trustServerCertificate: true (also the default) — set both to
false only when you have a properly trusted certificate chain.

Data types

DataTypes map to T-SQL as follows. Strings use the Unicode NVARCHAR family
by default.

prorm `DataTypes`	T-SQL type	Notes
`STRING`	`NVARCHAR(255)`	`STRING('max')` → `NVARCHAR(MAX)`
`CHAR`	`CHAR(n)`	defaults to `CHAR(1)`
`TEXT`	`NVARCHAR(n)` / `NVARCHAR(MAX)`	`n > 4000` or unbounded → `NVARCHAR(MAX)`; legacy `TEXT`/`NTEXT` are avoided
`INTEGER`	`INT`	length `1`→`TINYINT`, `2`→`SMALLINT`, `8`→`BIGINT`
`BIGINT`	`BIGINT`
`FLOAT`	`FLOAT` / `FLOAT(n)`
`DOUBLE`	`FLOAT`	SQL Server has no distinct `DOUBLE`
`DECIMAL(p, s)`	`DECIMAL(p, s)`	defaults to `DECIMAL(10, 0)`
`BOOLEAN`	`BIT`	escaped as `1`/`0`
`DATE`	`DATETIME2` / `DATETIME2(p)`
`DATEONLY`	`DATE`
`TIME`	`TIME` / `TIME(p)`
`BLOB`	`VARBINARY(MAX)`	`Buffer` escaped as `0x…` hex
`UUID`	`UNIQUEIDENTIFIER`
`ENUM(...)`	`NVARCHAR(255)`	no native enum; values are not constrained by the column type
`JSON` / `JSONB`	`NVARCHAR(MAX)`	SQL Server stores JSON as text (see JSON)
`GEOMETRY`	`GEOMETRY`

Parameter binding (`@pN`)

SQL Server uses named parameters prefixed with @. This dialect emits
positional placeholders named @p1, @p2, … and binds the accompanying value
array by position. When you call dialect.query() directly, pass the values
under replacements (a positional array) or bindings:

Every query builder below produces @pN placeholders and a parallel values
array, so you never hand-concatenate values into SQL. Identifiers are quoted
with brackets ([Product]), and embedded ] characters are doubled.

The supported WHERE operators are $eq, $ne, $gt, $gte, $lt, $lte,
$like, $notLike, $in, $notIn, $between, $notBetween, $isNull, plus
the logical $and, $or, $not. Any other operator throws rather than being
silently dropped.

The `OUTPUT` clause (T-SQL's `RETURNING`)

SQL Server has no RETURNING; the equivalent is the OUTPUT clause referencing
the INSERTED/DELETED pseudo-tables. Pass returning to any write builder:

UPDATE/DELETE also support a row cap via limit, which is rewritten to
UPDATE TOP(n) / DELETE TOP(n).

`IDENTITY` columns

autoIncrement: true emits IDENTITY(1,1). SQL Server rejects an explicit value
for an IDENTITY column unless SET IDENTITY_INSERT is enabled for the session,
and only one table per session may have it enabled at a time. The insert() and
bulkInsert() methods expose an opt-in identityInsert flag that brackets the
statement with SET IDENTITY_INSERT … ON/OFF in a try/finally, so the
session setting never leaks even if the insert throws:

You can also add an identity column to an existing table:

Pagination (`OFFSET` / `FETCH` / `TOP`)

SQL Server cannot combine TOP with OFFSET/FETCH in one statement, and
OFFSET/FETCH requires an ORDER BY. The select builder handles both:

limit only → SELECT TOP(n) … (inserted after DISTINCT when present).

offset present → … ORDER BY … OFFSET n ROWS FETCH NEXT m ROWS ONLY. If
you did not specify an order, a harmless ORDER BY (SELECT NULL) is injected
so pagination still works.

Upsert (`MERGE`)

Upserts compile to a MERGE statement (terminated with the semicolon T-SQL
requires). By default the primary/unique columns act as the match target:

Bulk insert

bulkInsert() splits large batches to respect T-SQL's hard limits — 1,000 rows
per table-value constructor and 2,100 parameters per batch — and wraps
multi-chunk inserts in a single BEGIN/COMMIT TRANSACTION for atomicity. Pass
batchSize to shrink batches further (it can only make them smaller) and
identityInsert to supply explicit identity values across the whole batch:

JSON

SQL Server stores JSON as NVARCHAR(MAX) text and queries it with the
JSON_VALUE/JSON_QUERY functions. buildJsonExtract() produces these:

The select builder also supports a forJson option (FOR JSON PATH/AUTO,
with root, includeNullValues, and withoutArrayWrapper) so the server can
render the whole result set as a JSON document, and CROSS APPLY/OUTER APPLY
lateral joins via an include entry with apply + tableFunction (e.g.
OPENJSON(...)).

Temporal (system-versioned) tables

createTable() accepts a temporal option that emits the hidden
PERIOD FOR SYSTEM_TIME column pair and
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = …)). Queries can then time-travel
via temporalAsOf, temporalBetween, or temporalAll:

Other T-SQL specifics

Views: T-SQL has no CREATE OR REPLACE VIEW; replace: true emits
CREATE OR ALTER VIEW (SQL Server 2016 SP1+).

CTEs: written with plain WITH — T-SQL has no RECURSIVE keyword, so the
recursive flag on a CTE definition is documentation-only.

Sequences, stored procedures, triggers, computed columns, filtered
(partial) indexes, full-text indexes, spatial indexes are all implemented.

Transactions use BEGIN/COMMIT/ROLLBACK TRANSACTION and nest via
@@TRANCOUNT.

User management creates a server LOGIN plus a database USER, and drops
them in the correct dependency order.

Row-level security maps to

CREATE SECURITY POLICY … ADD FILTER
PREDICATE

; it requires a predicate function name (there is no USING-clause
fallback).

Known limitations

Be aware of the following gaps, which are honest reflections of the current
implementation rather than oversights hidden behind silent no-ops:

Table partitioning is not modeled. createPartitionedTable,
createPartition, attachPartition, detachPartition, dropPartition, and
addPartition all throw. SQL Server uses CREATE PARTITION FUNCTION +
CREATE PARTITION SCHEME, which don't map onto the ORM's declarative
per-table partitioning API. Create partition functions/schemes with raw
query() SQL instead.

Materialized views are only partially supported. They map to indexed
views, but the required UNIQUE CLUSTERED INDEX is not generated — the
uniqueIndex option currently emits only a reminder comment, so you must
create that index manually for the view to actually materialize.

Foreign Data Wrappers are unsupported. All FDW methods throw; FDW is a
PostgreSQL feature. Use SQL Server linked servers (sp_addlinkedserver) via
raw SQL if you need cross-server access.

changeOwner, addConstraint, and removeConstraint throw. Note that
the separate createConstraint/dropConstraint methods do work (via
ALTER TABLE … ADD/DROP CONSTRAINT); prefer those.

No cascading drops. dropTable, dropView, and dropSchema reject a
cascade option because T-SQL has no cascading drop for these objects — drop
dependents manually in dependency order first.

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`mssql`
Transactions atomic	yes
Savepoints	yes (no explicit RELEASE)
Isolation	up to `SERIALIZABLE`; native levels `SNAPSHOT`
Transaction modes	read-only no; deferrable no; constraint deferral no
RETURNING	`OUTPUT INSERTED.…` on INSERT
Upsert	`MERGE INTO … WHEN MATCHED / WHEN NOT MATCHED`
JSON column	`NVARCHAR(MAX)` (`JSONB` → `NVARCHAR(MAX)`)
Vector column	`VECTOR(3)`
Array column	`NVARCHAR(255)`
WHERE builder	18 operator keys; `$and`/`$or`/`$not`; `$exists`: no; `$in` subquery: no; expression operands: no
SQL objects	family `mssql`: triggers yes; procedures yes; functions `multiStatement`; materialized views emulated (`indexedView`); sequences yes; enums `check`; RLS no
Streaming	`queryStream()` present; `LISTEN` absent; `createExtension()` present

Testing

tests/dialects/mssql.test.ts exercises the dialect with a mocked driver, so nothing touches a server. A live instance: prorm db up --dialect mssql (see Local databases).

All dialects — what each engine supports

Data types — how each type maps to this engine

Querying — how a FindOptions becomes SQL

Microsoft SQL Server Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

Transaction with a savepoint#

Skip locked rows (READPAST)#

Connection#

Data types#

Parameter binding (@pN)#

The OUTPUT clause (T-SQL's RETURNING)#

IDENTITY columns#

Pagination (OFFSET / FETCH / TOP)#

Upsert (MERGE)#

Bulk insert#

JSON#

Temporal (system-versioned) tables#

Other T-SQL specifics#

Known limitations#

See also#

Capabilities#

Testing#

Related reading#