Exasol dialect

Exasol dialect Overview Exasol is an in-memory, column-oriented, massively-parallel (MPP) analytic database. Its SQL grammar and wire behavior are close enough to PostgreSQL that this dialect extends PostgresDialect and reuses the pg-style grammar (double-quoted identifiers, $n positional placeholders, plain SELECT/WHERE/ORDER BY/GROUP BY/JOIN, LIMIT n OFFSET m) as its base, overriding only the places where Exasol genuinely differs. Source: src/dialects/exasol/index.ts (extends src/dialects/postgres/index.ts). Key differences from PostgreSQL captured by this dialect: Default SQL port is 8563 (not 5432). CREATE TABLE supports the MPP DISTRIBUTE BY and PARTITION BY clauses. No SERIAL / BIGSERIAL — auto-increment columns use an IDENTITY constraint on a numeric (DECIMAL) column. Type mapping differs: TEXT becomes VARCHAR(2000000), integers become DECIMAL, etc. (see Type mapping). Bulk load/unload uses IMPORT / EXPORT rather than PostgreSQL's COPY. No RETURNING clause and no ON CONFLICT — the inherited write builders are overridden to strip RETURNING and to express upserts as a MERGE. Connection You can also construct the dialect directly (this is how the connection-free test suite exercises it): The constructor applies port: 8563 as a default but any caller-supplied port still wins: Exasol-specific features All of the SQL-building helpers below are synchronous and can be exercised without a live connection. CREATE TABLE with DISTRIBUTE BY / PARTITION BY buildCreateTableSQL(tableName, columns, options?) emits an ordinary column list, then appends Exasol's MPP clauses when the options are present. DISTRIBUTE BY (hash distribution across cluster nodes) is always emitted before PARTITION BY (on-disk partitioning within a node). Both distributeBy and partitionBy accept a single column name or an array of column names. With a multi-column distribution list (distributeBy: ['region', 'id']): Pass { ifNotExists: true } to emit CREATE TABLE IF NOT EXISTS. IDENTITY auto-increment (not SERIAL) Exasol has no SERIAL / BIGSERIAL. Auto-increment is expressed as an IDENTITY column constraint on a numeric (DECIMAL) column. This applies both in CREATE TABLE and in addColumn: When a column is autoIncrement, any defaultValue is skipped. Column constraints (UNIQUE / REFERENCES) Column-level UNIQUE and REFERENCES constraints are supported: Produces (fragments): MODIFY COLUMN Exasol uses ALTER TABLE ... MODIFY COLUMN col type rather than PostgreSQL's ALTER COLUMN col TYPE type: IMPORT / EXPORT (bulk load / unload) buildImportSQL and buildExportSQL emit Exasol's file-based bulk load/unload statements. Options: format ('CSV' | 'FBV', defaults to 'CSV'), at (the AT '<connection-or-url>' endpoint), and file (the FILE '<path>' to read/write). Single quotes in at/file are escaped by doubling. The schema option (if any) is not used by these builders — qualify the table name yourself if you need a schema prefix. Upsert Exasol has no INSERT ... ON CONFLICT, so upserts are expressed as a MERGE statement. Two entry points build the same MERGE: buildUpsertQuery(tableName, values, options) — direct upsert. buildInsertQuery(tableName, values, options) with options.upsert set — routes to the same MERGE builder (buildMergeSQL). Both require conflictFields — the key column(s) to match existing rows on. Without them the builder throws (conflictFields is required), because Exasol's MERGE needs an explicit ON condition that cannot be inferred from the inserted columns. The single row is presented as a one-row derived source via a FROM-less SELECT $1 AS "col", ... (Exasol permits SELECT without FROM). Rows are matched on conflictFields; matched rows are updated and unmatched rows are inserted. The conflict columns are excluded from the UPDATE SET clause (Exasol forbids updating the columns that appear in the ON condition). Routing an INSERT to a MERGE (equivalent output): Behavior details: Explicit update list. Pass updateOnDuplicate to limit which columns are updated on match (conflict columns are still excluded): All columns are keys. If every column is a conflict key, no WHEN MATCHED clause is emitted (there is nothing left to update): Composite conflict keys are joined with AND in the ON condition: Type mapping getDataTypeSql overrides the PostgreSQL mapping for the types that differ on Exasol. Anything not listed below delegates to the PostgreSQL base mapping. ORM type (key) | Exasol SQL | Notes | TEXT | VARCHAR(2000000) | Exasol has no TEXT; uses its max VARCHAR width | STRING (length n) | VARCHAR(n) | Defaults to VARCHAR(255) when no length | CHAR (length n) | CHAR(n) | Defaults to CHAR(1) | INTEGER | DECIMAL(18,0) | No SERIAL; auto-increment via IDENTITY | BIGINT | DECIMAL(36,0) | Exasol has no narrow integer types | DECIMAL (p, s) | DECIMAL(p,s) | Defaults to DECIMAL(18,0) | BOOLEAN | BOOLEAN | Straight through | DATE | TIMESTAMP / TIMESTAMP(p) | With precision when provided | DATEONLY | DATE | | DATETIME (string form) | TIMESTAMP (and TIMESTAMP(p)) | The DATETIME pseudo-type is normalized to TIMESTAMP | Examples from the test suite: CRUD (inherited from PostgreSQL, with RETURNING/LIMIT stripped) INSERT uses $n placeholders and never emits RETURNING (the returning option is ignored): Bulk insert (inherited from the PG base) emits one VALUES tuple per row. UPDATE and DELETE inherit the PostgreSQL grammar, but Exasol supports neither RETURNING nor LIMIT on them, so both options are stripped before delegating to the base builder: Plain SELECT (WHERE, ORDER BY, GROUP BY / HAVING, JOIN) and buildLimitOffset are inherited unchanged — Exasol's SELECT grammar matches the PostgreSQL forms the base emits, including LIMIT n OFFSET m: Caveats Identifier case folding. Exasol upper-cases unquoted identifiers (like Oracle). This dialect quotes identifiers with double quotes (inherited from PostgresDialect), so the ORM's mixed-case names are preserved exactly rather than folded to upper case. No RETURNING. Exasol has no RETURNING on INSERT/UPDATE/DELETE. The write builders deliberately omit it even when options.returning is requested — you will not get generated keys or affected rows back inline. No ON CONFLICT. Upserts are always emitted as MERGE and require conflictFields (the builder throws otherwise). No LIMIT on UPDATE/DELETE. These options are silently stripped. Placeholders. This dialect keeps PostgreSQL's $n positional placeholder style so it composes with the rest of the ORM's parameter handling. Exasol's own JDBC/WebSocket drivers use ?; a live driver binding would need to translate accordingly. Registered with Prorm. dialect: 'exasol' is wired into the Prorm factory (lazy-loaded), so the connection snippet above works; the tests additionally construct ExasolDialect directly to exercise the sync builders without a connection. Verification status SQL-generation verified. All type mapping, CREATE TABLE (DISTRIBUTE BY / PARTITION BY / IDENTITY / UNIQUE / REFERENCES), MODIFY COLUMN, IMPORT / EXPORT, INSERT, MERGE-based upsert, UPDATE, DELETE, and SELECT output shown here is covered by unit tests (tests/dialects/exasol.test.ts, tests/dialects/exasol-crud.test.ts) that assert on the generated SQL strings. No live database is required for these. Needs a live Exasol. Actual execution against a running Exasol instance — driver binding, $n-to-? placeholder translation, IDENTITY/MERGE runtime semantics, and IMPORT/EXPORT against a real endpoint — is not exercised by the test suite and has not been verified here. </invoke> Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL