# tyrchen/postgres-mcp architecture

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

- Interactive diagram: https://gitdiagram.com/tyrchen/postgres-mcp
- Repository: https://github.com/tyrchen/postgres-mcp

## Overview

##### Project Identification
- Type & Purpose: Postgres MCP is a backend tool that implements the Model Context Protocol for PostgreSQL. It allows AI agents (and CLI clients) to execute database operations (select, insert, update, delete, table/index management, etc.) against PostgreSQL through a common interface.
- Key Concepts: Connection management, SQL validation (using sqlparser), interaction with PostgreSQL (using sqlx), asynchronous runtime (tokio), and protocol communication (rmcp with SSE transport support).

##### Analyzing the File Structure
- Rust Source Code:
- The main server is implemented in the Rust sections (src/main.rs, src/lib.rs, src/mcp.rs, src/pg.rs). These files contain the core logic for handling the MCP commands, connection management, and database operations.
- Python Agents:
- The "agents" directory includes Python files (e.g., pg_agent.py, sse_client.py) that likely act as clients or intermediaries interacting with the MCP server.
- Specifications and Examples:
- The "specs" folder documents detailed protocol specifications for agents, clients, and testers. This gives insights into protocol and interaction layers.
- "examples" and "fixtures" provide guidance on usage and include sample SQL migrations and commands.
- CI/CD and Quality Tools:
- The .github/workflows build configuration and linting/typo configuration files indicate testing, deployment automation, and code quality checks.

##### Key Components
a. Core Server (Rust Backend):
- Entry Point: src/main.rs, with lib.rs supporting shared functionalities.
- MCP Protocol Module: src/mcp.rs handles protocol parsing, command execution, and overall request dispatching.
- PostgreSQL Interaction Module: src/pg.rs manages actual DB connections, SQL validations, and query execution.
b. Agent/Client Layer:
- Python-based agents (agents/pg_agent.py, agents/sse_client.py) act as client applications interacting with the MCP server.
- Command-Line Interface: The README commands (e.g., pg_mcp register, query, insert, etc.) indicate that users can issue requests via CLI which feed into the server.
c. Database:
d. Protocol and Communication Channels:
- SSE Mode: The project supports Server-Sent Events; include a path showing SSE communication from agents to server.
- Direct CLI Commands: Also illustrate the standard input/command mode on the server.
e. Development and Infrastructure:
- CI/CD: Acknowledge the .github/workflows where automated builds are defined.

##### Relationships and Data Flow
- Commands/requests from agent/CLI components directed to the MCP server.
- Data and query flow from the MCP server to the PostgreSQL database.
- Responses flowing back from the database to the MCP server and then to the clients.
- Dependencies such as connection pooling and SQL parsing within the server component.

##### Architectural Patterns and Design Principles
- Modular Separation: Clearly differentiate the protocol (MCP) handling, database operation handling, and the client/agent interaction layers.
- Asynchronicity: Note the use of the asynchronous runtime (tokio) that drives non-blocking operations and concurrent request handling.
- Extensibility: Emphasize how the design supports multiple modes (standard and SSE) and handles varied commands via a modular approach which might pave the way for future enhancements or protocol versions.
- Dependency Injection: Optionally note the usage of dependencies like sqlx and rmcp, which encapsulate separate functionalities.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Backend (Rust) Layer
    subgraph "Backend (Rust)"
        A["MCP Server (Rust Backend)"]:::rust
        B["MCP Protocol Module"]:::rust
        C["PostgreSQL Connector"]:::rust
    end

    %% Client/CLI Layer
    subgraph "Client/CLI Layer"
        D["Python Agent - Standard Client"]:::python
        E["Python Agent - SSE Client"]:::python
        F["CLI Guidance & Usage Examples"]:::python
    end

    %% Infrastructure & External Systems
    subgraph "Infrastructure & External"
        G["PostgreSQL Database"]:::db
        H["Protocol Specifications & Documentation"]:::docs
        I["Database Setup & Migrations"]:::docs
        J["CI/CD Pipeline"]:::tools
        K["Testing Suite"]:::tools
    end

    %% Data Flow Relationships
    %% Client/CLI interactions
    D -->|"sendsCommand"| B
    E -->|"SSECommunication"| B
    F -->|"cliRequest"| A

    %% Backend internal flow
    A -->|"dispatches"| B
    B -->|"dbQuery"| C
    C -- "SQLExecution" --> G
    G -- "queryResult" --> C
    B -- "responseFlow" --> A

    %% External influences
    H -->|"protocolSpecs"| B
    I -->|"migrationData"| G
    J -->|"buildAndDeploy"| A
    K -->|"testsModule"| A

    %% Styles and Classes
    classDef rust fill:#a0c4ff,stroke:#000,stroke-width:2px;
    classDef python fill:#ffadad,stroke:#000,stroke-width:2px;
    classDef docs fill:#caffbf,stroke:#000,stroke-width:2px;
    classDef db fill:#fdffb6,stroke:#000,stroke-width:2px;
    classDef tools fill:#cddafd,stroke:#000,stroke-width:2px;

    %% Click Events
    click A "https://github.com/tyrchen/postgres-mcp/blob/master/src/main.rs"
    click B "https://github.com/tyrchen/postgres-mcp/blob/master/src/mcp.rs"
    click C "https://github.com/tyrchen/postgres-mcp/blob/master/src/pg.rs"
    click D "https://github.com/tyrchen/postgres-mcp/blob/master/agents/pg_agent.py"
    click E "https://github.com/tyrchen/postgres-mcp/blob/master/agents/sse_client.py"
    click F "https://github.com/tyrchen/postgres-mcp/blob/master/README.md"
    click H "https://github.com/tyrchen/postgres-mcp/tree/master/specs/"
    click I "https://github.com/tyrchen/postgres-mcp/tree/master/fixtures/"
    click J "https://github.com/tyrchen/postgres-mcp/tree/master/.github/workflows/"
    click K "https://github.com/tyrchen/postgres-mcp/blob/master/tests/mcp_test.rs"
```
