CockroachDB Dialect

CockroachDB Dialect CockroachDB is a distributed, horizontally-scalable SQL database that speaks the PostgreSQL wire protocol. The prorm CockroachDB dialect reuses the pg driver (the same driver as the Postgres dialect) and mirrors most of its query-building behavior, while adding CockroachDB-specific features: native UPSERT, serialization-failure retry handling, multi-region localities, and zone configuration. Source: cockroachdb Driver library: pg Default port: 26257 Overview Because CockroachDB is largely SQL-compatible with PostgreSQL, this dialect inherits the Postgres dialect's approach almost verbatim. The notable differences are: Default port is 26257 (not Postgres's 5432). Schema introspection (showTables, describeTable, showIndexes, showConstraints) prefers CockroachDB's SHOW ... statements instead of querying informationschema / pgcatalog directly. CREATE INDEX ... CONCURRENTLY is not emitted — index creation is online/non-blocking by default in CockroachDB, so a requested concurrently flag is ignored with a warning. Native UPSERT INTO shorthand is available in addition to Postgres-style INSERT ... ON CONFLICT ... DO UPDATE. isRetryableError() and runTransaction() implement CockroachDB's client-side transaction-retry contract for serialization failures (SQLSTATE 40001). Multi-region helpers (setPrimaryRegion, addRegion, setTableLocality, setSurvivalGoal) and zone configuration (configureZone) are provided. Honest limitations Interleaved tables (CockroachDB's deprecated INTERLEAVE IN PARENT syntax) are intentionally not implemented. rowFormat, engine, and charset table options are ignored with a warning — they have no CockroachDB equivalent. The concurrently index option is ignored with a warning (index creation is already online). CockroachDB runs under SERIALIZABLE isolation by default; other isolation levels passed to transactions may be silently upgraded by the server. Connection Create a Prorm instance with dialect: 'cockroachdb'. Connection options flow through to the pg pool. port defaults to 26257 when omitted, so most deployments only need host, database, and credentials. TLS / CockroachDB Cloud Managed CockroachDB (and any cluster running in secure mode) requires TLS. Pass an ssl object through — it is forwarded directly to the pg pool: Supported connection options include host, port, database, username, password, ssl, max (pool size), idleTimeoutMillis, connectionTimeoutMillis, statementTimeout, and queryTimeout. PostgreSQL compatibility Because CockroachDB speaks the pg-wire protocol, the dialect behaves like the Postgres dialect for the vast majority of operations: Identifiers are quoted with double quotes ("users"). Parameterized queries use $1, $2, ... placeholders. RETURNING is supported on inserts, upserts, updates, and deletes. SERIAL / BIGSERIAL auto-increment columns work as a Postgres-compat shorthand (CockroachDB maps these to an implicit sequence internally; its own default unique-ID generation otherwise uses uniquerowid()). Schema introspection Introspection prefers CockroachDB's native SHOW statements: Native UPSERT By default, upserts emit Postgres-style INSERT ... ON CONFLICT (...) DO UPDATE for consistency with the Postgres dialect. CockroachDB also offers a native UPSERT INTO shorthand that replaces the entire row matching the primary key — no conflict target required. Opt into it with nativeUpsert: true. The same nativeUpsert flag is accepted by buildInsertQuery when upsert is also set: Note: Native UPSERT replaces the row matching the table's primary key wholesale and does not accept a conflict target, so conflictFields and updateOnDuplicate are ignored when nativeUpsert is set. Use the default ON CONFLICT form when you need to match on a non-primary-key unique column or update only a subset of columns. Serialization retries CockroachDB runs transactions under SERIALIZABLE isolation. When two concurrent transactions conflict, one aborts with SQLSTATE 40001 (serializationfailure, surfaced to clients as a "restart transaction" error). This is a routine, expected occurrence, and CockroachDB's client contract is to retry the whole transaction from the beginning. isRetryableError() Detects whether an error is a serialization failure that should be retried. It checks the pg error's .code, walks nested .cause chains, and finally falls back to matching the message text (40001, serializationfailure, restart transaction) for call paths that lose the structured code. runTransaction() — automatic retry loop runTransaction() implements CockroachDB's client-side retry contract using the SAVEPOINT cockroachrestart pattern: a savepoint is created right after BEGIN, and on a retryable error the transaction rolls back to that savepoint (rather than aborting outright) so the callback is re-run. It retries up to maxRetries times (default 5) before rethrowing. Because the callback may run more than once, it should be idempotent and avoid externally-visible side effects other than through the database. Serialization retries are also folded into the default query-retry match lists, so single-statement query() calls that opt into retries ({ retry: { max } }) cover 40001 too — no bespoke configuration needed. Data types CockroachDB uses the Postgres type system. getDataTypeSql() maps the ORM's DataTypes to CockroachDB SQL: DataType | CockroachDB SQL | ----------------------- | --------------------------- | STRING(n) | VARCHAR(n) (default 255) | CHAR(n) | CHAR(n) | TEXT | TEXT | INTEGER | INTEGER (SERIAL if auto-increment) | BIGINT | BIGINT (BIGSERIAL if auto-increment) | FLOAT | REAL | DOUBLE | DOUBLE PRECISION | DECIMAL(p, s) | DECIMAL(p, s) | BOOLEAN | BOOLEAN | DATE | TIMESTAMP (TIMESTAMP(p) with precision) | DATEONLY | DATE | TIME | TIME (TIME(p) with precision) | BLOB | BYTEA | JSON | JSON | JSONB | JSONB | UUID | UUID | INET / CIDR | INET / CIDR | ARRAY(T) | T[] | GEOMETRY / GEOGRAPHY| spatial types (with optional SRID) | Array values are escaped using CockroachDB's ARRAY[...] literal syntax so they are usable directly in array-typed columns. CockroachDB-specific features Beyond the common dialect surface, the CockroachDB dialect exposes distributed features. These are grounded in real methods on the dialect instance. Multi-region localities Zone configuration Hash-sharded indexes and column families Hash-sharded index options (bucketCount / shard) and column families (storage-layout hints grouping hot/cold columns) can be passed alongside the common index and table options via CockroachDB's extended option types. See CockroachHashShardedIndexOptions and CockroachTableOptions in the source. See also CockroachDB UPSERT Transaction retry error reference Table localities Configure replication zones Related reading Running cockroachdb in Docker — get one going locally All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL