Claude Code with CockroachDB: Distributed SQL
Why CockroachDB without CLAUDE.md ships code that works on one node and fails across regions
CockroachDB is a distributed SQL database that speaks the Postgres wire protocol while running as a horizontally scalable, multi-region, strongly consistent cluster. The pitch is that you get Postgres-shaped SQL with the survivability of a distributed system: nodes can fail, regions can go offline, and the database keeps serving with serializable isolation. The trap for anyone generating "a CockroachDB integration" is that the Postgres compatibility is good enough to fool an assistant into writing plain Postgres code, which then hits the places CockroachDB is fundamentally not Postgres: its default isolation level retries transactions, its sequential keys create hotspots, and its multi-region features need schema awareness that single-node Postgres never required.
A Claude Code session that wires up CockroachDB without project rules produces queries and migrations that run fine against a single-node test cluster, which is enough to look correct. What it gets wrong is everything that matters at scale. It writes transactions with no retry loop, so under contention they fail with a serialization error that Postgres would never have thrown and the application surfaces as a 500. It uses a SERIAL or auto-incrementing primary key, which funnels every insert to the same range and creates a write hotspot that caps throughput. It runs a migration that updates a million rows in one transaction, which CockroachDB rejects or stalls on because large transactions hold contention across the cluster. It ignores table localities, so a multi-region cluster pays cross-region latency on every read. Each of these is correct Postgres and wrong CockroachDB, and none of them shows up until the cluster has more than one node and more than one region.
This guide covers the CLAUDE.md configuration that locks Claude Code into CockroachDB patterns that scale: retry loops on every transaction that can hit contention, primary keys that distribute instead of hotspot, batched writes for large operations, region-aware table localities, and the Postgres dialect gaps spelled out so Claude stops assuming compatibility it does not have. For the single-region Postgres patterns that overlap, Claude Code with PostgreSQL covers the shared SQL foundation. For the type-safe query layer, Claude Code with Drizzle covers an ORM that works against CockroachDB's Postgres dialect.
The CockroachDB CLAUDE.md template
The CLAUDE.md at your project root is read at the start of every Claude Code session. For a CockroachDB integration it needs to declare: the connection model, the retry policy, the primary key strategy, the multi-region topology, the dialect divergences, the migration conventions, and the hard rules that block the antipatterns CockroachDB punishes hardest.
# CockroachDB rules
## Connection
- Connect via the Postgres driver (pg, pgx) with sslmode=verify-full
- Use a connection pool sized to the cluster, not per-node
- Application name set so cluster logs attribute queries
- Read GO_CRDB_DSN / DATABASE_URL from env, never hardcoded
## Transactions (MANDATORY)
- EVERY transaction that can hit contention wraps in a retry loop
- Retry on SQLSTATE 40001 (serialization_failure) with backoff
- Keep transactions SHORT, never hold one across an external call
- Use SELECT ... FOR UPDATE only when the write depends on the read
## Primary keys (MANDATORY)
- Use UUID (gen_random_uuid()) or a hash-sharded key, NEVER SERIAL
- NEVER a monotonically increasing key as the leading column
- Composite keys order columns to spread writes across ranges
## Multi-region (MANDATORY)
- Declare table LOCALITY explicitly: REGIONAL BY ROW, GLOBAL, or REGIONAL
- Reference tables that are read-everywhere use GLOBAL
- Row-homed tables carry a crdb_region column
- NEVER leave locality at the default in a multi-region cluster
## Dialect gaps (Postgres compatibility is NOT total)
- No stored procedures with side effects beyond what CRDB supports
- Sequences exist but are discouraged; prefer UUID
- Some Postgres extensions are unavailable; check before using
- Foreign key cascades behave the same but cost more across ranges
## Migrations
- Schema changes are online and async; never assume instant completion
- Large data backfills run in BATCHES, never one transaction
- Use IF NOT EXISTS / IF EXISTS for idempotent migrations
## Hard rules
- NEVER run a transaction without retry handling for 40001
- NEVER use SERIAL or an auto-increment key as the primary key
- NEVER update or delete a large table in a single transaction
- NEVER leave table locality unset on a multi-region cluster
- NEVER hold a transaction open across a network call
- NEVER assume a Postgres extension is available without checking
- ALWAYS batch backfills with a LIMIT and a loop
- ALWAYS connect with sslmode=verify-full in production
Four rules here prevent the majority of the scaling failures Claude generates without them.
The retry loop rule is the one that distinguishes CockroachDB code from Postgres code. CockroachDB runs serializable isolation by default, which means that under concurrent access a transaction can fail with a 40001 serialization error and must be retried. This is not a bug or a configuration mistake; it is how the database guarantees correctness without locking. Postgres at its default isolation almost never throws this, so Postgres-shaped code has no retry loop and surfaces the error to the user. The rule requires every transaction that can contend to wrap in a retry loop that catches 40001 and retries with backoff.
The primary key rule prevents the write hotspot that caps throughput. A SERIAL or auto-incrementing key means every new row sorts to the end of the keyspace, so every insert lands on the same range, and that range becomes a bottleneck no amount of nodes can relieve. A UUID or hash-sharded key spreads inserts across ranges so write throughput scales with the cluster. The rule bans the sequential key outright in favour of gen_random_uuid() or an explicit hash-sharded index.
The multi-region locality rule is what makes a distributed cluster actually fast. Without an explicit LOCALITY, every table defaults to a single configuration that pays cross-region latency on reads and writes that could have been local. The rule requires declaring REGIONAL BY ROW for user-homed data, GLOBAL for read-everywhere reference tables, and REGIONAL for data pinned to one region, so the topology matches the access pattern.
Connection and pool setup
Connect with a standard Postgres driver. CockroachDB speaks the wire protocol, so pg or pgx connect without a special client. The configuration that matters is TLS and pool sizing.
// src/db/pool.ts
import { Pool } from "pg";
export const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: { rejectUnauthorized: true }, // verify-full in practice
max: 20,
application_name: "api-service", // shows in cluster query logs
idleTimeoutMillis: 30_000,
});
The pool is sized for the whole application against the cluster, not per node; CockroachDB load-balances connections internally. The application_name tags every query so the cluster's statement logs attribute load to the right service, which is the first thing you reach for when diagnosing a contention spike. TLS verification is on because a distributed database is a high-value target and an unverified connection invites a man-in-the-middle.
Transactions with a retry loop
The retry loop is the single most important pattern in CockroachDB application code. Every transaction that can contend with another wraps in a function that retries on 40001.
// src/db/retry.ts
import { PoolClient } from "pg";
import { pool } from "./pool";
export async function withRetry<T>(
fn: (client: PoolClient) => Promise<T>,
maxAttempts = 5
): Promise<T> {
const client = await pool.connect();
try {
for (let attempt = 0; attempt < maxAttempts; attempt++) {
try {
await client.query("BEGIN");
const result = await fn(client);
await client.query("COMMIT");
return result;
} catch (err: unknown) {
await client.query("ROLLBACK");
const code = (err as { code?: string }).code;
if (code === "40001" && attempt < maxAttempts - 1) {
const backoff = Math.random() * 100 * 2 ** attempt;
await new Promise((r) => setTimeout(r, backoff));
continue; // retry the whole transaction
}
throw err;
}
}
throw new Error("transaction retries exhausted");
} finally {
client.release();
}
}
The function begins a transaction, runs the caller's work, and commits. On a 40001 serialization failure it rolls back, waits a jittered exponential backoff, and retries the entire transaction from the top, because a serialization failure means the transaction's reads are no longer valid and it has to redo them against the current state. After the maximum attempts it gives up with a clear error. Every contended write in the application goes through this wrapper, so a serialization failure becomes a brief retry instead of a user-facing 500.
// usage
await withRetry(async (client) => {
const { rows } = await client.query(
"SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
[accountId]
);
const next = rows[0].balance - amount;
if (next < 0) throw new InsufficientFundsError();
await client.query("UPDATE accounts SET balance = $1 WHERE id = $2", [next, accountId]);
});
The transaction reads a balance, computes the new value, and writes it. Because it goes through withRetry, two concurrent debits that would serialize-conflict are retried rather than failing, and the FOR UPDATE makes the read-then-write dependency explicit so CockroachDB orders them correctly.
Primary keys that distribute
The schema decides whether writes scale. A sequential key creates a hotspot; a UUID or hash-sharded key spreads load across the cluster.
-- Wrong: SERIAL funnels every insert to the end of the keyspace
CREATE TABLE events (
id SERIAL PRIMARY KEY,
payload JSONB
);
-- Right: UUID spreads inserts across ranges
CREATE TABLE events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
payload JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Also right: hash-sharded index when you need an ordered column
CREATE TABLE events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
created_at TIMESTAMPTZ DEFAULT now(),
INDEX (created_at) USING HASH WITH (bucket_count = 8)
);
The UUID primary key means new rows scatter across the keyspace, so inserts hit different ranges and write throughput scales with node count. When you genuinely need to index a monotonic column like created_at, the hash-sharded index splits it into buckets so the writes spread instead of all landing on the latest range. This is the schema-level decision that the CLAUDE.md rule enforces and that Claude skips by default because Postgres habits make SERIAL the reflex.
Multi-region table localities
In a multi-region cluster, table locality decides where data lives and therefore how fast each access is. The three localities map to three access patterns.
-- REGIONAL BY ROW: each row homed in the region of the user it belongs to
ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
-- GLOBAL: read-everywhere reference data, fast reads in every region
ALTER TABLE feature_flags SET LOCALITY GLOBAL;
-- REGIONAL: the whole table pinned to one region
ALTER TABLE eu_audit_log SET LOCALITY REGIONAL IN "eu-west-1";
REGIONAL BY ROW homes each row in a region based on a crdb_region column, so a user in Europe reads and writes their own row with local latency while the same table serves users in other regions from their regions. GLOBAL is for data that is read everywhere and written rarely, like feature flags, where CockroachDB optimises for fast local reads in every region at the cost of slower writes. REGIONAL IN pins an entire table to one region, which suits data with a residency requirement. Setting these correctly is the difference between a multi-region cluster that feels local everywhere and one that pays a cross-ocean round trip on every query.
Batched backfills
A migration that touches a large table cannot run as one transaction, because CockroachDB holds contention for the duration and a long transaction across a distributed cluster either stalls or aborts. Large data changes run in batches.
// src/migrations/backfill.ts
import { pool } from "../db/pool";
export async function backfillStatus() {
const BATCH = 5_000;
let updated = BATCH;
while (updated === BATCH) {
const { rowCount } = await pool.query(
`UPDATE orders SET status = 'archived'
WHERE status IS NULL
LIMIT $1`,
[BATCH]
);
updated = rowCount ?? 0;
if (updated > 0) await new Promise((r) => setTimeout(r, 50));
}
}
The backfill updates rows in chunks of five thousand and loops until a batch comes back smaller than the limit, which signals the table is done. The small pause between batches keeps the cluster from saturating on the backfill. Each batch is its own transaction, short enough to avoid the contention a million-row update would create. This is the migration pattern the CLAUDE.md rule mandates and the one Claude omits when it writes the single UPDATE statement that Postgres would have tolerated.
Permission hooks for CockroachDB operations
CockroachDB has cluster-level operations that can take nodes offline, drop databases, or change the topology. Permission hooks in .claude/settings.local.json keep the destructive ones out of an automated session.
{
"permissions": {
"allow": [
"Bash(cockroach sql --execute=SHOW*)",
"Bash(cockroach node status*)",
"Bash(cockroach sql --execute=EXPLAIN*)"
],
"deny": [
"Bash(cockroach sql --execute=DROP*)",
"Bash(cockroach sql --execute=TRUNCATE*)",
"Bash(cockroach node decommission*)",
"Bash(cockroach quit*)",
"Bash(*DROP DATABASE*)",
"Bash(*DROP TABLE*)"
]
}
}
The allow list permits the read-only inspection Claude needs to understand the cluster: SHOW statements, node status, and EXPLAIN for query plans. The deny list blocks the operations that drop data, truncate tables, decommission nodes, or shut the cluster down. Combined with the Claude Code permissions model for the full policy surface, the result is a setup where Claude can investigate a slow query or a hot range without being able to take the cluster apart.
Common Claude Code mistakes with CockroachDB
Six patterns Claude generates incorrectly without CLAUDE.md constraints.
1. Transaction with no retry loop
Claude generates: a plain BEGIN ... COMMIT with no handling for serialization failures.
Correct pattern: wrap in a retry loop that catches 40001 and retries with backoff.
2. SERIAL primary key
Claude generates: id SERIAL PRIMARY KEY, creating a write hotspot.
Correct pattern: id UUID PRIMARY KEY DEFAULT gen_random_uuid() to distribute inserts.
3. Large single-transaction backfill
Claude generates: one UPDATE over a million rows.
Correct pattern: batch with a LIMIT and loop until the batch is short.
4. Table locality left at the default
Claude generates: tables created with no LOCALITY on a multi-region cluster.
Correct pattern: explicit REGIONAL BY ROW, GLOBAL, or REGIONAL per access pattern.
5. Transaction held across a network call
Claude generates: a transaction that awaits an HTTP request mid-flight.
Correct pattern: keep transactions short, do the network call before or after, never inside.
6. Assuming a Postgres extension is available
Claude generates: code that depends on a Postgres extension CockroachDB does not ship.
Correct pattern: check the supported feature set before relying on an extension.
Add each pattern to CLAUDE.md with the corrected form. Combined with CLAUDE.md examples for the general structure, the result is CockroachDB code Claude generates correctly the first time.
Testing against contention
The tests that matter for CockroachDB prove the retry loop works under concurrent access and that the schema distributes writes. A test against a single connection with no contention proves only that the SQL parses.
import { describe, it, expect } from "vitest";
import { withRetry } from "../src/db/retry";
describe("concurrent transactions", () => {
it("both concurrent debits succeed via retry", async () => {
await seedAccount("acct-1", 100);
const debit = () =>
withRetry(async (client) => {
const { rows } = await client.query(
"SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
["acct-1"]
);
await client.query("UPDATE accounts SET balance = $1 WHERE id = $2", [
rows[0].balance - 30,
"acct-1",
]);
});
await Promise.all([debit(), debit()]);
const balance = await getBalance("acct-1");
expect(balance).toBe(40); // both applied, no lost update
});
});
The test fires two concurrent debits against the same account, which would serialize-conflict, and asserts that both apply correctly through the retry loop with no lost update. This is the property that the retry rule guarantees and that Postgres-shaped code without a retry loop would fail. For the broader testing setup, Claude Code with Vitest covers the configuration.
Building CockroachDB that scales across regions
The CockroachDB CLAUDE.md in this guide produces code where every contended transaction retries on serialization failure, primary keys distribute writes instead of hotspotting, large backfills run in batches, table localities match the access pattern, and transactions stay short and never wrap a network call. The result is a data layer that scales from a single test node to a multi-region cluster without a rewrite, because the patterns that distributed SQL demands were there from the first migration.
The principle is the same as any database with Claude Code. CockroachDB without a CLAUDE.md produces code that runs against one node and conceals the distributed-systems realities, contention retries and region topology, that only appear at scale. The CLAUDE.md is what makes the scalable patterns the default for Claude, instead of the Postgres habits that look right and fail across regions.
For the single-region Postgres patterns that overlap, Claude Code with PostgreSQL covers the shared SQL foundation. For the type-safe query layer that works against the Postgres dialect, Claude Code with Drizzle covers the ORM. For the serverless Postgres alternative when you do not need multi-region, Claude Code with Neon covers branching and autoscaling Postgres.
Get Claudify. Ship CockroachDB code that survives the contention and region topology distributed SQL demands.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify
