Trino Dialect

Trino Dialect Reference for the trino SQL dialect. Source: trino Tests: trino, trino-crud 1. Overview Trino (formerly PrestoSQL) is a distributed, federated ANSI-SQL query engine. It is not a storage engine of its own — it runs SQL over pluggable connectors (Hive, Iceberg, MySQL, PostgreSQL, Kafka, …). Because its SQL surface is ANSI-flavored and it quotes identifiers with double quotes (like PostgreSQL), TrinoDialect extends PostgresDialect and overrides only where Trino differs: Key defaults and differences (all implemented in the source): Default port 8080 — Trino's HTTP/query port, not PostgreSQL's 5432. Injected by the constructor via super({ port: 8080, ...config }). Three-part catalog.schema.table naming when a catalog is configured. Connector table properties via CREATE TABLE ... WITH (...). CREATE TABLE AS SELECT (CTAS) as the primary way to materialize a table. No key constraints (PRIMARY KEY / FOREIGN KEY / UNIQUE / DEFAULT), no identity/SERIAL, no upsert, no indexes. 2. Connection Trino-specific options extend the PostgreSQL options with a connector catalog and a default schema: When constructing through Prorm, dialect-specific extras (like catalog and schema) pass through dialectOptions: The catalog option A Trino table lives in a connector catalog and a schema. catalog is what turns on Trino's fully-qualified three-part naming: With a catalog configured, quoteTable emits "catalog"."schema"."table" (schema included only when known). Without a catalog, it falls back to PostgreSQL's schema.table behavior (a bare "table" when no schema is given). Examples (from the tests): A per-call schema argument overrides the configured default schema. 3. Trino-specific behavior catalog.schema.table naming Every DDL/CRUD builder qualifies the target table through quoteTable, so once a catalog is set, statements target "catalog"."schema"."table": Connector table properties — WITH (...) buildCreateTableSQL appends a connector-properties clause built by buildTablePropertiesClause, which handles format, partitioning, and arbitrary tableProperties: Building a table with a file format and partitioning: produces SQL containing: Arbitrary tableProperties — string/number/boolean rendering buildTablePropertiesClause renders each tableProperties entry as key = value, single-quoting strings (escaping embedded quotes) and emitting numbers and booleans raw: renders the properties as: (format and partitioning, when present, are emitted first; tableProperties follow in object order.) CREATE TABLE AS SELECT (CTAS) buildCreateTableAsSelectSQL materializes a table from a raw SELECT. It supports ifNotExists, comment, the same WITH (...) connector properties, and a trailing WITH NO DATA (create structure only, copy no rows): produces: createTableAsSelect(tableName, selectQuery, options) is the async wrapper that executes the built statement. Pagination — OFFSET before LIMIT Trino orders OFFSET before LIMIT (unlike PostgreSQL's LIMIT n OFFSET m) and allows a standalone OFFSET: 4. Type mapping getDataTypeSql maps ORM types to Trino spellings. The cross-dialect DATETIME pseudo-type (emitted by the model-sync layer for timestamp columns) is rewritten to TIMESTAMP, preserving any precision. ORM type / key | Trino SQL | Notes | --- | --- | --- | STRING | VARCHAR | Unbounded — no length emitted | TEXT | VARCHAR | Unbounded | CHAR | CHAR(n) / CHAR | Length used when present | INTEGER | INTEGER | No SERIAL; autoIncrement ignored | BIGINT | BIGINT | | FLOAT | REAL | | DOUBLE | DOUBLE | Not DOUBLE PRECISION | DECIMAL | DECIMAL(precision, scale) | Defaults DECIMAL(10, 0) | BOOLEAN | BOOLEAN | | DATE | TIMESTAMP | Mapped to timestamp | DATEONLY | DATE | | TIME | TIME | | BLOB | VARBINARY | | JSON | JSON | | JSONB | JSON | | UUID | UUID | | ARRAY | ARRAY(elementType) | Falls back to ARRAY(VARCHAR) when the element type is unknown | 'DATETIME' / 'DATETIME(n)' (string pseudo-type) | TIMESTAMP / TIMESTAMP(n) | Precision preserved | unknown object / non-object | VARCHAR | Default fallback | A raw string data type that is not DATETIME is passed through unchanged. Verified assertions from the tests: 5. Unsupported operations Trino neither supports nor enforces key constraints, identity columns, upserts, or indexes. The dialect handles each explicitly. Key constraints — omitted (not thrown) Column definitions are built by getTrinoColumnDefinitionSql, which emits only the name, type, an optional NOT NULL, and an optional COMMENT. It silently ignores primaryKey, unique, references (FOREIGN KEY), defaultValue, and autoIncrement: So a table declared with PK/FK/UNIQUE/DEFAULT metadata still produces constraint-free DDL: NOT NULL and IF NOT EXISTS are honored. ADD COLUMN uses the same builder, so added columns also carry no constraints: No identity / SERIAL INTEGER maps to INTEGER and the autoIncrement flag is ignored — Trino has no SERIAL/BIGSERIAL/IDENTITY. Surrogate key values must be supplied by the application or source connector. No upsert / ON CONFLICT — throws Both buildInsertQuery (with options.upsert) and buildUpsertQuery throw rather than emitting invalid INSERT ... ON CONFLICT: The error message suggests using INSERT, or MERGE (on connectors that support it) via raw SQL. No RETURNING, no LIMIT on write statements INSERT, UPDATE, and DELETE never emit RETURNING or LIMIT, even when those options are passed: No TRUNCATE — degrades to DELETE Trino has no TRUNCATE; a truncate request becomes an unqualified DELETE FROM: No CASCADE on DROP TABLE dropTable supports IF EXISTS but never emits CASCADE (there are no dependent constraints): No indexes — throws Trino has no CREATE INDEX; data layout is controlled by connector table properties (partitioning, bucketedby, …). Every index entry point throws via noIndexes(...): addIndex createIndex removeIndex dropIndex createPartialIndex createExpressionIndex 6. Not-yet-verified The Trino test suites run without a database connection — they assert only on generated SQL strings and thrown errors. The following are covered by the source and tests: SQL-generation verified (no live Trino): catalog.schema.table naming and per-call schema override (quoteTable). CREATE TABLE with WITH (...) (format, partitioning, arbitrary tableProperties — string/number/boolean rendering). Omission of PRIMARY KEY / FOREIGN KEY / UNIQUE / DEFAULT / SERIAL; honoring of NOT NULL and IF NOT EXISTS. CREATE TABLE AS SELECT including WITH NO DATA. The full type-mapping table above. INSERT / UPDATE / DELETE SQL (catalog-qualified, no RETURNING/LIMIT), truncate → DELETE FROM, DROP TABLE without CASCADE, ADD/DROP COLUMN. Pagination ordering (OFFSET before LIMIT, standalone OFFSET). Upsert and all index operations throwing. Needs a live Trino cluster (not exercised here): Actual connection/handshake over port 8080 (the dialect inherits PostgreSQL's pg-based connection machinery, which is not the Trino wire protocol — real connectivity is unverified by these tests). Runtime execution of any statement (createTable, createTableAsSelect, addColumn, removeColumn, dropTable, and CRUD): UPDATE/DELETE support in particular is connector-dependent at execution time. Whether specific WITH (...) properties, file formats, and partitioning schemes are accepted by a given connector (Hive, Iceberg, …). MERGE-based upsert on connectors that support it (suggested by the error message but not implemented by the dialect). Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL