Microsoft SQL Server Dialect

Microsoft SQL Server Dialect 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. 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. 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 IDENTITYINSERT 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 IDENTITYINSERT … 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 JSONVALUE/JSONQUERY 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 SYSTEMTIME column pair and WITH (SYSTEMVERSIONING = ON (HISTORYTABLE = …)). 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 (spaddlinkedserver) 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. See also PostgreSQL dialect — the RETURNING/SERIAL/FDW baseline this dialect deviates from. mssql package docs Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL