Trino Dialect

export class TrinoDialect extends PostgresDialect {
  readonly name = 'trino';
  // ...
}

$ 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: 'trino',
  host: 'trino.internal',
  username: 'analyst',
  dialectOptions: {
    catalog: 'hive',
    schema: 'analytics',
  },
});

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

export const db = new Prorm({
  dialect: 'trino',
  url: 'trino://analyst@trino.internal:8080/hive',
  dialectOptions: {
    catalog: 'hive',
    schema: 'analytics',
  },
});

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

const sample = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  sample: { method: 'BERNOULLI', percent: 10 },
});
await db.query(sample.sql);

export interface TrinoDialectOptions extends PostgresDialectOptions {
  /** Connector catalog (e.g. 'hive', 'iceberg'). Enables catalog.schema.table naming. */
  catalog?: string;
  /** Default schema used when a query/table doesn't specify one. */
  schema?: string;
}

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

const prorm = new Prorm({
  dialect: 'trino',
  host: 'trino.internal',
  port: 8080,            // optional; the dialect defaults to 8080
  username: 'analyst',
  database: 'hive',      // PostgreSQL-style connection field
  dialectOptions: {
    catalog: 'hive',     // enables "catalog"."schema"."table" naming
    schema: 'analytics', // default schema when a query omits one
  },
});

quoteTable(tableName: string, schema?: string): string {
  if (this.trinoCatalog) {
    const effectiveSchema = schema ?? this.trinoSchema;
    const parts = [this.escapeId(this.trinoCatalog)];
    if (effectiveSchema) {
      parts.push(this.escapeId(effectiveSchema));
    }
    parts.push(this.escapeId(tableName));
    return parts.join('.');
  }
  return super.quoteTable(tableName, schema);
}

new TrinoDialect({ catalog: 'iceberg', schema: 'default' })
  .quoteTable('t', 'staging');            // => "iceberg"."staging"."t"

new TrinoDialect({}).quoteTable('events'); // => "events"  (no catalog -> bare name)

export interface TrinoTableOptions extends TableOptions {
  format?: string;                 // -> format = '...'
  partitioning?: string[];         // -> partitioning = ARRAY['...']
  tableProperties?: Record<string, string | number | boolean>;
  schema?: string;
}

d.buildCreateTableSQL(
  't',
  { id: { type: 'BIGINT' }, day: { type: 'VARCHAR' } },
  { format: 'ORC', partitioning: ['day'] },
);

if (typeof value === 'string') {
  props.push(`${key} = '${value.replace(/'/g, "''")}'`);
} else {
  props.push(`${key} = ${value}`);
}

d.buildCreateTableSQL(
  't',
  { id: { type: 'BIGINT' } },
  { tableProperties: { bucket_count: 8, transactional: true, sorted_by: 'id' } },
);

export interface TrinoCTASOptions extends TrinoTableOptions {
  withNoData?: boolean; // emit WITH NO DATA
}

d.buildCreateTableAsSelectSQL('summary', 'SELECT 1 AS n', {
  format: 'PARQUET',
  withNoData: true,
});

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

d.getDataTypeSql({ key: 'STRING' });                       // 'VARCHAR'
d.getDataTypeSql({ key: 'TEXT' });                         // 'VARCHAR'
d.getDataTypeSql({ key: 'FLOAT' });                        // 'REAL'
d.getDataTypeSql({ key: 'DOUBLE' });                       // 'DOUBLE'
d.getDataTypeSql({ key: 'BOOLEAN' });                      // 'BOOLEAN'
d.getDataTypeSql({ key: 'BIGINT' });                       // 'BIGINT'
d.getDataTypeSql({ key: 'DATE' });                         // 'TIMESTAMP'
d.getDataTypeSql({ key: 'DATEONLY' });                     // 'DATE'
d.getDataTypeSql({ key: 'JSON' });                         // 'JSON'
d.getDataTypeSql({ key: 'JSONB' });                        // 'JSON'
d.getDataTypeSql({ key: 'BLOB' });                         // 'VARBINARY'
d.getDataTypeSql({ key: 'ARRAY', type: { key: 'BIGINT' }});// 'ARRAY(BIGINT)'
d.getDataTypeSql({ key: 'ARRAY' });                        // 'ARRAY(VARCHAR)'
d.getDataTypeSql('DATETIME');                              // 'TIMESTAMP'
d.getDataTypeSql('DATETIME(6)');                           // 'TIMESTAMP(6)'

protected getTrinoColumnDefinitionSql(columnName, definition): string {
  let sql = `${this.escapeId(columnName)} ${this.getDataTypeSql(definition.type)}`;
  if (definition.allowNull === false) sql += ' NOT NULL';
  if (definition.comment) sql += ` COMMENT '...'`;
  return sql;
}

d.buildCreateTableSQL('t', {
  id:    { type: 'BIGINT', primaryKey: true, autoIncrement: true },
  orgId: { type: 'BIGINT', references: { table: 'orgs', field: 'id' } },
  name:  { type: 'VARCHAR', unique: true, defaultValue: 'x' },
});
// Output contains NONE of: PRIMARY KEY, FOREIGN KEY, REFERENCES, UNIQUE, SERIAL, DEFAULT

plain.buildInsertQuery('users', { id: 1 }, { upsert: true, conflictFields: ['id'] });
// throws: "Trino does not support upsert / ON CONFLICT. ..."

plain.buildUpsertQuery('users', { id: 1 }, { conflictFields: ['id'] });
// throws: "Trino does not support upsert / ON CONFLICT. ..."

plain.buildInsertQuery('users', { id: 1, name: 'ann' });
// INSERT INTO "users" ("id", "name") VALUES ($1, $2)   (no RETURNING)

cataloged.buildUpdateQuery('users', { name: 'bob' }, { id: 5 }, { returning: true, limit: 10 });
// UPDATE "hive"."sales"."users" SET "name" = $1 WHERE ...   (no RETURNING, no LIMIT)

cataloged.buildDeleteQuery('users', { id: 5 }, { returning: true, limit: 3 });
// DELETE FROM "hive"."sales"."users" WHERE ...             (no RETURNING, no LIMIT)

plain.buildDeleteQuery('users', {}, { truncate: true });
// DELETE FROM "users"

plain.dropTable('users', { ifExists: true, cascade: true });
// DROP TABLE IF EXISTS "users"

await plain.addIndex('t', 'i', ['c']);
// throws: "Trino does not support indexes (addIndex is unavailable). ..."

Trino 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 trino: 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.