Overview

The KWDB MCP Server implements the Model Context Protocol (MCP) to expose KWDB databases to MCP clients, including LLM agents and BI tools. It provides a consistent RPC-style interface and a set of MCP Resources and Tools so external systems can read, write, modify schema (DDL), and fetch runtime metrics from KWDB. The server also formats results and enforces safety defaults (for example, automatically limiting unbounded SELECTs) so downstream consumers get predictable, compact responses.

This server is useful when you want programmatic access to KWDB from language models, visualization tools, or automation workflows without building a custom integration. It exposes metadata (databases, table schemas), executes SQL (both read and write), and returns results in a standard JSON structure compatible with MCP clients.

Features

Read-only query execution: SELECT, SHOW, EXPLAIN and similar statements
Write and DDL support: INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
MCP Resources: expose product info, database metadata, and table schemas via URI
MCP Tools: executable endpoints that allow LLMs to run queries and admin calls
Automatic LIMIT injection: add LIMIT 20 to SELECTs that do not include a LIMIT to avoid large result sets
Standardized API responses and error wrapping for consistent client handling
Query metrics ingestion: fetch historical runtime metrics (time-series) from admin APIs
Separation of read vs write tools and query validation for safer operation

Installation / Configuration

Clone the repository and run the server. Replace placeholders with your environment values.

git clone https://github.com/KWDB/kwdb-mcp-server.git
cd kwdb-mcp-server

Run from source (example using Go if building from source):

# build (if the project is Go-based)
go build -o kwdb-mcp-server ./cmd/server

# run with environment variables
KWDB_DSN="postgres://user:pass@host:5432/kwdb" \
MCP_LISTEN="0.0.0.0:8080" \
./kwdb-mcp-server

Run with Docker (if a Dockerfile is provided):

docker build -t kwdb-mcp-server .
docker run -e KWDB_DSN="postgres://user:pass@host:5432/kwdb" -p 8080:8080 kwdb-mcp-server

Example configuration (YAML-style) — adapt to your config loader:

mcp:
  listen: "http://0.0.0.0:8080"   # HTTP SSE endpoint or STDIO mode
kwdb:
  dsn: "postgres://user:password@db:5432/kwdb"
limits:
  default_select_limit: 20
security:
  enable_read_write_separation: true

Available Resources

MCP Resources are readable URIs that return context for LLMs or clients.

Resource	URI format	Description
Product information	kwdb://product_info	Server and product metadata (version, capabilities)
Database metadata	kwdb://db_info/{database_name}	Per-database metadata: engine, comments, list of tables
Table schema	kwdb://table/{table_name}	Column definitions, types, indexes and example queries

Requesting these URIs yields structured JSON describing schema and metadata that can be used as context for prompts or UI metadata.

Available Tools

The server exposes MCP Tools (callable actions):

read-query
- Executes read-only SQL (SELECT, SHOW, EXPLAIN, etc.)
- Automatically appends LIMIT 20 to SELECTs without explicit LIMIT
- Returns rows as an array of objects
write-query
- Executes DML/DDL (INSERT, UPDATE, DELETE, CREATE, ALTER, DROP)
- Intended for state-changing operations; instrumented separately for security/audit
query-metrics-history
- Fetch historical query/runtime metrics from an admin time-series endpoint
- Accepts millisecond timestamps and normalization parameters
prompts (documentation)
- Return KWDB syntax guides and example prompts to help LLMs craft SQL

Example read invocation (SQL):

-- this will be limited automatically if LIMIT is omitted
SELECT id, name, email FROM users WHERE active = true;

Example write invocation:

INSERT INTO products (name, price) VALUES ('Widget', 9.99);
ALTER TABLE products ADD COLUMN description TEXT;

Response format and error handling

Tools typically return result objects; resources return content arrays. Errors follow consistent patterns:

Tool-level error (wrapped in a result object):

{
  "content": [{"type":"text","text":"Query error: syntax error at position 15"}],
  "isError": true
}

Resource/RPC error (JSON-RPC style):

{
  "jsonrpc": "2.0",
  "id": 1,
  "error": {
    "code": -32002,
    "message": "handler not found for resource URI 'kwdb://table/nonexistent': resource not found"
  }
}

or for internal processing:

{
  "jsonrpc": "2.0",
  "id": 1,
  "error": {
    "code": -32603,
    "message": "failed to get table schema for 'tablename': database connection error"
  }
}

Security and safety

Read/write operations are separated into distinct tools to reduce accidental writes.
Incoming SQL is validated to ensure the requested operation matches the tool type.
Automatic LIMIT prevents extremely large reads by default.
Clear, structured error messages help clients decide whether to retry, escalate, or present information to users.

Use Cases

LLM-assisted SQL generation: provide schema (kwdb://table/*) as context and run read-query to verify generated SQL and return results.
BI integration: expose table schemas and run ad-hoc SELECTs via MCP to power dashboards or exploratory analysis.
Automated schema migration tooling: use write-query to apply DDL changes and query-metrics-history to validate performance impacts.
Monitoring and alerting: periodically query historical metrics to detect query-runtime regressions or spikes.

For the latest code, issues, and deployment instructions, see the project repository on GitHub: https://github.com/KWDB/kwdb-mcp-server.

KWDB MCP Server for Querying and DDL