SynxDB MCP Service

The SynxDB Model Context Protocol (MCP) service is a middleware designed specifically for the SynxDB database. It provides a secure, AI-ready interface that allows large language model (LLM) applications, such as AI programming assistants, to interact with and manage the SynxDB database securely and efficiently.

Core features

Empower AI applications: Enables AI assistants to securely interact with your database to perform tasks like executing queries, monitoring performance, and managing objects.
Simplify development: Provides a standardized database interface for LLM applications, allowing developers to focus on application logic without dealing with complex database drivers and security issues.
Improve efficiency: Automates and intelligentizes tedious manual database management tasks, such as performance diagnostics and index recommendations.
Ensure security and reliability: Offers comprehensive security for database interactions with built-in mechanisms like SQL injection prevention, parameterized queries, connection pooling, and sensitive table protection.

Use cases

Intelligent data querying: Builds an AI assistant that can understand natural language and execute database queries.
Automated operations: Allows an AI assistant to monitor database status, analyze slow queries, and provide optimization suggestions based on context.
Data analysis and reporting: Quickly extracts data from the database using AI applications to generate insights and business reports.
AI-assisted development: Gets table structure information, query suggestions, and performance analysis from an AI assistant while writing SQL.

Installation

Prerequisites

Before you begin the installation, ensure your environment includes the following software:

Python 3.8 or higher
uv (a fast Python package installer and resolver)

Installation steps

If you have not installed uv, run the following command:
```
curl -sSfL https://astral.sh/uv/install.sh | sh
```
Download the MCP Server source code, navigate to the project directory, then use uv to create a virtual environment and sync dependencies.
```
cd mcp-server
uv venv
source .venv/bin/activate
uv sync
```
In the virtual environment, install MCP Server using pip.
```
uv pip install -e .
```
Run the build command to generate a wheel package.
```
uv build
```

Usage guide

Quick start: Connect to a demo cluster

This section guides you on how to quickly start the MCP service and connect it to a local SynxDB demo cluster, which is useful for development and testing.

Attention

Before you start, you need a running SynxDB demo cluster.

Step 1: Configure database connection permissions

To allow the MCP service to connect to the database, modify the pg_hba.conf file to permit local connections.

Warning

The following trust configuration is for demonstration purposes only, because it allows passwordless access. Do not use this configuration in a production environment.

vi ~/cloudberry/gpAux/gpdemo/datadirs/qddir/demoDataDir-1/pg_hba.conf

Add the following two lines to the end of the file:

# IPv4 local connections
host    all     all     127.0.0.1/32    trust
# IPv6 local connections
host    all     all     ::1/128         trust

After modifying the file, reload the database configuration to apply the changes:

gpstop -u

Step 2: Create an environment configuration file

Create a file named .env in the root directory of the MCP Server project and add the following content. This is the default configuration for the demo cluster.

# Database configuration (demo cluster defaults)
DB_HOST=localhost
DB_PORT=7000
DB_NAME=postgres
DB_USER=gpadmin
# No password needed for the demo cluster

# MCP service configuration
MCP_HOST=localhost
MCP_PORT=8000
MCP_DEBUG=false

Step 3: Start the MCP service

Run the following command in the project root directory to start the service:

MCP_HOST=0.0.0.0 MCP_PORT=8000 python -m cbmcp.server

If you see the following output, the service is running successfully:

[09/17/25 14:07:50] INFO     Starting MCP server '{{ product_name }} MCP Server' with transport        server.py:1572
                             'streamable-http' on http://0.0.0.0:8000/mcp/

Step 4: Configure the LLM client

In your AI assistant or IDE, add a new MCP service configuration:

Service Type: Streamable-HTTP
URL: http://[YOUR_HOST_IP]:8000/mcp

Replace [YOUR_HOST_IP] with the actual IP address of your host machine.

Integrate with an LLM client

You can integrate the MCP service with various clients and IDEs that support the protocol. This section provides examples for Claude Desktop, Cursor, Windsurf, and VS Code.

Claude Desktop

Add the following configuration to your Claude Desktop configuration file.

Stdio transport mode (Recommended)

{
"mcpServers": {
    "cloudberry-mcp-server": {
    "command": "uvx",
    "args": [
        "--with",
        "PATH/TO/cbmcp-0.1.0-py3-none-any.whl",
        "python",
        "-m",
        "cbmcp.server",
        "--mode",
        "stdio"
    ],
    "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "dvdrental",
        "DB_USER": "yangshengwen",
        "DB_PASSWORD": ""
    }
    }
}
}

HTTP transport mode

{
"mcpServers": {
    "cloudberry-mcp-server": {
    "type": "streamable-http",
    "url": "https://localhost:8000/mcp/",
    "headers": {
        "Authorization": ""
    }
    }
}
}

Cursor

Add the configuration to your .cursor/mcp.json file:

Stdio transport mode (Recommended for local development)

{
  "mcpServers": {
    "cloudberry-mcp": {
      "command": "uvx",
      "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "dvdrental",
        "DB_USER": "postgres",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}

HTTP transport mode

If your MCP service is running independently on a server (as shown in the “Quick start” section), you can connect using HTTP mode.
```
{
  "mcpServers": {
    "mpp-remote": {
      "type": "streamable-http",
      "url": "http://<your-server-ip>:8000/mcp/"
    }
  }
}
```
Replace <your-server-ip> with the IP address of the server where the MCP service is running.

Windsurf

Configure the settings in the Windsurf IDE:

{
  "mcp": {
    "servers": {
      "cloudberry-mcp": {
        "type": "stdio",
        "command": "uvx",
        "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"],
        "env": {
          "DB_HOST": "localhost",
          "DB_PORT": "5432",
          "DB_NAME": "dvdrental",
          "DB_USER": "postgres",
          "DB_PASSWORD": "your_password"
        }
      }
    }
  }
}

VS Code (with the Cline extension)

Add the following to the Cline extension settings in VS Code:

{
  "cline.mcpServers": {
    "cloudberry-mcp": {
      "command": "uvx",
      "args": ["--with", "cbmcp", "python", "-m", "cbmcp.server", "--mode", "stdio"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_NAME": "dvdrental",
        "DB_USER": "postgres",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}

Important notes and limitations

Secure configuration: In a production environment, never use trust authentication. Always configure strong passwords for database users and use secure authentication methods like scram-sha-256 or md5 in pg_hba.conf.
Write operation protection: The execute_query tool in the MCP service runs in read-only mode by default. To perform write operations, you must explicitly set the readonly parameter to false.
System table protection: For database stability and security, the service blocks direct access to system catalogs (for example, pg_catalog) by default.

Reference guide

Configuration methods

The MCP service is configured through environment variables. You can define them in a .env file or provide them directly at startup.

Environment variable	Description	Default value
`DB_HOST`	Database host address	`localhost`
`DB_PORT`	Database port	`5432`
`DB_NAME`	Database name	`postgres`
`DB_USER`	Database username	-
`DB_PASSWORD`	Database password	-
`MCP_HOST`	Service host address in HTTP mode	`localhost`
`MCP_PORT`	Service port in HTTP mode	`8000`
`MCP_DEBUG`	Enable debug logging	`false`

API reference

The MCP service provides a rich set of resources, tools, and hints to LLM clients.

Resources

postgres://schemas: Lists all database schemas.
postgres://database/info: Gets general information about the database.
postgres://database/summary: Gets a detailed summary of the database.

Tools

Query tools
- execute_query(query, params, readonly): Executes a SQL query.
- explain_query(query, params): Gets the execution plan for a query.
- get_table_stats(schema, table): Gets statistics for a table.
- list_large_tables(limit): Lists the largest tables.
User and permission management
- list_users(): Lists all database users.
- list_user_permissions(username): Lists permissions for a specified user.
- list_table_privileges(schema, table): Lists privileges for a specified table.
Schema and structure
- list_constraints(schema, table): Lists constraints for a table.
- list_foreign_keys(schema, table): Lists foreign keys for a table.
- list_referenced_tables(schema, table): Lists other tables that reference this table.
- get_table_ddl(schema, table): Gets the DDL (CREATE TABLE) statement for a table.
Performance and monitoring
- get_slow_queries(limit): Lists recent slow queries.
- get_index_usage(): Analyzes index usage statistics.
- get_table_bloat_info(): Analyzes table bloat information.
- get_database_activity(): Shows current database activity.
- get_vacuum_info(): Gets VACUUM and ANALYZE statistics.
Database objects
- list_functions(schema): Lists functions in a specified schema.
- get_function_definition(schema, function): Gets the definition of a function.
- list_triggers(schema, table): Lists triggers for a table.
- list_materialized_views(schema): Lists materialized views in a specified schema.
- list_active_connections(): Lists current active database connections.

Hints

analyze_query_performance: Assists with query performance analysis.
suggest_indexes: Recommends indexes based on queries and table structure.
database_health_check: Performs a health check assessment of the database.

Troubleshooting

Common issues

Connection refused: Checks whether the SynxDB database is running and whether the network connection is stable.
Authentication failed: Verifies that the database username and password in your .env file or environment variables are correct.
Module not found: Ensures you are in the correct Python virtual environment and that the MCP Server package is installed successfully.
Permission denied: Checks whether the user running the service has enough read and write permissions for the project files.

Debug mode

If you encounter complex issues, you can enable debug mode to get more detailed logs.

export MCP_DEBUG=true
# Then start the service
python -m cbmcp.server