# PostgreSQL MCP Server

> A [FastMCP](https://github.com/jlowin/fastmcp)-powered [Model Context Protocol](https://modelcontextprotocol.io/) 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

```bash
# 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:

```env
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):

```json
{
  "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)

```bash
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.