# aadarshjain/postgres-mcp-server architecture

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

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

## Overview

##### Project Type and Purpose
- This is a backend-only, open-source “MCP Server” tool that exposes PostgreSQL queries and metadata via the Model Context Protocol (MCP).
- It acts as a bridge between an MCP-capable client (e.g., Claude Desktop) and a live PostgreSQL database, providing select-only data exploration.

##### File Structure
- Top-level files:
– server.py: the single entry point for the MCP server.
– requirements.txt: Python dependencies (mcp, pg8000, sqlalchemy).
– images/: screenshots for documentation (not part of runtime).
- No frontend code or multiple services—just one Python process.

##### Key Insights from README
- The server accepts a PostgreSQL connection string on startup.
- It registers five MCP “tools”: execute_query, list_tables, get_table_schema, filter_instances, get_database_stats.
- Uses pg8000 (pure-Python PostgreSQL driver) and SQLAlchemy core for schema introspection.
- Security: only SELECT queries allowed, credentials passed on cmd line.

##### Main Components
a. MCP Client
– Example: Claude Desktop (or any MCP-aware client)
b. Python MCP Server
– server.py
– Uses the mcp library to define and expose tools
– Internally uses pg8000 + SQLAlchemy to communicate with PostgreSQL
c. PostgreSQL Database
– Any external or local Postgres instance
d. (Optional) Configuration/Environment
– Command-line args or environment variables holding the connection string

##### Relationships and Interactions
- Client → MCP Server
– Over standard I/O (stdin/stdout) or a socket, following the MCP wire protocol.
- MCP Server → PostgreSQL
– Over TCP/IP or UNIX socket, issuing SELECT SQL statements via pg8000.
- Data flows back from Postgres → MCP Server → Client as structured JSON/tool outputs.

##### Architectural Patterns and Design Principles
- Single-process, tool-oriented architecture (plugin pattern): each MCP tool implements a discrete capability.
- Clear separation of concerns:
– Protocol layer (mcp library)
– Data access layer (pg8000 + SQLAlchemy)
- Read-only data access to enforce security.

##### Relevant Technologies and Libraries
- Python 3.7+
- mcp (Model Context Protocol framework)
- pg8000 (PostgreSQL driver)
- SQLAlchemy Core (schema introspection)
- PostgreSQL database

##### Final Checklist
[ ] Labels for protocol (MCP), drivers (pg8000), libraries (SQLAlchemy)
[ ] Callout mentioning command-line invocation and config location
[ ] Annotation of the five MCP tools inside or alongside the server component

## Diagram (Mermaid)

```mermaid
graph TB
    Client["MCP Client\n(Claude Desktop)"]:::client
    Config["Configuration\n(connection string)"]:::config
    PythonMCP["Python MCP Server\n(server.py)\nTools:\n• execute_query\n• list_tables\n• get_table_schema\n• filter_instances\n• get_database_stats\nLibraries:\n• mcp\n• pg8000\n• SQLAlchemy"]:::server
    Dependencies["Python Dependencies\n(mcp, pg8000, SQLAlchemy)"]:::dependencies
    README["README & Docs"]:::docs
    Images["Doc Images"]:::docs
    DB["[(PostgreSQL Database)]"]:::db

    Client -->|"MCP requests\n(JSON/RPC)"| PythonMCP
    PythonMCP -->|"SELECT queries\n(pg8000/SQLAlchemy)"| DB
    DB -->|"Query results\n/ metadata"| PythonMCP
    PythonMCP -->|"MCP responses\n(JSON/RPC)"| Client

    Config --- PythonMCP
    Dependencies --- PythonMCP
    README --- Dependencies
    README --- Config
    Images -.-> README

    click PythonMCP "https://github.com/aadarshjain/postgres-mcp-server/blob/main/server.py"
    click Dependencies "https://github.com/aadarshjain/postgres-mcp-server/blob/main/requirements.txt"
    click README "https://github.com/aadarshjain/postgres-mcp-server/blob/main/README.md"
    click Images "https://github.com/aadarshjain/postgres-mcp-server/tree/main/images/"

    classDef client fill:#ADD8E6,stroke:#000
    classDef server fill:#90EE90,stroke:#000
    classDef db fill:#FFA500,stroke:#000
    classDef config fill:#FFFFFF,stroke:#000,stroke-dasharray:5 5
    classDef dependencies fill:#90EE90,stroke:#000
    classDef docs fill:#FFFACD,stroke:#000,stroke-dasharray:2 2
```
