CrateDB Dialect

CrateDB Dialect Overview CrateDB is a distributed SQL database built for search and analytics over large volumes of structured and semi-structured data. It speaks the PostgreSQL wire protocol (default port 5432) and is largely PG-compatible on the client side. Because of that wire compatibility, the ORM's CrateDBDialect extends PostgresDialect and reuses the pg driver for connectivity and most query building (SELECT / INSERT / UPDATE / DELETE). It overrides only the surface where CrateDB genuinely differs from PostgreSQL: CREATE TABLE generation (distribution/partitioning clauses + CrateDB type mapping) Type mapping (getDataTypeSql) No foreign keys No standalone CREATE INDEX (all columns are auto-indexed; fulltext indexes are declared inline at CREATE TABLE) No column/table renames An extra ON CONFLICT ... DO NOTHING insert variant The dialect reports its identity accordingly: Source: cratedb, base: postgres. Connection CrateDB is PG-wire compatible, so CrateDBDialectOptions is just the Postgres option set. The constructor forwards { port: 5432, ...config } to the Postgres base, so the default port stays 5432 even though you can override it. The Postgres base defaults host to 'localhost' and port to 5432 when omitted. Connection uses the standard pg pool (max: 20, idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000 by default, all inherited from the Postgres dialect). CrateDB-specific features CrateDB's CREATE TABLE clauses are passed via CrateDBTableOptions, which is combined with the shared TableOptions (an intersection type, since these options are not part of every dialect): Clause order After the column list, buildCreateTableSQL emits clauses in CrateDB's required order: 1. PARTITIONED BY (col, ...) 2. CLUSTERED [BY (col)] INTO n SHARDS 3. WITH ( key = value, ... ) Sharding, partitioning, and settings Generated SQL (clause order: PARTITIONED BY, then CLUSTERED, then WITH): Notes verified against the source and tests: Identifiers are double-quoted (inherited escapeId). CLUSTERED BY (col) can be omitted — clusteredInto (or shards) alone yields a bare CLUSTERED INTO n SHARDS: WITH values: string values are single-quoted (embedded quotes doubled), while numeric/boolean values are emitted bare. Keys are emitted verbatim, so a pre-quoted key such as '"refreshinterval"' is preserved as "refreshinterval". OBJECT columns OBJECT (and JSON/JSONB/HSTORE) types with declared sub-columns emit OBJECT(POLICY) AS (col type, ...); the policy defaults to DYNAMIC. With no sub-columns, a bare OBJECT is emitted. ARRAY columns CrateDB uses ARRAY(type), not Postgres's type[]: Fulltext indexes CrateDB has no standalone CREATE INDEX statement — the only user-defined indexes are fulltext indexes, and they must be declared inline at CREATE TABLE via fulltextIndexes. Each entry emits one INDEX name USING FULLTEXT (cols) [WITH (analyzer = '...')] column. emits (inside the column list): Without an analyzer: Related methods throw rather than emit invalid SQL: addIndex, createIndex, removeIndex, dropIndex — throw "no CREATE INDEX" (all columns are auto-indexed). createFulltextIndex — throws, directing you to declare it at CREATE TABLE. createSpatialIndex — throws (GEOSHAPE indexes are inline at CREATE TABLE). createTable with a generic options.indexes array logs a console.warn and ignores them (it does not emit CREATE INDEX). Renames CrateDB cannot rename columns or tables. renameColumn and renameTable both throw. addColumn is supported and emits ALTER TABLE ... ADD COLUMN ... using CrateDB's type mapping (dropping any FK reference). CRUD The standard SELECT / INSERT / UPDATE / DELETE builders are inherited from the Postgres base and produce valid CrateDB SQL. Identifiers are double-quoted and values are parameterized with $1, $2, ... placeholders. ON CONFLICT ... DO UPDATE (upsert) Inherited from the Postgres base unchanged — its output is valid CrateDB SQL: ON CONFLICT ... DO NOTHING (CrateDB override) CrateDBInsertOptions adds onConflictDoNothing. Unlike DO UPDATE, DO NOTHING may omit the conflict target. RETURNING (if requested) is appended after DO NOTHING. Foreign keys are omitted CrateDB has no foreign-key support, so both column-level references and table-level FOREIGN KEY constraints are dropped from generated DDL rather than emitted. The column itself is still created — only the FK reference is removed: The async FK helpers reject explicitly rather than emit unsupported DDL: addForeignKey — throws "CrateDB does not support foreign keys." addConstraint / createConstraint — throw for FOREIGN KEY, but delegate other constraint types (UNIQUE, CHECK, PRIMARY KEY) to the Postgres base. PRIMARY KEY (with routing-column caveat) PRIMARY KEY is preserved, both as a column modifier ("id" BIGINT PRIMARY KEY) and as a table constraint (PRIMARY KEY (fields)). Caveat: In CrateDB, a PRIMARY KEY must include every routing/clustered/partitioned column. The dialect does not validate or rewrite the PRIMARY KEY to enforce this — it is the caller's responsibility to make the primary key include the clusteredBy / partitionedBy columns. Type mapping getDataTypeSql overrides the Postgres mapping to emit CrateDB-native types. It accepts both structured DataType objects ({ key: '...' }) and cross-dialect type strings. Structured types (dt.key): ORM type (key) | CrateDB SQL | ---------------------------------------------------- | ------------------- | STRING, CHAR, TEXT, ENUM, UUID, INET, CIDR, MACADDR | TEXT | INTEGER | INTEGER | BIGINT | BIGINT | FLOAT, DOUBLE, DECIMAL | DOUBLE PRECISION | BOOLEAN | BOOLEAN | DATE, DATEONLY, TIME | TIMESTAMP | JSON, JSONB, HSTORE, OBJECT | OBJECT / OBJECT(POLICY) AS (...) | ARRAY | ARRAY(base) | GEOMETRY, GEOGRAPHY, GEOPOINT | GEOPOINT | GEOSHAPE | GEOSHAPE | VIRTUAL | '' (no column emitted) | unknown / non-object | TEXT | String inputs (cross-dialect / PG type strings) are normalized: Input string (regex) | CrateDB SQL | ------------------------------------------------------------- | ------------------ | DATETIME[(n)] | TIMESTAMP | VARCHAR, CHARACTER VARYING, CHARACTER, CHAR, STRING, NVARCHAR, NCHAR, TEXT, CLOB | TEXT | JSON / JSONB / HSTORE | OBJECT | UUID | TEXT | SERIAL / BIGSERIAL / SMALLSERIAL | BIGINT | REAL, FLOAT, NUMERIC, DECIMAL, DOUBLE [PRECISION] | DOUBLE PRECISION | TIMESTAMP, TIMESTAMPTZ, DATE, TIME [(n)] | TIMESTAMP | any other string | returned as-is | Examples verified in tests: 'VARCHAR(255)' → TEXT, 'SERIAL' → BIGINT, 'NUMERIC(10,2)' → DOUBLE PRECISION, 'TIMESTAMPTZ' → TIMESTAMP, 'JSONB' → OBJECT, 'DATETIME' → TIMESTAMP. Not-yet-verified SQL-generation verified (covered by unit tests, no database required — tests/dialects/cratedb.test.ts and tests/dialects/cratedb-crud.test.ts build SQL synchronously and never call connect()): getDataTypeSql mappings (structured and string inputs), including OBJECT(...), ARRAY(...), and geo types. buildCreateTableSQL output: clause order (PARTITIONED BY → CLUSTERED → WITH), CLUSTERED [BY] INTO n SHARDS, WITH settings formatting, fulltext INDEX, PRIMARY KEY preservation, and FK omission. INSERT / UPSERT / SELECT / UPDATE / DELETE string output, including ON CONFLICT ... DO NOTHING (with/without target, with RETURNING). The "unsupported" methods (addIndex, createIndex, createFulltextIndex, createSpatialIndex, removeIndex, dropIndex, addForeignKey, renameColumn, renameTable, and FK dispatch in addConstraint/createConstraint) throw as expected. Not verified against a live CrateDB — the following would require an actual running CrateDB server and are not exercised by the current tests: Actual connectivity via the pg driver against a CrateDB node (the dialect assumes PG-wire compatibility; no integration test connects). Runtime execution / acceptance of the generated DDL and DML by CrateDB (e.g. whether a given PRIMARY KEY satisfies CrateDB's routing-column requirement, analyzer validity, OBJECT policy semantics, refreshinterval / numberofreplicas behavior). createTable, addColumn, and other async methods that issue query(...) — only the synchronous SQL they build (or the errors they throw) is tested, not execution. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL