Azure Synapse Analytics (dedicated SQL pool)

$ 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: 'synapse',
  host: 'my-workspace.sql.azuresynapse.net',
  database: 'my_pool',
  username: 'sqladminuser',
  password: process.env.SYNAPSE_PASSWORD,
  dialectOptions: {
    encrypt: true,
  },
});

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

export const db = new Prorm({
  dialect: 'synapse',
  url: 'synapse://sqladminuser:secret@my-workspace.sql.azuresynapse.net:1433/my_pool',
  dialectOptions: {
    encrypt: true,
  },
});

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) },
}, { timestamps: false });

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

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 db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

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

const prorm = new Prorm({
  dialect: 'synapse',
  host: 'my-workspace.sql.azuresynapse.net',
  port: 1433,
  database: 'my_pool',
  username: 'sqladminuser',
  password: process.env.SYNAPSE_PASSWORD,
  dialectOptions: { encrypt: true },
});

const prorm = new Prorm({
  dialect: 'synapse',
  host: 'my-workspace.sql.azuresynapse.net',
  database: 'my_pool',
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: process.env.AZURE_CLIENT_ID } },
  },
});

const dialect = new SynapseDialect({ /* ... */ });
dialect.name;    // 'synapse'
dialect.library; // 'mssql' — inherited; there is no separate Synapse driver

await dialect.createTable(
  'FactSales',
  {
    saleId:    { type: 'BIGINT', allowNull: false },
    region:    { type: 'NVARCHAR(50)', allowNull: false },
    orderDate: { type: 'DATE', allowNull: false },
    amount:    { type: 'DECIMAL(18,2)' },
  },
  {
    distribution: { type: 'HASH', columns: 'region' },
    index: { type: 'CLUSTERED COLUMNSTORE INDEX' },
    partition: { column: 'orderDate', range: 'RIGHT', boundaries: ['2024-01-01', '2025-01-01'] },
  },
);

dialect.splitPartitionSQL('FactSales', '2026-01-01');            // ALTER TABLE ... SPLIT RANGE (...)
dialect.mergePartitionSQL('FactSales', '2024-01-01');            // ALTER TABLE ... MERGE RANGE (...)
dialect.switchPartitionSQL('Stage', 1, 'FactSales', 3);          // ALTER TABLE ... SWITCH PARTITION 1 TO ...

{ id: { type: 'INT', allowNull: false, primaryKey: true } }
// [id] INT NOT NULL PRIMARY KEY NONCLUSTERED NOT ENFORCED

dialect.buildSelectQuery({ tableName: 'FactSales', limit: 10 });
// SELECT TOP(10) * FROM [FactSales]

dialect.buildSelectQuery({ tableName: 'FactSales', limit: 10, offset: 20 });
// throws: OFFSET ... FETCH pagination is not supported by Azure Synapse Analytics (dedicated SQL pool).

await dialect.createTableAs(
  'AggSales',
  'SELECT region, SUM(amount) AS total FROM [FactSales] GROUP BY region',
  { distribution: { type: 'HASH', columns: 'region' }, index: { type: 'CLUSTERED COLUMNSTORE INDEX' } },
);
// CREATE TABLE [AggSales] WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([region])) AS SELECT ...

await dialect.createMaterializedView({
  name: 'mvSales',
  schema: 'dbo',
  query: 'SELECT region, SUM(amount) AS total FROM [FactSales] GROUP BY region',
  distribution: { type: 'HASH', columns: 'region' },
});
// CREATE MATERIALIZED VIEW [dbo].[mvSales] WITH (DISTRIBUTION = HASH([region])) AS SELECT ...

dialect.createStatisticsSQL('FactSales', 'st_region', ['region']);
dialect.updateStatisticsSQL('FactSales');