The KWDB MCP Server is a server implementation based on the MCP (Model Context Protocol) protocol, which provides a set of tools and resources for interacting with the KWDB database and providing business intelligence functionality through the MCP protocol. The KWDB MCP Server supports reading, writing, querying, modifying data, and performing DDL operations.
The core process of the KWDB MCP Server consists of the following components:
- Parse MCP protocol: deal with MCP StdIO or HTTP SSE requests.
- Schedule MCP Tools: distribute API requests based on the types of MCP Tools.
- Prepare queries: automatically add the
LIMIT 20
clause for SQL queries without aLIMIT
clause. - Format query results: adopt a consistent JSON format for all API responses.
- Read Operations: execute
SELECT
,SHOW
,EXPLAIN
, and other read-only queries. - Write Operations: execute
INSERT
,UPDATE
,DELETE
, andCREATE
,DROP
,ALTER
DDL operations. - Database Information: get information about the database, including tables and their schemas.
- Syntax Guide: access a comprehensive syntax guide for KWDB through Prompts.
- Standard API Response: provide a consistent JSON structure for all API responses.
{ "status": "success", // or "error" "type": "query_result", // response type "data": { ... }, // response data "error": null // errors, if successful, it is set to null }
- Automatic LIMIT: prevent large result sets by automatically adding the
LIMIT 20
clause toSELECT
queries without aLIMIT
clause.
The KWDB MCP Server provides the following security measures:
- Provide separate tools for read and write operations.
- Valid queries to ensure that they match the expected operation type.
- Print clear error messages for unauthorized operations.
MCP Resources allow the KWDB MCP Server to expose data and content that can be read by MCP clients and used as context for LLM interactions. The KWDB MCP Server provides the following MCP Resources:
Resources | URI Format | Description | Example |
---|---|---|---|
Product information | kwdb://product_info |
Product information, including the version and supported features | kwdb://product_info/ |
Database metadata | kwdb://db_info/{database_name} |
Information about a specific database, including the engine type, comments, and tables | kwdb://db_info/db_shig |
Table schema | kwdb://table/{table_name} |
Schema of a specific table, including columns and example queries | kwdb://table/user_profile |
The MCP Tools enable the KWDB MCP Server to expose executable functionality to MCP clients. Through MCP Tools, LLMs can interact with external systems. The KWDB MCP Server provides the following MCP Tools.
The KWDB MCP Server executes the SELECT
, SHOW
, EXPLAIN
statements, and other read-only queries to read data from the database. The read_query
function returns the query results in a format of array for your SQL statement. In addition, the KWDB MCP Server will automatically add the LIMIT 20
clause to SELECT
queries without a LIMIT
clause to prevent large result sets.
Examples:
-- Query table data.
SELECT * FROM users LIMIT 10;
-- List all created tables.
SHOW TABLES;
-- Execute a SQL query and generate details about the SQL query.
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
The KWDB MCP Server executes data modification queries, including DML and DDL operations.
Examples:
-- Insert data into the table.
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
-- Update data in the table.
UPDATE users SET email = '[email protected]' WHERE id = 1;
-- Remove data from the table.
DELETE FROM users WHERE id = 1;
-- Create a table.
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price DECIMAL);
-- Add a column to a table.
ALTER TABLE products ADD COLUMN description TEXT;
-- Remove a table.
DROP TABLE products;
MCP Prompts enable the KWDB MCP Server to define reusable prompt templates and workflows that MCP clients can easily surface to users and LLMs. They provide a powerful way to standardize and share common LLM interactions. The KWDB MCP Server provides the following MCP Prompts:
Type | Prompt Name | Description |
---|---|---|
Database description | db_description |
A comprehensive description of KWDB database, including core functions, supported features, and use cases. |
Syntax guide | syntax_guide |
A comprehensive syntax guide for KWDB, including examples of common queries and best practices. |
Cluster management | cluster_management |
A comprehensive guide for managing KWDB clusters, including node management, load balancing, and monitoring. |
Data migration | data_migration |
A guide for migrating data to and from KWDB, including import/export methods and best practices. |
Installation | installation |
A step-by-step guide for installing and deploying KWDB in various environments. |
Performance tunning | performance_tuning |
A guide for optimizing KWDB performance, including query optimization, indexing strategies, and system-level tuning. |
Troubleshooting | troubleshooting |
A guide for diagnosing and resolving common KWDB issues and errors. |
Backup and restore | backup_restore |
A comprehensive guide for backing up and restoring KWDB databases, including strategies, tools, and best practices. |
DBA templates | dba_template |
Templates and guidelines for MCP Prompts writing. |
The MCP Prompts are Markdown files stored in the pkg/prompts/docs/
directory. These files are embedded into the binary when compiling the KWDB MCP Server using Go's embed
package. Currently, the KWDB MCP Server provides the following Prompts files:
pkg/prompts/docs/ReadExamples.md
: contain read query examples (using theSELECT
statement).pkg/prompts/docs/WriteExamples.md
: contain write query examples (using theINSERT
,UPDATE
,DELETE
,CREATE
,ALTER
statements).pkg/prompts/docs/DBDescription.md
: contain the database description.pkg/prompts/docs/SyntaxGuide.md
: contain the SQL syntax guide.pkg/prompts/docs/ClusterManagementGuide.md
: contain the cluster management guide.pkg/prompts/docs/DataMigrationGuide.md
: contain the data migration guide.pkg/prompts/docs/InstallationGuide.md
: contain the installation guide.pkg/prompts/docs/PerformanceTuningGuide.md
: contain the performance tuning guide.pkg/prompts/docs/TroubleShootingGuide.md
: contain the troubleshooting guide.pkg/prompts/docs/BackupRestoreGuide.md
: contain the backup and restore guide.pkg/prompts/docs/DBATemplate.md
: contain the database administration template.
To add MCP Prompts, follow these steps:
- Create a Markdown file in the
pkg/prompts/docs/
directory, such asnew_usecase.md
. - Add the variable and load codes in the
pkg/prompts/prompts.go
file. - Create a registration function for the new MCP Prompts.
- Add the registration function call to
registerUseCasePrompts()
in thepkg/prompts/prompts.go
file. - Update the
README
file.
For details about how to add MCP Prompts, see comments in the pkg/prompts/prompts.go
file.
To modify MCP Prompts, follow these steps:
- Edit the specific Markdown file(s) in the
pkg/prompts/docs/
directory. - Run the
make build
command to rebuild the application. The updated MCP Prompts will be embedded in the binary.
- Install Go 1.23 or higher.
- Download and install PostgreSQL Driver
lib/pq
. - Install and start KWDB, configure the authentication method, and create a database. For details, see the KWDB Documentation Website.
- Create a user with appropriate privileges on tables and databases. For details, see Create Users.
-
Clone the repository.
git clone https://gitee.com/kwdb/kwdb-mcp-server cd kwdb-mcp-server
-
Install dependencies.
make deps
-
Build the application.
make build
If you succeed, the application adopts the following structure.
mcp-kwdb-server-go/
├── bin/
│ └── kwdb-mcp-server # Binary executable file
├── cmd/
│ └── kwdb-mcp-server/
│ └── main.go # The main application
├── pkg/
│ ├── db/
│ │ └── db.go # Database operations
│ ├── prompts/
│ │ ├── prompts.go # MCP Prompts
│ │ └── docs/ # MCP Prompts files
│ │ ├── ReadExamples.md # Read query examples
│ │ ├── WriteExamples.md # Write query examples
│ │ ├── DBDescription.md # Database descriptions
│ │ ├── SyntaxGuide.md # SQL Syntax guide
│ │ ├── ClusterManagementGuide.md # Cluster management guide
│ │ ├── DataMigrationGuide.md # Data migration guide
│ │ ├── InstallationGuide.md # Installation guide
│ │ ├── PerformanceTuningGuide.md # Performance tunning
│ │ ├── TroubleShootingGuide.md # Troubleshooting guide
│ │ ├── BackupRestoreGuide.md # Backup and restore guide
│ │ └── DBATemplate.md # DBA templates
│ ├── resources/
│ │ └── resources.go # MCP Resources
│ ├── server/
│ │ └── server.go # KWDB MCP Server configurations
│ ├── tools/
│ │ └── tools.go # MCP Tools
│ └── version/
│ └── version.go # Version information
├── Makefile # Commands for building and running the KWDB MCP Server
└── README.md # README file
The KWDB MCP Server supports the following two transport modes:
-
StdIO (Standard Input/Output) mode: the KWDB MCP Server uses the standard input/output for communication. This is the default mode.
-
SSE (Server-Sent Events) mode: the KWDB MCP Server uses HTTP POST for communication between the server and the client.
-
Run the KWDB MCP Server with a PostgreSQL connection string:
./bin/kwdb-mcp-server "postgresql://<username>:<password>@<hostname>:<port>/<database_name>?sslmode=disable"
-
Run the KWDB MCP Server using the Makefile:
CONNECTION_STRING="postgresql://<username>:<password>@<hostname>:<port>/<database_name>?sslmode=disable" make run
Parameters:
username
: the username used to connect to the KWDB databasepassword
: the password for the usernamehostname
: the IP address of the KWDB databaseport
: the port of the KWDB databasedatabase_name
: the name of the KWDB databasesslmode
: the SSL mode, available options aredisable
,allow
,prefer
,require
,verify-ca
andverify-full
. For details about the SSL mode, see SSL Mode Parameters.
To access KWDB databases deployed on other servers, you can start the KWDB MCP Server in SSE mode.
Note
You need to provide the database connection string as the last parameter.
-
Run the KWDB MCP Server with a PostgreSQL connection string:
./bin/kwdb-mcp-server -t sse -addr ":8080" -base-url "http://localhost:8080" "postgresql://<username>:<password>@<hostname>:<port>/<database_name>?sslmode=disable"
-
Run the KWDB MCP Server using the Makefile:
CONNECTION_STRING="postgresql://<username>:<password>@<hostname>:<port>/<database_name>?sslmode=disable" ADDR=":8080" BASE_URL="http://localhost:8080" make run -sse
Parameters:
-t
or-transport
: the transport type, available options arestdio
orsse
.stdio
: the StdIO modesse
: the SSE mode
-addr
orADDR
: the listening port of the KWDB MCP Server. By default, it is set to:8080
.-base-url
orBASE_URL
: the IP address of the KWDB MCP Server. By default, it is set tohttp://localhost:8080
.username
: the username used to connect to the KWDB databasepassword
: the password for the usernamehostname
: the IP address of the KWDB databaseport
: the port of the KWDB databasedatabase_name
: the name of the KWDB databasesslmode
: the SSL mode, available options aredisable
,allow
,prefer
,require
,verify-ca
andverify-full
. For details about the SSL mode, see SSL Mode Parameters.
For details about how the KWDB MCP Server integerates with LLM Agents, see Integrate with LLM Agents.
For details about how to troubleshoot the KWDB MCP Server, see Troubleshooting.
For documentation about the KWDB MCP Server, see the KWDB Documentation Website.
- Query history: implement query history functionality.
- Remote mode: support connecting to the remote KWDB MCP Server.
- Improved optimization suggestions: enhance query optimization recommendations.
- Metrics resource: aAdd database metrics.
Contributions are welcome! Please feel free to submit issues and pull requests.
This project is licensed under the MIT License.
- mark3labs/mcp-go - MCP Go server framework
- lib/pq - PostgreSQL Go driver