api-database-cockroachdb

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

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(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);
  });

  return pool;
}

export { createPool };

Why good: Standard pg Pool works unmodified, named constants, error handler prevents process crash, CockroachDB default port is 26257 (not 5432)

// Bad Example - SERIAL primary key
await pool.query(`
  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
  )
`);
// SERIAL creates sequential IDs via unique_rowid()
// which causes write hotspots on a single range

Why bad: Sequential IDs from SERIAL/unique_rowid() cluster writes on one range, creating a hotspot that defeats CockroachDB's distributed architecture

Pattern 2: Transaction Retry Logic (MANDATORY)

CockroachDB's serializable isolation means transactions can fail with SQLSTATE 40001 under normal operation. You MUST catch this and retry. See [examples/core.md](examples/core.md) for the full retry helper.

// Good Example - Transaction with retry logic
const CRDB_SERIALIZATION_FAILURE = "40001";
const MAX_RETRIES = 5;
const BASE_DELAY_MS = 50;

async function withRetry<T>(
  pool: pg.Pool,
  operation: (client: pg.PoolClient) => Promise<T>,
): Promise<T> {
  for (let attempt = 0; attempt <= MAX_RETRIES; attempt++) {
    const client = await pool.connect();
    try {
      await client.query("BEGIN");
      const result = await operation(client);
      await client.query("COMMIT");
      return result;
    } catch (err) {
      await client.query("ROLLBACK");
      if (isCrdbRetryError(err) && attempt < MAX_RETRIES) {
        const delay =
          BASE_DELAY_MS * Math.pow(2, attempt) + Math.random() * BASE_DELAY_MS;
        await new Promise((resolve) => setTimeout(resolve, delay));
        continue;
      }
      throw err;
    } finally {
      client.release();
    }
  }
  throw new Error("Retry loop exited unexpectedly");
}

Why good: Catches 40001 errors specifically, exponential backoff with jitter prevents thundering herd, fresh client per attempt, bounded retries, releases client in finally

// Bad Exampl