How stuzero/pg-mcp works
Project Type and Purpose
• This is a specialized backend server (with an accompanying client script) that extends the functionality of PostgreSQL by offering a resource-based API inspired by the Model Context Protocol.
• The goal is to facilitate secure, efficient database interactions for AI agents, featuring multi-database support, detailed schema discovery, and query analysis tools.
File Structure
• Top-level files like Dockerfile, docker-compose.yml, and configuration files (.env.example, pyproject.toml) indicate a containerized deployment and modern Python tooling.
• The “server” directory is the core of the implementation. Key files include:
• app.py – the main server entry point.
• config.py and database.py – for configuration management and database connectivity.
• The “resources” subdirectory – implementing endpoints that expose schema, data, and extension details from PostgreSQL.
• The “tools” subdirectory – contains utility functions for managing connections and query execution (e.g., connection.py and query.py).
• The “client” directory (with claude_cli.py) demonstrates a CLI for natural language to SQL transformation, designed to interact with the server via its API.
• Additional files like test.py and uv.lock support testing and dependency management.
README for Additional Insights
• The README clearly explains the server’s functionality, listing features such as connection management, query tools (pg_query and pg_explain), and detailed schema/resource endpoints.
• It highlights the use of SSE transport, asynchronous PostgreSQL connections (using asyncpg), and YAML files to provide extension context (for PostGIS, pgvector, etc.).
• Documentation on both Docker and manual installation emphasizes that the system’s architecture supports containerization and local development.
Main Components
a. Server/API Layer:
• Represented by “server/app.py” (the primary entry point) that routes requests to various resources.
• Within the server, include sub-components like configuration (config.py), database connection handling (database.py), and resource management.
b. Resources & Extensions:
• The “server/resources” directory is responsible for exposing data such as schemas, tables, columns, and extension metadata.
• YAML-based configuration for extensions (e.g., pgvector.yaml, postgis.yaml).
c. Tools for Query and Connection Management:
• Utilities from “server/tools” (e.g., connection.py, query.py) which handle secure connection identification, pooling, and query explanations.
d. Client Interface:
• The “client” module (especially claude_cli.py) which illustrates how users (or AI agents) interact with the server.
e. External Dependencies:
• External Libraries/Frameworks: fastMCP is central to protocol handling, and YAML is used for extension context display.
Relationships and Interactions
• Flow between client and server: CLI tools and AI agents dispatch requests (via HTTP with SSE transport as indicated in the README) that hit the server’s API endpoints.
• Server’s internal divisions: the API Layer routes requests to dedicated resource handlers and utility tools.
• Configuration-driven extension context where YAML files, located under server/resources/extensions, are loaded to enrich API responses about PostgreSQL extensions.
Architectural Patterns and Design Principles
• The project follows a resource-oriented architecture (ROA) where each endpoint corresponds to database objects (schemas, tables, etc.) and functionalities (query explanation, connection management).
• Asynchronous programming principles (via asyncpg) to handle concurrent database requests.
• The separation between client (CLI) and server (API backend) reinforces the modular design.