mcp-db-server
README.md
# mcp-db-server
[](https://opensource.org/licenses/Apache-2.0)
[](https://www.python.org/downloads/)
An MCP (Model Context Protocol) server that exposes databases (PostgreSQL/MySQL/SQLite/MongoDB) 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, MySQL, SQLite, and MongoDB
- **Natural Language to SQL**: Convert plain English queries to SQL using a configurable LLM
provider - Anthropic, OpenAI, OpenRouter, or local Ollama models - with a rule-based fallback
when no provider is configured
- **Rich Schema Metadata**: Primary/foreign keys, indexes, row counts, and sample rows are
auto-discovered and fed into the LLM prompt for more accurate SQL generation
- **RESTful API**: Clean FastAPI-based endpoints for database operations
- **Safety First**: Read-only operations with query validation and result limits; destructive
write tools are disabled by default and require explicit opt-in (see Safety section)
- **Docker Ready**: Complete containerization with Docker Compose
- **Production Ready**: Health checks, logging, and error handling
- **AI Agent Friendly**: Designed specifically for AI agent integration
## API Endpoints
The HTTP API is a deliberate **read-only subset** of the full tool set. It does not expose write
operations, dynamic database switching, rich metadata inspection, or connection examples - for the
full 13-tool surface, use the stdio MCP server (`mcp_server.py`, e.g. via Claude Desktop) instead.
| Endpoint | Method | Description |
| --------------------------------- | ------ | -------------------------------------------- |
| `/health` | GET | Health check - verifies the DB connection |
| `/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/shutterfly2011/database-mcp-server.git
cd database-mcp-server
docker-compose up --build
```
2. **Test the endpoints:**
```bash
# Health check
curl http://localhost:8000/health
# List tables
curl http://localhost:8000/mcp/list_tables
# Describe a table
curl http://localhost:8000/mcp/describe/customers
# Natural language query
curl -X POST "http://localhost:8000/mcp/query" \
-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"
# or for MySQL:
# export DATABASE_URL="mysql+aiomysql://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
## LLM Provider Configuration
SQL generation is delegated to whichever provider you configure via environment variables
(see `.env.example` for full examples). All four are interchangeable - only `LLM_PROVIDER`
and `LLM_MODEL` change:
| Provider | `LLM_PROVIDER` | API key | Notes |
| -------------- | --------------- | --------------------------------- | --------------------------------------- |
| Anthropic | `anthropic` | `LLM_API_KEY` or `ANTHROPIC_API_KEY` | |
| OpenAI | `openai` | `LLM_API_KEY` or `OPENAI_API_KEY` | |
| OpenRouter | `openrouter` | `LLM_API_KEY` or `OPENROUTER_API_KEY`| OpenAI-compatible; routes to many models |
| Ollama (local) | `ollama` | none | `LLM_BASE_URL` defaults to `http://localhost:11434` |
If `LLM_PROVIDER` is unset, the server falls back to simple rule-based SQL generation (no
network calls). If a configured LLM call fails at runtime, it also falls back to the rule-based
generator rather than erroring out.
## 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 | `0.0.0.0` |
| `PORT` | Server port | `8000` |
### 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
This image isn't published anywhere; build it locally first, then run it:
```bash
docker build -t mcp-database-server:latest .
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="<your_cloud_database_url>" \
mcp-database-server:latest
```
#### 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.
## Security Features
- **Read-Only Operations**: Only SELECT queries are allowed for `execute_sql`/`query_database`
- **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
- **Write Tools Disabled by Default**: `execute_unsafe_sql`, `create_table`, `insert_data`,
`update_data`, and `delete_data` all refuse to run unless `ALLOW_WRITE_OPERATIONS=true` is set.
`delete_data`/`update_data` additionally require a `where_condition` - or an explicit
`confirm_full_table=True` - before they'll touch every row in a table.
## 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 (LLM-backed)
│ ├── llm_providers.py # Anthropic/OpenAI/OpenRouter/Ollama provider adapters
│ └── metadata.py # Rich schema metadata (PKs, FKs, indexes, samples)
├── mcp_server.py # FastMCP stdio server (for Claude Desktop / MCP clients)
├── docker-compose.yml # Docker Compose configuration
├── Dockerfile # Container definition
├── init-scripts/
│ └── init_db.sql # Sample e-commerce seed data (postgres/mysql compose services)
├── 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`
## VS Code Integration
This server isn't published to the MCP Registry or a public container registry, so VS
Code's `mcp.json` needs to point at either a locally-built Docker image or a local
Python interpreter directly - both are shown below.
### Docker-based config example
Build the image first (`docker build -t mcp-database-server:latest .`), then:
```json
{
"servers": {
"mcp-db-server": {
"type": "stdio",
"command": "docker",
"args": [
"run",
"--rm",
"-i",
"-e",
"DATABASE_URL=sqlite+aiosqlite:////data/default.db",
"mcp-database-server:latest"
]
}
}
}
```
### Python-based config example (no Docker)
Running `mcp_server.py` directly with a local Python interpreter avoids the Docker
daemon entirely and uses noticeably less memory/CPU than a container - useful on
machines without Docker Desktop, or when you'd rather not run one just for this.
See `vscode_mcp_config_local_example.json`:
```json
{
"servers": {
"mcp-db-server-local": {
"type": "stdio",
"command": "${workspaceFolder}/.venv/bin/python",
"args": [
"${workspaceFolder}/mcp_server.py",
"--database-url",
"sqlite+aiosqlite:///${workspaceFolder}/data/local.db"
],
"env": {
"LLM_PROVIDER": "anthropic",
"LLM_MODEL": "claude-sonnet-5",
"ANTHROPIC_API_KEY": "sk-ant-...",
"ALLOW_WRITE_OPERATIONS": "false"
}
}
}
}
```
Requires `pip install -r requirements.txt` into that virtualenv first (see
[Option 2: Local Development](#option-2-local-development) above). On Windows, `command` should point
at `.venv\Scripts\python.exe` instead. If your VS Code version doesn't resolve
`${workspaceFolder}` inside `mcp.json`, replace it with an absolute path.
> **Note:** For stdio-based MCP servers such as `mcp_server.py`, the host process
> often relies on `stderr` to capture startup and runtime errors. The server is
> configured to write logs and exceptions to `stderr` so tools like OpenWorker can
> surface the real failure instead of a generic task-group error.
## 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
### Local Docker
This image isn't published to any registry; build and run it locally:
```bash
docker build -t mcp-database-server:latest .
docker run -d \
-p 8000:8000 \
-e DATABASE_URL="your_database_url_here" \
mcp-database-server:latest
```
### Kubernetes
Push `mcp-database-server:latest` to a registry your cluster can pull from first
(Docker Hub, GHCR, ECR, etc.), then reference that image below:
```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: <your-registry>/mcp-database-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
- [SQLAlchemy](https://sqlalchemy.org/) for database abstraction
- The Model Context Protocol (MCP) community
- This is a fork of [Souhar-dya/mcp-db-server](https://github.com/Souhar-dya/mcp-db-server)
(Apache-2.0), replacing its HuggingFace-based NL-to-SQL converter with a multi-provider LLM
backend (Anthropic/OpenAI/OpenRouter/Ollama), adding rich schema metadata, and hardening the
write tools with an opt-in safety gate
## Support
- [Report Issues](https://github.com/shutterfly2011/database-mcp-server/issues)
- [Discussions](https://github.com/shutterfly2011/database-mcp-server/discussions)
- Upstream project (pre-fork history/issues): [Souhar-dya/mcp-db-server](https://github.com/Souhar-dya/mcp-db-server)
---
**⭐ If this project helped you, please consider giving it a star!**
This server cannot be deployed
Maintenance
ActivitySlowing
ResponsivenessNo issues