Azure SQL Managed Instance Dialect

Reference for the azuresqlmi SQL dialect (Azure SQL Managed Instance).

Source: azuresqlmi
Tests: azuresqlmi

Overview

Azure SQL Managed Instance
is a whole SQL Server instance run by Azure: several databases, USE,
cross-database queries, linked servers (to SQL targets) and SQL Server Agent
all work. AzureSQLMIDialect extends MSSQLDialect — not
AzureSQLDialect, whose single-database refusals do not apply —
and models only the documented differences.

Property	Value
Dialect name	`azuresqlmi`
Extends	`MSSQLDialect`
Driver	`mssql` (tedious)
Optional SDK	`@azure/identity` (Entra tokens)
Default port	1433 (VNet-local); 3342 with `publicEndpoint`
Cloud SDK	`@azure/identity` (Entra only)
Local runtime	none — hosted service only

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. Add @azure/identity only for the token-based logins under Managed and cloud variants.

Connect

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net',
  database: 'sales',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  url: 'azuresqlmi://app:secret@mi1.abc123.database.windows.net:1433/sales',
});

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

Managed and cloud variants

One tab per way of logging in. The token-based ones fetch a short-lived token for every new pooled connection, so a pool that outlives a token keeps working; Cloud authentication covers the providers, caching and TLS rules behind them.

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net',
  database: 'sales',
  dialectOptions: {
    entra: true, // DefaultAzureCredential: env, workload/managed identity, Azure CLI
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net',
  database: 'sales',
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: process.env.AZURE_CLIENT_ID } },
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net',
  database: 'sales',
  dialectOptions: {
    entra: {
      credential: {
        type: 'client-secret',
        tenantId: process.env.AZURE_TENANT_ID!,
        clientId: process.env.AZURE_CLIENT_ID!,
        clientSecret: process.env.AZURE_CLIENT_SECRET!,
      },
    },
  },
});

Needs @azure/identity installed next to the driver.

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

export const db = new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net',
  database: 'sales',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

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

new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.abc123.database.windows.net', // VNet-local endpoint, port 1433
  database: 'sales',
  dialectOptions: {
    entra: true, // or entra: { credential }, tokenProvider, authentication, username/password
  },
});

new Prorm({
  dialect: 'azuresqlmi',
  host: 'mi1.public.abc123.database.windows.net',
  database: 'sales',
  dialectOptions: {
    publicEndpoint: true, // port 3342, fixed by the service
    entra: true,
  },
});

Authentication and TLS work exactly as in Azure SQL Database
(both share AzureTediousAuthPlan): Entra tokens are wired through
tediousAuth() in the resolvePoolConfig
hook, tedious-native blocks pass through, SQL logins still work, TLS is
verified by default, and an Entra credential is refused over unverified TLS.
The Azure transient-fault retry defaults (AZURE_SQL_RETRY_DEFAULTS) apply too.

What differs from MSSQL

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

Area	Overridden
Connection and execution	`connect`, `query`, `resolvePoolConfig`
DDL and schema	`buildCreateEntraContainedUserSql`, `buildCreateEntraLoginSql`
Introspection	`assertSupportedStatement`
Other	`blobUrls`, `buildBackupToUrlSql`, `buildRestoreFromUrlSql`

Allowed here (refused by Azure SQL Database)

USE, cross-database three-part names, SQL Server Agent jobs (T-SQL and SSIS
steps), CREATE LOGIN (SQL and Entra). The inherited SQL Server
CREATE LOGIN … ; CREATE USER … FOR LOGIN user builder is kept.

Documented differences, refused by name

query() checks each statement (literals and comments ignored); turn off with
statementGuard: false.

Statement	Why / alternative
`BACKUP LOG`	Log backups are automatic; use point-in-time restore
`BACKUP DATABASE … TO DISK/TAPE`	Only `TO URL` (Azure Blob storage)
`BACKUP DATABASE` without `COPY_ONLY`	`COPY_ONLY` is mandatory
`BACKUP DATABASE … DIFFERENTIAL`	Only full backups
`RESTORE … FROM DISK/TAPE`, `RESTORE LOG`	Only `FROM URL`
`CREATE LOGIN … FROM WINDOWS`	Use `CREATE LOGIN … FROM EXTERNAL PROVIDER`

Builders

Builder	SQL
`buildBackupToUrlSql(db, urls, { checksum, compression, stats })`	`BACKUP DATABASE [db] TO URL = '…'[, URL = '…'] WITH COPY_ONLY[, …]` — 1–32 https stripes
`buildRestoreFromUrlSql(db, urls)`	`RESTORE DATABASE [db] FROM URL = '…'` (no `WITH` options accepted)
`buildCreateEntraLoginSql(name, { databaseUser })`	`CREATE LOGIN [name] FROM EXTERNAL PROVIDER[; CREATE USER [name] FROM LOGIN [name]]`
`buildCreateEntraContainedUserSql(name)`	`CREATE USER [name] FROM EXTERNAL PROVIDER`

Backups to URL authenticate with a managed identity or SAS credential named
after the container URL; storage access keys are not accepted.

Types

Inherited from the SQL Server dialect. The native json type is
generally available on instances with the SQL Server 2025 or
Always-up-to-date update policy; vector requires Always-up-to-date.
Mapping is handled with the shared type-mapping work.

Caveats / not-yet-verified

Tests mock mssql; nothing has run against a live instance.

The instance's update policy is not detected, so json/vector are not
gated on it.

Other documented limits (no FILESTREAM/FileTable, limited sp_configure,
CREATE DATABASE without file options, ALTER DATABASE options that
cannot change) are left to the server's own errors.

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	`azuresqlmi`
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

T-SQL differences between SQL Server and Managed Instance

Configure the public endpoint

Azure SQL Database dialect, Cloud authentication

Azure SQL Managed Instance Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Managed and cloud variants#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

Transaction with a savepoint#

Skip locked rows (READPAST)#

Connection#

What differs from MSSQL#

Allowed here (refused by Azure SQL Database)#

Documented differences, refused by name#

Builders#

Types#

Caveats / not-yet-verified#

Capabilities#

Related reading#

Overview

Quick start

Install

Connect

Define a model

Query

Managed and cloud variants

Generated SQL

Create table

Insert, update and delete

Upsert

Pagination

Transaction with a savepoint

Skip locked rows (`READPAST`)

Connection

What differs from MSSQL

Allowed here (refused by Azure SQL Database)

Documented differences, refused by name

Builders

Types

Caveats / not-yet-verified

Capabilities

Related reading