Azure SQL Database Dialect

$ npm install ts-prorm-orm mssql

$ pnpm add ts-prorm-orm mssql

$ yarn add ts-prorm-orm mssql

$ bun add ts-prorm-orm mssql

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

export const db = new Prorm({
  dialect: 'azuresql',
  host: 'myserver.database.windows.net',
  database: 'appdb',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'azuresql',
  url: 'azuresql://app:secret@myserver.database.windows.net:1433/appdb',
});

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

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

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

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

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

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

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

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

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

export const db = new Prorm({
  dialect: 'azuresql',
  host: 'myserver.database.windows.net',
  database: 'appdb',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

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

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

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

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

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

// Microsoft Entra ID (DefaultAzureCredential: env, workload/managed identity, Azure CLI)
const dialect = new Prorm({
  dialect: 'azuresql',
  host: 'myserver.database.windows.net',
  database: 'appdb',
  dialectOptions: {
    entra: true,
  },
});

// A specific credential, or your own TokenCredential (no SDK loaded)
new Prorm({
  dialect: 'azuresql',
  host,
  database,
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: '…' } },
  },
});

// Any token provider (vault, sidecar)
new Prorm({
  dialect: 'azuresql',
  host,
  database,
  dialectOptions: {
    tokenProvider,
  },
});

// tedious's own Entra flows, passed through unchanged
new Prorm({
  dialect: 'azuresql',
  host,
  database,
  dialectOptions: {
    authentication: { type: 'azure-active-directory-msi-app-service' },
  },
});

// SQL authentication still works
new Prorm({
  dialect: 'azuresql',
  host,
  database,
  username: 'app',
  password: '…',
});

d.buildCreateUserQuery('app', { password: 's3cret' });
// CREATE USER [app] WITH PASSWORD = N's3cret'
d.buildCreateUserQuery('ana@contoso.com', { externalProvider: true });
// CREATE USER [ana@contoso.com] FROM EXTERNAL PROVIDER
d.buildCreateUserQuery('app-mi', { externalProvider: true, objectId: '…' });
// CREATE USER [app-mi] FROM EXTERNAL PROVIDER WITH OBJECT_ID = N'…'
d.buildAlterUserQuery('app', { password: 'new' }); // ALTER USER [app] WITH PASSWORD = N'new'
d.buildDropUserQuery('app', { ifExists: true }); // DROP USER IF EXISTS [app]