CockroachDB

CockroachDB speaks the Postgres wire protocol, so UQL drives it with pg and shares most of its SQL with PostgreSQL. Native arrays, JSONB, RETURNING ids and streaming work the same.

Install and connect

npm install uql-orm pg

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

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

Under Bun, bun:sql connects through its Postgres adapter and UQL still emits CockroachDB SQL.

Retry transactions

The default isolation is serializable, so contention fails transactions with 40001, which queryErrorKind reports as 'retryable'. Retry the whole callback. read committed avoids most retries, with a weaker guarantee:

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

Primary keys

A generated BIGINT identity key works, but increasing keys send every insert to one range. For a write-heavy table, use a random UUID; a UUIDv7 sorts by time and brings the hotspot back:

import { Field, f } from 'uql-orm';

@Field(f.uuid().id().onInsert(() => crypto.randomUUID())) id?: string;

What differs from PostgreSQL

Vector search is built in: no extension, and CREATE VECTOR INDEX for the index. cosine, l2 and inner work; l1 does not. Indexes support expressions and INCLUDE, but not NULLS FIRST/LAST or jsonb_path_ops. Full-text search reads the search as plain words, with no WEBSEARCH_TO_TSQUERY.

Migrations commit each schema change as it runs (autocommit_before_ddl), so a failed migration keeps the statements before the failure. With no advisory locks, a run holds its lock as a uql/lock row.