PostgreSQL MCP Server (Model Context Protocol)

PostgreSQL MCP Server

A FastMCP-powered Model Context Protocol server that gives AI assistants (Claude, Cursor, etc.) full, structured access to a PostgreSQL database — no SQL knowledge required from the user.

---

Overview

This server exposes your PostgreSQL database as a set of MCP Resources (read-only views) and MCP Tools (read/write actions) over the stdio transport. Any MCP-compatible AI client can connect, inspect your schema, and perform data operations in natural language.

Claude / Cursor / any MCP client
        │  stdio (MCP protocol)
        ▼
 postgres_mcp_server.py   (FastMCP layer — resources & tools)
        │  async
        ▼
 postgres_manager.py      (asyncpg connection pool)
        │
        ▼
  PostgreSQL Database

---

Features

Category	Capability
Schema	List all tables, inspect column definitions
Reads	Select with filters, ordering, and row limits
Writes	Insert, update, delete rows
DDL	Create and drop tables
Raw SQL	Execute arbitrary queries
Safety	Parameterised queries throughout (no SQL injection)
Logging	Structured stdout logs on every tool/resource call

---

MCP Resources

Resources are read-only, URI-addressable data feeds the AI client can subscribe to.

URI	Description
postgres://tables	JSON list of all tables in the database
postgres://schema/{table_name}	Column names, types, and constraints for a table
postgres://data/{table_name}	First 100 rows of a table (safe preview)

---

MCP Tools

Tools are callable actions the AI can invoke on your behalf.

execute_query

Execute any raw SQL statement and get results back as JSON.

query: str  — the SQL to run

create_table

Create a new table with custom column definitions.

table_name: str
columns:    [{"name": "id", "type": "SERIAL PRIMARY KEY"}, ...]

drop_table

Drop a table permanently.

table_name: str

insert_data

Insert a single row into a table.

table_name: str
data:       {"column": value, ...}

update_data

Update rows matching a condition.

table_name:       str
data:             {"column": new_value, ...}
condition:        "id = %s"
condition_params: [42]

delete_data

Delete rows matching a condition.

table_name:       str
condition:        "status = %s"
condition_params: ["inactive"]

select_data

Query a table with optional filtering, ordering, and pagination.

table_name:       str
columns:          ["id", "name"]          (optional, default: *)
condition:        "age > %s"              (optional)
condition_params: [18]                    (optional)
order_by:         "created_at DESC"       (optional)
limit:            100                     (optional, default: 100)

---

Prerequisites

• Python 3.8+
• PostgreSQL server (local or remote)
• uv package manager (recommended) — or pip
• An MCP-compatible AI client (Claude Desktop, Cursor, etc.)

---

Installation

# 1. Clone the repository
git clone https://github.com/VivekMalipatel/Postgres_MCP_Server.git
cd Postgres_MCP_Server

# 2. Create and activate a virtual environment
python -m venv .mcp
source .mcp/bin/activate        # macOS / Linux
# .mcp\Scripts\activate         # Windows

# 3. Install dependencies
pip install -r requirements.txt

---

Configuration

Create a .env file in the project root:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=your_database
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password

---

Connecting to Claude Desktop

Add the following block to your Claude Desktop MCP config file
(~/Library/Application Support/Claude/claude_desktop_config.json on macOS):

{
  "mcpServers": {
    "PostgreSQL MCP": {
      "command": "/absolute/path/to/.mcp/bin/uv",
      "args": [
        "run",
        "--with", "mcp[cli]",
        "--with", "asyncpg",
        "--with", "httpx",
        "--with", "python-dotenv",
        "--with", "psycopg2-binary",
        "mcp",
        "run",
        "/absolute/path/to/postgres_mcp_server.py"
      ],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "your_username",
        "POSTGRES_PASSWORD": "your_password",
        "POSTGRES_DB": "your_database"
      }
    }
  }
}

Replace all /absolute/path/to/ with the actual paths on your machine.
You can omit the env block if you are using a .env file instead.

---

Running Manually (stdio)

python postgres_mcp_server.py

The server starts and waits on stdin for MCP protocol messages. In normal use, your AI client launches it automatically via the config above.

---

Dependencies

Package	Purpose
mcp[cli]	MCP protocol + FastMCP framework
asyncpg	High-performance async PostgreSQL driver
psycopg2-binary	Synchronous PostgreSQL driver (fallback)
python-dotenv	.env file loading
httpx	Async HTTP client (available to tools)
uvicorn	ASGI server (for future HTTP transport)
uv	Fast Python package runner

---

Project Structure

Postgres_MCP_Server/
├── postgres_mcp_server.py   # FastMCP server — resources, tools, lifespan
├── postgres_manager.py      # Async DB manager (connection pool, CRUD)
├── requirements.txt
└── .env                     # (create this — not committed)

---

License

MIT — free to use, modify, and distribute.