CockroachDB Source

About

CockroachDB is a distributed SQL database designed for cloud-native applications. It provides strong consistency, horizontal scalability, and built-in resilience with automatic failover and recovery. CockroachDB uses the PostgreSQL wire protocol, making it compatible with many PostgreSQL tools and drivers while providing unique features like multi-region deployments and distributed transactions.

Minimum Version: CockroachDB v25.1 or later is recommended for full tool compatibility.

Available Tools

{{< list-tools >}}

Requirements

Database User

This source uses standard authentication. You will need to create a CockroachDB user to login to the database with. For CockroachDB Cloud deployments, SSL/TLS is required.

SSL/TLS Configuration

CockroachDB Cloud clusters require SSL/TLS connections. Use the queryParams section to configure SSL settings:

• For CockroachDB Cloud: Use sslmode: require at minimum
• For self-hosted with certificates: Use sslmode: verify-full with certificate paths
• For local development only: Use sslmode: disable (not recommended for production)

Example

kind: source
name: my_cockroachdb
type: cockroachdb
host: your-cluster.cockroachlabs.cloud
port: "26257"
user: myuser
password: mypassword
database: defaultdb
maxRetries: 5
retryBaseDelay: 500ms
queryParams:
  sslmode: require
  application_name: my-app

# MCP Security Settings (recommended for production)
readOnlyMode: true          # Read-only by default (MCP best practice)
enableWriteMode: false      # Set to true to allow write operations
maxRowLimit: 1000           # Limit query results
queryTimeoutSec: 30         # Prevent long-running queries
enableTelemetry: true       # Enable observability
telemetryVerbose: false     # Set true for detailed logs
clusterID: "my-cluster"     # Optional identifier
---
kind: tool
name: list_expenses
type: cockroachdb-sql
source: my_cockroachdb
description: List all expenses
statement: SELECT id, description, amount, category FROM expenses WHERE user_id = $1
parameters:
  - name: user_id
    type: string
    description: The user's ID
---
kind: tool
name: describe_expenses
type: cockroachdb-describe-table
source: my_cockroachdb
description: Describe the expenses table schema
---
kind: tool
name: list_expenses_indexes
type: cockroachdb-list-indexes
source: my_cockroachdb
description: List indexes on the expenses table

Reference

Required Parameters

Optional Parameters

MCP Security Parameters

CockroachDB integration includes security features following the Model Context Protocol (MCP) specification:

Query Parameters

Common query parameters for CockroachDB connections:

Advanced Usage

Security and MCP Compliance

Read-Only by Default: The integration follows MCP best practices by defaulting to read-only mode. This prevents accidental data modifications:

kind: source
name: my_cockroachdb
readOnlyMode: true        # Default behavior
enableWriteMode: false    # Explicit write opt-in required

To enable write operations:

kind: source
name: my_cockroachdb
readOnlyMode: false       # Disable read-only protection
enableWriteMode: true     # Explicitly allow writes

Query Limits: Automatic row limits prevent excessive data retrieval:

• SELECT queries automatically get LIMIT 1000 appended (configurable via maxRowLimit)
• Queries are terminated after 30 seconds (configurable via queryTimeoutSec)

Observability: Structured telemetry provides visibility into tool usage:

• Tool invocations are logged with status, latency, and row counts
• SQL queries are redacted to protect sensitive values
• Set telemetryVerbose: true for detailed JSON logs

Use UUID Primary Keys

CockroachDB performs best with UUID primary keys rather than sequential integers to avoid transaction hotspots:

CREATE TABLE expenses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  description TEXT,
  amount DECIMAL(10,2)
);

Automatic Transaction Retry

This source uses the official cockroach-go/v2 library which provides automatic transaction retry for serialization conflicts. For write operations requiring explicit transaction control, tools can use the ExecuteTxWithRetry method.

Multi-Region Deployments

CockroachDB supports multi-region deployments with automatic data distribution. Configure your cluster's regions and survival goals separately from the Toolbox configuration. The source will connect to any node in the cluster.

Connection Pooling

The source maintains a connection pool to the CockroachDB cluster. The pool automatically handles:

• Load balancing across cluster nodes
• Connection retry with exponential backoff
• Health checking of connections

Troubleshooting

SSL/TLS Errors

If you encounter "server requires encryption" errors:

1. For CockroachDB Cloud, ensure sslmode is set to require or higher:

   yamlCopy

   queryParams:
     sslmode: require
   

2. For certificate verification, download your cluster's root certificate and configure:

   yamlCopy

   queryParams:
     sslmode: verify-full
     sslrootcert: /path/to/ca.crt
   

Connection Timeouts

If experiencing connection timeouts:

1. Check network connectivity to the CockroachDB cluster
2. Verify firewall rules allow connections on port 26257
3. For CockroachDB Cloud, ensure IP allowlisting is configured
4. Increase maxRetries or retryBaseDelay if needed

Transaction Retry Errors

CockroachDB may encounter serializable transaction conflicts. The integration automatically handles these retries using the cockroach-go library. If you see retry-related errors, check:

1. Database load and contention
2. Query patterns that might cause conflicts
3. Consider using SELECT FOR UPDATE for explicit locking

Additional Resources

• CockroachDB Documentation
• CockroachDB Best Practices
• Multi-Region Capabilities
• Connection Parameters