Text To Sql MCP Server

🚀 Text-to-SQL MCP Server

This is a secure database query service based on the FastMCP framework, designed for database query and analysis.

🚀 Quick Start

This project is an MCP (Model Context Protocol) server dedicated to providing secure database query services. Built on the FastMCP framework, it supports MySQL database connections and offers comprehensive permission management and security control mechanisms.

✨ Features

• Database Connection Management: Secure MySQL database connections and queries.
• Permission Authentication: Bearer Token authentication based on RSA key pairs.
• Secure Query: Security checks to prevent SQL injection and dangerous operations.
• Table Structure Query: Retrieve the database table list and table structure information.
• SQL Execution: Secure SQL query execution with result limitation support.
• Health Check: Monitor the service status.
• Permission Management: Fine-grained permission control.

🛠️ Technical Stack

• Python 3.x: The primary programming language.
• FastMCP: The MCP server framework.
• MySQL: The database system.
• uvicorn: The ASGI server.
• python-dotenv: Environment variable management.

📦 Installation

pip install -r requirements.txt

Dependency Package Explanation

python >=3.10

• fastmcp==2.10.6: The MCP server framework.
• python-dotenv==1.1.0: Environment variable loading.
• mysql-connector-python==8.2.0: The MySQL database connector.
• uvicorn==0.24.0: The ASGI server.

⚙️ Configuration

1. Environment Variable Configuration

Copy the .env.example file to .env and configure the database connection information:

cp .env.example .env

Edit the .env file:

# Database configuration
DB_HOST=localhost
DB_PORT=3306
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

# Optional: Server configuration
MCP_HOST=127.0.0.1
MCP_PORT=8000

2. Database Initialization

Use the provided dataset.sql file to initialize the sample data:

mysql -u your_username -p your_database < dataset.sql

This file contains sample data for a contracts table with contract information.

🚀 Start the Service

python mcp_server.py

After the service starts, it will display:

Authorization=Bearer ...
🚀 Starting the MCP data query server...
📍 Address: http://127.0.0.1:8000
📋 Available tools:
   - health_check: Check the service and database connection status.
   - get_user_permissions: Get the current user's permission information.
   - get_database_tables: Get the list of all tables in the database.
   - get_table_structure: Get the structure information, row count, and sample data of a specified table.
   - execute_sql_query: Execute a SQL query securely.
   - generate_sql_from_question: Generate SQL from natural language.
   - analyze_query_result: Analyze the query results.

MCP Client Configuration

🔧 API Tools

1. Health Check

• Tool Name: health_check
• Description: Check the service and database connection status.
• Permissions: No special permissions required.

2. Get User Permissions

• Tool Name: get_user_permissions
• Description: Get the current user's permission information.
• Permissions: A valid access token is required.

3. Get Database Table List

• Tool Name: get_database_tables
• Description: Get the list of all tables in the database.
• Permissions: The data:read_tables permission is required.

4. Get Table Structure

• Tool Name: get_table_structure
• Parameters:
  • table_name (string): The table name.
• Description: Get the structure information, row count, and sample data of a specified table.
• Permissions: The data:read_tables permission is required.

5. Execute SQL Query

• Tool Name: execute_sql_query
• Parameters:
  • sql_query (string): The SQL query statement.
  • limit (int, optional): The maximum number of rows to return, default is 100.
• Description: Execute a SQL query securely.
• Permissions: The data:read_table_data permission is required.
• Security Restrictions: Modification operations such as DROP, DELETE, UPDATE, and INSERT are prohibited.

🔐 Security Features

Permission System

• JWT Token authentication based on RSA key pairs.
• Fine-grained permission control:
  • data:read_tables: Permission to read table structures.
  • data:read_table_data: Permission to read table data.

Security Checks

• SQL Injection Protection: Prohibit dangerous SQL operations.
• Query Limitation: Automatically add LIMIT restrictions to prevent large-scale data queries.
• Sensitive Data Protection: Conduct additional permission checks for queries containing sensitive keywords.

📁 Project Structure

text-to-sql-mcp/
├── mcp_server.py          # The main server file
├── database.py            # The database management module
├── auth_token.py          # The authentication token generation module
├── requirements.txt       # Project dependencies
├── .env.example          # Example environment variables
├── dataset.sql           # Example database structure and data
└── README.md             # Project documentation

🔍 Core Module Explanation

mcp_server.py, the main server file, includes:

• Initialization of the MCP server.
• Definition of all API tools.
• Permission verification logic.
• Security check mechanisms.

database.py, the database management module, provides:

• MySQL database connection management.
• SQL query execution.
• Table structure information retrieval.
• Data type conversion.

auth_token.py, the authentication module, is responsible for:

• RSA key pair generation.
• JWT access token creation.
• Authentication provider configuration.

🚨 Important Note

1. Database Security: Ensure that database user permissions are minimized, and only grant necessary query permissions.
2. Network Security: It is recommended to use HTTPS and firewalls in the production environment.
3. Token Management: Regularly update access tokens to avoid using the same token for an extended period.
4. Query Monitoring: Monitor query performance to prevent complex queries from affecting database performance.

🤝 Contribution

Welcome to submit Issues and Pull Requests to improve this project.

My Works

The n8n workflow automation template station, which collects a large number of ready-to-use automation processes: https://n8ntemplates.dev