Mcp Db Server

What is Mcp Db Server about?

Mcp Db Server exposes relational databases (PostgreSQL, MySQL, SQLite) through a FastAPI‑based HTTP interface. It translates plain English questions into safe, read‑only SQL queries using HuggingFace transformer models, then returns the query results as JSON suitable for AI agents.

How to use Mcp Db Server?

1. Docker Compose (recommended)

   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
   

   The service will be reachable at http://localhost:8000.

2. Local development

   pip install -r requirements.txt
   export DATABASE_URL="postgresql+asyncpg://user:pass@localhost:5432/dbname"
   export MCP_API_KEY="$(openssl rand -hex 32)"
   python -m app.server
   

3. Calling the API (all database routes require the X‑API‑Key header)

   curl -H "X-API-Key: $MCP_API_KEY" http://localhost:8000/mcp/list_tables
   curl -X POST http://localhost:8000/mcp/query \
        -H "X-API-Key: $MCP_API_KEY" \
        -H "Content-Type: application/json" \
        -d '{"nl_query": "top 5 customers by total order amount"}'
   

Key features of Mcp Db Server

• Multi‑database support (PostgreSQL, MySQL, SQLite) with async drivers
• Natural‑language‑to‑SQL conversion powered by HuggingFace Transformers
• Read‑only execution, automatic dangerous‑SQL detection, and row‑limit enforcement (default 50 rows)
• Mandatory X‑API‑Key authentication for all data endpoints
• Secure defaults: binds to 127.0.0.1, CORS disabled unless configured
• Docker‑ready with official images (souhardyak/mcp-db-server:1.4.0 / latest)
• Health endpoint (/health) for container orchestration
• OpenAPI documentation available at /docs

Use cases of Mcp Db Server

• AI agents that need to fetch relational data without hard‑coded SQL
• Chat‑bots providing on‑demand analytics from an existing business database
• Low‑code dashboards where end users type questions like “show recent orders”
• Testing environments that simulate database access for LLM prompting
• Secure data‑as‑a‑service layer exposing only SELECT queries to external services

FAQ from the Mcp Db Server

Q: Which databases are supported? A: PostgreSQL, MySQL/MariaDB (via async drivers) and SQLite (for quick local testing).

Q: Can I run INSERT/UPDATE statements? A: No. The server enforces read‑only SELECT operations for safety.

Q: How is SQL injection prevented? A: The NL‑to‑SQL model output is validated, table names are checked against the actual schema, and only SELECT statements are allowed. Results are also capped.

Q: Do I need to expose the API key publicly? A: No. Set MCP_API_KEY as an environment variable and pass it in the X‑API‑Key header for every protected request.

Q: How do I enable CORS for a web front‑end? A: Populate the CORS_ALLOW_ORIGINS environment variable with a comma‑separated list of allowed origins.

Q: What port does the server listen on? A: Default 8000 on 127.0.0.1; customize via HOST and PORT environment variables.

mcp-db-server

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

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:

   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:

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

   pip install -r requirements.txt
   

3. Set environment variables:

   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:

   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:

# 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

# 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

# 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

docker run -d \
  -p 8000:8000 \
  -e DATABASE_URL="<your_cloud_database_url>" \
  -e MCP_API_KEY="<your_random_api_key>" \
  souhardyak/mcp-db-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.

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

export MCP_API_KEY="$(openssl rand -hex 32)"

Generate one in Windows 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:

docker run -d \
  -p 8000:8000 \
  -e DATABASE_URL="<your_database_url>" \
  -e MCP_API_KEY="$MCP_API_KEY" \
  souhardyak/mcp-db-server:latest

Call a protected endpoint with the same key:

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

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:

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

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

python tests/docker/smoke_test.py

This verifies Docker daemon access, image build, container startup, and health status.

Deployment

Docker Hub

# 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

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

# 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
• HuggingFace Transformers for NL to SQL capabilities
• SQLAlchemy for database abstraction
• The Model Context Protocol (MCP) community

Support

• Report Issues
• Discussions
• Documentation

---

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