# mcp-db-server

[![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://opensource.org/licenses/Apache-2.0)
[![Python](https://img.shields.io/badge/python-3.11+-blue.svg)](https://www.python.org/downloads/)
<a href="https://mcpindex.ai/server/io-github-souhar-dya-mcp-db-server"><img src="https://mcpindex.ai/api/v1/badge/io-github-souhar-dya-mcp-db-server" alt="mcpindex verdict" height="20" /></a>

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="<your_cloud_database_url>" \
  -e MCP_API_KEY="<your_random_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="<your_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="<your_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!**