CockroachDB

CockroachDB speaks the Postgres wire protocol, so UQL drives it with pg and shares the AST, quoting, JSONB, full-text and upsert logic with PostgreSQL. It is a separate dialect only because of the differences listed below.

npm install uql-orm pg

import { CrdbQuerierPool } from 'uql-orm/cockroachdb';

export const pool = new CrdbQuerierPool({ connectionString: process.env.DATABASE_URL, max: 10 });

Transactions retry

The default isolation is serializable, and contention surfaces as a retryable 40001. Retry the whole transaction callback so the new attempt starts from a fresh snapshot:

const isRetryable = (err: unknown) =>
  typeof err === 'object' && err !== null && 'code' in err && err.code === '40001';

read committed is available on modern clusters and cuts most retries at the cost of the weaker guarantee:

await pool.transaction(run, { isolationLevel: 'read committed' });

Primary keys

UQL’s generated surrogate key is BIGINT GENERATED BY DEFAULT AS IDENTITY. That works, but a monotonically increasing key concentrates every insert on one range. For a table under insert load, use a random UUID so writes spread:

import { Id } from 'uql-orm';

@Id({ type: 'uuid', onInsert: () => crypto.randomUUID() }) id?: string;

Random, not time-ordered: a UUIDv7 sorts by time and rebuilds the hotspot it was meant to avoid.

Differences from Postgres

• created on upserts is always undefined: Postgres derives it from xmax, and CockroachDB’s transaction model has no equivalent system column, by design.
• Vector search is native, so no CREATE EXTENSION vector, and indexes use CREATE VECTOR INDEX with no access-method keyword. Three of pgvector’s four metrics work (cosine, l2, inner); l1 is unimplemented server-side.
• Index features are narrower: expression and INCLUDE indexes work, NULLS FIRST/LAST and jsonb_path_ops do not.

Everything else, native arrays, JSONB, RETURNING ids, streaming, full-text and migrations, behaves as on Postgres. Under Bun, bun:sql reaches CockroachDB through its Postgres adapter while UQL keeps emitting CockroachDB SQL.