SQL Server

Connecting

Set type to DBType.MSSQL and supply an mssql-style connection string. The driver (mssql v12) is a dependency of the ORM, so nothing extra needs installing.

import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.MSSQL,
  connectionString:
    "Server=localhost,1433;Database=mydb;User Id=sa;Password=Your_password123;TrustServerCertificate=true",
  retryAttempts: 3,
  retryDelay: 1000,
};

export default dbConfig;

Unlike the pg and mysql2 pools, the SQL Server pool is not opened in the DBClient constructor. ConnectionPool.connect() is asynchronous and the constructor is not, so the pool is built there and connected on first use instead. The first query therefore pays the connection cost, and a bad connection string surfaces on that first query rather than at construction. Concurrent first queries share a single connection attempt.

// Nothing connects here...
const orm = new Stabilize(dbConfig);

// ...the pool opens here, on the first statement.
const users = await orm.getRepository(User).find().execute(orm.client);

What the Dialect Changes

T-SQL has no equivalent for several constructs the other three dialects share. Each one is rewritten rather than emulated at runtime, so statements sent to SQL Server are valid T-SQL:

Writes and OUTPUT INSERTED.*

RETURNING and OUTPUT do different jobs in different places. RETURNING trails the whole statement; OUTPUT has to sit between the column list and the VALUES clause. Appending it the way RETURNING is appended is a syntax error — Incorrect syntax near 'OUTPUT'.create() and upsert() place it correctly, and read the row back out of the result set rather than relying on last_insert_rowid() or LAST_INSERT_ID(), neither of which exists here.

-- What create() emits:
INSERT INTO "users" ("email", "name") OUTPUT INSERTED.* VALUES (@param0, @param1)

Upsert Becomes MERGE

SQL Server supports neither ON CONFLICT … DO UPDATE nor ON DUPLICATE KEY UPDATE. The equivalent is MERGE: the payload is offered as a one-row source table whose columns are the bound parameters, and the WHEN MATCHED / WHEN NOT MATCHED branches refer to those columns by name. Every value is bound exactly once — on the USING line — however many times the column is referenced afterwards.

-- await repo.upsert({ email: "ada@example.com", name: "Ada" }, ["email"])
MERGE INTO "users" AS target
USING (SELECT @param0 AS email, @param1 AS name) AS source
ON (target.email = source.email)
WHEN MATCHED THEN UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (source.email, source.name)
OUTPUT INSERTED.*;

-- With an empty key list there is nothing to match against, so it can only
-- ever insert, and the builder emits a plain INSERT ... OUTPUT INSERTED.*.

The statement is terminated with a semicolon, which MERGE requires.

Row Limiting Needs an ORDER BY

T-SQL has no LIMIT. It spells row limiting OFFSET … ROWS FETCH NEXT … ROWS ONLY, and both clauses are legal only on a statement that carries an ORDER BY. A query with no ordering is therefore given ORDER BY (SELECT NULL) purely to satisfy that rule — a constant ordering, which leaves the row order undefined exactly as an unordered LIMIT did.

-- repo.find().limit(10).offset(20), no ORDER BY set:
SELECT * FROM "users"
ORDER BY (SELECT NULL)
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

-- A bare limit skips nothing, because FETCH NEXT cannot appear without OFFSET:
-- repo.find().limit(10)
SELECT * FROM "users"
ORDER BY (SELECT NULL)
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

-- An offset with no limit is also legal:
-- repo.find().offset(20)
SELECT * FROM "users"
ORDER BY (SELECT NULL)
OFFSET 20 ROWS

As on every other dialect, paginate(page, pageSize) composes limit and offset — so SQL Server pagination works through the same call.

Schema and Migrations

Two DDL clauses used elsewhere do not exist in T-SQL. Both get an equivalent written as a leading existence check on the same batch.

-- CREATE TABLE IF NOT EXISTS does not exist. Instead:
IF OBJECT_ID(N'users', N'U') IS NULL CREATE TABLE "users" ("id" INT IDENTITY(1,1) PRIMARY KEY, ...)

-- CREATE INDEX IF NOT EXISTS does not exist either. A catalogue probe instead:
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'users_email_uniq' AND object_id = OBJECT_ID(N'users'))
  CREATE UNIQUE INDEX "users_email_uniq" ON "users" ("email")

-- ALTER TABLE ... ADD COLUMN does not exist -- T-SQL's grammar is ADD <definition>:
ALTER TABLE "users" ADD "nickname" NVARCHAR(255) NOT NULL

On MySQL the CREATE INDEX check cannot be written inline at all, so autoMigrate does it in TypeScript by reading the table's indexes first. That pre-check runs on every dialect, which is what makes autoMigrate idempotent everywhere. stabilize_migrations is created with the same IF OBJECT_ID guard.

IDENTITY Rejects Explicit Values

An integer id becomes INT IDENTITY(1,1) PRIMARY KEY, so the database generates it and you must leave it out of the payload. That is the ordinary path and it works: create({ email }) inserts and returns the row with its generated id.

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true, unique: true },
    email: { type: DataTypes.STRING, length: 255, required: true },
  },
});

// Correct -- id is IDENTITY, so it is generated:
const user = await userRepo.create({ email: "ada@example.com" });

// Fails on SQL Server, though the same call succeeds on the other dialects:
// await userRepo.create({ id: 1, email: "ada@example.com" });

Updating a row is unaffected: update(id, data) builds its SET list from the fields you pass, so the id column is not written back and the identity guard above is never reached. The exception is a payload that itself contains id, which fails with Cannot update identity column 'id'.

// UUID id -- not an IDENTITY column. It maps to UNIQUEIDENTIFIER and the
// caller supplies the value, exactly as on the other dialects.
const Account = defineModel({
  tableName: "accounts",
  columns: {
    id: { type: DataTypes.UUID, required: true, unique: true },
    email: { type: DataTypes.STRING, length: 255, required: true },
  },
});

await accountRepo.create({ id: generateUUID(), email: "ada@example.com" });

A STRING id is caller-supplied too, but note that the same declaration becomes a different column per dialect — see Data Types.

Type Mapping

Every DataTypes member maps to a T-SQL type. The unmapped fallback is NVARCHAR(MAX), not TEXT — T-SQL's TEXT is deprecated and unusable in most expressions.

Parameter Binding

mssql infers a parameter's type from the value it is handed, and has nothing to infer from for null — the request would be sent with a type the server rejects. Those are bound explicitly as a nullable NVARCHAR. A plain object or array gets the same treatment for a different reason: only Postgres' driver serialises an object parameter to JSON on the way out, so on SQL Server (and MySQL) the ORM encodes it to JSON text itself.Date and Buffer are left to the driver, which infers a correct type for both.

const Event = defineModel({
  tableName: "events",
  columns: {
    id: { type: DataTypes.INTEGER, required: true, unique: true },
    // NVARCHAR(MAX) on SQL Server; the object is stringified on the way in.
    payload: { type: DataTypes.JSON },
  },
});

await eventRepo.create({ payload: { type: "signup", plan: "pro" } });

Transactions

SQL Server has no BEGIN/COMMIT text commands. A transaction is a server-side object opened on a borrowed pooled connection, and every statement inside it must be sent through a Request built from that object — one built from the pool would run on an unrelated connection and commit on its own. The ORM does that wiring for you; the callback looks the same as on every other dialect.

await orm.transaction(async (txClient) => {
  const userRepo = orm.getRepository(User);
  const profileRepo = orm.getRepository(Profile);

  const user = await userRepo.create({ email: "ada@example.com" }, {}, txClient);
  await profileRepo.create({ userId: user.id }, {}, txClient);
});

Nothing has to be released by hand: unlike the pg and mysql2 pools, mssql returns the borrowed connection to the pool as part of commit or rollback.

Limitations