# mcp-db-server
[](https://opensource.org/licenses/Apache-2.0)
[](https://www.python.org/downloads/)
An MCP (Model Context Protocol) server that exposes relational databases (PostgreSQL/MySQL) to AI agents with natural language query support. Transform natural language questions into SQL queries and get structured results.
## Features
- **Multi-Database Support**: Works with PostgreSQL and MySQL
- **Natural Language to SQL**: Convert plain English queries to SQL using HuggingFace transformers
- **RESTful API**: Clean FastAPI-based endpoints for database operations
- **Safety First**: Read-only operations with query validation and result limits
- **Docker Ready**: Complete containerization with Docker Compose
- **Production Ready**: Health checks, logging, and error handling
- **AI Agent Friendly**: Designed specifically for AI agent integration
## Version 1.4.0 Changes
- Added mandatory `X-API-Key` authentication for all database HTTP endpoints; `/health` remains public for health checks.
- Removed unrestricted credentialed CORS and made allowed origins explicitly configurable.
- Changed the default HTTP bind address to `127.0.0.1`.
- Added table-name validation against existing database tables to block SQL injection through table routes.
- Added PostgreSQL `sslmode=require` compatibility for `asyncpg` connections.
- Updated Docker Compose, environment examples, and documentation for secure API-key configuration.
- Published Docker images:
- `souhardyak/mcp-db-server:1.4.0`
- `souhardyak/mcp-db-server:latest`
- Added GitHub Container Registry publishing for:
- `ghcr.io/souhar-dya/mcp-db-server:1.4.0`
- `ghcr.io/souhar-dya/mcp-db-server:latest`
## API Endpoints
| Endpoint | Method | Description |
| --------------------------------- | ------ | -------------------------------------------- |
| `/health` | GET | Health check and service status |
| `/mcp/list_tables` | GET | List all available tables with column counts |
| `/mcp/describe/{table_name}` | GET | Get detailed schema for a specific table |
| `/mcp/query` | POST | Execute natural language queries |
| `/mcp/tables/{table_name}/sample` | GET | Get sample data from a table |
## Quick Start
### Option 1: Docker Compose (Recommended)
1. **Clone and start the services:**
```bash
git clone https://github.com/Souhar-dya/mcp-db-server.git
cd mcp-db-server
export MCP_API_KEY="$(openssl rand -hex 32)"
docker-compose up --build
```
2. **Test the endpoints:**
```bash
# Health check
curl http://localhost:8000/health
# List tables (requires the configured API key)
curl -H "X-API-Key: $MCP_API_KEY" http://localhost:8000/mcp/list_tables
# Describe a table
curl -H "X-API-Key: $MCP_API_KEY" http://localhost:8000/mcp/describe/customers
# Natural language query
curl -X POST "http://localhost:8000/mcp/query" \
-H "X-API-Key: $MCP_API_KEY" \
-H "Content-Type: application/json" \
-d '{"nl_query": "show top 5 customers by total orders"}'
```
### Option 2: Local Development
1. **Prerequisites:**
- Python 3.11+
- PostgreSQL or MySQL database
2. **Install dependencies:**
```bash
pip install -r requirements.txt
```
3. **Set environment variables:**
```bash
export DATABASE_URL="postgresql+asyncpg://user:password@localhost:5432/dbname"
export MCP_API_KEY="$(openssl rand -hex 32)"
# or for MySQL:
# export DATABASE_URL="mysql+pymysql://user:password@localhost:3306/dbname"
```
4. **Run the server:**
```bash
python -m app.server
```
## Sample Database
The project includes a sample database with realistic e-commerce data:
- **customers**: Customer information (10 sample customers)
- **orders**: Order records (17 sample orders)
- **order_items**: Individual items within orders
- **order_summary**: View combining order and customer data
## Natural Language Query Examples
The server can understand various types of natural language queries:
```bash
# Get all customers
curl -X POST "http://localhost:8000/mcp/query" \
-H "Content-Type: application/json" \
-d '{"nl_query": "show all customers"}'
# Count orders by status
curl -X POST "http://localhost:8000/mcp/query" \
-H "Content-Type: application/json" \
-d '{"nl_query": "count orders by status"}'
# Top customers by order value
curl -X POST "http://localhost:8000/mcp/query" \
-H "Content-Type: application/json" \
-d '{"nl_query": "top 5 customers by total order amount"}'
# Recent orders
curl -X POST "http://localhost:8000/mcp/query" \
-H "Content-Type: application/json" \
-d '{"nl_query": "show recent orders from last week"}'
```
## Configuration
### Environment Variables
| Variable | Description | Default |
| -------------- | ---------------------------- | ---------------------------------------------------------------- |
| `DATABASE_URL` | Full database connection URL | `postgresql+asyncpg://postgres:postgres@localhost:5432/postgres` |
| `DB_HOST` | Database host | `localhost` |
| `DB_PORT` | Database port | `5432` |
| `DB_USER` | Database username | `postgres` |
| `DB_PASSWORD` | Database password | `postgres` |
| `DB_NAME` | Database name | `postgres` |
| `HOST` | Server host | `127.0.0.1` |
| `PORT` | Server port | `8000` |
| `MCP_API_KEY` | Required API key for database HTTP routes | Not set (API disabled) |
| `CORS_ALLOW_ORIGINS` | Comma-separated allowed browser origins | Empty (disabled) |
### Database Connection Examples
```bash
# PostgreSQL
DATABASE_URL=postgresql+asyncpg://user:pass@localhost:5432/mydb
# MySQL
DATABASE_URL=mysql+pymysql://user:pass@localhost:3306/mydb
# PostgreSQL with SSL
DATABASE_URL=postgresql+asyncpg://user:pass@localhost:5432/mydb?sslmode=require
### Database Connection Examples
```bash
# PostgreSQL (local or cloud)
DATABASE_URL=postgresql+asyncpg://user:password@host:5432/dbname
# MySQL (local or cloud)
DATABASE_URL=mysql+aiomysql://user:password@host:3306/dbname
# PostgreSQL with SSL (cloud, e.g. Neon, Supabase, Aiven)
DATABASE_URL=postgresql+asyncpg://user:password@host:5432/dbname?sslmode=require
# MySQL with SSL (cloud, e.g. Aiven, PlanetScale)
DATABASE_URL=mysql+aiomysql://user:password@host:3306/dbname?ssl-mode=REQUIRED
```
> **Note:**
> - For MySQL cloud providers, the `ssl-mode` parameter in the URL is ignored by the driver, but SSL is always enabled in the MCP server for cloud connections.
> - For PostgreSQL, use `sslmode=require` for cloud DBs. For MySQL, just use the standard URL; SSL is handled automatically.
> - If you see errors about `ssl-mode` or `sslmode`, check your URL and ensure you are using the correct driver prefix (`mysql+aiomysql` or `postgresql+asyncpg`).
#### Cloud Database Examples
```bash
# Neon (PostgreSQL)
DATABASE_URL=postgresql+asyncpg://username:password@ep-xxxxxx-pooler.us-east-2.aws.neon.tech/dbname
# Aiven (MySQL)
DATABASE_URL=mysql+aiomysql://avnadmin:yourpassword@mysql-xxxxxx-username-xxxx.aivencloud.com:11079/defaultdb?ssl-mode=REQUIRED
```
#### Docker Usage with Cloud DB
```bash
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="" \
-e MCP_API_KEY="" \
souhardyak/mcp-db-server:latest
```
GitHub Container Registry is also available:
```bash
docker login ghcr.io
docker pull ghcr.io/souhar-dya/mcp-db-server:latest
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="" \
-e MCP_API_KEY="$MCP_API_KEY" \
ghcr.io/souhar-dya/mcp-db-server:latest
```
Images are published automatically when a `v*.*.*` tag is pushed. The workflow also creates the matching GitHub Release. To publish an existing tag such as `v1.4.0`, open the workflow's **Run workflow** action and enter `1.4.0`. Set the GHCR package visibility to **Public** in the repository's **Packages** settings if unauthenticated pulls should be allowed.
#### Troubleshooting
- If you get `connect() got an unexpected keyword argument 'ssl-mode'`, ignore it: SSL is still enabled.
- For network errors, check firewall and DB credentials.
- For MySQL, always use `mysql+aiomysql` in the URL for async support.
```
### PostgreSQL SSL connection note
For PostgreSQL cloud providers, use an asyncpg URL and `sslmode=require`:
```bash
DATABASE_URL=postgresql+asyncpg://user:password@host:5432/dbname?sslmode=require
```
The server normalizes `sslmode=require` to the `ssl` option expected by `asyncpg`. A temporary PostgreSQL verification was completed successfully against a compatible cloud database using `CREATE`, `INSERT`, `SELECT`, `UPDATE`, `DELETE`, and `DROP`; no test data was retained. Never commit or share a connection URL containing a real password.
### API key authentication
`MCP_API_KEY` is a secret that you generate and provide to the server. It protects all database HTTP endpoints through the `X-API-Key` request header. The `/health` endpoint remains public for container health checks.
Generate a strong key on Linux or macOS:
```bash
export MCP_API_KEY="$(openssl rand -hex 32)"
```
Generate one in Windows PowerShell:
```powershell
$bytes = New-Object byte[] 32
[Security.Cryptography.RandomNumberGenerator]::Create().GetBytes($bytes)
$env:MCP_API_KEY = ([BitConverter]::ToString($bytes) -replace "-", "").ToLower()
```
Start the Docker image with the key:
```bash
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="" \
-e MCP_API_KEY="$MCP_API_KEY" \
souhardyak/mcp-db-server:latest
```
Call a protected endpoint with the same key:
```bash
curl -H "X-API-Key: $MCP_API_KEY" http://localhost:8000/mcp/list_tables
```
Keep the key in a password manager or deployment secret store. Do not commit it to Git, put it in a public image, or include it in logs.
## Security Features
- **Mandatory HTTP Authentication**: Database API routes require `X-API-Key`; `/health` remains public for health checks
- **Secure Network Default**: HTTP mode binds to `127.0.0.1` unless explicitly configured otherwise
- **Restricted CORS**: Cross-origin access is disabled unless origins are explicitly configured
- **Read-Only Operations**: Only SELECT queries are allowed
- **Query Validation**: Automatic detection and blocking of dangerous SQL operations
- **Result Limiting**: Maximum 50 rows per query (configurable)
- **Input Sanitization**: Protection against SQL injection
- **Safe Defaults**: Secure configuration out of the box
## Architecture
```
mcp-db-server/
├── app/
│ ├── __init__.py # Package initialization
│ ├── server.py # FastAPI application and endpoints
│ ├── db.py # Database connection and operations
│ └── nl_to_sql.py # Natural language to SQL conversion
├── .github/workflows/
│ └── docker-publish.yml # CI/CD pipeline
├── docker-compose.yml # Docker Compose configuration
├── Dockerfile # Container definition
├── init_db.sql # Sample database schema and data
├── requirements.txt # Python dependencies
└── README.md # This file
```
## Model Context Protocol (MCP) Integration
This server is designed to work seamlessly with MCP-compatible AI agents:
1. **Standardized Endpoints**: RESTful API following MCP conventions
2. **Structured Responses**: JSON responses optimized for AI consumption
3. **Error Handling**: Consistent error messages and status codes
4. **Documentation**: OpenAPI/Swagger documentation available at `/docs`
## Publish To VS Code MCP Store (Registry)
VS Code MCP gallery uses MCP Registry metadata. This repository now includes
`server.json` for registry publication.
### 1) Build and publish Docker image
```bash
docker build -t souhardyak/mcp-db-server:1.3.1 .
docker push souhardyak/mcp-db-server:1.3.1
```
### 2) Validate server metadata
`server.json` is configured for an OCI package and stdio transport:
- `name`: `io.github.Souhar-dya/mcp-db-server`
- `registryType`: `oci`
- `identifier`: `docker.io/souhardyak/mcp-db-server:1.3.1`
The Dockerfile includes registry ownership annotation:
- `io.modelcontextprotocol.server.name=io.github.Souhar-dya/mcp-db-server`
### 3) Publish to MCP Registry
Install publisher and publish metadata:
```bash
mcp-publisher login github
mcp-publisher publish
```
After publishing, users can discover/install it from MCP-compatible clients, including VS Code MCP experiences that read from the registry.
### 4) Local VS Code config example
```json
{
"servers": {
"mcp-db-server": {
"type": "stdio",
"command": "docker",
"args": [
"run",
"--rm",
"-i",
"-e",
"DATABASE_URL=sqlite+aiosqlite:////data/default.db",
"souhardyak/mcp-db-server:1.3.1"
]
}
}
}
```
## Docker Smoke Test
Use the dedicated Docker smoke test in `tests/docker`:
```bash
python tests/docker/smoke_test.py
```
This verifies Docker daemon access, image build, container startup, and health status.
## Deployment
### Docker Hub
```bash
# Pull the latest image
docker pull souhardyak/mcp-db-server:latest
# Run with your database
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="your_database_url_here" \
souhardyak/mcp-db-server:latest
```
### Kubernetes
```yaml
apiVersion: apps/v1
kind: Deployment
metadata:
name: mcp-db-server
spec:
replicas: 3
selector:
matchLabels:
app: mcp-db-server
template:
metadata:
labels:
app: mcp-db-server
spec:
containers:
- name: mcp-db-server
image: souhardyak/mcp-db-server:latest
ports:
- containerPort: 8000
env:
- name: DATABASE_URL
valueFrom:
secretKeyRef:
name: db-secret
key: url
---
apiVersion: v1
kind: Service
metadata:
name: mcp-db-server-service
spec:
selector:
app: mcp-db-server
ports:
- port: 80
targetPort: 8000
type: LoadBalancer
```
## Testing
### Run Tests Locally
```bash
# Start test database
docker-compose up postgres -d
# Wait for database to be ready
sleep 10
# Run tests
python -m pytest tests/ -v
```
### Manual Testing
```bash
# Test health endpoint
curl http://localhost:8000/health
# Test table listing
curl http://localhost:8000/mcp/list_tables
# Test natural language query
curl -X POST "http://localhost:8000/mcp/query" \
-H "Content-Type: application/json" \
-d '{"nl_query": "show me all customers from California"}'
```
## Contributing
1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request
## License
This project is licensed under the Apache License 2.0 - see the [LICENSE](LICENSE) file for details.
## 📝 Changelog
### v1.3.0 (2025-12-24) - Docker Path Fix
- **Fixed**: Resolved import path issues in Docker container causing `from db import DatabaseManager` to fail
- **Fixed**: Changed relative paths to absolute paths in Dockerfile and docker-compose.yml healthchecks
- **Improved**: `mcp_server.py` now uses robust path resolution that works both locally and in Docker containers
- **Updated**: Docker image rebuilt and pushed with all path fixes
### v1.2.0 (2025-11-03) - MySQL Column Access Fix
- **Fixed**: Resolved `Could not locate column in row for column 'column_name'` error with MySQL databases
- **Fixed**: Changed `describe_table` method to use index-based row access for better SQLAlchemy compatibility
- **Improved**: Enhanced cross-database compatibility for schema introspection
- **Resolved**: GitHub Issue [#1](https://github.com/Souhar-dya/mcp-db-server/issues/1)
### v1.1.0 (2025-09-28) - Async Bug Fix
- **Fixed**: Resolved `str can't be used in 'await' expression` error in MCP server
- **Improved**: NLP query processing now works correctly with Claude Desktop integration
- **Enhanced**: Added comprehensive test database setup scripts
- **Updated**: Docker image rebuilt with bug fixes and updated dependencies
### v1.0.0 (2025-09-25) - Initial Release
- **Initial**: Full MCP Database Server implementation
- **Added**: RESTful API with FastAPI
- **Added**: Natural language to SQL conversion
- **Added**: Docker containerization and deployment
- **Added**: Multi-database support (PostgreSQL, MySQL, SQLite)
## Acknowledgments
- [FastAPI](https://fastapi.tiangolo.com/) for the excellent web framework
- [HuggingFace Transformers](https://huggingface.co/transformers/) for NL to SQL capabilities
- [SQLAlchemy](https://sqlalchemy.org/) for database abstraction
- The Model Context Protocol (MCP) community
## Support
- [Report Issues](https://github.com/Souhar-dya/mcp-db-server/issues)
- [Discussions](https://github.com/Souhar-dya/mcp-db-server/discussions)
- [Documentation](https://github.com/Souhar-dya/mcp-db-server/wiki)
---
**⭐ If this project helped you, please consider giving it a star!**