Presto Dialect

Reference for the presto SQL dialect (PrestoDB).

Source: presto
Tests: presto

Overview

PrestoDB is the original Presto distributed SQL engine.
Trino is its 2019 fork (formerly PrestoSQL). The SQL grammar is
shared — ANSI SQL, double-quoted identifiers, catalog.schema.table, OFFSET
before LIMIT, connector properties in CREATE TABLE ... WITH (...), no key
constraints, no indexes, MERGE INTO — so PrestoDialect extends
TrinoDialect and overrides only what has verifiably diverged.

Property	Value
Dialect name	`presto`
Extends	`TrinoDialect` (trino)
Driver	none — global `fetch`
Cloud SDK	none
Default port	8080
Local runtime	self-hosted; no image in this repo

export class PrestoDialect extends TrinoDialect {
  // name === 'presto'; library === 'fetch'
}

Quick start

Install

$ npm install ts-prorm-orm

$ pnpm add ts-prorm-orm

$ yarn add ts-prorm-orm

$ bun add ts-prorm-orm

No driver package: the dialect talks to the service over fetch.

Connect

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

export const db = new Prorm({
  dialect: 'presto',
  host: 'presto.internal',
  username: 'analyst', // X-Presto-User
  dialectOptions: {
    catalog: 'hive',
    schema: 'web',
  },
});

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

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

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

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

Query

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

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

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

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

MERGE statement

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

JSON

[!NOTE] No JSON operator in the WHERE builder
DataTypes.JSON() maps to JSON, but this dialect's WHERE builder refuses Op.contains on it: Cannot render WHERE condition at meta.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.

Transaction

[!CAUTION] No transactions
This Presto client runs every statement in autocommit. Presto transactions are connector-limited (the Hive connector has none) and are carried in the X-Presto-Transaction-Id header, which a multi-statement ORM transaction would have to thread through every request. Make each statement self-contained (INSERT ... SELECT, MERGE, CTAS) instead.

Table sampling

sample on the select builder reads a random fraction of the table, for estimates over large tables:

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

Type mapping for the portable descriptors: STRING → VARCHAR, auto-increment INTEGER → INTEGER, BOOLEAN → BOOLEAN, DATE → TIMESTAMP, UUID → UUID, JSON → JSON. The type comparison lines the major engines up side by side.

Connection — Presto's REST protocol

Presto has no socket protocol. A client POSTs the statement to
/v1/statement and follows each response's nextUri until there is none.
Session state travels in headers — and this is the key divergence from
Trino: PrestoDB's headers are X-Presto-*; Trino renamed all of them to
X-Trino-*.

TrinoDialect inherits PostgreSQL's pg pool, which speaks neither engine's
protocol. PrestoDialect replaces the connection layer with a small
fetch-based client. No npm driver is needed — fetch is global from
Node 18. Every header name comes from one protected field,
protocolHeaderPrefix = 'X-Presto-'.

The dialect is reached through Prorm (the class is not exported from the package root):

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

const d = new Prorm({
  dialect: 'presto',
  host: 'presto.internal',
  // port defaults to 8080
  username: 'analyst',        // X-Presto-User (required)
  password: process.env.PRESTO_PASSWORD, // HTTP Basic
  schema: 'web',              // X-Presto-Schema
  dialectOptions: {
    protocol: 'https',          // default 'http'; password auth requires https
    catalog: 'hive',            // X-Presto-Catalog and "catalog"."schema"."table" naming
    sessionProperties: { query_max_run_time: '1h' },
  },
});
await d.connect();            // probes GET /v1/info

Option	Default	Header / effect
`username`	— (required)	`X-Presto-User`
`password`	—	`Authorization: Basic` (refused over `http`)
`catalog`, `schema`	—	`X-Presto-Catalog`, `X-Presto-Schema`
`source`	`'ts-prorm-orm'`	`X-Presto-Source`
`timeZone`	`'UTC'`	`X-Presto-Time-Zone`
`language`	—	`X-Presto-Language`
`clientTags`	—	`X-Presto-Client-Tags`
`sessionProperties`	`{ offset_clause_enabled: true }`	`X-Presto-Session`
`queryTimeoutMs`	`300000`	cancel (`DELETE nextUri`) and throw after this
`fetch`	global `fetch`	injectable for tests/proxies

The client:

retries HTTP 503 after 50–100 ms, as the protocol specifies;

applies X-Presto-Set-Catalog, -Set-Schema, -Set-Session and
-Clear-Session response headers, so USE and SET SESSION persist;

throws the server's error.message and errorName;

reports DML updateCount as rowCount;

DELETEs the running query's nextUri on timeout, so it does not keep
running on the cluster.

getDatabaseVersion() reads nodeVersion.version from /v1/info.

Values

The protocol has no bind parameters outside PREPARE/EXECUTE, so $n and
:name replacements are inlined as Presto literals (formatPrestoLiteral):

Value	Literal
string	`'it''s'`
`Date`	`TIMESTAMP 'yyyy-MM-dd HH:mm:ss.SSS'` (UTC — the default session zone)
`Buffer`	`X'6869'`
array	`ARRAY[...]`
object	`JSON '{...}'`
boolean / null	`TRUE` / `FALSE` / `NULL`
`NaN` / `Infinity`	refused

Presto does not coerce a varchar to a timestamp, which is why a Date is a
typed literal rather than a quoted string. If you change timeZone, pass
dates as explicit literals.

What differs from Trino

PrestoDialect extends TrinoDialect (trino) and
overrides 23 of its methods (Object.getOwnPropertyNames(PrestoDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
Connection and execution	`applySessionHeaders`, `buildSetSessionSql`, `commitTransaction`, `connect`, `disconnect`, `getConnection`, `isConnected`, `listen`, `notify`, `query`, `queryStream`, `rollbackTransaction`, `startTransaction`
DML builders	`buildDeleteQuery`, `replaceReplacements`
Types	`getDataTypeSql`
DDL and schema	`createSavepointSQL`
Introspection	`fetchInfo`, `getDatabaseVersion`
Other	`buildRequestHeaders`, `formatPrestoLiteral`, `request`, `transactionRefusal`

Divergences from Trino

	Trino dialect	Presto dialect
Headers	(`pg` pool — no REST client)	`X-Presto-*`
`OFFSET`	always available	off by default server-side (`offset-clause-enabled=false`); the dialect sends `offset_clause_enabled=true` in the session header unless you override it
`TIMESTAMP(p)`, `TIME(p)`	emitted	precision dropped — Presto's types are fixed at milliseconds and `TIMESTAMP(6)` is a syntax error
truncate	`DELETE FROM t`	`TRUNCATE TABLE t`
`SET SESSION`	—	`buildSetSessionSql(name, value)` (name validated, value a literal)

Inherited unchanged: three-part names, WITH (...) connector properties, CTAS,
OFFSET ... LIMIT ordering, no key constraints, no indexes (the index refusal
message names Trino, the parent), and the MERGE INTO upsert — which on
Presto runs only against the Iceberg connector.

Type mapping

As Trino, except DATETIME(n) → TIMESTAMP and
TIME(n) → TIME (no precision). UUID, JSON, VARBINARY, REAL,
DOUBLE, ARRAY(T) exist on Presto as on Trino.

Refused, by name

Call	Why
`startTransaction`, `commitTransaction`, `rollbackTransaction`	Every statement runs in autocommit. Presto transactions are connector-limited (Hive has none) and carried in `X-Presto-Transaction-Id`; this client does not thread one through.
`createSavepointSQL`	No `SAVEPOINT`.
`listen`, `notify`	PostgreSQL machinery inherited through Trino; Presto has none.
`connect()` with a password over `http`	The coordinator refuses it, and it would expose the password.

Caveats / not-yet-verified

The client is tested against a mocked fetch (tests/dialects/presto.test.ts);
it has not been run against a live coordinator in this suite.

queryStream uses the shared LIMIT/OFFSET pager, which re-runs the query
per page.

Dialect.library is 'fetch' — there is no package to install.

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`presto`
Transactions atomic	yes
Savepoints	no — a nested `transaction()` throws
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only yes; deferrable yes; constraint deferral yes
RETURNING	not emitted; `returning` is accepted and ignored
Upsert	`MERGE INTO … WHEN MATCHED / WHEN NOT MATCHED`
JSON column	`JSON` (`JSONB` → `JSON`)
Vector column	`ARRAY(REAL)`
Array column	`ARRAY(INTEGER)`
WHERE builder	46 operator keys; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `unknown`: triggers no; procedures no; functions no; materialized views no; sequences no; enums `check`; RLS no
Streaming	`queryStream()` present; `LISTEN` present; `createExtension()` present

All dialects

Trino dialect — everything inherited

Presto client REST protocol

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

JSON#

Transaction#

Table sampling#

Connection — Presto's REST protocol#

Values#

What differs from Trino#

Divergences from Trino#

Type mapping#

Refused, by name#

Caveats / not-yet-verified#

Capabilities#

Related reading#