QuestDB Dialect

QuestDB Dialect Reference for the questdb SQL dialect. Source: questdb Tests: questdb, questdb-crud 1. Overview QuestDB is a high-performance, column-oriented time-series database that speaks the PostgreSQL wire protocol. Because of that wire compatibility, QuestDBDialect extends PostgresDialect and reuses the exact same pg driver: dialect.name is 'questdb'. dialect.library is 'pg' (inherited from PostgresDialect). The default SQL port is 8812 (QuestDB's pgwire port), not Postgres's 5432. Any port you pass overrides it. Identifiers are double-quoted (inherited Postgres escapeId, e.g. "trades"). Everything is inherited from Postgres except the areas where QuestDB's SQL genuinely diverges — CREATE TABLE DDL, type mapping, LIMIT pagination, INSERT/UPSERT/UPDATE/DELETE, ADD COLUMN, SYMBOL indexes, RENAME TABLE, and the (unsupported) relational constraints. The core DDL builder buildCreateTableSQL() is a pure, synchronous function that returns a SQL string, so it can be unit-tested without a live connection. createTable() simply runs it through the inherited query(). 2. Connection If you omit port, the dialect defaults to QuestDB's pgwire port 8812. 3. QuestDB-specific features All of the following are emitted by the pure builder buildCreateTableSQL(tableName, columns, options). The emitted clause order is: Designated timestamp + PARTITION BY Time-series tables elect one column as the designated timestamp and are physically partitioned by a time unit. Supply timestamp (the column name) and partitionBy in the table options. PARTITION BY is only emitted inside the timestamp branch — QuestDB only partitions tables that declare a designated timestamp. When timestamp is set but partitionBy is omitted, the unit defaults to NONE. Supported units (QuestDBPartitionUnit): NONE | YEAR | MONTH | DAY | HOUR | WEEK. SYMBOL type (CAPACITY / CACHE) SYMBOL is QuestDB's interned dictionary type for low-cardinality repeated strings (ticker symbols, device ids). A column is emitted as SYMBOL when any of these hold: symbol: true on the column definition, or the data type's key is 'SYMBOL', or the column's string type is 'SYMBOL' (case-insensitive). Two optional modifiers layer on: symbolCapacity → CAPACITY n (distinct-value hint), and symbolCache: true → CACHE (keep the dictionary in memory). Note that symbol: true overrides the declared type — the STRING above becomes SYMBOL. WAL / BYPASS WAL For partitioned tables, wal controls the write-ahead log: true → WAL, false → BYPASS WAL. When wal is omitted, no WAL clause is emitted (the server default applies). The clause is only emitted inside the timestamp branch. DEDUP UPSERT KEYS Since QuestDB has no ON CONFLICT, row de-duplication is declared on the table via DEDUP UPSERT KEYS(...) (WAL tables only). Rows sharing the given key columns are upserted; the designated timestamp must be one of the keys. Supply dedupUpsertKeys (only emitted when the array is non-empty, inside the timestamp branch). 4. Type mapping getDataTypeSql() overrides the inherited Postgres mapping to use QuestDB's type vocabulary. It handles both DataTypes. objects (via the type's key) and string shorthands (e.g. 'BIGINT', 'DATETIME'). ORM type (DataTypes. / key) | QuestDB type | --- | --- | INTEGER / INT | INT | BIGINT | LONG | FLOAT / REAL | FLOAT | DOUBLE / DECIMAL / NUMERIC / DOUBLE PRECISION | DOUBLE | BOOLEAN / BOOL | BOOLEAN | STRING / TEXT / CHAR / VARCHAR | STRING | SYMBOL | SYMBOL | DATE / DATEONLY / TIME / DATETIME / TIMESTAMP | TIMESTAMP | UUID | UUID | BLOB | BINARY | JSON / JSONB | STRING (QuestDB has no native JSON type) | anything else (object) | falls back to super.getDataTypeSql() (Postgres mapping) | Examples verified in tests: 5. Constraints / CRUD divergences No relational constraints in CREATE TABLE / ADD COLUMN QuestDB has no PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, or DEFAULT clauses. The column builder (buildQuestColumnSql) emits only "name" TYPE — every constraint field on the column definition (primaryKey, unique, references, allowNull, defaultValue, autoIncrement) is deliberately ignored. The generated SQL contains no PRIMARY KEY, UNIQUE, FOREIGN KEY, or REFERENCES. The table-level constraint methods fail loudly rather than emit invalid SQL: addForeignKey() → throws QuestDB does not support FOREIGN KEY constraints. addConstraint() / createConstraint() → throw QuestDB does not support table constraints (PRIMARY KEY / FOREIGN KEY / UNIQUE / CHECK). Use DEDUP UPSERT KEYS on CREATE TABLE for row de-duplication. INSERT — no ON CONFLICT, no RETURNING buildInsertQuery() emits a plain parameterized INSERT with $1, $2, ... placeholders. Literal values are inlined rather than parameterized. Any upsert / conflictFields / returning options are ignored. UPSERT — DEDUP-based, so a bare INSERT buildUpsertQuery() delegates to buildInsertQuery() and emits a plain INSERT (no ON CONFLICT, no DO UPDATE). Conflict resolution comes from the table's DEDUP UPSERT KEYS(...) configuration, not the statement — conflictFields and updateOnDuplicate are ignored. UPDATE — no LIMIT, no RETURNING buildUpdateQuery() supports UPDATE t SET ... WHERE ... but omits LIMIT and RETURNING even if passed. DELETE — no row-level delete; TRUNCATE only QuestDB has no row-level DELETE. buildDeleteQuery(): With { truncate: true } → returns TRUNCATE TABLE "trades". Any other call → throws: QuestDB does not support row-level DELETE. Remove data by dropping time partitions (ALTER TABLE ... DROP PARTITION ...) or truncating the table (pass { truncate: true }). To remove a time range, drop the relevant partition(s) via raw SQL (ALTER TABLE ... DROP PARTITION ...). LIMIT / pagination — no OFFSET keyword buildLimitOffset(limit, offset) uses QuestDB's LIMIT forms (there is no OFFSET keyword): limit only → LIMIT n limit + offset → LIMIT lo, hi where lo = offset and hi = offset + limit offset only → emits nothing (no upper bound to express) A SELECT with { limit: 100, offset: 200 } produces LIMIT 200, 300 (no OFFSET). ADD COLUMN — constraint-free, SYMBOL-aware buildAddColumnSQL() reuses the QuestDB column builder, so NOT NULL / DEFAULT and other constraints are dropped, and SYMBOL options are honored. Indexes — SYMBOL-column only, via ALTER TABLE QuestDB has no CREATE INDEX. Indexes exist only on SYMBOL columns, added/removed with ALTER TABLE ... ALTER COLUMN ... ADD/DROP INDEX. addIndex / createIndex iterate each field; the index name and PG-only options (unique/using/expression/where/include) are ignored. For removal, the indexName/columnName argument is treated as the column name. RENAME TABLE — dedicated statement QuestDB renames with RENAME TABLE, not ALTER TABLE ... RENAME TO. 6. Not-yet-verified SQL-generation-verified (no database required). Every builder above is pure and synchronous, and its output is asserted in the offline test suites (questdb.test.ts, questdb-crud.test.ts): buildCreateTableSQL, getDataTypeSql, buildInsertQuery, buildUpsertQuery, buildUpdateQuery, buildDeleteQuery, buildLimitOffset, buildAddColumnSQL, buildAddIndexSQL, buildDropIndexSQL, buildRenameTableSQL, and the throwing constraint methods. dialect.name === 'questdb' and dialect.library === 'pg' are also asserted. Needs a live QuestDB server (not exercised by these tests). The following are inherited from PostgresDialect or route through query(), and no test opens a QuestDB connection: Actual connectivity over pgwire (port 8812), authentication, and connect()/disconnect(). Server-side acceptance of the generated DDL/DML — whether QuestDB parses and runs each statement (e.g. that a given PARTITION BY unit, DEDUP key set, WAL/BYPASS WAL combination, or TRUNCATE/ADD INDEX/RENAME TABLE is accepted at runtime). Any inherited Postgres behavior not overridden here (transactions, SELECT result mapping beyond the LIMIT form, LISTEN/NOTIFY, schema/extension helpers) as it behaves against QuestDB specifically. The WEEK partition unit is accepted by the type but is not covered by a test. </invoke> Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL