Overview

XiYan MCP Server is an implementation of a Model Context Protocol (MCP) server that exposes a text-to-SQL tool called XiyanSQL. It accepts natural language queries, converts them into SQL using an LLM-based translator, runs the SQL against a connected database, and returns the query results as structured JSON. This enables developers to add ad-hoc data access and conversational querying to apps, chatbots, and dashboards without writing SQL manually.

The server is designed to act as a bridge between LLM tooling and databases. By following MCP conventions it can be integrated with other MCP-aware clients and model controllers. It’s useful for data exploration, internal analytics, conversational agents that need up-to-date database answers, and embedding a safe, auditable text-to-SQL layer in your stack.

Features

Natural language → SQL translation via XiyanSQL
Executes generated SQL against your database and returns results
MCP-compatible tool endpoints for integration with model orchestration systems
Configurable database connection (SQLAlchemy-style DATABASE_URL)
JSON-based API for tool requests and responses
Simple deployment using Python + ASGI server (uvicorn)

Installation / Configuration

Clone and prepare the project:

git clone https://github.com/XGenerationLab/xiyan_mcp_server.git
cd xiyan_mcp_server
python -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Set required environment variables (example using PostgreSQL):

export DATABASE_URL="postgresql://dbuser:dbpass@dbhost:5432/dbname"
export Xiyan_MODEL_PROVIDER="openai"        # or "local" depending on implementation
export Xiyan_API_KEY="sk-..."               # if using an external model provider
export PORT=8000

Optional configuration file (config.yaml):

server:
  host: "0.0.0.0"
  port: 8000
xiyan:
  model: "gpt-4-xiyan"
  max_tokens: 512
database:
  url: "postgresql://dbuser:dbpass@dbhost:5432/dbname"
security:
  enable_query_whitelist: false

Run the server (example):

uvicorn app:app --host 0.0.0.0 --port ${PORT:-8000}

Adjust commands according to how the repository organizes its ASGI app (app:app, main:app, etc.).

Available Tools / Resources

/health — basic health/status check
/tools/xiyansql — MCP-style text-to-SQL tool endpoint
/openapi.json — optional OpenAPI spec (if provided)
Database connection via DATABASE_URL (SQLAlchemy connection string)
Config via environment variables or config.yaml

Environment variables reference:

Variable	Purpose	Default
DATABASE_URL	Connection string for your DB (required)	none
Xiyan_MODEL_PROVIDER	Model backend (“openai” / “local”)	“openai”
Xiyan_API_KEY	API key for model provider	none
PORT	Server listening port	8000

API Examples

Basic curl example (ask for a report):

curl -X POST "http://localhost:8000/tools/xiyansql" \
  -H "Content-Type: application/json" \
  -d '{
    "input": "List the top 10 customers by revenue in the last 90 days",
    "context": {
      "schema_hint": "customers(id,name), orders(id,customer_id,amount,created_at)"
    }
  }'

Typical JSON response structure:

{
  "sql": "SELECT c.id, c.name, SUM(o.amount) AS revenue FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.created_at >= CURRENT_DATE - INTERVAL '90 days' GROUP BY c.id, c.name ORDER BY revenue DESC LIMIT 10;",
  "rows": [
    {"id": 123, "name": "Acme Corp", "revenue": 12345.67},
    ...
  ],
  "metadata": {
    "columns": ["id", "name", "revenue"],
    "row_count": 10,
    "execution_time_ms": 45
  }
}

Python client example:

import requests

resp = requests.post(
    "http://localhost:8000/tools/xiyansql",
    json={"input": "How many users signed up in the last 30 days?"}
)
print(resp.json())

Use Cases

Conversational analytics: Embed a chat UI that answers business questions in natural language and shows the underlying SQL for auditability.
Customer support tools: Allow support agents to query user activity or transaction logs without needing SQL knowledge.
BI prototyping: Quickly generate ad-hoc queries during exploration and iterate on filters and aggregations.
Product dashboards: Build a backend that converts user-friendly filters into SQL and returns live results for custom widgets.
Data governance: Centralize and audit text-to-SQL translations so generated queries can be inspected and whitelisted or blocked.

Example prompts and expected SQL patterns:

Prompt	SQL intent
“Show daily signups for the past week”	GROUP BY DATE(created_at), COUNT(*)
“Top 5 products by revenue last month”	SUM(amount) grouped by product id/name with ORDER BY and LIMIT 5
“Which users haven’t logged in for 90+ days?”	WHERE last_login <= CURRENT_DATE - INTERVAL ‘90 days’

Notes & Best Practices

Provide schema hints or a sample schema to improve translation accuracy and reduce hallucinated columns.
Restrict writable statements (INSERT/UPDATE/DELETE) by default; prefer read-only mode for safety.
Log generated SQL for auditing and debugging.
Use connection pooling and set query timeouts to protect the database from expensive queries.

For full code, usage examples, and contribution guidelines, see the repository: https://github.com/XGenerationLab/xiyan_mcp_server.

XiYan MCP Server with XiyanSQL Text-to-SQL