CockroachDB Has a Free API — Here's How to Build Distributed SQL Apps

#cockroachdb #database #sql #distributed

Why CockroachDB?

Traditional databases force you to choose: consistency OR scalability. CockroachDB gives you both — distributed SQL that survives node failures, scales horizontally, and speaks PostgreSQL.

CockroachDB Serverless free tier: 10 GiB storage, 50M Request Units/month. That's ~250M row reads or ~12M row writes.

Getting Started

Option 1: CockroachDB Serverless (Free)

Sign up at cockroachlabs.com
Create a free serverless cluster
Get your connection string

Option 2: Local (Docker)

docker run -d --name cockroach \
  -p 26257:26257 -p 8080:8080 \
  cockroachdb/cockroach start-single-node --insecure

Create Tables (Standard PostgreSQL)

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email STRING UNIQUE NOT NULL,
  name STRING NOT NULL,
  region STRING NOT NULL DEFAULT 'us-east',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  status STRING DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

Python Example

import psycopg2

conn = psycopg2.connect(
    "postgresql://user:pass@free-tier.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full"
)
cur = conn.cursor()

# Insert users
cur.execute("""
    INSERT INTO users (email, name, region) VALUES
    ('alice@example.com', 'Alice', 'us-east'),
    ('bob@example.com', 'Bob', 'eu-west'),
    ('charlie@example.com', 'Charlie', 'ap-south')
    ON CONFLICT (email) DO NOTHING
    RETURNING id, name;
""")

for row in cur.fetchall():
    print(f"Created user: {row[1]} ({row[0]})")

conn.commit()

# Distributed query — works across regions
cur.execute("""
    SELECT u.name, u.region, COUNT(o.id) as order_count, COALESCE(SUM(o.total), 0) as total_spent
    FROM users u LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.name, u.region
    ORDER BY total_spent DESC;
""")

for row in cur.fetchall():
    print(f"{row[0]} ({row[1]}): {row[2]} orders, ${row[3]} spent")

Node.js Example

const { Pool } = require("pg");

const pool = new Pool({
  connectionString: "postgresql://user:pass@free-tier.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full"
});

// Transaction with automatic retry (CockroachDB best practice)
async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amount, fromId]);
    await client.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amount, toId]);
    await client.query("COMMIT");
    console.log(`Transferred $${amount}`);
  } catch (e) {
    await client.query("ROLLBACK");
    if (e.code === "40001") {
      // Serialization error — retry
      return transferFunds(fromId, toId, amount);
    }
    throw e;
  } finally {
    client.release();
  }
}

Multi-Region (Built-In)

-- Geo-partition data by region
ALTER TABLE users SET LOCALITY REGIONAL BY ROW AS region;

-- Now reads in us-east are fast for us-east users
-- And eu-west reads are fast for eu-west users
-- All without application-level sharding!

CockroachDB vs Alternatives

Feature	CockroachDB	PostgreSQL	PlanetScale
Distributed	Yes	No	Yes
PostgreSQL compatible	Yes	Native	No (MySQL)
Multi-region	Built-in	Manual	Yes
Free tier	10 GiB	N/A	5 GiB
Survive node failure	Yes	No	Yes

Need to populate your database with web data? I build production-ready scrapers that output clean, structured data ready for import. Check out my Apify actors or email spinov001@gmail.com for custom data pipelines.

Building something distributed? Share your use case below!

DEV Community