Amazon RDS for SQL Server Dialect

Overview

Amazon RDS for SQL Server
is Microsoft SQL Server run by AWS. It speaks T-SQL over TDS through the same
mssql (tedious) driver, so RdsMSSQLDialect extends MSSQLDialect and
inherits all of its SQL. It changes three things: TLS is verified against the
RDS CA, statements that need sysadmin are refused before they are sent, and
the RDS stored procedures are available as builders.

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

mssql is the same driver the mssql dialect uses.

Connect

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

export const db = new Prorm({
  dialect: 'rdsmssql',
  host: 'mydb.abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'admin',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    caPath: '/etc/ssl/rds/global-bundle.pem',
  },
});

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

export const db = new Prorm({
  dialect: 'rdsmssql',
  url: 'rdsmssql://admin:secret@mydb.abc123.us-east-1.rds.amazonaws.com:1433/app',
  dialectOptions: {
    caPath: '/etc/ssl/rds/global-bundle.pem',
  },
});

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:

IF OBJECT_ID(N'[users]', N'U') IS NULL CREATE TABLE [users] ([id] INT PRIMARY KEY IDENTITY(1,1), [email] NVARCHAR(255) NOT NULL UNIQUE, [name] NVARCHAR(100), [createdAt] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, [updatedAt] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);

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 } });

-- insert
INSERT INTO [users] ([email], [name]) VALUES ('ada@example.com', 'Ada');

-- update
UPDATE [users] SET [name] = 'Grace', [updatedAt] = '2026-09-27 01:01:42.950' WHERE [email] = 'ada@example.com';

-- delete
DELETE FROM [users] WHERE [id] = 1;

Upsert

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

MERGE INTO [users] AS target USING (SELECT 1 AS [id], 'ada@example.com' AS [email], 'Ada Lovelace' AS [name]) AS source ON target.[id] = source.[id] WHEN MATCHED THEN UPDATE SET [email] = source.[email], [name] = source.[name] WHEN NOT MATCHED THEN INSERT ([id], [email], [name]) VALUES (source.[id], source.[email], source.[name]);;

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,
});

SELECT * FROM [users] WHERE [name] LIKE 'A%' ORDER BY [id] ASC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

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(() => {});
});

-- driver: begin() (mssql)
INSERT INTO [users] ([email], [name]) VALUES ('grace@example.com', 'Grace');
SAVE TRANSACTION [prorm_sp_1];
DELETE FROM [users] WHERE [id] = 1;
ROLLBACK TRANSACTION [prorm_sp_1];
-- driver: commit() (mssql)

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 });
});

SELECT * FROM [users] WITH (UPDLOCK, ROWLOCK, READPAST) WHERE [name] = 'Ada';

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 and TLS

Every RDS for SQL Server instance has a certificate whose CN is the instance
endpoint, issued by an Amazon RDS CA that Node does not trust by default.
The parent dialect defaults to trustServerCertificate: true (encrypted but
unverified); this dialect defaults to encrypt: true,
trustServerCertificate: false, and hands tedious the RDS CA bundle as
options.cryptoCredentialsDetails.ca through the resolvePoolConfig hook.

const db = new Prorm({
  dialect: 'rdsmssql',
  host: 'mydb.abc123.us-east-1.rds.amazonaws.com',
  database: 'app',
  username: 'admin',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    caPath: '/etc/ssl/rds/global-bundle.pem', // or `ca: pem`, or $PRORM_RDS_CA_BUNDLE
    multiAz: false,
  },
});

Download the bundle once from
https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem (see
cloud-auth). With verification on and no
bundle configured, connect() throws CloudAuthError (INVALID_CONFIG)
naming the URL. trustServerCertificate: true or encrypt: false turn
verification off (and need no bundle). To make the server refuse unencrypted
clients, set rds.force_ssl = 1 in the DB parameter group and reboot.

IAM database authentication is not available for SQL Server (RDS
supports it for MariaDB, MySQL and PostgreSQL only), so there is no token path.

What differs from MSSQL

RdsMSSQLDialect extends MSSQLDialect (mssql) and
overrides 12 of its methods (Object.getOwnPropertyNames(RdsMSSQLDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

What it refuses

The master user is in the processadmin, public and setupadmin server
roles and is db_owner of user databases, without backup permissions.
RdsMSSQLDialect.query() runs assertAllowedOnRds() first:

Keywords inside string literals, [bracketed] names and comments are ignored.

RDS stored procedures, as builders

Each returns the EXEC text with escaped N'...' literals; run it with
db.dialect.query(sql). Backup and restore are asynchronous tasks.

const d = db.dialect as RdsMSSQLDialect;
await d.query(
  d.buildBackupDatabaseSql({
    database: 'app',
    s3Arn: 'arn:aws:s3:::my-bucket/app*.bak',
    numberOfFiles: 4,
    type: 'FULL',
  }),
);
await d.query(d.buildTaskStatusSql({ database: 'app' }));

References

• RDS for SQL Server
• Security: roles and permissions
• Unsupported features
• Native backup and restore
• Using SSL
• Renaming a Multi-AZ database
• Stored procedure list

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.

Testing

tests/dialects/rdsmssql.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

See also

• All dialects — the family tree and the capability matrix
• mssql — the parent dialect this one extends