SAP HANA Dialect

SAP HANA Dialect The hana dialect targets SAP HANA — both on-premise HANA 2.0 and the continuously-delivered HANA Cloud editions. It is implemented in hana on top of SAP's official hdb client: a pure JavaScript, callback-based driver with no native bindings. HANA is a column-store-first, in-memory HTAP database. The dialect leans into that: tables are column tables by default, and HANA-native constructs like UPSERT ... WITH PRIMARY KEY, GENERATED ALWAYS AS IDENTITY, sequences, and system-versioned temporal tables are all first-class. Overview | | Dialect key | 'hana' | Driver / library | hdb | Default port | 30015 | Default user | SYSTEM | Identifier quoting | Double quotes ("col"), case-sensitive | Default table storage | Column store (CREATE COLUMN TABLE) | Pagination | LIMIT n OFFSET m | Upsert | Native UPSERT ... WITH PRIMARY KEY | Install the driver alongside the ORM: The hdb package ships no bundled TypeScript types; the dialect provides its own minimal ambient shim (src/dialects/hana/hdb.d.ts), so you do not need @types/hdb. Connection Connect through the Prorm constructor with dialect: 'hana'. HANA-specific options (schema, useTLS, extraOptions) are passed under dialectOptions. Defaults if omitted: host: 'localhost', port: 30015, username: 'SYSTEM', password: ''. The default port 30015 follows SAP's 3<instance>15 convention — the SQL port of the tenant database's index server on a Multi-Database Container (MDC) system using instance number 00 (30013 is the system database on the same instance; instance 01 uses 30115/30117). HANA Cloud vs on-premise HANA Cloud connections require TLS (useTLS: true). You can detect which edition you are connected to — the dialect parses SYS.MDATABASE.VERSION (HANA Cloud reports a 4.x-prefixed version; on-premise HANA 2.0 reports 2.x). Reach the live dialect instance with getDialectInstance(). The edition helpers are HANA-specific (not on the shared Dialect interface), so cast to reach them: The remaining examples in this guide operate on that same dialect instance returned by getDialectInstance(). Column vs row tables HANA's signature feature is the column store. Tables are created as CREATE COLUMN TABLE by default. To create a legacy row-store table, pass the HANA-specific columnStore: false flag through TableOptions: Auto-increment columns emit GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) rather than a serial/AUTOINCREMENT keyword. System-versioned (temporal) tables Column tables can opt into HANA's system-versioning for automatic history tracking. The dialect appends the validfrom/validto period columns and a PERIOD FOR SYSTEMTIME clause: If historyTable is omitted, HANA auto-generates and manages the history table. System-versioning is column-store only — combining it with columnStore: false throws. To retrofit an existing table, use alterTableSystemVersioning(), which issues the required period-column and versioning ALTER TABLE statements in order (HANA has no single-statement form for this). Data types The dialect maps the ORM's generic type keys onto HANA-native types. HANA is a Unicode-first engine, so string types map to their N-prefixed variants: ORM type | HANA type | STRING / NVARCHAR | NVARCHAR(n) (length: 'max' → NCLOB) | CHAR | NCHAR(n) | TEXT / MEDIUMTEXT / LONGTEXT | NCLOB | SHORTTEXT | SHORTTEXT(n) | ALPHANUM | ALPHANUM(n) | INTEGER | TINYINT / SMALLINT / INTEGER / BIGINT (by length 1/2/-/8) | TINYINT / BIGINT | TINYINT / BIGINT | FLOAT | REAL | DOUBLE | DOUBLE | DECIMAL / NUMERIC / NUMBER | DECIMAL(p, s) | BOOLEAN / BIT | BOOLEAN | DATE / DATETIME / TIMESTAMP | TIMESTAMP | SECONDDATE | SECONDDATE (second-precision timestamp) | DATEONLY | DATE | TIME | TIME | BLOB | BLOB | BINARY / VARBINARY | VARBINARY(n) | JSON | NCLOB | JSONB | BLOB | UUID | NVARCHAR(36) | ENUM | NVARCHAR(255) (HANA has no native ENUM) | GEOMETRY | STGEOMETRY (optionally STGEOMETRY(srid)) | STPOINT | STPOINT (optionally STPOINT(srid)) | Any unrecognized type string is passed through verbatim, so you can supply HANA-native types directly (e.g. 'SECONDDATE', 'ALPHANUM(10)'). Pagination and LIMIT HANA uses the standard LIMIT n OFFSET m clause — unlike Oracle/Db2's OFFSET ... FETCH FIRST .... There is one quirk: HANA does not allow a bare OFFSET without a LIMIT. When only an offset is supplied, the dialect emits a sentinel maximum limit (2147483647) to satisfy the parser: Upserts The dialect prefers HANA's native UPSERT ... WITH PRIMARY KEY statement — a genuine HANA-only construct distinct from the MERGE INTO every other enterprise dialect uses. With no explicit conflict target, it matches on the table's primary key: When you name explicit conflictFields other than the primary key, the dialect falls back to a full MERGE INTO ... WHEN MATCHED / WHEN NOT MATCHED statement (sourced from the single-row DUMMY pseudo-table), since UPSERT ... WITH PRIMARY KEY can only match on the actual primary key: Sequences HANA has Oracle-style sequences accessed via NEXTVAL/CURRVAL pseudo-columns, selected from the single-row DUMMY table (HANA's equivalent of Oracle's DUAL): nextSequenceValue() is part of the shared Dialect interface. The HANA dialect additionally exposes currSequenceValue() (emitting SELECT "orderseq".CURRVAL FROM DUMMY); reach it by casting the instance, since it is a HANA-specific extra rather than a shared interface method. Transactions hdb has no built-in connection pool, so each transaction is backed by its own dedicated client connection (with autocommit disabled). This keeps a transaction's statements from interleaving with unrelated queries on the shared connection: The isolation level is applied via SET TRANSACTION ISOLATION LEVEL ... on the dedicated connection before any statements run. Identifiers and case folding This is the single biggest gotcha for developers coming from MySQL/Postgres. HANA follows the Oracle/Db2 rule: unquoted identifiers are folded to uppercase. The dialect always quotes identifiers with double quotes, so an identifier is stored and referenced exactly as written: Because of this, catalog lookups (describeTable, showTables, dropTable with ifExists, etc.) uppercase the name when querying SYS. views and scope results to the configured schema (or CURRENTSCHEMA) — without that scoping, same-named tables across schemas would collide. Quirks and limitations The dialect is honest about features HANA models differently or not at all — these methods throw a descriptive error rather than emitting invalid SQL: No materialized views. HANA has no CREATE MATERIALIZED VIEW. The nearest equivalents are Calculation Views (modeled objects, out of scope for a CRUD ORM) or a manually-refreshed snapshot table. createMaterializedView() and friends throw. No CREATE EXTENSION. Extensions are PostgreSQL-specific; the extension methods throw. Federation is Smart Data Access, not FDW. HANA's analogue of Postgres foreign data wrappers is CREATE REMOTE SOURCE / CREATE VIRTUAL TABLE. It does not map cleanly onto the generic (Postgres-shaped) FDW interface, so the FDW methods throw with a pointer to Smart Data Access. No partial (filtered) indexes. createPartialIndex() throws — HANA does not support a WHERE clause on CREATE INDEX. Full-text (CREATE FULLTEXT INDEX), spatial (CREATE SPATIAL INDEX), and expression indexes are supported. No row-level-security policies. HANA uses Structured/Analytic Privileges instead; createPolicy(), enableRLS(), etc. throw. No ALTER ... OWNER TO. Object ownership is managed via privileges/roles, so changeOwner() throws. Partitioning: range and hash partitioning are modeled by createPartitionedTable(). HANA's ROUNDROBIN partitioning is not modeled — use raw SQL via query(). Attaching/detaching a standalone table as a partition is not supported (attachPartition/detachPartition throw); use ADD PARTITION / MERGE PARTITIONS instead. Also worth noting: ALTER TABLE column DDL is parenthesized and can batch multiple columns in one clause (ADD (...), DROP (...), ALTER (...)), distinct from the single-column-at-a-time syntax of most dialects. Column renames use a dedicated RENAME COLUMN t.old TO new statement. Stored procedures use SQLScript (LANGUAGE SQLSCRIPT). Calling a procedure returns OUT/INOUT parameters plus zero or more result sets; the dialect normalizes CALL results into { rows, rowCount, outputParams, resultSets }. Recursive CTEs and window/OLAP functions are fully supported via buildRecursiveCteClause() and buildWindowFunction() (e.g. ROWNUMBER() OVER (PARTITION BY ... ORDER BY ...)). See also Source: hana Driver: hdb on npm SAP HANA SQL Reference Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL