CockroachDB is a distributed SQL database with PostgreSQL wire compatibility — DATABASE_URL=postgresql://user:password@host:26257/mydb?sslmode=verify-full connects via the standard Postgres protocol. Prisma: provider = "cockroachdb" in schema.prisma — all standard Prisma models work. Node.js: new Pool({ connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: true } }) using pg. Multi-region: ALTER TABLE orders SET LOCALITY REGIONAL BY ROW partitions rows by the crdb_region column — queries from us-east-1 read from the nearest replica automatically. ALTER TABLE config SET LOCALITY GLOBAL replicates a table to every region with serializable reads. ALTER TABLE sessions SET LOCALITY REGIONAL BY TABLE IN PRIMARY REGION pins the whole table to one region. UPSERT INTO users (id, email) VALUES ($1, $2) handles insert-or-update atomically. INSERT INTO items VALUES (...) ON CONFLICT (id) DO UPDATE SET ... is the standard SQL equivalent. Changefeeds: CREATE CHANGEFEED FOR TABLE orders INTO 'kafka://...' WITH updated,resolved streams CDC events. Serverless: CockroachDB Serverless auto-scales to zero with per-RU billing. SHOW JOBS monitors schema change jobs (DDL is async in CRDB). Claude Code generates CockroachDB Prisma schemas, multi-region configurations, and Node.js connection pools.
CLAUDE.md for CockroachDB
## CockroachDB Stack
- Prisma provider: cockroachdb (use instead of postgresql — required for CRDB-specific types)
- Connection: DATABASE_URL=postgresql://user:pass@HOST:26257/DBNAME?sslmode=verify-full
- SSL: always needed for Serverless — ssl: { rejectUnauthorized: true } in pg Pool
- UPSERT: supported natively — UPSERT INTO table (id, col) VALUES ($1, $2)
- Serial/sequences: use gen_random_uuid() for UUIDs or @default(sequence()) for int IDs
- Prisma IDs: @default(cuid()) or @default(uuid()) — avoid autoincrement() (prefer UUID for distribution)
- DDL async: schema changes run as jobs — SHOW JOBS to check progress
- Multi-region: requires Dedicated/Serverless multi-region planPrisma Schema for CockroachDB
// prisma/schema.prisma — CockroachDB Prisma configuration
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "cockroachdb"
url = env("DATABASE_URL")
}
model User {
id String @id @default(cuid())
email String @unique
name String?
plan String @default("free")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
orders Order[]
}
model Order {
id String @id @default(cuid())
userId String
user User @relation(fields: [userId], references: [id])
status OrderStatus @default(PENDING)
amount Decimal @db.Decimal(10, 2)
currency String @default("USD")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@index([userId])
@@index([status])
}
enum OrderStatus {
PENDING
PROCESSING
COMPLETED
CANCELLED
}
model Product {
id String @id @default(cuid())
sku String @unique
name String
description String?
price Decimal @db.Decimal(10, 2)
stock Int @default(0)
createdAt DateTime @default(now())
@@index([sku])
}Database Client
// lib/db/cockroach.ts — CockroachDB with pg and Prisma
import { Pool, type PoolConfig } from "pg"
import { PrismaClient } from "@prisma/client"
// Raw pg Pool for transactions or bulk operations
const poolConfig: PoolConfig = {
connectionString: process.env.DATABASE_URL!,
ssl: { rejectUnauthorized: true },
max: 10,
idleTimeoutMillis: 30_000,
connectionTimeoutMillis: 5_000,
}
export const pool = new Pool(poolConfig)
// Prisma client (singleton)
const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient }
export const db =
globalForPrisma.prisma ??
new PrismaClient({
log: process.env.NODE_ENV === "development" ? ["query"] : ["error"],
})
if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = db
// ── CRDB-specific helpers ──────────────────────────────────────────────────
/** UPSERT — faster than INSERT + ON CONFLICT for known-conflict scenarios */
export async function upsertUser(
id: string,
email: string,
name: string,
): Promise<void> {
await pool.query(
`UPSERT INTO "User" (id, email, name, "createdAt", "updatedAt")
VALUES ($1, $2, $3, now(), now())`,
[id, email, name],
)
}
/** Bulk upsert with COPY-like INSERT */
export async function bulkUpsertProducts(
products: Array<{ id: string; sku: string; name: string; price: number }>,
): Promise<void> {
if (products.length === 0) return
const values = products.flatMap((p) => [p.id, p.sku, p.name, p.price])
const placeholders = products
.map((_, i) => `($${i * 4 + 1}, $${i * 4 + 2}, $${i * 4 + 3}, $${i * 4 + 4}, now())`)
.join(", ")
await pool.query(
`INSERT INTO "Product" (id, sku, name, price, "createdAt")
VALUES ${placeholders}
ON CONFLICT (sku) DO UPDATE SET
name = EXCLUDED.name,
price = EXCLUDED.price`,
values,
)
}
/** Transaction with retry logic (CRDB recommends retrying serialization errors) */
export async function withRetry<T>(
fn: () => Promise<T>,
maxRetries = 3,
): Promise<T> {
for (let attempt = 0; attempt <= maxRetries; attempt++) {
try {
return await fn()
} catch (err: any) {
// CRDB serialization error: code 40001
if (err?.code === "40001" && attempt < maxRetries) {
await new Promise((r) => setTimeout(r, 2 ** attempt * 50))
continue
}
throw err
}
}
throw new Error("Max retries exceeded")
}
export async function trasnsact<T>(
fn: (client: import("pg").PoolClient) => Promise<T>,
): Promise<T> {
const client = await pool.connect()
try {
await client.query("BEGIN")
const result = await fn(client)
await client.query("COMMIT")
return result
} catch (err) {
await client.query("ROLLBACK")
throw err
} finally {
client.release()
}
}Multi-Region Setup SQL
-- Multi-region CockroachDB setup (Dedicated / Multi-Region Serverless)
-- 1. Add regions to the database
ALTER DATABASE mydb PRIMARY REGION "us-east1";
ALTER DATABASE mydb ADD REGION "eu-west1";
ALTER DATABASE mydb ADD REGION "ap-southeast1";
-- 2. Pin global reference data to all regions (low-latency reads everywhere)
ALTER TABLE "Product" SET LOCALITY GLOBAL;
-- 3. Partition user-rooted data by region (rows live near their users)
ALTER TABLE "User" SET LOCALITY REGIONAL BY ROW;
ALTER TABLE "Order" SET LOCALITY REGIONAL BY ROW;
-- The crdb_region column is added automatically
-- INSERT INTO "User" (id, email, crdb_region) VALUES (..., 'us-east1')Next.js API Route
// app/api/orders/route.ts — create order with CockroachDB transaction
import { NextResponse } from "next/server"
import { z } from "zod"
import { db, withRetry } from "@/lib/db/cockroach"
import { auth } from "@/lib/auth"
const OrderSchema = z.object({
items: z.array(z.object({ productId: z.string(), quantity: z.number().int().positive() })),
})
export async function POST(req: Request) {
const session = await auth()
if (!session) return NextResponse.json({ error: "Unauthorized" }, { status: 401 })
const { items } = OrderSchema.parse(await req.json())
const order = await withRetry(async () => {
return db.$transaction(async (tx) => {
// Calculate total
const products = await tx.product.findMany({
where: { id: { in: items.map((i) => i.productId) } },
select: { id: true, price: true, stock: true },
})
const total = items.reduce((sum, item) => {
const product = products.find((p) => p.id === item.productId)
if (!product) throw new Error(`Product ${item.productId} not found`)
if (product.stock < item.quantity) throw new Error(`Insufficient stock for ${item.productId}`)
return sum + Number(product.price) * item.quantity
}, 0)
// Create order
const newOrder = await tx.order.create({
data: { userId: session.user.id, amount: total, status: "PENDING" },
})
return newOrder
})
})
return NextResponse.json(order, { status: 201 })
}For the Neon alternative when needing a PostgreSQL-compatible serverless database that scales to zero, has branching for preview environments, and connect via the standard pg or Prisma PostgreSQL provider without any CRDB-specific schema changes — Neon is a serverless PostgreSQL-as-a-service while CockroachDB is the better choice for truly distributed multi-region workloads that need geographic data partitioning, see the Neon guide. For the PlanetScale alternative when needing a serverless MySQL-compatible database with schema branching, deploy requests as a database migration workflow, and Vitess-based horizontal sharding — PlanetScale uses MySQL wire protocol while CockroachDB uses PostgreSQL wire protocol with stronger ACID guarantees and native multi-region support, see the PlanetScale guide. The Claude Skills 360 bundle includes CockroachDB skill sets covering Prisma setup, UPSERT patterns, and multi-region configuration. Start with the free tier to try distributed SQL generation.