Medical Sql MCP

🚀 Medical SQL MCP Server

A server based on the Model Context Protocol (MCP) designed to connect large language models (LLMs) with medical/hospital management databases. It enables AI assistants to explore database schemas, execute SQL queries, and retrieve real-time data through structured tools.

✨ Features

• Virtual File System Navigation (schema_ls): Transforms the flat database table structure into a semantic virtual directory tree (e.g., /Patient Services/Registration Table), helping LLMs understand the business domain more intuitively.
• Intelligent Schema Retrieval (get_table_schema): Allows on-demand retrieval of detailed field definitions for specific tables, reducing context usage.
• Secure and Controllable SQL Execution (query_database):
  • Supports executing SQL queries to retrieve data.
  • Safe Mode: Controls whether non-SELECT operations (e.g., INSERT, UPDATE, DELETE) are allowed via the environment variable ALLOW_NON_SELECT_QUERIES. Read-only protection is enabled by default.
• Time Awareness (get_current_time): Provides the precise current server time, ensuring accuracy when handling relative time queries such as "today" or "in the last three days".

🚀 Quick Start

Prerequisites

• Node.js (v16 or higher)
• MySQL database instance
• OpenAI API Key (for generating the semantic directory structure on first startup)

📦 Installation

1. Clone the project and install dependencies:

   npm install
   

Configuration

Create a .env file in the project root directory and configure the following environment variables:

# Database connection information
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_DATABASE=hospital_management

# MCP server port
PORT=3000

# OpenAI configuration (for building the virtual directory tree)
OPENAI_API_KEY=sk-your_key_here
OPENAI_BASE_URL=https://api.openai.com/v1

# Security configuration
# false: Only allow SELECT queries (recommended)
# true: Allow all SQL operations
ALLOW_NON_SELECT_QUERIES=false

Running the Server

Development Mode:

npm run dev

Build and Run:

npm run build
npm start

💻 Usage Examples

MCP Tools List

📚 Documentation

Project Structure

• src/mcp-server.ts: Entry point for the MCP server, defining the processing logic for tools and resources.
• src/mapping.ts: Responsible for converting database metadata into a virtual file system tree structure.
• src/db.ts: Encapsulation of the MySQL database connection pool.
• src/llm-logger.ts: Logging of LLM calls.
• schema-cache.json: Caches the generated virtual directory structure to avoid repeated LLM calls.

📄 Notes

⚠️ Important Note

On the first run, the system will read the database table structure and call the LLM to generate semantic paths, which may take a few seconds. The results will be cached in schema-cache.json.

💡 Usage Tip

It is recommended to always keep ALLOW_NON_SELECT_QUERIES=false to ensure data security, unless you explicitly need the AI to modify data.