CockroachDB

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

• cockroachdb-sql Execute SQL queries as prepared statements in CockroachDB (alias for execute-sql).

• cockroachdb-execute-sql Run parameterized SQL statements in CockroachDB.

• cockroachdb-list-schemas List schemas in a CockroachDB database.

• cockroachdb-list-tables List tables in a CockroachDB database.

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

sources:
  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

tools:
  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
  
  describe_expenses:
    type: cockroachdb-describe-table
    source: my_cockroachdb
    description: Describe the expenses table schema
  
  list_expenses_indexes:
    type: cockroachdb-list-indexes
    source: my_cockroachdb
    description: List indexes on the expenses table

Configuration Parameters

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:

Best Practices

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:

sources:
  my_cockroachdb:
    readOnlyMode: true        # Default behavior
    enableWriteMode: false    # Explicit write opt-in required

To enable write operations:

sources:
  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:

   queryParams:
     sslmode: require
   

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

   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