# stuzero/pg-mcp architecture

> Architecture diagram of the GitHub repository stuzero/pg-mcp, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-02). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/stuzero/pg-mcp
- Repository: https://github.com/stuzero/pg-mcp

## Overview

##### 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.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Deployment & Configuration
    subgraph "Deployment & Configuration"
        DE1["Dockerfile"]:::deployment
        DE2["docker-compose.yml"]:::deployment
        DE3[".env example"]:::deployment
    end

    %% Server Core
    subgraph "Server Core"
        API["API Gateway (server/app.py)"]:::backend
        CONFIG["Config Manager (server/config.py)"]:::backend
        DB["DB Connector (server/database.py)"]:::backend

        %% Resource Handlers
        subgraph "Resource Handlers"
            SCHEMA["Schema Handler (server/resources/schema.py)"]:::resource
            DATA["Data Handler (server/resources/data.py)"]:::resource
            EXT["Extensions Handler (server/resources/extensions.py)"]:::resource
            EXT_YAML["Extensions Config (YAML)"]:::resource
        end

        %% Tools
        subgraph "Tools"
            CONN["Connection Manager (server/tools/connection.py)"]:::tool
            QUERY["Query Executor (server/tools/query.py)"]:::tool
        end
    end

    %% Client Interface
    subgraph "Client Interface"
        CLIENT["CLI Client (client/claude_cli.py)"]:::client
    end

    %% External Systems
    subgraph "External Systems"
        PG["PostgreSQL Database(s)"]:::external
    end

    %% Testing
    subgraph "Testing"
        TEST["Test Utilities (test.py)"]:::test
    end

    %% Flows and Relationships
    CLIENT -->|"HTTP_Request_SSE"| API
    API -->|"uses"| CONFIG
    API -->|"connects"| DB
    API -->|"routes_to"| SCHEMA
    API -->|"routes_to"| DATA
    API -->|"routes_to"| EXT
    API -->|"utilizes"| CONN
    API -->|"utilizes"| QUERY
    EXT -->|"loads"| EXT_YAML
    API -->|"SQL_Queries"| PG
    TEST -->|"tests"| API

    %% Click Events for Deployment Files
    click DE1 "https://github.com/stuzero/pg-mcp/tree/main/Dockerfile"
    click DE2 "https://github.com/stuzero/pg-mcp/blob/main/docker-compose.yml"
    click DE3 "https://github.com/stuzero/pg-mcp/blob/main/.env example"

    %% Click Events for Server/API Layer and Components
    click API "https://github.com/stuzero/pg-mcp/blob/main/server/app.py"
    click CONFIG "https://github.com/stuzero/pg-mcp/blob/main/server/config.py"
    click DB "https://github.com/stuzero/pg-mcp/blob/main/server/database.py"
    click SCHEMA "https://github.com/stuzero/pg-mcp/blob/main/server/resources/schema.py"
    click DATA "https://github.com/stuzero/pg-mcp/blob/main/server/resources/data.py"
    click EXT "https://github.com/stuzero/pg-mcp/blob/main/server/resources/extensions.py"
    click EXT_YAML "https://github.com/stuzero/pg-mcp/tree/main/server/resources/extensions"
    click CONN "https://github.com/stuzero/pg-mcp/blob/main/server/tools/connection.py"
    click QUERY "https://github.com/stuzero/pg-mcp/blob/main/server/tools/query.py"

    %% Click Event for Client Interface
    click CLIENT "https://github.com/stuzero/pg-mcp/blob/main/client/claude_cli.py"

    %% Click Event for Testing Utilities
    click TEST "https://github.com/stuzero/pg-mcp/blob/main/test.py"

    %% Styling Classes
    classDef backend fill:#a7d8de,stroke:#2c3e50,stroke-width:2px;
    classDef resource fill:#ffeaa7,stroke:#d35400,stroke-width:2px;
    classDef tool fill:#dff9fb,stroke:#130f40,stroke-width:2px;
    classDef client fill:#fab1a0,stroke:#e17055,stroke-width:2px;
    classDef deployment fill:#c7ecee,stroke:#16a085,stroke-width:2px;
    classDef test fill:#fd79a8,stroke:#b71540,stroke-width:2px;
    classDef external fill:#55efc4,stroke:#00b894,stroke-width:2px;
```
