Amazon Athena Dialect

export class AthenaDialect extends TrinoDialect {
  readonly library = '@aws-sdk/client-athena';
  // ...
}

$ npm install ts-prorm-orm @aws-sdk/client-athena

$ pnpm add ts-prorm-orm @aws-sdk/client-athena

$ yarn add ts-prorm-orm @aws-sdk/client-athena

$ bun add ts-prorm-orm @aws-sdk/client-athena

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

export const db = new Prorm({
  dialect: 'athena',
  database: 'analytics', // the Glue database
  dialectOptions: {
    region: 'us-east-1',
    workGroup: 'primary',
    outputLocation: 's3://my-bucket/athena-results/',
  },
});

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.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

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

export interface AthenaDialectOptions extends TrinoDialectOptions {
  region?: string;                  // e.g. 'us-east-1'
  workGroup?: string;               // default 'primary'
  outputLocation?: string;          // 's3://my-bucket/athena-results/'
  encryptionOption?: 'SSE_S3' | 'SSE_KMS' | 'CSE_KMS';
  kmsKey?: string;
  resultReuseMaxAgeMinutes?: number;
  credentials?: { accessKeyId: string; secretAccessKey: string; sessionToken?: string };
  endpoint?: string;
  pollIntervalMs?: number;          // default 200
  maxPollIntervalMs?: number;       // default 5000
  queryTimeoutMs?: number;          // default 300000
  pageSize?: number;                // default 1000 (Athena's maximum)
  tableType?: 'hive' | 'iceberg';   // default 'hive'
}

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

const prorm = new Prorm({
  dialect: 'athena',
  database: 'analytics',            // the Glue database
  dialectOptions: {
    region: 'us-east-1',
    workGroup: 'analytics-wg',
    outputLocation: 's3://my-bucket/athena-results/',
    catalog: 'AwsDataCatalog',      // enables "catalog"."db"."table" in queries
    tableType: 'iceberg',           // unlocks UPDATE / DELETE / MERGE
  },
});

new AthenaDialect({ region: 'us-east-1' }).connect();
// throws: "Athena needs somewhere to write query results: set `outputLocation` ..."

d.escapeId('events');        // "events"          — queries (inherited from Trino)
d.escapeHiveId('events');    // `events`          — DDL
d.quoteTable('events');      // "analytics"."events" (with a catalog: three parts)
d.quoteHiveTable('events');  // `analytics`.`events`

d.buildCreateTableSQL(
  'events',
  { id: { type: DataTypes.BIGINT }, name: { type: DataTypes.STRING } },
  { location: 's3://bucket/events/' },
);

d.buildCreateTableSQL('events', { id: { type: DataTypes.BIGINT } });
// throws: "Athena cannot create the table 'events' without an S3 `location`. ..."

export interface AthenaTableOptions extends TrinoTableOptions {
  location?: string;                                  // LOCATION 's3://...'   (required)
  partitionedBy?: Record<string, DataType>;           // PARTITIONED BY (`day` string)
  partitionTransforms?: string[];                     // Iceberg: PARTITIONED BY (day(ts), bucket(16, id))
  storedAs?: string;                                  // STORED AS PARQUET
  serde?: string;                                     // ROW FORMAT SERDE '...'
  serdeProperties?: Record<string, string>;           // WITH SERDEPROPERTIES (...)
  rowFormatDelimited?: string;                        // ROW FORMAT DELIMITED ...
  bucketedBy?: string[];                              // CLUSTERED BY (...) INTO n BUCKETS
  bucketCount?: number;
  tblProperties?: Record<string, string | number | boolean>;
  partitionProjection?: AthenaPartitionProjection;
  tableType?: 'hive' | 'iceberg';                     // per-table override
  external?: boolean;                                 // default true for Hive
}

// Hive partition columns live in the S3 key, never in the data file.
d.buildCreateTableSQL('events', { day: { type: DataTypes.STRING } },
  { location: 's3://b/e/', partitionedBy: { day: DataTypes.STRING } });
// throws: "'day' is declared both as a column and in `partitionedBy` ..."

d.buildCreateTableSQL('events', { id: { type: DataTypes.BIGINT } },
  { location: 's3://b/e/', bucketedBy: ['id'] });
// throws: "`bucketedBy` on 'events' needs a `bucketCount` ..."

new AthenaDialect({ database: 'analytics', tableType: 'iceberg' }).buildCreateTableSQL(
  'events',
  { id: { type: DataTypes.BIGINT }, ts: { type: DataTypes.DATE } },
  { location: 's3://bucket/events/', format: 'PARQUET',
    partitionTransforms: ['day(ts)', 'bucket(16, id)'] },
);

d.buildCreateTableAsSelectSQL('summary', 'SELECT day, count(*) AS n FROM events GROUP BY day', {
  location: 's3://bucket/summary/',
  format: 'PARQUET',
  partitionedBy: ['day'],
  writeCompression: 'ZSTD',
});

await d.repairTable('events');
// MSCK REPAIR TABLE `analytics`.`events`      — scan S3 for key=value directories

await d.addPartitionSpec('events', { day: '2024-01-01' }, {
  location: 's3://bucket/events/2024-01-01/', ifNotExists: true,
});
// ALTER TABLE `analytics`.`events` ADD IF NOT EXISTS PARTITION (`day` = '2024-01-01')
//   LOCATION 's3://bucket/events/2024-01-01/'

await d.dropPartitionSpec('events', { day: '2024-01-01' }, { ifExists: true });
await d.showPartitions('events');               // SHOW PARTITIONS `analytics`.`events`

d.buildCreateTableSQL('events', { id: { type: DataTypes.BIGINT } }, {
  location: 's3://bucket/events/',
  partitionedBy: { region: DataTypes.STRING, day: DataTypes.STRING },
  partitionProjection: {
    storageLocationTemplate: 's3://bucket/events/${region}/${day}/',
    columns: {
      region: { type: 'enum', values: ['us-east-1', 'eu-west-1'] },
      day: { type: 'date', range: ['2020/01/01', 'NOW'], format: 'yyyy/MM/dd',
             interval: 1, intervalUnit: 'DAYS' },
    },
  },
});

{ region: { type: 'enum' } }                       // throws: is type 'enum' but lists no `values`
{ day: { type: 'date', format: 'yyyy' } }          // throws: is type 'date' but has no `range`
{ day: { type: 'date', range: ['2020', 'NOW'] } }  // throws: is type 'date' but has no `format`

(await d.query('SELECT id FROM t')).rows[0].id;  // '9007199254740993', not 9007199254740992

d.getLastQueryExecutionId();   // 'a1b2c3d4-...' — the key for Athena history and CloudTrail
d.getLastQueryStatistics();    // { dataScannedInBytes, engineExecutionTimeInMillis, ... }

await d.query('SELECT * FROM t WHERE name = $1', { replacements: ["o'brien"] });
// submitted: SELECT * FROM t WHERE name = 'o''brien'

await d.getDatabaseVersion();   // 'Athena engine version 3'   (via GetWorkGroup)

d.getHiveDataTypeSql(DataTypes.TIME);
// throws: "A TIME column is not supported on athena. Athena table definitions have no
//          time-of-day type. Store the value as a `string`, or as a `timestamp` ..."

const hive = new AthenaDialect({ database: 'analytics' });
hive.buildUpdateQuery('events', { n: 1 }, { id: 1 });
// throws: "UPDATE is not supported on athena. Athena runs UPDATE only against Apache
//          Iceberg tables. Configure the dialect with `tableType: 'iceberg'` ..."

const iceberg = new AthenaDialect({ database: 'analytics', tableType: 'iceberg' });
iceberg.buildUpsertQuery('events', { id: 1, n: 2 }, { conflictFields: ['id'] });
// MERGE INTO "events" AS t USING (VALUES ($1, $2)) AS s ("id", "n") ON t."id" = s."id"
//   WHEN MATCHED THEN UPDATE SET "n" = s."n"
//   WHEN NOT MATCHED THEN INSERT ("id", "n") VALUES (s."id", s."n")

await d.addColumn('events', 'age', { type: DataTypes.INTEGER });
// ALTER TABLE `analytics`.`events` ADD COLUMNS (`age` int)

await d.dropTable('events', { ifExists: true, cascade: true });
// DROP TABLE IF EXISTS `analytics`.`events`        (never CASCADE)

await d.createSchema('staging');
// CREATE DATABASE IF NOT EXISTS `staging`          (a Glue database; SCHEMA is a synonym)

await d.dropSchema('staging', { ifExists: true, cascade: true });
// DROP DATABASE IF EXISTS `staging` CASCADE

await hive.removeColumn('events', 'age');
// throws: "... For a Hive table, restate the surviving columns with
//          `ALTER TABLE ... REPLACE COLUMNS (...)` ..."

await d.showTables();        // information_schema.tables  WHERE table_schema = 'analytics'
await d.showViews();         // information_schema.views
await d.describeTable('events');