Content
# SQL-MCP
[](https://github.com/polarisxb/sql-mcp/actions/workflows/ci.yml)
[](https://github.com/polarisxb/sql-mcp/actions/workflows/docker.yml)
[](https://www.npmjs.com/package/@polarisxb/sql-mcp)
[](LICENSE)
[](https://deepwiki.com/polarisxb/sql-mcp)
**SQL-MCP** is a server that implements the Model Context Protocol (MCP), serving as a bridge between large language models (LLM) and databases. It allows LLMs to securely and efficiently access database information, including performing **metadata queries**, **data sampling**, and **read-only SQL queries**.
This project currently primarily supports **MySQL** and provides two flexible transport methods: **Stdio** and **HTTP**, allowing it to run as an independent HTTP service or easily integrate into other development toolchains.
For the English version, please refer to [README.en.md](README.en.md).
---
## ✨ Features
- **Metadata Queries**: Detailed queries for database, tables, columns, indexes, foreign keys, and other metadata.
- **Data Sampling**: Securely obtain sample data from tables, supporting pagination and automatic data masking.
- **Read-Only Queries**: Strictly limit the execution of read-only SQL such as `SELECT` and `SHOW` to ensure data security.
- **Quick Retrieval**: Provide `searchTables` and `searchColumns` interfaces for fast table and column lookups.
- **Cache Management**: Support manual refresh of metadata cache to ensure real-time accuracy of information retrieved by large models.
- **Secure and Reliable**: Built-in multi-layer security mechanisms including API Key authentication, CORS control, and IP rate limiting.
### SQL Mentor Tools (New)
- **explainQuery(sql)**: Explain the tables/join/filter/sort involved in the query and highlight potential risks.
- **optimizeQuery(sql)**: Provide heuristic optimization suggestions (index/rewrite/SELECT column trimming, etc.).
- **generateExamples(tableName)**: Generate common example queries and brief explanations based on the table.
- **fixQuery(sql, error)**: Provide read-only equivalent syntax or fix suggestions based on error messages.
- **indexAdvisor(sql)**: Focus on index suggestions (filter/join/sort/redundancy) with evidence JSON.
- **rewriteQuery(sql)**: Draft read-only equivalent rewrites (e.g., Keyset pagination templates, avoiding SELECT*, avoiding leading wildcards).
- **doctor()**: System self-check (connectivity, read-only queries, EXPLAIN availability, duplicate/redundant index statistics).
Example parameters (select the corresponding tool in MCP Inspector and fill in params):
```json
// explainQuery
{ "sql": "SELECT p.id, p.name FROM products p JOIN categories c ON p.category_id=c.id WHERE price>50 ORDER BY p.id LIMIT 5" }
```
```json
// optimizeQuery
{ "sql": "SELECT * FROM orders WHERE order_date >= NOW() - INTERVAL 7 DAY ORDER BY id LIMIT 20" }
```
```json
// indexAdvisor
{ "sql": "SELECT p.id FROM products p JOIN categories c ON p.category_id=c.id WHERE p.price>50 ORDER BY p.id LIMIT 5" }
```
```json
// rewriteQuery
{ "sql": "SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 100" }
```
```json
// fixQuery
{ "sql": "SELECT x FROM products", "error": "Unknown column 'x' in 'field list'" }
```
```json
// doctor (no parameters required)
{}
```
Return description: The above tools return in `text + resource(application/json)` format; the evidence JSON contains structured information such as execution plans/analysis/suggestions/rewrites, facilitating secondary processing or verification.
---
## 🚀 Quick Start
### 1. Installation
You can choose to install globally via `npm` or clone from the source and build.
**Global Installation (Recommended)**:
```bash
npm i -g @polarisxb/sql-mcp
```
**Build from Source**:
```bash
git clone https://github.com/polarisxb/sql-mcp.git
cd sql-mcp
npm ci
npm run build
```
### 2. Start the Service
Choose the appropriate startup method based on your use case.
**Via Stdio (Standard Input/Output)**:
This method is suitable for local integration in **Cursor** or other tools that support imperative MCP.
```bash
sql-mcp --type mysql \
--host 127.0.0.1 --port 3306 \
--user root --password your_password --database your_db \
--transport stdio
```
**Via HTTP**:
Run SQL-MCP as an independent HTTP service for remote applications to call.
```bash
sql-mcp --type mysql \
--host 127.0.0.1 --port 3306 \
--user root --password your_password --database your_db \
--transport http --httpPort 3000
```
The service will provide API endpoints at `http://127.0.0.1:3000/mcp`.
### 3. Demo Quick Experience (MySQL + Sample E-commerce Database)
```bash
# Start (will automatically initialize the sample database mydb on first run)
docker compose up -d
# Health Check (should return 200)
curl -i http://127.0.0.1:3001/health
```
- Note: `mysql:8` exposes port 3306, and `sql-mcp` is exposed as `http://127.0.0.1:3001/mcp`.
- If the port is occupied, you can adjust the port mapping in `docker-compose.yml`.
### 4. Start with DSN (Local CLI)
```bash
# Local build version (supports --dsn)
npm run build
node dist/cli.js --transport stdio --dsn "mysql://root:root@127.0.0.1:3306/mydb"
# Published package (npx, does not support --dsn, uses explicit parameters)
npx -y @polarisxb/sql-mcp --transport stdio \
--type mysql --host 127.0.0.1 --port 3306 \
--user root --password root --database mydb
```
### 5. MCP Inspector Debugging
```bash
npx -y @modelcontextprotocol/inspector
```
- Connect via HTTP: Select “Connect to HTTP server”, fill in `http://127.0.0.1:3001/mcp`
- Launch local process (stdio): Select “Launch a process”, fill in the command and parameters (same as above)
- Common tools: `listTables`, `getTableSchema`, `getSampleData`, `executeQuery`, `searchTables`, `searchColumns`
---
## 🔌 Cursor Integration
In Cursor, you can easily integrate SQL-MCP by configuring the `mcp.json` file.
**Configuration File Path**:
- **Windows**: `%USERPROFILE%\\.cursor\\mcp.json`
- **macOS/Linux**: `~/.cursor/mcp.json`
### Stdio Mode (Recommended)
This is the simplest and most direct integration method, requiring no manual service startup.
**Using npx (no global installation required)**:
```json
{
"mcpServers": {
"sql-mcp-server": {
"command": "npx",
"args": ["-y", "@polarisxb/sql-mcp"],
"env": {
"SQL_MCP_DB_TYPE": "mysql",
"SQL_MCP_DB_HOST": "127.0.0.1",
"SQL_MCP_DB_PORT": "3306",
"SQL_MCP_DB_USER": "root",
"SQL_MCP_DB_PASSWORD": "your_password",
"SQL_MCP_DB_NAME": "your_database",
"SQL_MCP_LOG_LEVEL": "warn"
}
}
}
}
```
**Run from Source (for developers)**:
If you want to use a development version, you can configure it to start from the project directory.
```json
{
"mcpServers": {
"sql-mcp-dev": {
"command": "node",
"args": [
"C:/path/to/your/sql-mcp/dist/cli.js",
"--transport", "stdio"
],
"env": {
"SQL_MCP_DB_HOST": "127.0.0.1"
}
}
}
}
```
### HTTP Mode
If you have SQL-MCP running as an independent HTTP service, you can connect via URL in Cursor.
```json
{
"mcpServers": {
"sql-mcp-http": {
"url": "http://127.0.0.1:3000/mcp"
}
}
}
```
---
## ⚙️ Configuration Options
SQL-MCP supports configuration through **command line arguments**, **environment variables**, and **configuration files**.
**Configuration Priority**: **Command line arguments > Environment variables > Configuration files**.
### Command Line Arguments
Here are some commonly used command line arguments:
| Option | Type | Default | Description |
|---|---|---|---|
| `--type` | string | `mysql` | Database type (currently only supports `mysql`) |
| `--host` | string | `127.0.0.1` | Database host |
| `--port` | number | `3306` | Database port |
| `--user` | string | - | Database username (recommended to use read-only permissions) |
| `--password` | string | - | Database password |
| `--database` | string | - | Default database |
| `--transport`| enum | `stdio` | Transport mode (`stdio` or `http`) |
| `--httpPort` | number | `3000` | HTTP service port |
| `--verbose` | flag | `false` | Output detailed `debug` logs |
| `--log-dest` | enum | `console` | Log output location (`console` or `file`) |
| `--log-file` | string | - | Log file path (when `log-dest` is `file`) |
| `--stdio-safe`| flag | `false`| Stdio safety preset: optimize logs, streamline output |
| `--compact`| flag | `false`| Compact output, reduce Markdown size |
| `--json-only`| flag | `false`| Output only JSON content, no Markdown rendering |
### Environment Variables
All configuration items can be set using environment variables prefixed with `SQL_MCP_`. For example, the environment variable corresponding to `--host` is `SQL_MCP_DB_HOST`.
Copy the `ENV.example` file to `.env` and fill in your database connection information for a quick start.
#### Environment Variable Reference Table
| Environment Variable | Default Value | Description |
|---|---|---|
| **Database Connection** | | |
| `SQL_MCP_DB_TYPE` | `mysql` | Database type |
| `SQL_MCP_DB_HOST` | `127.0.0.1` | Host |
| `SQL_MCP_DB_PORT` | `3306` | Port |
| `SQL_MCP_DB_USER` | - | User |
| `SQL_MCP_DB_PASSWORD` | - | Password |
| `SQL_MCP_DB_NAME` | - | Database name |
| `SQL_MCP_DB_TIMEOUT` | `10000` | Connection timeout (ms) |
| `SQL_MCP_DB_POOL_CONNECTION_LIMIT` | `10` | Maximum number of connections in the connection pool |
| `SQL_MCP_DB_POOL_QUEUE_LIMIT` | `0` | Maximum wait queue limit (`0`=unlimited) |
| **Logging** | | |
| `SQL_MCP_LOG_LEVEL` | `info` | Log level: `debug|info|warn|error` |
| `SQL_MCP_LOG_DESTINATION` | `console` | Log destination: `console|file` |
| `SQL_MCP_LOG_FILE_PATH` | - | File path (when destination is `file`) |
| `SQL_MCP_LOG_SLOW_QUERY_MS` | `1000` | Slow query threshold (ms) |
| `SQL_MCP_LOG_HTTP_REQUESTS` | `true` | Whether to log HTTP requests |
| **Service and Security** | | |
| `SQL_MCP_MCP_TRANSPORT` | `stdio` | MCP transport: `stdio|http` |
| `SQL_MCP_MCP_HTTP_PORT` | `3000` | HTTP port |
| `SQL_MCP_MCP_HTTP_API_KEY` | - | Single API Key |
| `SQL_MCP_MCP_HTTP_API_KEYS` | - | Multiple API Keys, separated by commas |
| `SQL_MCP_MCP_ENABLE_DNS_REBINDING_PROTECTION` | `false` | Enable host verification |
| `SQL_MCP_MCP_CORS_ALLOWED_ORIGINS` | - | Allowed CORS origins, separated by commas |
| `SQL_MCP_SECURITY_QUERY_TIMEOUT_MS` | `10000` | Query timeout (ms) |
| `SQL_MCP_SECURITY_SAMPLE_MAX_ROWS` | `100` | Maximum row limit for sampling |
| `SQL_MCP_SECURITY_QUERY_MAX_ROWS` | `200` | Maximum row limit for a single return of `executeQuery` |
| `SQL_MCP_SECURITY_RATE_LIMIT_ENABLED` | `false` | Enable rate limiting for `/mcp` route |
| `SQL_MCP_SECURITY_RATE_LIMIT_WINDOW_MS` | `60000` | Rate limiting window (ms) |
| `SQL_MCP_SECURITY_RATE_LIMIT_MAX` | `120` | Global maximum request count within the window |
| `SQL_MCP_SECURITY_RATE_LIMIT_PER_IP_MAX` | `60` | Maximum request count per IP within the window |
| **Others** | | |
| `SQL_MCP_CACHE_PREWARM_ON_START` | `true` | Background pre-warm table list on startup |
| `SQL_MCP_MCP_STDIO_SAFE` | `false` | Enable stdio safety preset |
| `SQL_MCP_MCP_STDIO_COMPACT` | `false` | Enable compact output |
| `SQL_MCP_OUTPUT_JSON_ONLY` | `false` | Output only JSON content |
> For more configuration details, please refer to `src/core/config/loader.ts`.
---
## 🏛️ Project Structure
```
.
├── src/
│ ├── cli/ # Command Line Interface (CLI) related logic
│ ├── connectors/ # Database connectors (currently for MySQL)
│ ├── core/ # Core business logic, including configuration loading and logging
│ ├── mcp/ # MCP protocol implementation and handlers
│ ├── middleware/ # Express middleware (authentication, logging, rate limiting, etc.)
│ ├── services/ # Core services (metadata, queries, caching, etc.)
│ ├── types/ # TypeScript type definitions
│ ├── utils/ # Common utility functions
│ ├── cli.ts # CLI entry file
│ └── index.ts # HTTP service entry file
├── Dockerfile # For building Docker images
├── package.json # Project dependencies and scripts
└── tsconfig.json # TypeScript configuration file
```
---
## 🤝 Contribution
We warmly welcome the community to contribute to the project through **Pull Requests** or **Issues**. Before submitting code, please ensure your code passes lint and test checks.
```bash
# Code style check
npm run lint
# Run unit tests
npm run test
```
---
## 📄 Open Source License
This project is open-sourced under the [MIT](LICENSE) license.
