mcp-db-server

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:

   git clone https://github.com/shutterfly2011/database-mcp-server.git
   cd database-mcp-server
   docker-compose up --build
   

2. Test the endpoints:

   # 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:

   pip install -r requirements.txt
   

3. Set environment variables:

   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:

   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:

# 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

# 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

# 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:

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:

{
  "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:

{
  "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 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:

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:

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:

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

# 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

# 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 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

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 for the excellent web framework
• SQLAlchemy for database abstraction
• The Model Context Protocol (MCP) community
• This is a fork of 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
• Discussions
• Upstream project (pre-fork history/issues): Souhar-dya/mcp-db-server

---

⭐ If this project helped you, please consider giving it a star!