Exasol dialect

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

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

export const db = new Prorm({
  dialect: 'exasol',
  host: 'exasol.internal',
  database: 'sys',
  username: 'sys',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'exasol',
  url: 'exasol://sys:secret@exasol.internal:8563/sys',
});

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

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

const orm = new Prorm({
  dialect: 'exasol',
  host: 'exasol.internal',
  port: 8563, // default if omitted; the dialect sets 8563 when no port is given
  username: 'sys',
  password: 'exasol',
  database: 'sys',
});

import { ExasolDialect, createExasolDialect } from 'prorm/dialects/exasol';

const dialect = new ExasolDialect({});
// or:
const dialect2 = createExasolDialect();

// From ExasolDialect constructor:
super({ port: 8563, ...config });

Exasol has no row-level locking, so `lock` cannot be honoured. Remove the `lock` option; a lock the caller believes it holds but does not is worse than an error.

Cannot render WHERE condition at tags.$contains for exasol: the operator '$contains' is not implemented by its WHERE builder. The statement was not sent, because running it without the condition would match rows it should not.

const sql = dialect.buildCreateTableSQL(
  'sales',
  {
    id: { type: { key: 'INTEGER' }, primaryKey: true, autoIncrement: true },
    region: { type: { key: 'STRING', length: 50 } },
    amount: { type: { key: 'DECIMAL', precision: 10, scale: 2 } },
  },
  { distributeBy: 'id', partitionBy: 'region' },
);

await dialect.addColumn('sales', 'seq', {
  type: { key: 'INTEGER' },
  autoIncrement: true,
});

const sql = dialect.buildCreateTableSQL('orders', {
  id: { type: { key: 'INTEGER' }, primaryKey: true, autoIncrement: true },
  email: { type: { key: 'STRING', length: 200 }, unique: true },
  customer_id: {
    type: { key: 'INTEGER' },
    references: { table: 'customers', field: 'id' },
  },
});

await dialect.changeColumn('sales', 'region', {
  type: { key: 'STRING', length: 100 },
  allowNull: false,
});

dialect.buildImportSQL('sales', {
  at: 'http://files.example.com',
  file: 'data.csv',
});

dialect.buildExportSQL('sales', {
  at: 'http://files.example.com',
  file: 'out.csv',
});

const { sql, values } = dialect.buildUpsertQuery(
  'sales',
  { id: 1, region: 'EU', amount: 10 },
  { conflictFields: ['id'] },
);
// values -> [1, 'EU', 10]

dialect.buildInsertQuery(
  'sales',
  { id: 1, region: 'EU', amount: 10 },
  { upsert: true, conflictFields: ['id'] },
);
// sql contains 'MERGE INTO "sales"' and never 'ON CONFLICT'

dialect.getDataTypeSql({ key: 'STRING', length: 100 }); // 'VARCHAR(100)'
dialect.getDataTypeSql({ key: 'STRING' });              // 'VARCHAR(255)'
dialect.getDataTypeSql({ key: 'TEXT' });                // 'VARCHAR(2000000)'
dialect.getDataTypeSql({ key: 'DECIMAL', precision: 12, scale: 4 }); // 'DECIMAL(12,4)'
dialect.getDataTypeSql({ key: 'INTEGER' });             // 'DECIMAL(18,0)'
dialect.getDataTypeSql({ key: 'BOOLEAN' });             // 'BOOLEAN'
dialect.getDataTypeSql({ key: 'DATE' });                // 'TIMESTAMP'

dialect.buildInsertQuery('sales', { region: 'EU', amount: 10 });
// sql:    INSERT INTO "sales" ("region", "amount") VALUES ($1, $2)
// values: ['EU', 10]

dialect.buildUpdateQuery('sales', { amount: 99 }, { region: 'EU' });
// UPDATE "sales" SET "amount" = $1 WHERE "region" = $2  ; values [99, 'EU']

dialect.buildDeleteQuery('sales', { region: 'EU' });
// DELETE FROM "sales" WHERE "region" = $1              ; values ['EU']

// returning/limit are dropped:
dialect.buildUpdateQuery('sales', { amount: 99 }, { region: 'EU' }, { returning: true, limit: 5 });
// sql contains neither 'RETURNING' nor 'LIMIT'

dialect.buildLimitOffset(5, 15); // ' LIMIT 5 OFFSET 15'