KaiwuDB MCP Service Endpoint

This component furnishes a backend service implementing the Model Context Protocol (MCP) specification to enable sophisticated, LLM-driven interaction with a KaiwuDB data repository. It handles all transactional and metadata operations.

中文文档

System Architecture Overview

The core operational flow of the KaiwuDB MCP Gateway involves several coordinated stages:

MCP Request Parsing: Ingesting and interpreting incoming commands, whether delivered via StdIO stream or HTTP streaming endpoints.
Tool Orchestration: Routing incoming calls to the appropriate specialized internal tool based on the requested action type.
Query Pre-processing: Enforcing safety by automatically augmenting any SQL 'SELECT' statements lacking an explicit 'LIMIT' directive with LIMIT 20 to cap result set size.
Response Standardization: Ensuring all external data interchange adheres to a uniform JSON structure for reliable consumption.

Core Capabilities

Data Retrieval: Execution of read-only commands such as SELECT, SHOW, and query planning utilities like EXPLAIN.
Data Modification & Definition: Handling DML (INSERT, UPDATE, DELETE) alongside DDL commands (CREATE, DROP, ALTER).
Metadata Access: Providing introspection into the database structure, including available tables and their field definitions.
Procedural Guidance: Offering access to standardized KWDB syntax documentation via curated Prompts.
Error Reporting Contract: Maintaining consistency in failure reporting:
- Tool Execution Failures: Errors embedded within tool result objects, marked by "isError": true. json { "content": [{"type": "text", "text": "Query execution failed: [details]"}], "isError": true }
- Resource Access Failures: Direct conveyance of standard JSON-RPC errors for resource URI resolution issues. json { "jsonrpc": "2.0", "id": 1, "error": { "code": -32002, "message": "Handler not found for URI 'kwdb://table/missing_entity'" } }
- Or internal processing faults: json { "jsonrpc": "2.0", "id": 1, "error": { "code": -32603, "message": "Schema acquisition failure for 'data_set_x': connectivity problem" } }
- Successful Outcomes: Tool operations yield result objects; resource requests return content arrays.
Result Throttling: Automatic application of a 20-row limit to unconstrained SELECT operations.

Security Posture

Robust safeguards are implemented through:

Segregation of operational tools based on read versus write intent.
Rigorous validation to confirm executed queries match intended operation profiles.
Clear, informative error feedback for any attempted unauthorized actions.

MCP External Resources

The Gateway exposes specific data sets and informational artifacts accessible via MCP Resource URIs, serving as context material for Language Models.

Resource Type	URI Pattern	Purpose	Sample Access Path
System Information	`kwdb://product_info`	Versioning and supported feature manifest.	`kwdb://product_info/`
Database Metadata	`kwdb://db_info/{database_name}`	Engine specifics, descriptive notes, and table listings for a target database.	`kwdb://db_info/production_kdb`
Table Definition	`kwdb://table/{table_name}`	Column structure details and typical query patterns for a specific table.	`kwdb://table/user_records`

MCP Execution Tools

These tools represent executable functions exposed by the Gateway, allowing LLMs to invoke external system capabilities.

`data-read`

This function is utilized for executing read-only SQL statements (SELECT, SHOW, EXPLAIN). Results are returned as an array structure corresponding to the query output. Crucially, if a SELECT query omits a LIMIT clause, LIMIT 20 is appended internally.

Examples:

-- Retrieve the top 10 user records.
SELECT user_id, name FROM app_users WHERE active = TRUE LIMIT 10;

-- Inventory of current database objects.
SHOW TABLES;

-- Detailed execution plan analysis.
EXPLAIN ANALYZE SELECT * FROM sales_data WHERE region = 'WEST';

`data-modify`

Used for executing statements that alter data or the database structure (DML/DDL).

Examples:

-- Add a new user entry.
INSERT INTO users (username, status) VALUES ('Alice', 'Active');

-- Update a contact detail.
UPDATE users SET contact_email = 'new.alice@corp.com' WHERE id = 42;

-- Purge an obsolete record.
DELETE FROM audit_log WHERE timestamp < '2023-01-01';

-- Establish a new dataset table.
CREATE TABLE logs (entry_id BIGINT PRIMARY KEY, message TEXT, event_ts TIMESTAMP);

-- Refine table structure.
ALTER TABLE logs ADD COLUMN severity VARCHAR(10);

-- Decommission an old table.
DROP TABLE old_temp_cache;

Templated Interactions (MCP Prompts)

MCP Prompts define persistent, reusable instruction templates accessible via the client interface, standardizing common LLM workflows.

Topic Category	Prompt Identifier	Purpose
DB Summary	`db_description`	Full description of the KWDB environment, core capabilities, and typical use cases.
SQL Reference	`syntax_guide`	Authoritative guide to KWDB SQL syntax, including usage patterns and best practices.
Cluster Administration	`cluster_management`	Procedures for managing cluster nodes, handling load distribution, and monitoring status.
Data Movement	`data_migration`	Best practices and methods for importing/exporting data to/from KaiwuDB.
Setup Guide	`installation`	Step-by-step instructions for deployment across varied operational settings.
Performance Tuning	`performance_tuning`	Strategies for maximizing throughput, covering query optimization and indexing tactics.
Error Resolution	`troubleshooting`	Diagnostic procedures for resolving frequent system errors and anomalies.
Data Durability	`backup_restore`	Comprehensive strategy for backup procedures, restoration processes, and best practices.
Administration Guides	`dba_template`	Templates and standardized guidelines for creating administrative MCP Prompts.

Managing Embedded Prompts

These instructional assets are embedded as Markdown files during compilation, sourced from the pkg/prompts/docs/ folder.

Adding New Prompts: Requires file creation in pkg/prompts/docs/, registration updates in pkg/prompts/prompts.go, and updating this README.
Modifying Existing Prompts: Edit the source Markdown file and execute make build to embed the changes.

Development & Compilation

Prerequisites for Building

Go language environment (version 1.23 or newer).
PostgreSQL connectivity library (lib/pq).
A running, accessible KaiwuDB instance with appropriate connection credentials configured.

Build Sequence

Clone the repository.

shell git clone https://gitee.com/kwdb/kwdb-mcp-server cd kwdb-mcp-server
Fetch required libraries.

shell make deps
Compile the executable.

shell make build

Deployment Modes

The Gateway supports three primary communication transports:

StdIO: Default mode, communicating via standard input/output streams.
HTTP (Preferred): Recommended for production environments, utilizing standard HTTP endpoints.
SSE (Legacy): Server-Sent Events via HTTP POST; scheduled for deprecation.

Establishing Connectivity (Connection String Format)

All modes require a PostgreSQL connection string, conforming to the format: postgresql://<user>:<password>@<host>:<port>/<db_name>?sslmode=<mode>

SSL Modes: Valid options include disable, allow, prefer, require, verify-ca, verify-full. Refer to the official documentation for detailed SSL parameter definitions.

HTTP Operation (Recommended)

Execute with port specification (e.g., 9000):

CONNECTION_STRING="..." PORT=9000 make run-http

This initiates an HTTP listener, typically exposing the MCP interface at http://<host>:9000/mcp.

Command Line Flags (HTTP/SSE):

Flag	Alias	Description
`--transport`	`-t`	Communication method: `stdio`, `sse` (legacy), or `http` (recommended).
`--port`	`-p`	Network port to bind the service to (default: 8080).

Integration and Support

LLM Agent Integration: Detailed instructions are located in ./docs/integrate-llm-agent_en.md.
Troubleshooting: Consult ./docs/troubleshooting_en.md for common issue resolution.
Comprehensive Documentation: Visit the KaiwuDB Documentation Portal for extensive reference materials.

Development Roadmap

[ ] Implement persistent query logging and history retrieval.
[x] Support for connecting to Gateway instances running on remote hosts.
[x] Enhanced sophistication in query execution optimization suggestions.
[ ] Expose real-time database performance metrics via a dedicated MCP Resource.

Project Governance

We welcome community contributions via issues and pull requests.

Licensing & Credits

Licensed under the MIT License.

Acknowledgements: - Reference implementation framework: [mark3labs/mcp-go] - Database connector: [lib/pq]

This service is registered and validated via MCP Review Platform.

Note on Related Technologies

In the realm of software tooling, technologies like Tree-sitter are employed for robust source code parsing. Tree-sitter generates Concrete Syntax Trees (CSTs) essential for code comprehension in text editors and analyzers. Its key strength lies in incremental parsing, allowing rapid syntax tree updates during live editing, complemented by an S-expression querying system. Major editors (Atom, Neovim, Emacs) utilize it. It employs a GLR parsing strategy and was pioneered by GitHub.