Content
<div align="center">
<img src="https://raw.githubusercontent.com/XGenerationLab/XiYan-SQL/main/xiyanGBI.png" height="80" alt="XiYan Logo">
<h1>XiYan MCP Server</h1>
<p>
<b>Model Context Protocol (MCP) server based on Ali XiYanSQL framework</b>
</p>
<p>
Supports querying databases such as GreptimeDB, CockroachDB, MySQL, and PostgreSQL through natural language
</p>
<p>
<a href="https://opensource.org/licenses/Apache-2.0">
<img src="https://img.shields.io/badge/License-Apache%202.0-blue.svg" alt="License: Apache 2.0">
</a>
<a href="https://www.python.org/">
<img src="https://img.shields.io/badge/Python-3.13+-blue.svg" alt="Python 3.13+">
</a>
<a href="https://github.com/4iKZ/xiyan_mcp_server">
<img src="https://img.shields.io/github/stars/4iKZ/xiyan_mcp_server?style=social" alt="GitHub stars">
</a>
<a href="https://github.com/4iKZ/xiyan_mcp_server/releases">
<img src="https://img.shields.io/github/v/release/4iKZ/xiyan_mcp_server" alt="Release">
</a>
</p>
</div>
---
## Table of Contents
- [Project Overview](#project-overview)
- [Core Features](#core-features)
- [Installation](#installation)
- [Configuration](#configuration)
- [Start Service](#start-service)
- [API Usage](#api-usage)
- [Docker Deployment](#docker-deployment)
- [Available Tools](#available-tools)
- [Project Architecture](#project-architecture)
- [Main Differences from the Original Project](#main-differences-from-the-original-project)
- [Schema Knowledge Base Management](#schema-knowledge-base-management)
- [Testing](#testing)
- [FAQ](#faq)
- [Changelog](#changelog)
- [Open Source License](#open-source-license)
- [Citation](#citation)
---
## Project Overview
This project is based on [XGenerationLab/xiyan_mcp_server](https://github.com/XGenerationLab/xiyan_mcp_server) with significant modifications, enhancing the following features:
- Supports GreptimeDB time-series database
- Supports CockroachDB distributed database
- Schema semantic filtering and lazy loading optimization
- Flexible Embedding model configuration (Cloud API / Local vLLM)
- Local vLLM model support
- Multi-format output (Markdown, JSON, CSV)
- SQL automatic repair mechanism (up to 5 retries)
## Core Features
### Database Support
- **GreptimeDB** (Time-series database)
- **CockroachDB** (Distributed relational database)
- **MySQL**
- **PostgreSQL**
- **SQLite**
### Model Support
- **General LLMs** (GPT, Qwen-Max, etc.)
- **XiYanSQL-QwenCoder** series models
- **Local vLLM** deployed models
### Performance Optimization
- Redis semantic Schema retrieval
- Lazy loading of column information
- Global database connection pool (supports high concurrency queries)
- Base connection pool size: 10
- Maximum extra connections: 20
- Supports a maximum of 30 concurrent database connections in total
- Automatic connection recycling and health check
- SQL automatic error correction
- Multi-thread safe design (supports SQLite concurrent access)
## Installation
### System Requirements
- Python 3.13+
- Redis (optional, for Schema filtering)
### Method 1: Install from Source
```bash
# Clone the repository
git clone https://github.com/4iKZ/xiyan_mcp_server.git
cd xiyan_mcp_server
# Install dependencies
pip install -e .
```
### Method 2: Install from Release
```bash
# Download the source code of a specific version
wget https://github.com/4iKZ/xiyan_mcp_server/archive/refs/tags/v0.1.5.tar.gz
tar -xzf v0.1.5.tar.gz
cd xiyan_mcp_server-0.1.5
# Install dependencies
pip install -e .
```
## Configuration
Edit `src/xiyan_mcp_server/config.yml`:
### Basic Configuration
```yaml
model:
name: "/share/modelscope/XiYanSQL-QwenCoder-14B-2504"
key: "not-needed"
url: "http://10.0.0.8:10000/v1/"
database:
system: "cockroach" # Optional: greptimedb, mysql, postgresql, sqlite
dialect: "greptimedb" # Database dialect
host: "10.0.0.8"
port: 4003
user: "root"
password: ""
database: "public"
schema_filter:
enabled: true
knowledge_dir: "json"
top_k: 5
score_threshold: 0.4
```
**Important**: The `json/` directory must contain the database Schema documentation in JSON format, otherwise the semantic retrieval function will not work. These documents need to be written manually or generated by a script.
### Redis Configuration
host: "localhost"
port: 6379
password: ""
index_name: "xiyan_schema"
```
### Concurrency Configuration (Optional)
Database connection pool configuration can be modified in `src/xiyan_mcp_server/utils/db_util.py`:
```python
POOL_CONFIG = {
"pool_size": 10, # Base number of connections
"max_overflow": 20, # Maximum number of extra connections
"pool_timeout": 30, # Connection timeout (seconds)
"pool_recycle": 3600, # Connection recycle time (seconds)
"pool_pre_ping": True, # Connection health check
}
```
**Description**:
- By default, it supports up to 30 concurrent database connections
- Suitable for high concurrency scenarios
- Automatically recycle idle connections to avoid connection leaks
### Embedding Configuration (Important)
**Note: After switching the embedding model, you must regenerate the Redis index:**
```bash
python scripts/index_knowledge.py --config src/xiyan_mcp_server/config.yml --rebuild
```
#### Option 1: Cloud ModelScope API (Recommended)
Full-precision model, best effect:
```yaml
embedding:
model: "Qwen/Qwen3-Embedding-8B"
use_api: true
api_key: "your-modelscope-api-key"
vector_dim: 4096
```
#### Option 2: Local vLLM API
You need to deploy the embedding model yourself:
```yaml
embedding:
model: "Qwen/Qwen3-Embedding-8B"
use_api: true
use_vllm_format: true
api_url: "http://localhost:10001/v1/"
api_key: "not-needed"
vector_dim: 4096
```
**Precautions:**
- Using 4bit quantization may reduce vector discrimination, it is recommended to use FP16/BF16 quantization
- The cloud API will automatically add the `encoding_format="float"` parameter to obtain full-precision vectors
## Start Service
### Method 1: Direct Start
```bash
# stdio mode (default)
python -m xiyan_mcp_server
# HTTP mode
python -m xiyan_mcp_server streamable-http --host 0.0.0.0 --port 8000
# SSE mode
python -m xiyan_mcp_server sse --host 0.0.0.0 --port 8000
```
### Method 2: Systemd Service
```bash
systemctl start xiyan-mcp-server
systemctl status xiyan-mcp-server
```
### Method 3: View Logs
```bash
tail -f /tmp/xiyan_server.log
```
## API Usage
### Initialize Session
```python
import requests
base_url = "http://localhost:8000/mcp"
headers = {
"Content-Type": "application/json",
"Accept": "application/json, text/event-stream"
}
# Initialize
init_request = {
"jsonrpc": "2.0",
"method": "initialize",
"params": {
"protocolVersion": "2024-11-05",
"capabilities": {},
"clientInfo": {"name": "test-client", "version": "1.0"}
},
"id": 1
}
resp = requests.post(base_url, headers=headers, json=init_request, timeout=60)
session_id = resp.headers.get("Mcp-Session-Id")
```
### Query Data
```python
headers["Mcp-Session-Id"] = session_id
call_request = {
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"name": "get_data",
"arguments": {
"query": "Query the average memory usage of the database cluster in the last 5 hours",
"format": "markdown" # markdown, json, csv
}
},
"id": 2
}
resp = requests.post(base_url, headers=headers, json=call_request, timeout=300)
```
## Docker Deployment
### Method 1: Use Docker Command
```bash
# Build image
docker build -t xiyan-mcp-server .
# Run container (stdio mode)
docker run -v $(pwd)/src/xiyan_mcp_server/config.yml:/app/src/xiyan_mcp_server/config.yml \
xiyan-mcp-server
# Run container (HTTP mode)
docker run -p 8000:8000 \
-v $(pwd)/src/xiyan_mcp_server/config.yml:/app/src/xiyan_mcp_server/config.yml \
xiyan-mcp-server \
python -m xiyan_mcp_server streamable-http --host 0.0.0.0
```
### Method 2: Use Docker Compose (Recommended)
```bash
# Start service
docker-compose up -d
# View logs
docker-compose logs -f xiyan-mcp-server
# Stop service
docker-compose down
```
**Hint**: Edit `docker-compose.yml` to:
- Switch transmission mode (stdio/HTTP/SSE)
- Add Redis and GreptimeDB dependent services
- Configure environment variables
## Available Tools
### get_data
Query the database through natural language and return the results.
Parameters:
- `query` (required): Natural language query question
- `format` (optional): Output format
- `markdown`: Table format (default)
- `json`: JSON format
- `csv`: CSV format
## Project Architecture
```
src/xiyan_mcp_server/
├── server.py # MCP service entry
├── database_env.py # Database environment encapsulation
├── config.yml # Configuration file
├── config.example.yml # Configuration file template
└── utils/
├── db_util.py # Database connection pool
├── llm_util.py # LLM API call
├── schema_retriever.py # Schema retrieval
├── embedding_service.py # Embedding service (supports cloud/local)
├── db_source.py # Database metadata
└── greptimedb_source.py # GreptimeDB support
scripts/
└── index_knowledge.py # Schema knowledge base indexing tool
json/ # Schema knowledge base directory
```
## Main Differences from the Original Project
1. **GreptimeDB Support**: Added GreptimeDB dialect and dedicated data source
2. **CockroachDB Support**: Full support for CockroachDB distributed database
3. **Schema Optimization**: Redis-based semantic retrieval + lazy loading
4. **Local Model**: Full support for local vLLM deployment
5. **Output Format**: Supports Markdown/JSON/CSV formats
6. **Error Handling**: Enhanced SQL repair logic (5 retries)
7. **Configuration Separation**: Embedding and main model configuration are separated
8. **Embedding Flexibility**: Supports both cloud API and local vLLM deployment methods
## Schema Knowledge Base Management
**Important Note**: This project relies on the database Schema explanation documents in the `json/` directory. These documents can be:
1. **Manually Created**: Manually write the description documents of tables and columns according to the database structure
2. **Automatically Generated by Script**: Automatically extract Schema information using database scripts
If the `json/` directory is empty or lacks relevant documents, the Schema semantic retrieval function will not work properly. Please make sure to prepare these documents before use.
### First Indexing
Index the database Schema information in the `json/` directory to Redis:
```bash
python scripts/index_knowledge.py --config src/xiyan_mcp_server/config.yml
```
### Rebuild Index
**Note: You need to rebuild the index in the following situations:**
- Switching Embedding models (Cloud API ↔ Local vLLM)
- Replacing Embedding model version
- Modifying database Schema structure
- Adjusting vector dimension configuration
```bash
python scripts/index_knowledge.py --config src/xiyan_mcp_server/config.yml --rebuild
```
### Check Schema
```bash
# Check Schema knowledge base
python check_schema.py
# Check table structure
python check_tables.py
```
## Testing
```bash
# General test
python test.py
# Natural language query test
python test_natural_language_queries.py
# CockroachDB test
python test_cockroachdb.py
```
## FAQ
### 1. Python Version Requirements
This project requires Python 3.13 or higher.
### 2. Redis Connection Failed
Make sure the Redis service is running:
```bash
# Check Redis status
systemctl status redis
# Start Redis
systemctl start redis
```
### 3. Embedding Model Switching
After switching the embedding model, you must regenerate the Redis index, otherwise it will affect the retrieval effect.
### 4. Docker Container Cannot Access Database
Make sure the container network can access the database service, you can use `docker network` or `--network host` mode.
### 5. Concurrent Connection Limit
The default database connection pool supports up to 30 concurrent connections. If you need higher concurrency, please modify `POOL_CONFIG` in `src/xiyan_mcp_server/utils/db_util.py`:
```python
"pool_size": 20, # Increase the base number of connections
"max_overflow": 30, # Increase the maximum number of extra connections
```
**Note**: Increasing the number of connections requires ensuring that the database server can handle the corresponding number of concurrent connections.
### 6. Connection Pool Exhaustion
If a connection pool exhaustion error occurs, possible reasons:
- Too many concurrent requests, exceeding the maximum number of connections
- The connection is not released correctly (code bug)
- Database query time is too long
**Solution**:
- Increase the connection pool size
- Optimize slow queries
- Check the code for connection leaks
### 7. json Directory is Empty or Lacks Documents
If the `json/` directory is empty or lacks database Schema documents, the Schema semantic retrieval function will not work properly.
**Solution**:
1. Manually create database Schema documents in JSON format
2. Use database scripts to automatically generate Schema documents
3. Make sure the document contains table names, column names, data types, and business descriptions
## Changelog
### v0.1.5 (2025-02-05) - Beta
**New Features**
- Support for GreptimeDB time-series database
- Support for CockroachDB distributed database
- Schema semantic filtering and lazy loading optimization
- Data desensitization processing
**Optimizations and Improvements**
- Fixed Async function blocking issue
- Improved .gitignore configuration
- Updated Python version requirement to 3.13
- Optimized dependency version management
**Docker Support**
- Rewrote Dockerfile to support Python 3.13
- Added .dockerignore file
- Added docker-compose.yml configuration
## Open Source License
This project is open source under the Apache 2.0 license.
## Acknowledgements and Original Authors
This project is based on [XGenerationLab/xiyan_mcp_server](https://github.com/XGenerationLab/xiyan_mcp_server) for modification.
Thanks to all the contributors of the original project:
- XGenerationLab
- ahmedmustahid
- YifuLiu-L
- eltociear
- lwsinclair
- Matvey-Kuk
- willyomg
- ZhuangbilityY
The original project's technical support comes from the [XiYan-SQL](https://github.com/XGenerationLab/XiYan-SQL) framework.
## Citation
If you use this project in your research, you are welcome to cite it:
```bibtex
@article{XiYanSQL,
title={XiYan-SQL: A Novel Multi-Generator Framework For Text-to-SQL},
author={Yifu Liu and Yin Zhu and Yingqi Gao and Zhiling Luo and Xiaoxia Li and Xiaorong Shi and Yuntao Hong and Jinyang Gao and Yu Li and Bolin Ding and Jingren Zhou},
year={2025},
eprint={2507.04701},
archivePrefix={arXiv},
primaryClass={cs.CL},
url={https://arxiv.org/abs/2507.04701},
}
```
