Database MCP Server

# Database MCP Server **Query and manage databases through the Model Context Protocol.** ## Overview The Database MCP Server provides AI-accessible database operations for PostgreSQL and MongoDB. It enables: - PostgreSQL queries and data management - MongoDB document operations - Automatic schema discovery - Network-wide database access through MCP Discovery Hub - Zero-configuration deployment with automatic broadcasting Perfect for building AI applications that need to interact with databases safely and efficiently. ## Features ### PostgreSQL Support - Get server version and database info - List tables in any schema - Query data with configurable limits - Insert new records - SQL validation and safety checks ### MongoDB Support - List collections - Find documents with filters - Insert documents - ObjectId handling and JSON serialization ### Network Integration - Automatic multicast broadcasting for discovery - Multi-transport support (HTTP and streamable-http) - Compatible with MCP Discovery Hub - Zero-configuration networking ## Installation ### Prerequisites - Python 3.10+ - PostgreSQL server (or MongoDB, or both) - `uv` package manager (or `pip`) ### Setup ```bash # Clone or navigate to project cd database-mcp-server # Install dependencies uv sync # Or with pip: pip install -r requirements.txt ``` ## Configuration ### Environment Variables ```env # Transport mode MCP_TRANSPORT=http # http, streamable-http, or stdio (default) # Server settings MCP_HOST=0.0.0.0 # Binding host MCP_PORT=3002 # Server port MCP_SERVER_NAME=Database MCP Server # Display name # PostgreSQL DATABASE_URL=postgresql://user:pass@localhost:5432/dbname # Or individual settings: POSTGRES_USER=postgres POSTGRES_PASSWORD= POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_DB=postgres # MongoDB MONGODB_URL=mongodb://localhost:27017 MONGODB_DB=test # Broadcasting (for MCP Discovery Hub) MCP_ENABLE_BROADCAST=true # Enable/disable broadcasting MCP_BROADCAST_INTERVAL=30 # Seconds between announcements ``` ### .env File Create a `.env` file in the project root: ```env # Database Connections DATABASE_URL=postgresql://postgres:password@localhost:5432/mydb MONGODB_URL=mongodb://localhost:27017 MONGODB_DB=mydb # MCP Server MCP_TRANSPORT=http MCP_PORT=3002 MCP_SERVER_NAME=Database MCP Server MCP_ENABLE_BROADCAST=true MCP_BROADCAST_INTERVAL=30 ``` ### Docker Example ```bash # With PostgreSQL in Docker docker run -d \ -e POSTGRES_PASSWORD=mypassword \ -p 5432:5432 \ postgres:15 # With MongoDB in Docker docker run -d \ -p 27017:27017 \ mongo:latest # Start MCP server MCP_TRANSPORT=http MCP_PORT=3002 uv run main.py ``` ## Usage ### Start in HTTP Mode (with broadcasting) ```bash # Using environment variables MCP_TRANSPORT=http MCP_PORT=3002 uv run main.py # Or with .env file uv run main.py ``` ### Start in Streamable-HTTP Mode ```bash MCP_TRANSPORT=streamable-http MCP_PORT=3002 uv run main.py ``` ### Start in Stdio Mode (for Claude) ```bash # Default mode, works with Claude Desktop uv run main.py ``` ## Available Tools ### PostgreSQL Tools #### Get DB Version ``` pg_version() ``` Retrieve PostgreSQL server version information #### List Tables ``` pg_list_tables(schema: str = "public") ``` List all tables in a schema **Example:** ```json { "method": "tools/call", "params": { "name": "pg_list_tables", "arguments": { "schema": "public" } } } ``` #### List Rows ``` pg_list_rows(table: str, limit: int = 100) ``` Query data from a table with limit **Example:** ```json { "method": "tools/call", "params": { "name": "pg_list_rows", "arguments": { "table": "users", "limit": 50 } } } ``` #### Insert Row ``` pg_insert_row(table: str, data: dict) ``` Insert a new record and return the inserted ID **Example:** ```json { "method": "tools/call", "params": { "name": "pg_insert_row", "arguments": { "table": "users", "data": { "name": "John", "email": "john@example.com" } } } } ``` ### MongoDB Tools #### List Collections ``` mongo_list_collections() ``` Get all collection names in the database #### Find Documents ``` mongo_find( collection: str, query: dict = {}, limit: int = 10 ) ``` Query documents from a collection **Example:** ```json { "method": "tools/call", "params": { "name": "mongo_find", "arguments": { "collection": "users", "query": { "status": "active" }, "limit": 20 } } } ``` #### Insert Document ``` mongo_insert(collection: str, doc: dict) ``` Insert a document into a collection **Example:** ```json { "method": "tools/call", "params": { "name": "mongo_insert", "arguments": { "collection": "logs", "doc": { "timestamp": "2024-10-17T10:00:00Z", "level": "info", "message": "Server started" } } } } ``` ## Integration with MCP Discovery Hub ### Automatic Discovery When broadcasting is enabled, the database server automatically registers: 1. **Server broadcasts**: Every 30 seconds on `239.255.255.250:5353` 2. **Hub discovers**: Discovery hub receives and probes the server 3. **Tools registered**: All 7 database tools become available network-wide ### Multi-Server Setup Deploy multiple database servers for different purposes: ``` Database Server 1 (PostgreSQL, port 3002) ↓ Database Server 2 (MongoDB, port 3003) ↓ Database Server 3 (Mixed, port 3004) ↓ MCP Discovery Hub (port 8000) ↓ AI Tool (Claude, etc.) ``` All servers discovered and available to AI automatically. ## API Endpoints (When in HTTP Mode) ### GET / Server information ```bash curl http://localhost:3002/ ``` ### POST /mcp MCP protocol endpoint All MCP communication (initialize, tools/list, tools/call) ## Use Cases ### 1. Data Analysis AI-powered analysis of your database: ``` "User: Summarize user activity from the last month" AI: I'll query the activity logs for you... → calls pg_list_rows(table="activity_logs", limit=1000) → analyzes and summarizes results ``` ### 2. Automated Reporting Generate reports from database data: ``` "User: Create a report of orders by region" AI: Let me fetch the order data... → calls pg_list_rows(table="orders", limit=10000) → groups and aggregates by region → generates report ``` ### 3. Data Entry and Updates AI-assisted data entry: ``` "User: Add a new customer with this information" AI: I'll add them to the database... → calls pg_insert_row(table="customers", data={...}) ``` ### 4. Document Search and Retrieval MongoDB document management: ``` "User: Find all documents with status pending" AI: Searching for pending documents... → calls mongo_find(collection="tasks", query={"status": "pending"}) ``` ### 5. System Monitoring Database health and activity monitoring: ``` "User: Check if there are any slow queries" AI: Let me check the query logs... → calls pg_list_rows(table="query_logs") → identifies slow queries ``` ## Safety Features ### Input Validation - Table and column names validated against regex - SQL injection prevention through parameterized queries - Data type validation for inserts ### Error Handling - Database connection errors caught and reported - Timeout protection (30 seconds default) - Clear error messages for debugging ### Best Practices 1. **Read-only operations first**: Start with queries before modifying data 2. **Use limits**: Always set reasonable limits on queries 3. **Monitor logs**: Check `database_mcp.log` for issues 4. **Backup data**: Ensure backups before AI access to production 5. **Audit trail**: Log all database modifications from MCP ## Performance Considerations - **Query performance**: Depends on query complexity and data size - **Connection pooling**: PostgreSQL pool_size=5 for concurrency - **Broadcasting overhead**: Minimal (30-byte UDP packets) - **Timeout protection**: 30-second limit on operations ### Optimization Tips - Use `limit` parameter to reduce data transfer - Filter documents with `query` parameter in MongoDB - Create appropriate database indexes for common queries - Use `schema` parameter to narrow PostgreSQL searches ## Logs Server logs are written to `database_mcp.log`: ```bash # View logs tail -f database_mcp.log # Check for errors grep ERROR database_mcp.log # Monitor database operations grep "Listing tables\|Inserting\|Finding" database_mcp.log ``` ## Troubleshooting ### PostgreSQL Connection Error ```bash # Check PostgreSQL is running psql postgresql://user:pass@localhost:5432/db # Verify credentials in .env echo $DATABASE_URL ``` ### MongoDB Connection Error ```bash # Check MongoDB is running mongo --eval "db.version()" # Verify connection string echo $MONGODB_URL ``` ### Broadcasting Not Working ```bash # Verify multicast is enabled ip route show | grep 239.255.255.250 # Check firewall settings sudo firewall-cmd --list-all ``` ### Port Already in Use ```bash # Use different port MCP_PORT=3003 uv run main.py ``` ## Performance Metrics Typical response times: - Simple SELECT: 10-50ms - Database info queries: 5-20ms - MongoDB find operations: 20-100ms - Insert operations: 30-200ms (depending on triggers) Network overhead (with broadcasting): - Broadcasting: 0.01% overhead - Discovery: One-time cost per server ## Requirements - Python 3.10+ - FastAPI - SQLAlchemy - PyMongo - FastMCP - python-dotenv ## Contributing Improvements welcome! Potential enhancements: - Additional database support (MySQL, SQLite) - Stored procedure execution - Transaction support - Advanced query builder - Connection pooling configuration - Database replication support ## License MIT License - See LICENSE file for details ## Support - Issues: Report on GitHub - Documentation: See MCP Discovery Hub wiki - Examples: Check examples/ directory - Database docs: PostgreSQL and MongoDB official documentation