Getting Started with CockroachDB

PostgreSQL is a great default. It handles relational data well, has a mature ecosystem, and runs just about anywhere. But what happens when you outgrow a single server? When you need data replicated across regions, zero-downtime deployments, or horizontal scaling without manual sharding?

CockroachDB is a distributed SQL database that speaks the PostgreSQL wire protocol. Same SQL, same pg driver, same ORMs. Under the hood, it automatically replicates, shards, and distributes your data across nodes. A node goes down? Queries keep working. Need data close to users in multiple regions? Handled. For single-node dev, it's just PostgreSQL with extra guarantees.

We'll build an orders system with customers across regions in one TypeScript file. Run CockroachDB locally, or use Layerbase Cloud for a managed instance that's ready in seconds.

Contents

• Create a CockroachDB Instance
  • Local with SpinDB
  • Layerbase Cloud
• Set Up the Project
• The Orders Dataset
• Connect and Create Tables
• Insert Data
• Query with Joins and Aggregations
• CockroachDB-Specific Features
  • Data Distribution with SHOW RANGES
  • Time-Travel Queries
  • Distributed UPSERT
• The PostgreSQL Compatibility Story
• When to Reach for CockroachDB
• Wrapping Up

Create a CockroachDB Instance

Local with SpinDB

A local CockroachDB node takes about 30 seconds with SpinDB, a CLI for running databases without Docker. (What is SpinDB?)

Install SpinDB globally:

npm i -g spindb    # npm
pnpm add -g spindb # pnpm

Or run it directly without installing:

npx spindb create crdb1 -e cockroachdb --start  # npm
pnpx spindb create crdb1 -e cockroachdb --start # pnpm

If you installed globally, create and start a CockroachDB instance:

spindb create crdb1 -e cockroachdb --start

SpinDB downloads the CockroachDB binary for your platform, configures it, and starts a single-node cluster. Verify it's running:

spindb url crdb1

postgresql://root@127.0.0.1:26257/defaultdb?sslmode=disable

Leave the server running. We'll connect to it from TypeScript in the next section.

Layerbase Cloud

Don't want to install anything? Layerbase Cloud provisions a managed CockroachDB cluster you can connect to immediately. The Quick Connect panel has your PostgreSQL connection string ready.

Cloud instances use TLS, so the connection string will include sslmode=require:

const client = new pg.Client({
  connectionString:
    'postgresql://user:password@cloud.layerbase.dev:26257/defaultdb?sslmode=require',
})

Everything else in this guide works identically whether you're running locally or on Layerbase Cloud. Just swap in your connection details.

Set Up the Project

mkdir crdb-orders && cd crdb-orders
pnpm init
pnpm add pg
pnpm add -D tsx typescript @types/pg

Create a file called orders.ts. All the code in this post goes into that one file.

The Orders Dataset

A simple commerce system: customers spread across regions, each placing orders. Natural fit for CockroachDB because the data has a built-in geographic dimension. In production, you could pin data to the region where each customer lives.

const customers = [
  { id: 1, name: 'Alice Chen', email: 'alice@example.com', region: 'us-east' },
  { id: 2, name: 'Bob Martinez', email: 'bob@example.com', region: 'us-west' },
  { id: 3, name: 'Clara Schmidt', email: 'clara@example.com', region: 'eu-west' },
  { id: 4, name: 'David Park', email: 'david@example.com', region: 'us-east' },
  { id: 5, name: 'Eva Johansson', email: 'eva@example.com', region: 'eu-west' },
  { id: 6, name: 'Frank Torres', email: 'frank@example.com', region: 'us-west' },
  { id: 7, name: 'Grace Okafor', email: 'grace@example.com', region: 'eu-west' },
  { id: 8, name: 'Hiro Tanaka', email: 'hiro@example.com', region: 'us-east' },
]

const orders = [
  { id: 1, customerId: 1, product: 'Widget Pro', amount: 49.99, status: 'completed' },
  { id: 2, customerId: 1, product: 'Gadget Mini', amount: 29.99, status: 'completed' },
  { id: 3, customerId: 2, product: 'Widget Pro', amount: 49.99, status: 'shipped' },
  { id: 4, customerId: 2, product: 'Connector XL', amount: 89.99, status: 'completed' },
  { id: 5, customerId: 3, product: 'Widget Pro', amount: 49.99, status: 'pending' },
  { id: 6, customerId: 3, product: 'Gadget Mini', amount: 29.99, status: 'completed' },
  { id: 7, customerId: 3, product: 'Adapter Plus', amount: 19.99, status: 'completed' },
  { id: 8, customerId: 4, product: 'Connector XL', amount: 89.99, status: 'shipped' },
  { id: 9, customerId: 5, product: 'Widget Pro', amount: 49.99, status: 'completed' },
  { id: 10, customerId: 5, product: 'Adapter Plus', amount: 19.99, status: 'pending' },
  { id: 11, customerId: 6, product: 'Gadget Mini', amount: 29.99, status: 'completed' },
  { id: 12, customerId: 6, product: 'Widget Pro', amount: 49.99, status: 'shipped' },
  { id: 13, customerId: 7, product: 'Connector XL', amount: 89.99, status: 'completed' },
  { id: 14, customerId: 8, product: 'Widget Pro', amount: 49.99, status: 'completed' },
  { id: 15, customerId: 8, product: 'Gadget Mini', amount: 29.99, status: 'pending' },
]

Eight customers across three regions, fifteen orders with different statuses. Enough to show joins, aggregations, and CockroachDB's distribution model.

Connect and Create Tables

Since CockroachDB speaks the PostgreSQL wire protocol, we connect with the standard pg driver. Connection string from spindb url crdb1:

import pg from 'pg'

const client = new pg.Client({
  connectionString:
    'postgresql://root@127.0.0.1:26257/defaultdb?sslmode=disable',
})

await client.connect()

// Clean up from previous runs
await client.query('DROP TABLE IF EXISTS orders')
await client.query('DROP TABLE IF EXISTS customers')

await client.query(`
  CREATE TABLE customers (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    region TEXT NOT NULL
  )
`)

await client.query(`
  CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers (id),
    product TEXT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
  )
`)

console.log('Created tables: customers, orders')

Standard SQL. Nothing CockroachDB-specific yet. If you've used PostgreSQL, this looks familiar.

Insert Data

Insert the customers and orders using parameterized queries:

for (const c of customers) {
  await client.query(
    'INSERT INTO customers (id, name, email, region) VALUES ($1, $2, $3, $4)',
    [c.id, c.name, c.email, c.region],
  )
}
console.log(`Inserted ${customers.length} customers`)

for (const o of orders) {
  await client.query(
    'INSERT INTO orders (id, customer_id, product, amount, status) VALUES ($1, $2, $3, $4, $5)',
    [o.id, o.customerId, o.product, o.amount, o.status],
  )
}
console.log(`Inserted ${orders.length} orders`)

Query with Joins and Aggregations

Queries work exactly as they do in PostgreSQL:

Revenue by region:

const revenueByRegion = await client.query(`
  SELECT c.region, COUNT(o.id) AS order_count, SUM(o.amount) AS total_revenue
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  WHERE o.status = 'completed'
  GROUP BY c.region
  ORDER BY total_revenue DESC
`)

console.log('\nRevenue by region (completed orders):')
for (const row of revenueByRegion.rows) {
  console.log(`  ${row.region}: ${row.order_count} orders, $${row.total_revenue}`)
}

Revenue by region (completed orders):
  eu-west: 4 orders, $189.96
  us-east: 3 orders, $129.97
  us-west: 2 orders, $119.98

Top customers by spend:

const topCustomers = await client.query(`
  SELECT c.name, c.region, COUNT(o.id) AS orders, SUM(o.amount) AS total_spent
  FROM customers c
  JOIN orders o ON o.customer_id = c.id
  GROUP BY c.id, c.name, c.region
  ORDER BY total_spent DESC
  LIMIT 5
`)

console.log('\nTop customers:')
for (const row of topCustomers.rows) {
  console.log(`  ${row.name} (${row.region}): ${row.orders} orders, $${row.total_spent}`)
}

Top customers:
  Bob Martinez (us-west): 2 orders, $139.98
  Clara Schmidt (eu-west): 3 orders, $99.97
  Grace Okafor (eu-west): 1 orders, $89.99
  David Park (us-east): 1 orders, $89.99
  Alice Chen (us-east): 2 orders, $79.98

Standard SQL, standard results. Now for what CockroachDB adds on top.

CockroachDB-Specific Features

Here's where CockroachDB diverges from PostgreSQL. These features are built in, require no extensions, and reveal the distributed architecture underneath.

Data Distribution with SHOW RANGES

CockroachDB splits tables into ranges, which are chunks of the keyspace that get distributed across nodes. Even on a single node, you can see how the data is organized:

const ranges = await client.query('SHOW RANGES FROM TABLE orders WITH DETAILS')

console.log('\nData ranges for orders table:')
for (const row of ranges.rows) {
  console.log(`  Range: ${row.start_key || '(start)'} to ${row.end_key || '(end)'}`)
  console.log(`    Replicas: ${row.replicas}, Lease holder: ${row.lease_holder}`)
}

Data ranges for orders table:
  Range: (start) to (end)
    Replicas: 1, Lease holder: 1

On a single node, everything lives in one range on one replica. In production with multiple nodes, this table would split into ranges as it grows, each with three replicas on different nodes. If a node goes down, CockroachDB re-replicates automatically. No manual intervention.

The key insight: adding a node automatically rebalances data. No manual sharding, no downtime, no application changes. Your queries keep working.

Time-Travel Queries

CockroachDB keeps a history of every row for its garbage collection window (default: 25 hours). You can query data as it existed at any point within that window using AS OF SYSTEM TIME:

// Update an order status
await client.query(
  "UPDATE orders SET status = 'completed' WHERE id = 5",
)
console.log('\nUpdated order 5 to completed')

// Query the current state
const current = await client.query(
  'SELECT id, status FROM orders WHERE id = 5',
)
console.log(`Current status: ${current.rows[0].status}`)

// Query the state from 10 seconds ago
const past = await client.query(
  "SELECT id, status FROM orders AS OF SYSTEM TIME '-10s' WHERE id = 5",
)
console.log(`Status 10 seconds ago: ${past.rows[0].status}`)

Updated order 5 to completed
Current status: completed
Status 10 seconds ago: pending

Built into every query, zero setup. In PostgreSQL, you'd need pg_temporal, manual audit tables, or change data capture. In CockroachDB, it's one clause on any SELECT.

Useful for auditing (what did this record look like before that deploy?), debugging (what was the data when the bug got reported?), and analytics (consistent snapshots without worrying about concurrent writes).

Distributed UPSERT

CockroachDB's UPSERT and INSERT ... ON CONFLICT work like PostgreSQL's, but they're designed for distributed environments where concurrent writes to the same key can come from different nodes:

// UPSERT: insert or update in one atomic operation
await client.query(`
  UPSERT INTO customers (id, name, email, region)
  VALUES (9, 'Ines Dubois', 'ines@example.com', 'eu-west')
`)

// Run it again: same id updates instead of failing
await client.query(`
  UPSERT INTO customers (id, name, email, region)
  VALUES (9, 'Ines Dubois', 'ines@example.com', 'eu-west')
`)

// INSERT ... ON CONFLICT for fine-grained control
await client.query(`
  INSERT INTO orders (id, customer_id, product, amount, status)
  VALUES (16, 9, 'Widget Pro', 49.99, 'pending')
  ON CONFLICT (id) DO UPDATE SET status = 'pending', amount = 49.99
`)

const newCustomer = await client.query(
  'SELECT name, region FROM customers WHERE id = 9',
)
console.log(`\nUpserted customer: ${newCustomer.rows[0].name} (${newCustomer.rows[0].region})`)

Upserted customer: Ines Dubois (eu-west)

Syntax identical to PostgreSQL. The difference is under the hood: CockroachDB coordinates these across distributed nodes using serializable isolation by default. PostgreSQL defaults to read committed and requires you to opt into serializable. CockroachDB gives you the strongest guarantee from the start.

The PostgreSQL Compatibility Story

You might have noticed: we used the pg npm package throughout. No special driver. No custom syntax. That's the PostgreSQL wire protocol at work.

The compatibility goes beyond raw SQL:

• ORMs: Drizzle, Prisma, TypeORM, and Sequelize all work with CockroachDB. Point them at the connection string and they'll treat it like a PostgreSQL database.
• Migrations: Standard migration tools (Drizzle Kit, Prisma Migrate) generate valid SQL.
• Connection pooling: PgBouncer and other PostgreSQL connection poolers work as expected.
• Monitoring: PostgreSQL-compatible tools and pg_stat_statements-style introspection work.

Migration from PostgreSQL is straightforward for most apps. Write SQL as usual; CockroachDB handles distribution. The differences surface in edge cases: some extensions aren't supported, and serializable isolation can surface contention errors that PostgreSQL's read committed hides. The CockroachDB documentation has the full compatibility matrix.

When to Reach for CockroachDB

CockroachDB makes sense when you outgrow what a single PostgreSQL server provides:

• Horizontal scaling: add nodes instead of buying bigger machines
• Multi-region deployments: data pinned close to users, low-latency reads from the nearest node
• Zero-downtime operations: rolling upgrades, node failures, and schema changes without maintenance windows
• Strong consistency: serializable isolation by default, across distributed nodes
• Time-travel queries: built-in historical reads for auditing, debugging, and consistent analytics snapshots

If your app fits on a single PostgreSQL server and you don't anticipate needing multi-region or horizontal scaling, PostgreSQL is simpler and has broader extension support. CockroachDB is for when you know you'll need distributed guarantees, or when you want them from day one so you never have to migrate later.

Wrapping Up

Under 100 lines of core logic. Standard pg driver, standard SQL, queries that would work identically on PostgreSQL. Then the extras: range-based data distribution, time-travel queries, and distributed upserts with serializable isolation.

Clean up and close the connection at the end of orders.ts:

await client.end()
console.log('\nDone.')

Run the full script:

npx tsx orders.ts

The CockroachDB documentation covers advanced topics like locality-aware partitioning, change data capture, multi-region database configurations, and cluster topology patterns.

To manage your local CockroachDB instance:

spindb stop crdb1    # Stop the server
spindb start crdb1   # Start it again
spindb connect crdb1 # Open the SQL shell
spindb list          # See all your database instances

Since CockroachDB speaks the PostgreSQL wire protocol, it pairs well with the entire PG ecosystem. SpinDB manages 20+ database engines, so you can run CockroachDB for distributed data alongside local PostgreSQL for comparison testing, or add a MariaDB for a legacy service, all from the same CLI.