---
title: "CockroachDB"
type: docs
weight: 1
description: >
CockroachDB is a distributed SQL database built for cloud applications.
---
## About
[CockroachDB][crdb-docs] 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.
[crdb-docs]: https://www.cockroachlabs.com/docs/
## Available Tools
- [`cockroachdb-sql`](../tools/cockroachdb/cockroachdb-sql.md)
Execute SQL queries as prepared statements in CockroachDB (alias for execute-sql).
- [`cockroachdb-execute-sql`](../tools/cockroachdb/cockroachdb-execute-sql.md)
Run parameterized SQL statements in CockroachDB.
- [`cockroachdb-list-schemas`](../tools/cockroachdb/cockroachdb-list-schemas.md)
List schemas in a CockroachDB database.
- [`cockroachdb-list-tables`](../tools/cockroachdb/cockroachdb-list-tables.md)
List tables in a CockroachDB database.
## Requirements
### Database User
This source uses standard authentication. You will need to [create a CockroachDB user][crdb-users] to login to the database with. For CockroachDB Cloud deployments, SSL/TLS is required.
[crdb-users]: https://www.cockroachlabs.com/docs/stable/create-user.html
### 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
```yaml
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
| Parameter | Type | Description |
|-----------|------|-------------|
| `type` | string | Must be `cockroachdb` |
| `host` | string | The hostname or IP address of the CockroachDB cluster |
| `port` | string | The port number (typically "26257") |
| `user` | string | The database user name |
| `database` | string | The database name to connect to |
### Optional Parameters
| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| `password` | string | "" | The database password (can be empty for certificate-based auth) |
| `maxRetries` | integer | 5 | Maximum number of connection retry attempts |
| `retryBaseDelay` | string | "500ms" | Base delay between retry attempts (exponential backoff) |
| `queryParams` | map | {} | Additional connection parameters (e.g., SSL configuration) |
### MCP Security Parameters
CockroachDB integration includes security features following the [Model Context Protocol (MCP)](https://modelcontextprotocol.io/) specification:
| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| `readOnlyMode` | boolean | true | Enables read-only mode by default (MCP requirement) |
| `enableWriteMode` | boolean | false | Explicitly enable write operations (INSERT/UPDATE/DELETE/CREATE/DROP) |
| `maxRowLimit` | integer | 1000 | Maximum rows returned per SELECT query (auto-adds LIMIT clause) |
| `queryTimeoutSec` | integer | 30 | Query timeout in seconds to prevent long-running queries |
| `enableTelemetry` | boolean | true | Enable structured logging of tool invocations |
| `telemetryVerbose` | boolean | false | Enable detailed JSON telemetry output |
| `clusterID` | string | "" | Optional cluster identifier for telemetry |
### Query Parameters
Common query parameters for CockroachDB connections:
| Parameter | Values | Description |
|-----------|--------|-------------|
| `sslmode` | `disable`, `require`, `verify-ca`, `verify-full` | SSL/TLS mode (CockroachDB Cloud requires `require` or higher) |
| `sslrootcert` | file path | Path to root certificate for SSL verification |
| `sslcert` | file path | Path to client certificate |
| `sslkey` | file path | Path to client key |
| `application_name` | string | Application name for connection tracking |
## 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:
```yaml
sources:
my_cockroachdb:
readOnlyMode: true # Default behavior
enableWriteMode: false # Explicit write opt-in required
```
To enable write operations:
```yaml
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:
```sql
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:
```yaml
queryParams:
sslmode: require
```
2. For certificate verification, download your cluster's root certificate and configure:
```yaml
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](https://www.cockroachlabs.com/docs/)
- [CockroachDB Best Practices](https://www.cockroachlabs.com/docs/stable/performance-best-practices-overview.html)
- [Multi-Region Capabilities](https://www.cockroachlabs.com/docs/stable/multiregion-overview.html)
- [Connection Parameters](https://www.cockroachlabs.com/docs/stable/connection-parameters.html)
