SAP HANA Dialect

$ npm install ts-prorm-orm hdb

$ pnpm add ts-prorm-orm hdb

$ yarn add ts-prorm-orm hdb

$ bun add ts-prorm-orm hdb

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

export const db = new Prorm({
  dialect: 'hana',
  host: 'my-instance.hanacloud.ondemand.com',
  port: 443,
  username: 'DBADMIN',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    schema: 'APP',
    useTLS: true, // required by HANA Cloud
  },
});

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

await db.transaction(async (t) => {
  await User.findAll({ where: { id: 1 }, lock: { level: 'UPDATE', nowait: true }, transaction: t });
});

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

const prorm = new Prorm({
  dialect: 'hana',
  host: 'my-hana-host.hanacloud.ondemand.com',
  port: 30015,
  username: 'DBADMIN',
  password: 'secret',
  database: 'HXE', // tenant database name (optional)
  dialectOptions: {
    schema: 'MY_APP',   // default schema for unqualified references
    useTLS: true,       // required for HANA Cloud
    // Any additional raw hdb.createClient() options:
    extraOptions: { encrypt: true, sslValidateCertificate: false },
  },
});

await prorm.connect();

const dialect = prorm.getDialectInstance()!;

const hana = dialect as unknown as {
  getEditionInfo(): Promise<{
    edition: 'cloud' | 'on-premise' | 'unknown';
    version: string;
    major?: number;
    minor?: number;
    revision?: number;
  }>;
  isCloudEdition(): Promise<boolean>;
};

const info = await hana.getEditionInfo();

if (await hana.isCloudEdition()) {
  // branch on Cloud-only feature availability
}

// Column table (default) — best for analytics and most workloads
await dialect.createTable('events', {
  id: { type: 'BIGINT', autoIncrement: true, primaryKey: true },
  payload: { type: 'JSON' },
  created_at: { type: 'TIMESTAMP' },
});
// -> CREATE COLUMN TABLE "events" (...)

// Row table — for narrow, high-frequency single-row OLTP access patterns
await dialect.createTable(
  'sessions',
  {
    token: { type: 'NVARCHAR', length: 64, primaryKey: true },
    user_id: { type: 'BIGINT' },
  },
  { columnStore: false },
);
// -> CREATE ROW TABLE "sessions" (...)

await dialect.createTable(
  'accounts',
  {
    id: { type: 'BIGINT', primaryKey: true },
    balance: { type: 'DECIMAL', precision: 15, scale: 2 },
  },
  { systemVersioning: { historyTable: 'accounts_history' } },
);
// -> CREATE COLUMN TABLE "accounts" (..., "valid_from" TIMESTAMP(6) NOT NULL
//    GENERATED ALWAYS AS ROW START, "valid_to" TIMESTAMP(6) NOT NULL GENERATED
//    ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME ("valid_from", "valid_to"))
//    WITH SYSTEM VERSIONING HISTORY TABLE "accounts_history"

const { sql } = dialect.buildSelectQuery({
  tableName: 'events',
  offset: 100, // no limit given
});
// -> ... LIMIT 2147483647 OFFSET 100

const { sql, values } = dialect.buildUpsertQuery('accounts', {
  id: 42,
  balance: 100.5,
});
// -> UPSERT "accounts" ("id", "balance") VALUES (?, ?) WITH PRIMARY KEY

const { sql } = dialect.buildUpsertQuery(
  'accounts',
  { email: 'a@b.com', balance: 0 },
  { conflictFields: ['email'], updateOnDuplicate: ['balance'] },
);
// -> MERGE INTO "accounts" AS target USING (SELECT ? AS "email", ? AS "balance"
//    FROM DUMMY) AS source ON target."email" = source."email"
//    WHEN MATCHED THEN UPDATE SET "balance" = source."balance"
//    WHEN NOT MATCHED THEN INSERT (...) VALUES (...)

await dialect.createSequence({ name: 'order_seq', startWith: 1000, incrementBy: 1 });

const next = await dialect.nextSequenceValue('order_seq'); // SELECT "order_seq".NEXTVAL FROM DUMMY

const tx = await dialect.startTransaction({ isolationLevel: 'SERIALIZABLE' });
try {
  await dialect.query('UPDATE "accounts" SET "balance" = "balance" - ? WHERE "id" = ?', {
    replacements: [50, 1],
    transaction: tx,
  });
  await dialect.commitTransaction(tx);
} catch (err) {
  await dialect.rollbackTransaction(tx);
  throw err;
}

dialect.escapeId('users');   // -> "users"  (stays lowercase)
dialect.quoteTable('users', 'MY_APP'); // -> "MY_APP"."users"