Microsoft Fabric Data Warehouse 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: 'fabric',
  host: 'abc123.datawarehouse.fabric.microsoft.com',
  database: 'Sales', // the warehouse
  dialectOptions: {
    entra: true, // DefaultAzureCredential
  },
});

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

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

export const db = new Prorm({
  dialect: 'fabric',
  host: 'abc123.datawarehouse.fabric.microsoft.com',
  database: 'Sales',
  dialectOptions: {
    entra: true, // DefaultAzureCredential: env, workload/managed identity, Azure CLI
  },
});

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

export const db = new Prorm({
  dialect: 'fabric',
  host: 'abc123.datawarehouse.fabric.microsoft.com',
  database: 'Sales',
  dialectOptions: {
    entra: { credential: { type: 'managed-identity', clientId: process.env.AZURE_CLIENT_ID } },
  },
});

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

export const db = new Prorm({
  dialect: 'fabric',
  host: 'abc123.datawarehouse.fabric.microsoft.com',
  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!,
      },
    },
  },
});

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

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

const db = new Prorm({
  dialect: 'fabric',
  host: 'abc123.datawarehouse.fabric.microsoft.com', // the warehouse's SQL connection string
  database: 'Sales', // the warehouse
  dialectOptions: {
    authentication: {
      type: 'azure-active-directory-service-principal-secret',
      options: { clientId, clientSecret, tenantId },
    },
  },
});

await db.dialect.createTable(
  'Orders',
  {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    customerId: { type: DataTypes.BIGINT, references: { table: 'Customers', field: 'id' } },
  },
  { schema: 'dbo', clusterBy: ['customerId'] },
);

db.dialect.buildSelectQuery({
  tableName: 'Sales',
  forTimestampAsOf: new Date('2024-03-13T19:39:35.28Z'),
});
// SELECT * FROM [Sales] OPTION (FOR TIMESTAMP AS OF '2024-03-13T19:39:35.280')

db.dialect.buildCtasSql(
  'Recent',
  { tableName: 'Sales', where: { region: 'EU' } },
  { clusterBy: ['region'] },
);
// CREATE TABLE [Recent] WITH (CLUSTER BY ([region])) AS SELECT * FROM [Sales] WHERE [region] = 'EU'

db.dialect.buildCloneTableSql('Employee', 'EmployeeUSA', { at: '2023-05-23T14:24:10' });
// CREATE TABLE [Employee] AS CLONE OF [EmployeeUSA] AT '2023-05-23T14:24:10'

db.dialect.buildCopyIntoSql('Sales', {
  schema: 'dbo',
  from: 'https://acct.blob.core.windows.net/c/sales/*.parquet',
  fileType: 'PARQUET',
  credential: { identity: 'Shared Access Signature', secret: sas },
});

db.dialect.buildReseedIdentitySql('DimProduct', 'dbo'); // DBCC CHECKIDENT('dbo.DimProduct', RESEED)
db.dialect.createStatisticsSQL('Sales', 'st_region', ['region']);
db.dialect.updateStatisticsSQL('Sales');