{
"markdown": "# mcp-db-server\n\n[](https://opensource.org/licenses/Apache-2.0)\n[](https://www.python.org/downloads/)\n![\"mcpindex](\"https://mcpindex.ai/api/v1/badge/io-github-souhar-dya-mcp-db-server\")
\n\nAn 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.\n\n## Features\n\n- **Multi-Database Support**: Works with PostgreSQL and MySQL\n- **Natural Language to SQL**: Convert plain English queries to SQL using HuggingFace transformers\n- **RESTful API**: Clean FastAPI-based endpoints for database operations\n- **Safety First**: Read-only operations with query validation and result limits\n- **Docker Ready**: Complete containerization with Docker Compose\n- **Production Ready**: Health checks, logging, and error handling\n- **AI Agent Friendly**: Designed specifically for AI agent integration\n\n## API Endpoints\n\n| Endpoint | Method | Description |\n| --------------------------------- | ------ | -------------------------------------------- |\n| `/health` | GET | Health check and service status |\n| `/mcp/list_tables` | GET | List all available tables with column counts |\n| `/mcp/describe/{table_name}` | GET | Get detailed schema for a specific table |\n| `/mcp/query` | POST | Execute natural language queries |\n| `/mcp/tables/{table_name}/sample` | GET | Get sample data from a table |\n\n## Quick Start\n\n### Option 1: Docker Compose (Recommended)\n\n1. **Clone and start the services:**\n\n ```bash\n git clone https://github.com/Souhar-dya/mcp-db-server.git\n cd mcp-db-server\n docker-compose up --build\n ```\n\n2. **Test the endpoints:**\n\n ```bash\n # Health check\n curl http://localhost:8000/health\n\n # List tables\n curl http://localhost:8000/mcp/list_tables\n\n # Describe a table\n curl http://localhost:8000/mcp/describe/customers\n\n # Natural language query\n curl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"show top 5 customers by total orders\"}'\n ```\n\n### Option 2: Local Development\n\n1. **Prerequisites:**\n\n - Python 3.11+\n - PostgreSQL or MySQL database\n\n2. **Install dependencies:**\n\n ```bash\n pip install -r requirements.txt\n ```\n\n3. **Set environment variables:**\n\n ```bash\n export DATABASE_URL=\"postgresql+asyncpg://user:password@localhost:5432/dbname\"\n # or for MySQL:\n # export DATABASE_URL=\"mysql+pymysql://user:password@localhost:3306/dbname\"\n ```\n\n4. **Run the server:**\n ```bash\n python -m app.server\n ```\n\n## Sample Database\n\nThe project includes a sample database with realistic e-commerce data:\n\n- **customers**: Customer information (10 sample customers)\n- **orders**: Order records (17 sample orders)\n- **order_items**: Individual items within orders\n- **order_summary**: View combining order and customer data\n\n## Natural Language Query Examples\n\nThe server can understand various types of natural language queries:\n\n```bash\n# Get all customers\ncurl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"show all customers\"}'\n\n# Count orders by status\ncurl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"count orders by status\"}'\n\n# Top customers by order value\ncurl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"top 5 customers by total order amount\"}'\n\n# Recent orders\ncurl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"show recent orders from last week\"}'\n```\n\n## Configuration\n\n### Environment Variables\n\n| Variable | Description | Default |\n| -------------- | ---------------------------- | ---------------------------------------------------------------- |\n| `DATABASE_URL` | Full database connection URL | `postgresql+asyncpg://postgres:postgres@localhost:5432/postgres` |\n| `DB_HOST` | Database host | `localhost` |\n| `DB_PORT` | Database port | `5432` |\n| `DB_USER` | Database username | `postgres` |\n| `DB_PASSWORD` | Database password | `postgres` |\n| `DB_NAME` | Database name | `postgres` |\n| `HOST` | Server host | `0.0.0.0` |\n| `PORT` | Server port | `8000` |\n\n### Database Connection Examples\n\n```bash\n# PostgreSQL\nDATABASE_URL=postgresql+asyncpg://user:pass@localhost:5432/mydb\n\n# MySQL\nDATABASE_URL=mysql+pymysql://user:pass@localhost:3306/mydb\n\n# PostgreSQL with SSL\nDATABASE_URL=postgresql+asyncpg://user:pass@localhost:5432/mydb?sslmode=require\n\n### Database Connection Examples\n\n```bash\n# PostgreSQL (local or cloud)\nDATABASE_URL=postgresql+asyncpg://user:password@host:5432/dbname\n\n# MySQL (local or cloud)\nDATABASE_URL=mysql+aiomysql://user:password@host:3306/dbname\n\n# PostgreSQL with SSL (cloud, e.g. Neon, Supabase, Aiven)\nDATABASE_URL=postgresql+asyncpg://user:password@host:5432/dbname?sslmode=require\n\n# MySQL with SSL (cloud, e.g. Aiven, PlanetScale)\nDATABASE_URL=mysql+aiomysql://user:password@host:3306/dbname?ssl-mode=REQUIRED\n```\n\n> **Note:**\n> - 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.\n> - For PostgreSQL, use `sslmode=require` for cloud DBs. For MySQL, just use the standard URL; SSL is handled automatically.\n> - 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`).\n\n#### Cloud Database Examples\n\n```bash\n# Neon (PostgreSQL)\nDATABASE_URL=postgresql+asyncpg://username:password@ep-xxxxxx-pooler.us-east-2.aws.neon.tech/dbname\n\n# Aiven (MySQL)\nDATABASE_URL=mysql+aiomysql://avnadmin:yourpassword@mysql-xxxxxx-username-xxxx.aivencloud.com:11079/defaultdb?ssl-mode=REQUIRED\n```\n\n#### Docker Usage with Cloud DB\n\n```bash\ndocker run -d \\\n -p 8000:8000 \\\n -e DATABASE_URL=\"\" \\\n souhardyak/mcp-db-server:latest\n```\n\n#### Troubleshooting\n\n- If you get `connect() got an unexpected keyword argument 'ssl-mode'`, ignore it: SSL is still enabled.\n- For network errors, check firewall and DB credentials.\n- For MySQL, always use `mysql+aiomysql` in the URL for async support.\n```\n\n## Security Features\n\n- **Read-Only Operations**: Only SELECT queries are allowed\n- **Query Validation**: Automatic detection and blocking of dangerous SQL operations\n- **Result Limiting**: Maximum 50 rows per query (configurable)\n- **Input Sanitization**: Protection against SQL injection\n- **Safe Defaults**: Secure configuration out of the box\n\n## Architecture\n\n```\nmcp-db-server/\n├── app/\n│ ├── __init__.py # Package initialization\n│ ├── server.py # FastAPI application and endpoints\n│ ├── db.py # Database connection and operations\n│ └── nl_to_sql.py # Natural language to SQL conversion\n├── .github/workflows/\n│ └── docker-publish.yml # CI/CD pipeline\n├── docker-compose.yml # Docker Compose configuration\n├── Dockerfile # Container definition\n├── init_db.sql # Sample database schema and data\n├── requirements.txt # Python dependencies\n└── README.md # This file\n```\n\n## Model Context Protocol (MCP) Integration\n\nThis server is designed to work seamlessly with MCP-compatible AI agents:\n\n1. **Standardized Endpoints**: RESTful API following MCP conventions\n2. **Structured Responses**: JSON responses optimized for AI consumption\n3. **Error Handling**: Consistent error messages and status codes\n4. **Documentation**: OpenAPI/Swagger documentation available at `/docs`\n\n## Publish To VS Code MCP Store (Registry)\n\nVS Code MCP gallery uses MCP Registry metadata. This repository now includes\n`server.json` for registry publication.\n\n### 1) Build and publish Docker image\n\n```bash\ndocker build -t souhardyak/mcp-db-server:1.3.1 .\ndocker push souhardyak/mcp-db-server:1.3.1\n```\n\n### 2) Validate server metadata\n\n`server.json` is configured for an OCI package and stdio transport:\n\n- `name`: `io.github.Souhar-dya/mcp-db-server`\n- `registryType`: `oci`\n- `identifier`: `docker.io/souhardyak/mcp-db-server:1.3.1`\n\nThe Dockerfile includes registry ownership annotation:\n\n- `io.modelcontextprotocol.server.name=io.github.Souhar-dya/mcp-db-server`\n\n### 3) Publish to MCP Registry\n\nInstall publisher and publish metadata:\n\n```bash\nmcp-publisher login github\nmcp-publisher publish\n```\n\nAfter publishing, users can discover/install it from MCP-compatible clients, including VS Code MCP experiences that read from the registry.\n\n### 4) Local VS Code config example\n\n```json\n{\n \"servers\": {\n \"mcp-db-server\": {\n \"type\": \"stdio\",\n \"command\": \"docker\",\n \"args\": [\n \"run\",\n \"--rm\",\n \"-i\",\n \"-e\",\n \"DATABASE_URL=sqlite+aiosqlite:////data/default.db\",\n \"souhardyak/mcp-db-server:1.3.1\"\n ]\n }\n }\n}\n```\n\n## Docker Smoke Test\n\nUse the dedicated Docker smoke test in `tests/docker`:\n\n```bash\npython tests/docker/smoke_test.py\n```\n\nThis verifies Docker daemon access, image build, container startup, and health status.\n\n## Deployment\n\n### Docker Hub\n\n```bash\n# Pull the latest image\ndocker pull souhardyak/mcp-db-server:latest\n\n# Run with your database\ndocker run -d \\\n -p 8000:8000 \\\n -e DATABASE_URL=\"your_database_url_here\" \\\n souhardyak/mcp-db-server:latest\n```\n\n### Kubernetes\n\n```yaml\napiVersion: apps/v1\nkind: Deployment\nmetadata:\n name: mcp-db-server\nspec:\n replicas: 3\n selector:\n matchLabels:\n app: mcp-db-server\n template:\n metadata:\n labels:\n app: mcp-db-server\n spec:\n containers:\n - name: mcp-db-server\n image: souhardyak/mcp-db-server:latest\n ports:\n - containerPort: 8000\n env:\n - name: DATABASE_URL\n valueFrom:\n secretKeyRef:\n name: db-secret\n key: url\n---\napiVersion: v1\nkind: Service\nmetadata:\n name: mcp-db-server-service\nspec:\n selector:\n app: mcp-db-server\n ports:\n - port: 80\n targetPort: 8000\n type: LoadBalancer\n```\n\n## Testing\n\n### Run Tests Locally\n\n```bash\n# Start test database\ndocker-compose up postgres -d\n\n# Wait for database to be ready\nsleep 10\n\n# Run tests\npython -m pytest tests/ -v\n```\n\n### Manual Testing\n\n```bash\n# Test health endpoint\ncurl http://localhost:8000/health\n\n# Test table listing\ncurl http://localhost:8000/mcp/list_tables\n\n# Test natural language query\ncurl -X POST \"http://localhost:8000/mcp/query\" \\\n -H \"Content-Type: application/json\" \\\n -d '{\"nl_query\": \"show me all customers from California\"}'\n```\n\n## Contributing\n\n1. Fork the repository\n2. Create your feature branch (`git checkout -b feature/amazing-feature`)\n3. Commit your changes (`git commit -m 'Add some amazing feature'`)\n4. Push to the branch (`git push origin feature/amazing-feature`)\n5. Open a Pull Request\n\n## License\n\nThis project is licensed under the Apache License 2.0 - see the [LICENSE](LICENSE) file for details.\n\n## 📝 Changelog\n\n### v1.3.0 (2025-12-24) - Docker Path Fix\n\n- **Fixed**: Resolved import path issues in Docker container causing `from db import DatabaseManager` to fail\n- **Fixed**: Changed relative paths to absolute paths in Dockerfile and docker-compose.yml healthchecks\n- **Improved**: `mcp_server.py` now uses robust path resolution that works both locally and in Docker containers\n- **Updated**: Docker image rebuilt and pushed with all path fixes\n\n### v1.2.0 (2025-11-03) - MySQL Column Access Fix\n\n- **Fixed**: Resolved `Could not locate column in row for column 'column_name'` error with MySQL databases\n- **Fixed**: Changed `describe_table` method to use index-based row access for better SQLAlchemy compatibility\n- **Improved**: Enhanced cross-database compatibility for schema introspection\n- **Resolved**: GitHub Issue [#1](https://github.com/Souhar-dya/mcp-db-server/issues/1)\n\n### v1.1.0 (2025-09-28) - Async Bug Fix\n\n- **Fixed**: Resolved `str can't be used in 'await' expression` error in MCP server\n- **Improved**: NLP query processing now works correctly with Claude Desktop integration\n- **Enhanced**: Added comprehensive test database setup scripts\n- **Updated**: Docker image rebuilt with bug fixes and updated dependencies\n\n### v1.0.0 (2025-09-25) - Initial Release\n\n- **Initial**: Full MCP Database Server implementation\n- **Added**: RESTful API with FastAPI\n- **Added**: Natural language to SQL conversion\n- **Added**: Docker containerization and deployment\n- **Added**: Multi-database support (PostgreSQL, MySQL, SQLite)\n\n## Acknowledgments\n\n- [FastAPI](https://fastapi.tiangolo.com/) for the excellent web framework\n- [HuggingFace Transformers](https://huggingface.co/transformers/) for NL to SQL capabilities\n- [SQLAlchemy](https://sqlalchemy.org/) for database abstraction\n- The Model Context Protocol (MCP) community\n\n## Support\n\n- [Report Issues](https://github.com/Souhar-dya/mcp-db-server/issues)\n- [Discussions](https://github.com/Souhar-dya/mcp-db-server/discussions)\n- [Documentation](https://github.com/Souhar-dya/mcp-db-server/wiki)\n\n---\n\n**⭐ If this project helped you, please consider giving it a star!**\n",
"bytes": 14218,
"sha": "c05f26e32d937e10b80b51eba8c5e26bdb94b10caa044626b0bf3be695af1c4e",
"repo_slug": "souhar-dya/mcp-db-server",
"fonte": "repo",
"truncated": false,
"api": "https://agentalog.com/api/listings/mcp_io_github_souhar_dya_mcp_db_server_c87e51a5/readme"
}