Databricks dialect

export class DatabricksDialect extends MySQLDialect {
  readonly name = 'databricks';

  constructor(config: MySQLDialectOptions) {
    // Databricks SQL Warehouses are reached over HTTPS on port 443.
    super({ port: 443, ...config });
  }
  // ...
}

escapeId(identifier: unknown): string {
  const id = String(identifier ?? '');
  return `\`${id.replace(/`/g, '``')}\``;
}

dialect.escapeId('my_table'); // => `my_table`

$ npm install ts-prorm-orm mysql2

$ pnpm add ts-prorm-orm mysql2

$ yarn add ts-prorm-orm mysql2

$ bun add ts-prorm-orm mysql2

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

export const db = new Prorm({
  dialect: 'databricks',
  host: 'dbc-a1b2c3d4-e5f6.cloud.databricks.com',
  dialectOptions: {
    catalog: 'main',
    schema: 'default',
  },
});

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

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

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

const orm = new Prorm({
  dialect: 'databricks',
  host: 'dbc-xxxxxxxx-xxxx.cloud.databricks.com',
  // port defaults to 443 (HTTPS SQL Warehouse); no need to set it
});

import {
  DatabricksDialect,
  createDatabricksDialect,
} from 'prorm/dialects/databricks';

const dialect = new DatabricksDialect({});
// or the factory that mirrors the other dialects:
const dialect2 = createDatabricksDialect();

dialect.buildCreateTableSql(
  'events',
  {
    id: { type: 'BIGINT' },
    country: { type: 'STRING' },
    created_at: { type: 'DATE' },
  },
  { using: 'DELTA', partitionedBy: ['country'] },
);

dialect.buildCreateTableSql('t', { id: { type: 'BIGINT' } });
// => CREATE TABLE `t` (`id` BIGINT) USING DELTA

dialect.buildCreateTableSql(
  'sales',
  { id: { type: 'BIGINT' }, region: { type: 'STRING' } },
  {
    clusteredBy: { columns: ['id'], buckets: 8 },
    location: 's3://bucket/sales',
    tblproperties: { 'delta.appendOnly': 'true' },
  },
);

dialect.buildCreateTableSql('users', {
  id: { type: 'BIGINT', primaryKey: true, autoIncrement: true },
  name: { type: 'STRING' },
});

dialect.buildCreateTableAsSelect(
  'summary',
  'SELECT country, COUNT(*) AS n FROM events GROUP BY country',
  { using: 'DELTA' },
);

const { sql, values } = dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Ada', email: 'ada@example.com' },
  { conflictFields: ['id'] },
);

dialect.buildInsertQuery('users', { id: 1, name: 'Ada' });
// => INSERT INTO `users` (`id`, `name`) VALUES (?, ?)

dialect.buildInsertQuery(
  'users',
  { id: 1, name: 'Ada' },
  { upsert: true, conflictFields: ['id'] },
);
// => MERGE INTO `users` ...  (never ON DUPLICATE KEY)

dialect.getDataTypeSql('VARCHAR(255)');                 // 'STRING'
dialect.getDataTypeSql({ key: 'DECIMAL', precision: 12, scale: 4 }); // 'DECIMAL(12,4)'
dialect.getDataTypeSql({ key: 'BLOB' });                // 'BINARY'
dialect.getDataTypeSql({ key: 'DATE' });                // 'TIMESTAMP'
dialect.getDataTypeSql({ key: 'ARRAY', type: { key: 'STRING' } });   // 'ARRAY<STRING>'
dialect.getDataTypeSql({
  key: 'MAP',
  keyType: { key: 'STRING' },
  valueType: { key: 'BIGINT' },
});                                                       // 'MAP<STRING, BIGINT>'
dialect.getDataTypeSql({
  key: 'STRUCT',
  fields: { a: { key: 'STRING' }, b: { key: 'BIGINT' } },
});                                                       // 'STRUCT<a: STRING, b: BIGINT>'

dialect.buildUpdateQuery('users', { name: 'Ada' }, { id: 1 }, { limit: 5 });
// => UPDATE `users` SET `name` = ? WHERE ...   (no LIMIT), values ['Ada', 1]

dialect.buildDeleteQuery('users', { id: 1 }, { limit: 5 });
// => DELETE FROM `users` WHERE ...             (no LIMIT)

dialect.buildLimitOffset(undefined, 20); // ' OFFSET 20'
dialect.buildLimitOffset(5);             // ' LIMIT 5'
dialect.buildLimitOffset(5, 10);         // ' LIMIT 5 OFFSET 10'