# MCP PostgreSQL Query Agent

A read-only natural-language database agent built with **FastMCP**, **LangChain**, **SQLAlchemy**, and **PostgreSQL**. The MCP server exposes schema-discovery and `SELECT` tools, while the LangChain client decides which tools to call, generates SQL, and turns the result into a natural-language answer.

## What it demonstrates

- Serving database capabilities as MCP tools
- Discovering tables, columns, and foreign-key relationships
- Translating natural-language questions into SQL
- Answering questions that require joins across related tables
- Restricting the exposed query tool to a single read-only `SELECT`
- Connecting LangChain agents to a streamable HTTP MCP server

## Architecture

```text
User question
     │
     ▼
LangChain agent + OpenRouter model
     │
     ▼
MCP client (streamable HTTP)
     │
     ▼
FastMCP server
 ├── list_tables
 ├── describe_table
 └── run_select
     │
     ▼
PostgreSQL
```

## Project structure

```text
.
├── hw_db.py           # Shared SQLAlchemy connection setup
├── hw_db_seed.py      # Demo schema and sample data
├── hw_db_server.py    # FastMCP database server
├── hw_db_client.py    # LangChain MCP client and agent
├── docker-compose.yml # Local PostgreSQL service
├── .env.example       # Required environment variables
└── pyproject.toml
```

## Requirements

- Python 3.11+
- Docker, or an existing PostgreSQL instance
- An OpenRouter API key

## Setup

Clone the repository and enter the project directory:

```bash
git clone https://github.com/YOUR_USERNAME/mcp-postgres-query-agent.git
cd mcp-postgres-query-agent
```

Create the environment file:

```bash
cp .env.example .env
```

Add your OpenRouter key to `.env`:

```env
PG_DSN=postgresql+psycopg://postgres:postgres@localhost:5432/mcp_demo
OPENROUTER_API_KEY=your_openrouter_api_key
```

Install the dependencies with `uv`:

```bash
uv sync
```

Or with `pip`:

```bash
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

## Run locally

Start PostgreSQL:

```bash
docker compose up -d
```

Create and seed the demo tables:

```bash
python hw_db_seed.py
```

Start the MCP server in one terminal:

```bash
python hw_db_server.py
```

Run the client in another terminal:

```bash
python hw_db_client.py
```

You can also pass a custom question:

```bash
python hw_db_client.py "Which department has the highest average salary?"
```

## MCP tools

| Tool | Purpose |
|---|---|
| `list_tables` | Lists available database tables |
| `describe_table` | Returns columns and foreign keys for a selected table |
| `run_select` | Executes one read-only `SELECT` and returns a Markdown table |

## Demo schema

The seed script creates two related tables:

- `departments`: department name and location
- `employees`: employee name, salary, country, and department reference

The default question requires the agent to inspect both schemas and join the tables before answering.

## Safety notes

The MCP query tool rejects non-`SELECT` statements and multiple SQL statements. This is a useful application-level guard, but it is not a complete database security boundary. For real deployments, connect with a PostgreSQL role that has read-only permissions and access only to the intended schemas.

## Possible extensions

- Add a dedicated read-only PostgreSQL role
- Validate generated SQL with a parser instead of prefix checks
- Add query timeouts and row-level access controls
- Support multiple databases through separate MCP servers
- Add tracing and evaluation for generated SQL accuracy

## License

This project is available under the MIT License.