CockroachDB MCP Server for AI Agents and LLMs

Overview

This MCP (Model Context Protocol) server connects large language models and AI agents to CockroachDB, letting models manage, monitor, and query your cluster using natural-language prompts. It implements a small HTTP service that exposes CockroachDB-aware tools—SQL execution, schema inspection, query explain/analysis, and basic operational checks—so agents can request database actions and receive structured results that are safe to surface in an LLM context.

The server is useful when you want to augment observability, automate routine DBA tasks, or let an LLM act as a guided interface for analytics and troubleshooting. Instead of issuing raw SQL or running ad hoc scripts, agents can ask human-friendly questions (for example: “show me the slowest queries in the last hour” or “create an index to speed up this join”) and the MCP server translates those requests to validated DB operations and returns context-rich responses.

Features

• Natural-language driven access to CockroachDB: translate prompts into monitored SQL operations.
• Schema inspection and metadata queries (tables, indexes, column types).
• Safe SQL execution with optional read-only / preview modes and result limiting.
• Explain plans and performance diagnostics (EXPLAIN, EXPLAIN ANALYZE).
• Basic operational checks and metrics (connections, ranges, replication status).
• Pluggable authentication / API key support for agent safety and auditability.
• Docker-friendly and environment-driven configuration for deployment.

Installation / Configuration

Clone the repository and configure environment variables:

git clone https://github.com/amineelkouhen/mcp-cockroachdb.git
cd mcp-cockroachdb

Create a .env file (example keys). Adjust values for your CockroachDB instance and deployment preferences:

# CockroachDB connection
COCKROACH_HOST=localhost
COCKROACH_PORT=26257
COCKROACH_DATABASE=mydb
COCKROACH_USER=root
COCKROACH_PASSWORD=secret

# MCP server
MCP_PORT=3000
MCP_API_KEY=replace-with-secure-key

# Optional behavior
READ_ONLY=true        # if true, deny modifying statements
RESULT_LIMIT=100      # max rows returned for any query

Run with Docker (recommended for isolation):

docker build -t mcp-cockroachdb .
docker run --env-file .env -p 3000:3000 mcp-cockroachdb

Or run locally (if there is a language-specific entrypoint). Replace the command below with the actual start script in the repository:

# Example: if the project provides a start script
./start-server.sh
# Or, for Node.js projects:
npm install
npm run start

After startup, the server listens on MCP_PORT and exposes HTTP endpoints for agents to call.

Available Tools / Resources

The MCP server exposes a set of tools/endpoints that agents can call. Common endpoints include:

Example: run a read query via curl

curl -X POST http://localhost:3000/mcp/query \
  -H "Authorization: Bearer $MCP_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT id, name FROM users ORDER BY created_at DESC LIMIT 5"}'

Security notes:

• Always enable API key / token authentication for production.
• Use READ_ONLY mode for agents that should not perform DDL/DML.
• Log and audit agent requests for traceability.

Use Cases

1. Schema exploration and onboarding

   • Natural language: “List all tables in the public schema and show primary keys.”
   • Action: The server calls the schema tool, returns a structured list of tables and primary key columns, suitable to feed into prompts or UIs.

2. Ad-hoc analytics via agents

   • Natural language: “Show the top 10 customers by lifetime spend in the last 90 days.”
   • Action: The assistant translates to a parametrized SQL query, applies result limits, executes, and returns a clean tabular result or JSON for downstream processing.

3. Performance triage

   • Natural language: “What are the slowest queries over the last hour and how can we optimize them?”
   • Action: The server pulls recent query statistics, runs EXPLAIN for problematic statements, and returns both metrics and suggested optimizations (e.g., missing index suggestions).

4. Safe schema changes (with review)

   • Natural language: “Add an index to speed up the join between orders and customers on customer_id.”
   • Action: In read-only preview mode the server can return the recommended CREATE INDEX statement and an EXPLAIN showing projected improvements. With explicit approval and write privileges, it can execute the DDL and report success.

Example prompt → concrete SQL mapping

• Prompt: “Show me the row count for each table in analytics schema.”
• Mapped SQL:

  SELECT table_name, row_count
  FROM [SHOW TABLES FROM analytics]
  -- or use crdb_internal.table_row_count depending on privileges
  

Getting help and next steps

• Inspect the repository README and source for exact startup commands and implementation details.
• Integrate the MCP server with your agent framework (LangChain, LlamaIndex