/api-database-cockroachdb

name: api-database-cockroachdb
description: CockroachDB distributed SQL -- transaction retries, multi-region, online schema changes, follower reads, PostgreSQL compatibility gaps

CockroachDB Patterns

> **Quick Guide:** CockroachDB connects via the standard `pg` driver (PostgreSQL wire protocol). The single most important difference from PostgreSQL: **transaction retries are mandatory**. CockroachDB's serializable isolation means any transaction can fail with SQLSTATE `40001` -- your application MUST catch this and retry the entire transaction. Use `UUID` with `gen_random_uuid()` for primary keys (never `SERIAL` -- sequential IDs cause distributed hotspots). DDL runs as online schema changes in background jobs and **cannot be inside explicit transactions**. Use `AS OF SYSTEM TIME` for follower reads to reduce latency in multi-region deployments.

---

<critical_requirements>

CRITICAL: Before Using This Skill

**(You MUST implement transaction retry logic for SQLSTATE `40001` errors -- CockroachDB WILL return serialization errors under normal operation, unlike PostgreSQL where they are rare)**

**(You MUST use `UUID` with `gen_random_uuid()` for primary keys -- NEVER use `SERIAL` or sequential IDs, which cause distributed write hotspots)**

**(You MUST NOT put DDL statements inside explicit transactions -- most DDL runs as background jobs and can fail at COMMIT time with a partially applied state. `CREATE TABLE`/`CREATE INDEX` are exceptions but the safest practice is always: one DDL statement per implicit transaction)**

**(You MUST use `Pool` from `pg` for all database access -- same as PostgreSQL, but be aware that each node in the cluster is a valid connection target)**

</critical_requirements>

---

Examples

• [Core Patterns](examples/core.md) -- Pool setup, parameterized queries, transaction retry logic, error handling
• [Multi-Region & Performance](examples/multi-region.md) -- Locality, survival goals, follower reads, AS OF SYSTEM TIME
• [Schema & Operations](examples/schema-ops.md) -- Online schema changes, IMPORT INTO, CHANGEFEED, cockroach CLI

**Additional resources:**

• [reference.md](reference.md) -- PostgreSQL compatibility gaps, error codes, type differences, production checklist

---

**Auto-detection:** CockroachDB, cockroachdb, cockroach, CRDB, crdb, cockroach_restart, SAVEPOINT cockroach_restart, 40001, serialization_failure, retry transaction, restart transaction, gen_random_uuid, unique_rowid, AS OF SYSTEM TIME, follower_read_timestamp, CHANGEFEED, CREATE CHANGEFEED, IMPORT INTO, cockroach sql, cockroach start, multi-region, survival goal, zone survival, region survival, locality, REGIONAL BY ROW

**When to use:**

• Direct SQL queries against CockroachDB via the `pg` driver
• Distributed transactions requiring serializable isolation
• Multi-region database deployments with locality-aware reads/writes
• Applications migrating from PostgreSQL to CockroachDB
• Change data capture with CHANGEFEED
• Bulk data loading with IMPORT INTO

**Key patterns covered:**

• Transaction retry logic (SQLSTATE 40001 handling with exponential backoff)
• UUID primary keys with gen_random_uuid() (hotspot avoidance)
• AS OF SYSTEM TIME for follower reads and historical queries
• Multi-region configuration (locality, survival goals, regional tables)
• Online schema changes (DDL behavior differences from PostgreSQL)
• PostgreSQL compatibility gaps (what does NOT work)

**When NOT to use:**

• You need an ORM or query builder -- use your ORM/query builder skill instead
• You are targeting standard PostgreSQL without CockroachDB -- use the PostgreSQL skill
• You need features CockroachDB lacks (advisory locks, full stored procedure support, CREATE DOMAIN)

---

<philosophy>

Philosophy

CockroachDB is a **distributed SQL database** that uses the PostgreSQL wire protocol. The core principle: **write PostgreSQL-compatible SQL, but design for distribution.**

**Core principles:**

1. **Retry everything** -- Serializable isolation means any transaction can be aborted by CockroachDB to resolve conflicts. Your code MUST handle SQLSTATE `40001` and retry the full transaction. This is not an edge case -- it happens under normal load. 2. **Distribute evenly** -- Sequential primary keys (`SERIAL`, auto-increment) create write hotspots because CockroachDB sorts data by primary key across ranges. Use `UUID` with `gen_random_uuid()` to scatter writes across the cluster. 3. **DDL is async** -- Schema changes run as background jobs. They cannot be wrapped in explicit transactions. Plan migrations accordingly -- one DDL statement at a time in production. 4. **Read from followers** -- Use `AS OF SYSTEM TIME` to read slightly stale data from the nearest replica instead of always hitting the leaseholder. This is the single biggest latency optimization in multi-region deployments. 5. **PostgreSQL, mostly** -- CockroachDB supports most PostgreSQL syntax and the `pg` driver works directly. But certain features are missing or behave differently. Know the gaps before you hit them in production.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Connection Pool Setup

CockroachDB uses the standard `pg` driver. Pool setup is nearly identical to PostgreSQL, but the connection string points to a CockroachDB node (or load balancer). See [examples/core.md](examples/core.md) for full configuration.

// Good Example - CockroachDB pool with error handling
import pg from "pg";

const POOL_MAX_CLIENTS = 20;
const IDLE_TIMEOUT_MS = 30_000;
const CONNECTION_TIMEOUT_MS = 5_000;

function createPool(): pg.Pool {
  const pool = new pg.Pool({
    connectionString: process.env.DATABASE_URL,
    // Example: postgresql://user:pass@crdb-lb:26257/mydb?sslmode=verify-full
    max: POOL_MAX_CLIENTS,
    idleTimeoutMillis: IDLE_TIMEOUT_MS,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MS,
  });

  pool.on("error", (err) => {
    console.error("Unexpected idle client error:", err.message)