# CockroachDB -- Core Pattern Examples

> Pool setup, transaction retry logic, UUID primary keys, parameterized queries, and error handling. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [multi-region.md](multi-region.md) -- Locality, survival goals, follower reads, AS OF SYSTEM TIME
- [schema-ops.md](schema-ops.md) -- Online schema changes, IMPORT INTO, CHANGEFEED

---

## Pool Setup

CockroachDB uses the standard `pg` driver. Configuration is identical to PostgreSQL except for the default port (26257) and SSL requirements.

```typescript
import pg from "pg";

const POOL_MAX_CLIENTS = 20;
const IDLE_TIMEOUT_MS = 30_000;
const CONNECTION_TIMEOUT_MS = 5_000;
const MAX_LIFETIME_SECONDS = 1_800;

function createPool(): pg.Pool {
  const connectionString = process.env.DATABASE_URL;
  if (!connectionString) {
    throw new Error("DATABASE_URL environment variable is required");
  }

  const pool = new pg.Pool({
    connectionString,
    // Example: postgresql://user:pass@crdb-lb:26257/mydb?sslmode=verify-full
    max: POOL_MAX_CLIENTS,
    idleTimeoutMillis: IDLE_TIMEOUT_MS,
    connectionTimeoutMillis: CONNECTION_TIMEOUT_MS,
    maxLifetimeSeconds: MAX_LIFETIME_SECONDS,
  });

  // REQUIRED: idle client errors crash the process if unhandled
  pool.on("error", (err) => {
    console.error("Unexpected idle client error:", err.message);
  });

  return pool;
}

export { createPool };
```

**Why good:** Standard pg Pool, environment variable validation, named constants, error handler prevents process crash, `connectionTimeoutMillis` prevents infinite waits on pool exhaustion

```typescript
// Bad Example - Wrong port, no SSL
import pg from "pg";

const pool = new pg.Pool({
  host: "crdb-node-1",
  port: 5432, // WRONG -- CockroachDB default is 26257
  database: "mydb",
  user: "root",
  // No SSL -- CockroachDB Cloud requires it, self-hosted strongly recommends it
});
// No pool.on("error") handler
```

**Why bad:** Wrong port (5432 is PostgreSQL default, CockroachDB is 26257), no SSL for a distributed database, hardcoded credentials, no error handler

---

## Transaction Retry Helper (MANDATORY)

This is the most important pattern for CockroachDB. Any transaction can fail with SQLSTATE `40001` due to serialization conflicts. Your application MUST retry.

```typescript
import type pg from "pg";

const CRDB_SERIALIZATION_FAILURE = "40001";
const CRDB_STATEMENT_COMPLETION_UNKNOWN = "40003";
const MAX_RETRIES = 5;
const BASE_DELAY_MS = 50;

const RETRYABLE_CODES = new Set([
  CRDB_SERIALIZATION_FAILURE,
  CRDB_STATEMENT_COMPLETION_UNKNOWN,
]);

interface PgError extends Error {
  code: string;
  constraint?: string;
  detail?: string;
}

function isPgError(err: unknown): err is PgError {
  return err instanceof Error && "code" in err;
}

function isCrdbRetryError(err: unknown): boolean {
  if (!isPgError(err)) return false;
  if (RETRYABLE_CODES.has(err.code)) return true;
  return err.message.startsWith("restart transaction");
}

async function withCrdbRetry<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) {
        // Exponential backoff with jitter
        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("CockroachDB retry loop exited unexpectedly");
}

export {
  withCrdbRetry,
  isCrdbRetryError,
  isPgError,
  CRDB_SERIALIZATION_FAILURE,
  CRDB_STATEMENT_COMPLETION_UNKNOWN,
};
```

**Why good:** Handles both 40001 and 40003, checks message prefix for CockroachDB-specific retry signals, exponential backoff with jitter, fresh client per attempt (avoids tainted connection state), bounded retries

**Gotcha:** Errors can occur on `COMMIT`, not just on individual statements. The retry loop above correctly catches errors from any point in the transaction including the COMMIT.

**Gotcha:** The operation callback must NOT have side effects outside the database (e.g., sending emails, making HTTP calls). If the transaction retries, the callback runs again. Keep side effects AFTER the `withCrdbRetry` call returns.

---

## Using the Retry Helper

```typescript
import type pg from "pg";

interface TransferResult {
  fromBalance: string; // numeric returns as string
  toBalance: string;
}

async function transferFunds(
  pool: pg.Pool,
  fromAccountId: string, // UUID
  toAccountId: string,
  amount: string, // numeric as string for precision
): Promise<TransferResult> {
  return withCrdbRetry(pool, async (client) => {
    // Lock rows in consistent order to reduce contention
    const { rows } = await client.query<{ id: string; balance: string }>(
      `SELECT id, balance FROM accounts
       WHERE id = ANY($1)
       ORDER BY id FOR UPDATE`,
      [[fromAccountId, toAccountId]],
    );

    const fromAccount = rows.find((r) => r.id === fromAccountId);
    const toAccount = rows.find((r) => r.id === toAccountId);

    if (!fromAccount || !toAccount) {
      throw new Error("Account not found");
    }

    if (parseFloat(fromAccount.balance) < parseFloat(amount)) {
      throw new Error("Insufficient balance");
    }

    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
      [amount, fromAccountId],
    );
    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
      [amount, toAccountId],
    );

    return {
      fromBalance: (
        parseFloat(fromAccount.balance) - parseFloat(amount)
      ).toString(),
      toBalance: (
        parseFloat(toAccount.balance) + parseFloat(amount)
      ).toString(),
    };
  });
}

export { transferFunds };
```

**Why good:** Wrapped in `withCrdbRetry` for automatic 40001 handling, `FOR UPDATE` with `ORDER BY id` reduces contention and prevents deadlocks, UUID primary keys, numeric handled as strings

---

## Advanced Retry: SAVEPOINT cockroach_restart

CockroachDB supports a special savepoint name `cockroach_restart` that enables an advanced retry protocol. This is useful when you want CockroachDB to automatically handle retries for transactions within the server's internal buffer size.

```typescript
import type pg from "pg";

const MAX_RETRIES = 5;
const BASE_DELAY_MS = 50;

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

export { withSavepointRetry };
```

**Why good:** Uses CockroachDB's native retry protocol, single client across retries (avoids pool checkout overhead), `ROLLBACK TO SAVEPOINT` resets transaction state cleanly

**When to use:** High-contention workloads where you want to avoid the overhead of checking out a new client per retry. The standard `withCrdbRetry` (fresh client per attempt) is simpler and recommended for most cases.

---

## Parameterized Queries with UUID

```typescript
import type pg from "pg";

interface UserRow {
  id: string; // UUID
  email: string;
  name: string;
  created_at: Date;
}

// SELECT with UUID parameter
async function getUserById(
  pool: pg.Pool,
  userId: string,
): Promise<UserRow | undefined> {
  const result = await pool.query<UserRow>(
    "SELECT id, email, name, created_at FROM users WHERE id = $1",
    [userId],
  );
  return result.rows[0];
}

// INSERT with gen_random_uuid() (database generates the UUID)
async function createUser(
  pool: pg.Pool,
  email: string,
  name: string,
): Promise<UserRow> {
  const result = await pool.query<UserRow>(
    `INSERT INTO users (email, name)
     VALUES ($1, $2)
     RETURNING id, email, name, created_at`,
    [email, name],
  );
  return result.rows[0];
}

// Array parameter -- use ANY($1), not IN ($1)
async function getUsersByIds(
  pool: pg.Pool,
  userIds: string[],
): Promise<UserRow[]> {
  const result = await pool.query<UserRow>(
    "SELECT id, email, name, created_at FROM users WHERE id = ANY($1)",
    [userIds],
  );
  return result.rows;
}

export { getUserById, createUser, getUsersByIds };
```

**Why good:** UUID primary keys (string type in TypeScript), parameterized queries, `RETURNING` avoids a second query, `= ANY($1)` for array parameters (not `IN ($1)`)

---

## Error Handling with CockroachDB-Specific Codes

```typescript
import type pg from "pg";

// PostgreSQL SQLSTATE codes (also used by CockroachDB)
const PG_UNIQUE_VIOLATION = "23505";
const PG_FOREIGN_KEY_VIOLATION = "23503";
const PG_NOT_NULL_VIOLATION = "23502";
const PG_CHECK_VIOLATION = "23514";

// CockroachDB-specific retry codes
const CRDB_SERIALIZATION_FAILURE = "40001";
const CRDB_STATEMENT_COMPLETION_UNKNOWN = "40003";

interface PgError extends Error {
  code: string;
  constraint?: string;
  detail?: string;
  table?: string;
  column?: string;
}

function isPgError(err: unknown): err is PgError {
  return err instanceof Error && "code" in err;
}

async function createUser(
  pool: pg.Pool,
  email: string,
  name: string,
): Promise<UserRow> {
  // NOTE: this should be called INSIDE withCrdbRetry for 40001 handling
  try {
    const result = await pool.query<UserRow>(
      "INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *",
      [email, name],
    );
    return result.rows[0];
  } catch (err) {
    if (!isPgError(err)) throw err;

    switch (err.code) {
      case PG_UNIQUE_VIOLATION:
        throw new ConflictError(
          `Duplicate value for constraint: ${err.constraint}`,
        );
      case PG_FOREIGN_KEY_VIOLATION:
        throw new NotFoundError(
          `Referenced entity does not exist: ${err.detail}`,
        );
      case PG_NOT_NULL_VIOLATION:
        throw new ValidationError(`Missing required field: ${err.column}`);
      case PG_CHECK_VIOLATION:
        throw new ValidationError(`Validation failed: ${err.constraint}`);
      default:
        throw err;
    }
  }
}

export {
  PG_UNIQUE_VIOLATION,
  PG_FOREIGN_KEY_VIOLATION,
  PG_NOT_NULL_VIOLATION,
  PG_CHECK_VIOLATION,
  CRDB_SERIALIZATION_FAILURE,
  CRDB_STATEMENT_COMPLETION_UNKNOWN,
};
```

**Why good:** Named constants for all error codes, type guard for safe property access, CockroachDB uses the same PostgreSQL SQLSTATE codes for constraint violations

**Gotcha:** `40001` errors should be handled at the transaction level (by `withCrdbRetry`), not inside individual query functions. Constraint violations (23xxx) are application errors that should NOT be retried.

---

## Graceful Pool Shutdown

```typescript
import type pg from "pg";

async function gracefulShutdown(pool: pg.Pool): Promise<void> {
  console.log("Shutting down database pool...");
  await pool.end();
  console.log("Database pool closed");
}

process.on("SIGTERM", async () => {
  await gracefulShutdown(pool);
  process.exit(0);
});

process.on("SIGINT", async () => {
  await gracefulShutdown(pool);
  process.exit(0);
});
```

**Why good:** Same as PostgreSQL -- `pool.end()` drains the pool cleanly

---

## Table Schema Design

```sql
-- Good Example - CockroachDB-optimized table
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

  INDEX idx_orders_user_id (user_id),
  INDEX idx_orders_status (status) WHERE status != 'completed'
);
```

**Why good:** UUID primary key distributes writes, foreign key to UUID column, partial index on status reduces index size, DECIMAL for money (returned as string by pg driver)

```sql
-- Bad Example - PostgreSQL patterns that hurt CockroachDB
CREATE TABLE orders (
  id SERIAL PRIMARY KEY, -- hotspot!
  user_id INTEGER NOT NULL REFERENCES users(id), -- assumes integer PK on users
  total MONEY NOT NULL, -- MONEY type has portability issues
  status TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NOT NULL DEFAULT now() -- use TIMESTAMPTZ, not TIMESTAMP
);
```

**Why bad:** SERIAL creates write hotspot, INTEGER FK assumes sequential PK, MONEY type has formatting issues across locales, TIMESTAMP without timezone loses timezone info

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# CockroachDB -- Multi-Region & Performance Examples

> Locality configuration, survival goals, follower reads, AS OF SYSTEM TIME, and performance optimization. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [core.md](core.md) -- Pool setup, transaction retry logic, parameterized queries
- [schema-ops.md](schema-ops.md) -- Online schema changes, IMPORT INTO, CHANGEFEED

---

## Multi-Region Database Setup

Configure a database to span multiple regions with locality-aware data placement.

```sql
-- Step 1: Start nodes with locality flags
-- cockroach start --locality=region=us-east1,zone=us-east1-b ...
-- cockroach start --locality=region=us-west1,zone=us-west1-a ...
-- cockroach start --locality=region=eu-west1,zone=eu-west1-c ...

-- Step 2: Set primary region
ALTER DATABASE myapp PRIMARY REGION "us-east1";

-- Step 3: Add secondary regions
ALTER DATABASE myapp ADD REGION "us-west1";
ALTER DATABASE myapp ADD REGION "eu-west1";

-- Step 4: Set survival goal
ALTER DATABASE myapp SURVIVE ZONE FAILURE;
-- Or for maximum availability (requires 3+ regions):
-- ALTER DATABASE myapp SURVIVE REGION FAILURE;
```

**Why good:** Locality flags on nodes tell CockroachDB where each node is physically located, enabling intelligent data placement. Region failure survival requires 5 replicas (2+2+1 across 3 regions).

---

## Table Locality Configuration

### Regional By Table (Default)

All data for the table resides in one region. Best for data accessed primarily from one location.

```sql
-- Data lives in the primary region
ALTER TABLE users SET LOCALITY REGIONAL BY TABLE IN PRIMARY REGION;

-- Or pin to a specific region
ALTER TABLE eu_compliance_logs SET LOCALITY REGIONAL BY TABLE IN "eu-west1";
```

**When to use:** Tables accessed from a single region (e.g., region-specific audit logs, compliance data that must stay in a jurisdiction).

---

### Regional By Row

Each row is placed in the region specified by its `crdb_region` column. Best for user data where users are geo-distributed.

```sql
-- Step 1: Add the region column
ALTER TABLE users ADD COLUMN crdb_region crdb_internal_region
  NOT NULL DEFAULT gateway_region()
  AS (
    CASE
      WHEN country IN ('US', 'CA', 'MX') THEN 'us-east1'
      WHEN country IN ('GB', 'DE', 'FR') THEN 'eu-west1'
      ELSE 'us-west1'
    END
  ) STORED;

-- Step 2: Set the locality
ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
```

**Why good:** Each user's data lives in their nearest region. Reads and writes for that user are local. The computed column automatically places rows based on the `country` field.

**When to use:** User profiles, session data, per-tenant data -- anything where each record has a natural region affinity.

```typescript
// Application code -- insert with explicit region
await pool.query(
  `INSERT INTO users (email, name, country, crdb_region)
   VALUES ($1, $2, $3, $4)`,
  [email, name, country, "us-east1"],
);

// Or let the computed column handle it
await pool.query(
  `INSERT INTO users (email, name, country)
   VALUES ($1, $2, $3)`,
  [email, name, country],
  // crdb_region is computed from country
);
```

---

### Global Tables

Data is replicated to all regions for fast reads everywhere. Writes are slower because they require cross-region consensus.

```sql
ALTER TABLE feature_flags SET LOCALITY GLOBAL;
ALTER TABLE exchange_rates SET LOCALITY GLOBAL;
ALTER TABLE app_config SET LOCALITY GLOBAL;
```

**When to use:** Reference data, configuration, feature flags -- tables that are read frequently from all regions but updated rarely.

**Gotcha:** Global table writes require consensus across all regions, adding latency proportional to the cross-region round-trip time. Only use GLOBAL for tables with a high read-to-write ratio.

---

## AS OF SYSTEM TIME (Follower Reads)

### Basic Follower Read

Read slightly stale data from the nearest replica. This avoids the round-trip to the leaseholder.

```typescript
import type pg from "pg";

interface ProductRow {
  id: string;
  name: string;
  price: string; // DECIMAL returns as string
  category: string;
}

// Follower read -- served by nearest replica
async function getProducts(
  pool: pg.Pool,
  category: string,
): Promise<ProductRow[]> {
  const result = await pool.query<ProductRow>(
    `SELECT id, name, price, category
     FROM products
     WHERE category = $1
     AS OF SYSTEM TIME follower_read_timestamp()`,
    [category],
  );
  return result.rows;
}

export { getProducts };
```

**Why good:** `follower_read_timestamp()` automatically computes a safe staleness window (at least 4.2 seconds in the past). The query is served by any replica, avoiding leaseholder round-trip.

---

### Explicit Staleness

When you need a specific staleness guarantee or a historical snapshot.

```typescript
// Read data as of 10 seconds ago
const TEN_SECONDS_AGO = "-10s";

const result = await pool.query<ProductRow>(
  `SELECT id, name, price
   FROM products
   WHERE category = $1
   AS OF SYSTEM TIME now() - $2::INTERVAL`,
  [category, TEN_SECONDS_AGO],
);

// Read at a specific timestamp
const result2 = await pool.query<ProductRow>(
  `SELECT id, name, price
   FROM products
   AS OF SYSTEM TIME '2025-01-15 12:00:00+00:00'`,
);
```

**When to use:** Historical snapshots for reports, consistent reads across multiple queries (same timestamp), analytics on past data.

---

### Bounded Staleness Read

Read the freshest data available on the nearest replica, with a maximum staleness guarantee.

```typescript
const MAX_STALENESS = "10s";

// Bounded staleness: single-row lookup by primary key
const result = await pool.query<ProductRow>(
  `SELECT id, name, price
   FROM products
   WHERE id = $1
   AS OF SYSTEM TIME with_max_staleness($2::INTERVAL)`,
  [productId, MAX_STALENESS],
);
```

**Why good:** Tries to serve the freshest data locally. If the local replica's data is within the staleness bound, it answers immediately. If not, it fetches from the leaseholder.

**When to use:** When you want low latency but also want reasonably fresh data (e.g., a product price that can be up to 10 seconds stale).

**Gotcha:** `with_max_staleness()` and `with_min_timestamp()` have strict limitations: they must be used in a **single-statement implicit transaction**, must read from a **single row**, and must not require an index join. For multi-row follower reads, use `follower_read_timestamp()` instead.

---

### Follower Reads in Explicit Transactions

```typescript
async function generateReport(pool: pg.Pool): Promise<ReportData> {
  const client = await pool.connect();
  try {
    // All reads in this transaction see the same snapshot
    await client.query("BEGIN AS OF SYSTEM TIME follower_read_timestamp()");

    const users = await client.query("SELECT count(*) FROM users");
    const orders = await client.query("SELECT sum(total) FROM orders");
    const products = await client.query("SELECT count(*) FROM products");

    await client.query("COMMIT");

    return {
      userCount: parseInt(users.rows[0].count, 10),
      orderTotal: orders.rows[0].sum,
      productCount: parseInt(products.rows[0].count, 10),
    };
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

export { generateReport };
```

**Why good:** All queries in the transaction see a consistent snapshot, served by nearest replica, no retry logic needed (read-only follower reads cannot conflict)

**Gotcha:** `AS OF SYSTEM TIME` transactions are read-only. You cannot INSERT, UPDATE, or DELETE within them.

---

## SELECT FOR UPDATE (Pessimistic Locking)

CockroachDB supports `SELECT ... FOR UPDATE` but it acquires locks across the cluster. Use it for high-contention scenarios.

```typescript
async function claimJob(
  pool: pg.Pool,
  workerId: string,
): Promise<JobRow | null> {
  return withCrdbRetry(pool, async (client) => {
    // SKIP LOCKED avoids waiting on rows locked by other workers
    const { rows } = await client.query<JobRow>(
      `SELECT id, payload FROM job_queue
       WHERE status = 'pending'
       ORDER BY created_at
       LIMIT 1
       FOR UPDATE SKIP LOCKED`,
    );

    if (rows.length === 0) return null;

    await client.query(
      `UPDATE job_queue
       SET status = 'processing', worker_id = $1, started_at = now()
       WHERE id = $2`,
      [workerId, rows[0].id],
    );

    return rows[0];
  });
}

export { claimJob };
```

**Why good:** `FOR UPDATE SKIP LOCKED` is ideal for job queues -- it skips rows already claimed by other workers instead of waiting. Wrapped in `withCrdbRetry` for 40001 handling.

**Gotcha:** `SELECT ... FOR UPDATE` has higher latency in CockroachDB than PostgreSQL because the lock must be coordinated across nodes. For low-contention workloads, optimistic locking (version columns) is often faster.

---

## Optimistic Locking with Version Columns

An alternative to `FOR UPDATE` that avoids distributed lock coordination.

```typescript
async function updateProductPrice(
  pool: pg.Pool,
  productId: string,
  newPrice: string,
  expectedVersion: number,
): Promise<boolean> {
  return withCrdbRetry(pool, async (client) => {
    const result = await client.query(
      `UPDATE products
       SET price = $1, version = version + 1, updated_at = now()
       WHERE id = $2 AND version = $3`,
      [newPrice, productId, expectedVersion],
    );

    if ((result.rowCount ?? 0) === 0) {
      throw new ConflictError(
        "Product was modified by another transaction. Re-read and retry.",
      );
    }

    return true;
  });
}

export { updateProductPrice };
```

**Why good:** No distributed lock overhead, version check detects concurrent modifications, conflict results in a clear error message

**When to use:** Low-to-moderate contention scenarios. For high contention (many concurrent writes to the same row), use `FOR UPDATE`.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_

# CockroachDB -- Schema & Operations Examples

> Online schema changes, IMPORT INTO for bulk data, CHANGEFEED for CDC, and cockroach CLI usage. Reference from [SKILL.md](../SKILL.md).

**Related examples:**

- [core.md](core.md) -- Pool setup, transaction retry logic, parameterized queries
- [multi-region.md](multi-region.md) -- Locality, survival goals, follower reads

---

## Online Schema Changes

CockroachDB DDL runs as background jobs. Tables remain available for reads and writes throughout the change. DDL statements CANNOT be inside explicit transactions.

### Adding Columns

```typescript
import type pg from "pg";

// Good Example - DDL as individual statements
async function addPhoneColumn(pool: pg.Pool): Promise<void> {
  // Each DDL runs as an implicit transaction (individual statement)
  await pool.query("ALTER TABLE users ADD COLUMN phone TEXT");
  // Table is fully available during this operation
}

// Add column with default (backfills existing rows in background)
async function addStatusColumn(pool: pg.Pool): Promise<void> {
  await pool.query(
    "ALTER TABLE orders ADD COLUMN priority TEXT NOT NULL DEFAULT 'normal'",
  );
  // CockroachDB backfills the default value in a background job
}

export { addPhoneColumn, addStatusColumn };
```

**Why good:** Individual DDL statements, no transaction wrapper, table stays available

```typescript
// Bad Example - DDL inside explicit transaction
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("ALTER TABLE users ADD COLUMN phone TEXT");
  await client.query("ALTER TABLE users ADD COLUMN address TEXT");
  await client.query("COMMIT");
  // Most DDL can fail at COMMIT time with a partially applied state
  // CREATE TABLE/CREATE INDEX are exceptions, but mixing DDL types is unsafe
} finally {
  client.release();
}
```

**Why bad:** Most DDL in explicit transactions can fail at COMMIT time with a partially applied state. The safest practice is one DDL statement per implicit transaction.

---

### Creating Indexes

```sql
-- Good Example - Index creation (runs as background job)
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Partial index (reduces index size)
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

-- GIN index for JSONB columns
CREATE INVERTED INDEX idx_users_metadata ON users (metadata);
```

**Why good:** Index creation runs in the background without blocking reads or writes. Partial indexes reduce storage and improve write performance.

**Gotcha:** CockroachDB supports both `CREATE INVERTED INDEX` (CockroachDB-specific) and `CREATE INDEX ... USING GIN` (PostgreSQL-compatible) for JSONB/array indexes. Both are equivalent.

---

### Monitoring Schema Change Progress

```sql
-- Check running schema change jobs
SHOW JOBS WHERE job_type = 'SCHEMA CHANGE';

-- Detailed job status
SELECT job_id, description, status, fraction_completed, error
FROM [SHOW JOBS]
WHERE job_type = 'SCHEMA CHANGE'
  AND status != 'succeeded';

-- Cancel a running schema change
CANCEL JOB <job_id>;
```

**Gotcha:** In production, run one DDL statement at a time and monitor via `SHOW JOBS`. Multiple concurrent schema changes compete for resources and can slow each other down significantly.

---

## Migration Pattern

Since DDL cannot be in transactions, migrations need a different approach than PostgreSQL.

```typescript
import type pg from "pg";
import { readFileSync, readdirSync } from "node:fs";
import { join } from "node:path";

const MIGRATIONS_TABLE = "schema_migrations";

async function ensureMigrationsTable(pool: pg.Pool): Promise<void> {
  // This DDL is fine as an implicit transaction
  await pool.query(`
    CREATE TABLE IF NOT EXISTS ${MIGRATIONS_TABLE} (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name TEXT NOT NULL UNIQUE,
      applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  `);
}

async function getAppliedMigrations(pool: pg.Pool): Promise<Set<string>> {
  const result = await pool.query<{ name: string }>(
    `SELECT name FROM ${MIGRATIONS_TABLE} ORDER BY applied_at`,
  );
  return new Set(result.rows.map((r) => r.name));
}

async function runMigrations(
  pool: pg.Pool,
  migrationsDir: string,
): Promise<string[]> {
  await ensureMigrationsTable(pool);
  const applied = await getAppliedMigrations(pool);

  const files = readdirSync(migrationsDir)
    .filter((f) => f.endsWith(".sql"))
    .sort();

  const newMigrations: string[] = [];

  for (const file of files) {
    if (applied.has(file)) continue;

    const sql = readFileSync(join(migrationsDir, file), "utf-8");

    // Split file into individual statements
    // DDL and DML may need to run as separate statements
    const statements = sql
      .split(";")
      .map((s) => s.trim())
      .filter((s) => s.length > 0);

    for (const statement of statements) {
      await pool.query(statement);
    }

    // Record migration AFTER all statements succeed
    await pool.query(`INSERT INTO ${MIGRATIONS_TABLE} (name) VALUES ($1)`, [
      file,
    ]);

    newMigrations.push(file);
  }

  return newMigrations;
}

export { runMigrations };
```

**Why good:** UUID primary key on migrations table, each DDL statement runs individually (not in a transaction), migration recorded after success

**Gotcha:** Unlike PostgreSQL, if a migration fails partway through, some statements may have been applied but the migration is not recorded. You may need to manually clean up. Design migrations to be idempotent where possible (e.g., `CREATE INDEX IF NOT EXISTS`).

---

## IMPORT INTO (Bulk Data Loading)

`IMPORT INTO` is CockroachDB's high-performance bulk loading mechanism. It is significantly faster than individual INSERTs for large datasets.

```sql
-- Import from CSV (cloud storage)
IMPORT INTO users (id, email, name, created_at)
  CSV DATA (
    'gs://my-bucket/users-part1.csv',
    'gs://my-bucket/users-part2.csv'
  )
  WITH skip = '1', nullif = '';

-- Import from S3
IMPORT INTO orders (id, user_id, total, status)
  CSV DATA (
    's3://my-bucket/orders.csv?AWS_ACCESS_KEY_ID={key}&AWS_SECRET_ACCESS_KEY={secret}'
  );

-- Import with custom delimiter (TSV)
IMPORT INTO products (id, name, price)
  CSV DATA ('gs://my-bucket/products.tsv')
  WITH delimiter = e'\t';

-- Run import as detached background job
IMPORT INTO users (id, email, name)
  CSV DATA ('gs://my-bucket/users.csv')
  WITH detached;
```

**Why good:** Parallel bulk loading from multiple files, supports cloud storage directly, `detached` option runs as background job

**Gotcha:** `IMPORT INTO` takes the target table **offline** during the import. The table cannot serve reads or writes until the import completes. Plan accordingly -- import during maintenance windows or into staging tables.

**Gotcha:** Column order in the CSV must match the column order specified in the `IMPORT INTO` statement.

---

## CHANGEFEED (Change Data Capture)

Stream row-level changes from tables to external systems.

### Changefeed to Kafka

```sql
-- Stream all changes from orders table to Kafka
CREATE CHANGEFEED FOR TABLE orders
  INTO 'kafka://broker.internal:9092'
  WITH updated, resolved = '10s',
       format = 'json',
       topic_prefix = 'crdb_';
-- Produces to topic: crdb_orders
```

### Changefeed to Webhook

```sql
CREATE CHANGEFEED FOR TABLE orders
  INTO 'webhook-https://my-api.example.com/webhooks/orders'
  WITH updated, resolved = '30s';
```

### Changefeed to Cloud Storage

```sql
CREATE CHANGEFEED FOR TABLE orders
  INTO 'gs://my-bucket/cdc/orders'
  WITH updated, resolved = '1m',
       format = 'json';
```

### Sinkless Changefeed (SQL Client)

```sql
-- Streams to the SQL client indefinitely
CREATE CHANGEFEED FOR TABLE orders WITH updated;

-- With filtering (CDC query)
CREATE CHANGEFEED WITH updated AS
  SELECT id, status, updated_at
  FROM orders
  WHERE status IN ('shipped', 'delivered');
```

### Managing Changefeeds

```sql
-- List active changefeeds
SELECT job_id, description, status
FROM [SHOW JOBS]
WHERE job_type = 'CHANGEFEED';

-- Pause a changefeed
PAUSE JOB <job_id>;

-- Resume a paused changefeed
RESUME JOB <job_id>;

-- Cancel a changefeed
CANCEL JOB <job_id>;
```

**When to use:** Event-driven architectures, real-time data replication, audit logging, cache invalidation, feeding data to analytics pipelines.

**Gotcha:** CDC queries support only a single table per changefeed. You cannot JOIN across tables in a changefeed query.

**Gotcha:** Enterprise changefeeds (with sinks like Kafka, S3, webhooks) require a CockroachDB Enterprise license. Sinkless changefeeds are available in all editions.

---

## cockroach CLI Operations

### Starting a Local Cluster

```bash
# Start a single-node cluster for development
cockroach start-single-node --insecure --store=node1 --listen-addr=localhost:26257

# Start a 3-node cluster
cockroach start --insecure --store=node1 --listen-addr=localhost:26257 \
  --join=localhost:26257,localhost:26258,localhost:26259

cockroach start --insecure --store=node2 --listen-addr=localhost:26258 \
  --join=localhost:26257,localhost:26258,localhost:26259

cockroach start --insecure --store=node3 --listen-addr=localhost:26259 \
  --join=localhost:26257,localhost:26258,localhost:26259

# Initialize the cluster (run once after first start)
cockroach init --insecure --host=localhost:26257
```

### SQL Shell

```bash
# Connect to local insecure cluster
cockroach sql --insecure --host=localhost:26257

# Connect with SSL
cockroach sql --url "postgresql://user@crdb-host:26257/mydb?sslmode=verify-full&sslrootcert=ca.crt"

# Execute a single command
cockroach sql --insecure -e "SELECT count(*) FROM users"
```

### Cluster Operations

```bash
# Check node status
cockroach node status --insecure --host=localhost:26257

# Decommission a node (graceful removal)
cockroach node decommission 3 --insecure --host=localhost:26257

# Check running jobs (schema changes, imports, etc.)
cockroach sql --insecure -e "SHOW JOBS WHERE status = 'running'"

# Collect debug info for support
cockroach debug zip debug.zip --insecure --host=localhost:26257
```

### Quick Demo Cluster

```bash
# Spin up a temporary in-memory cluster with sample data
cockroach demo

# Demo with specific dataset
cockroach demo --nodes=5 --demo-locality=region=us-east1:region=us-west1:region=eu-west1
```

---

## Testing with CockroachDB

### Docker-Based Test Cluster

```bash
# Start CockroachDB for testing
docker run -d --name crdb-test \
  -p 26257:26257 -p 8080:8080 \
  cockroachdb/cockroach:latest start-single-node --insecure

# Create test database
docker exec crdb-test cockroach sql --insecure \
  -e "CREATE DATABASE testdb"
```

### Test Pool Configuration

```typescript
import pg from "pg";

const TEST_POOL_MAX = 5;
const TEST_IDLE_TIMEOUT_MS = 1_000;
const TEST_CONNECTION_TIMEOUT_MS = 3_000;

function createTestPool(): pg.Pool {
  const pool = new pg.Pool({
    connectionString:
      process.env.TEST_DATABASE_URL ??
      "postgresql://root@localhost:26257/testdb?sslmode=disable",
    max: TEST_POOL_MAX,
    idleTimeoutMillis: TEST_IDLE_TIMEOUT_MS,
    connectionTimeoutMillis: TEST_CONNECTION_TIMEOUT_MS,
    allowExitOnIdle: true,
  });

  pool.on("error", (err) => {
    console.error("Test pool error:", err.message);
  });

  return pool;
}

export { createTestPool };
```

### Transaction Rollback Test Isolation

```typescript
import type pg from "pg";

async function withTestTransaction<T>(
  pool: pg.Pool,
  testFn: (client: pg.PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    const result = await testFn(client);
    return result;
  } finally {
    await client.query("ROLLBACK");
    client.release();
  }
}

export { withTestTransaction };
```

**Why good:** Each test runs in a transaction that rolls back, leaving the database clean. Same pattern as PostgreSQL.

**Gotcha:** Code under test must accept a `PoolClient` (not a `Pool`) so the test can inject the transactional client. If code uses `pool.query()`, it gets a different connection that cannot see the test transaction's uncommitted data.

---

_Full skill documentation: [SKILL.md](../SKILL.md) | Quick reference: [reference.md](../reference.md)_