PostgreSQL MCP Server

# PostgreSQL MCP Server Demo ## Overview This project implements a **Model Context Protocol (MCP) server** that provides a standardized interface for AI assistants to interact with PostgreSQL databases. MCP enables secure, structured communication between AI models and external data sources through well-defined tools, resources, and prompts. ## Theoretical Foundation ### Core Objectives 1. **Database Accessibility**: Enable AI assistants to safely query PostgreSQL databases without direct database access 2. **Structured Interaction**: Provide standardized tools for database operations through MCP protocol 3. **Security**: Implement read-only operations with strict query validation to prevent data manipulation 4. **Context Awareness**: Supply database schema information and context for intelligent query generation ### Architecture Components #### 1. MCP Server Framework - **FastMCP**: Lightweight MCP server implementation providing transport layers (stdio/SSE) - **Transport Layer**: Dual transport support for local development (stdio) and network deployment (SSE via HTTP) - **Registration System**: Decorators for automatic registration of tools, resources, and prompts #### 2. Configuration Management - **YAML-based Configuration**: Centralized settings for database connections and server parameters - **Environment Variables**: Runtime configuration override capabilities - **Validation Layer**: Pydantic models ensuring data integrity and type safety #### 3. Database Abstraction - **Connection Pooling**: Async PostgreSQL connections with automatic lifecycle management - **Query Execution Engine**: Isolated read-only operations with comprehensive error handling - **Result Serialization**: Consistent data format conversion for MCP protocol compatibility #### 4. Component Organization - **Tools**: Executable database operations (schema listing, table inspection, query execution) - **Resources**: Static/contextual data endpoints providing database metadata - **Prompts**: Dynamic instruction templates guiding AI query generation ## Implementation Description ### Entry Point Architecture The main application serves as a transport-aware launcher that initializes the MCP server with appropriate communication protocols. It supports both local development through standard I/O streams and production deployment via HTTP streaming. ### Configuration System Externalized settings management loads database credentials and server parameters from structured configuration files. The system provides fallback mechanisms and environment variable overrides for flexible deployment across different environments. ### Database Connection Layer Asynchronous connection management establishes secure PostgreSQL connections using connection pooling. The abstraction layer handles connection lifecycle, error recovery, and resource cleanup while maintaining connection isolation for concurrent operations. ### MCP Tools Implementation Six specialized tools provide comprehensive database interaction capabilities: - **Health Monitoring**: Basic connectivity verification returning server status - **Schema Discovery**: Enumerates available database schemas for navigation - **Table Enumeration**: Lists tables within specified schemas with metadata - **Schema Inspection**: Retrieves detailed column information and constraints - **Query Execution**: Safe SQL execution with forbidden operation filtering - **Performance Analysis**: Query optimization insights through EXPLAIN plan generation ### Resource Management Contextual data endpoints serve static database information and schema-specific guidance. These provide AI assistants with domain knowledge about table relationships, data types, and common query patterns without requiring direct database inspection. ### Prompt Engineering Dynamic instruction templates guide AI assistants in generating appropriate database queries. The system provides structured workflows for safe query construction, emphasizing read-only operations and performance considerations. ### Testing Framework Connection validation utilities enable developers to verify database connectivity and explore schema structures. The testing module provides diagnostic capabilities for troubleshooting deployment issues and validating configuration correctness. ## Setup 1. Install dependencies: ```bash uv sync ``` 2. Configure database connection in `config.yaml` 3. Install Cloudflare Tunnel (for exposing server): ```bash # macOS brew install cloudflared # Or download from: https://developers.cloudflare.com/cloudflare-one/connections/connect-apps/install-and-setup/installation/ ``` ## Running the Server ### Local Development (stdio) ```bash MCP_TRANSPORT=stdio uv run python main.py ``` ### Expose via Cloudflare Tunnel (SSE) 1. **Start the server** (defaults to SSE transport): ```bash uv run python main.py ``` The server will start on `http://127.0.0.1:8000` by default. 2. **In another terminal, start Cloudflare tunnel**: ```bash cloudflared tunnel --url http://127.0.0.1:8000 ``` Cloudflare will provide a public URL (e.g., `https://xxxxx.trycloudflare.com`) that you can use to access your MCP server. ### Environment Variables - `MCP_TRANSPORT`: Transport type - `sse` (default) or `stdio` - `MCP_HOST`: Host address (default: `127.0.0.1`) - `MCP_PORT`: Port number (default: `8000`) Example: ```bash MCP_PORT=3000 MCP_HOST=0.0.0.0 uv run python main.py ``` ## Tools Available - `ping`: Health check - `list_schemas`: List all database schemas - `list_tables`: List tables in a schema - `get_table_info`: Get table schema information - `run_sql_query`: Execute read-only SQL queries - `run_explain_query`: Get query performance metrics ## Resources - `db://context`: Database context information - `db://schema/{schema_name}`: Schema-specific context ## Prompts - `get_table_data_prompt`: Prompt to generate queries for table data ## Notes - The server uses `main.py` as the entry point - SSE transport is used for HTTP/network access (Cloudflare tunnel) - stdio transport is used for local development