Cockroachdb MCP Server

🚀 CockroachDB MCP Server

English | Simplified Chinese

This is a CockroachDB MCP server based on Cursor, implemented according to the Model Context Protocol (MCP) specification, allowing you to directly interact with the CockroachDB database.

✨ Features

• Connect to the CockroachDB database.
• Retrieve all tables in the database.
• Obtain table structure information.
• Execute SQL queries.
• Provide database status resources.
• Offer SQL query templates.
• Implement an automatic reconnection mechanism to ensure connection stability.
• Employ a connection keep-alive mechanism to prevent timeouts.
• Handle the graceful exit of processes.
• Maintain detailed logs for troubleshooting.
• Support manual disconnection.

📦 Installation

Installation via Smithery

Automatically install the CockroachDB MCP Server for Cursor through Smithery:

npx -y @smithery/cli install @Swayingleaves/cockroachdb-mcp-server --client claude

1. Clone the repository and navigate to the project directory.
2. Install the dependencies:

pip install -r requirements.txt

3. Install uv:

curl -LsSf https://astral.sh/uv/install.sh | sh

💻 Usage Examples

Basic Usage

{
    "mcpServers": {
        "cockroachdb-mcp": {
            "command": "uv",
            "args": [
                "--directory",
                "/Users/local/cockroachdb-mcp",
                "run",
                "server.py"
            ],
            "jdbc_url": "jdbc:postgresql://localhost:2626/your_database",
            "username": "your_username",
            "password": "your_password"
        }
    }
}

📚 Documentation

MCP Function Descriptions

Tools

connect_database

• Description: Connect to the CockroachDB database.
• Parameters:
  • jdbc_url: The JDBC URL of the database to connect to.
  • username: The username for logging into the database.
  • password: The password for logging into the database.

Resources

/tables

• Description: Retrieve all tables in the database.
• Method: GET
• Response: Returns a list containing the names of all tables.

/schema/{table}

• Description: Obtain the structure information of the specified table.
• Method: GET
• Path Parameters:
  • table: The name of the table for which to obtain the structure information.
• Response: Returns information about the fields and their data types of the specified table.

Examples

curl http://localhost:2626/tables
# Response: ["users", "products", "orders"]

curl http://localhost:2626/schema/users
# Response: {
#   "columns": ["id", "name", "email", "created_at"],
#   "types": ["int", "text", "text", "timestamp"]
# }

Logs

Location

• File: ./cockroachdb-mcp-server.log
• Content: Records server startup, requests, and error information.

Sample Log Entries

INFO: Server started on port 2626
DEBUG: Received request GET /tables
INFO: Response sent with status 200
ERROR: Error executing query: table 'nonexistent' not found

Special Character Handling

• Escaping: Use the backslash \ to escape special characters.
• Example:

{
    "query": "SELECT * FROM `users` WHERE id = \\$1"
}

TCP Keep-Alive Settings

Configuration

uvicorn --reload --host 0.0.0.0 --port 2626 yourapp:app

Parameters

• Automatic Reconnection: Implement automatic reconnection using a database connection pool.
• Heartbeat Detection: Periodically execute queries to keep the connection alive.

Troubleshooting

Common Issues

Connection Refused

• Cause: The server is not running or the binding address is incorrect.
• Solution: Check if the server is running on the specified port and ensure that the firewall allows access.

Query Timeout

• Cause: The query execution time is too long or the database load is too high.
• Solution: Optimize the query or increase the database resources.

Database Not Found

• Cause: The database name is incorrect or not properly configured.
• Solution: Check if the JDBC URL and credentials are correct.

Advanced

Using Environment Variables

export DATABASE_URL="jdbc:postgresql://localhost:2626/your_database"
export DB_USER="your_username"
export DB_PASSWORD="your_password"

Configuration File Example

from typing import List, Dict
import logging
from fastapi import FastAPI

app = FastAPI()

@app.get("/tables")
async def get_tables() -> List[str]:
    return ["users", "products", "orders"]

@app.get("/schema/{table}")
async def get_schema(table: str) -> Dict:
    schemas: Dict = {
        "users": {"columns": ["id", "name", "email"], "types": ["int", "text", "text"]},
        "products": {"columns": ["id", "name", "price"], "types": ["int", "text", "float"]}
    }
    return schemas.get(table, {"error": "Table not found"})

Contribution

Welcome to submit issues and pull requests! Repository URL: https://github.com/yourusername/cockroachdb-mcp-server

📄 License

MIT License