# I Gave Claude Access to My PostgreSQL Database

A practical MCP project for **The Curious Architect**.

The project connects:

**Claude → MCP → Python MCP Server → PostgreSQL**

Claude can inspect the database schema and ask the MCP server to run read-only SQL queries.

## What this demonstrates

- MCP tools
- MCP resources
- Streamable HTTP transport
- Python MCP SDK v2
- PostgreSQL with `psycopg`
- Schema discovery
- AI-generated SQL
- Read-only database access
- A dedicated PostgreSQL read-only user
- Query result limiting and statement timeout

## Project structure

```text
mcp-postgres-claude/
├── server.py
├── pyproject.toml
├── .env.example
├── docker-compose.yml
├── db/
│   └── init.sql
└── README.md
```

## 1. Start PostgreSQL

Make sure Docker is installed, then:

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

The demo database contains:

- `customers`
- `orders`

Check it:

```bash
docker compose ps
```

## 2. Create the Python environment

Python 3.10+ is required.

Using `uv`:

```bash
python3 -m venv .venv
source .venv/bin/activate
uv pip install -e .
```

Or with pip:

```bash
python -m venv .venv
source .venv/bin/activate
pip install -e .
```

The current official MCP Python SDK is v2, and `pip install mcp` now installs the v2 line. The server therefore uses `from mcp.server import MCPServer` rather than the old v1 `FastMCP` import.

## 3. Configure the database

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

The example points to the Docker PostgreSQL instance:

```text
DATABASE_URL=postgresql://mcp_readonly:mcp_readonly_password@localhost:5432/company
```

The database user is intentionally read-only.

## 4. Start the MCP server

```bash
python server.py
```

The server starts at:

```text
http://127.0.0.1:8000/mcp
```

You can also use the MCP CLI during development:

```bash
mcp dev server.py
```

## 5. Test with MCP Inspector

For the HTTP server, connect the Inspector to:

```text
http://127.0.0.1:8000/mcp
```

Try:

### list_tables

```text
What tables are available?
```

### describe_table

```text
Describe the orders table.
```

### query_database

```sql
SELECT
    c.country,
    COUNT(*) AS orders,
    SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC
```

The model can generate this query from a natural-language request such as:

> Show me revenue by country for paid orders.

## 6. Connect Claude

Expose the local MCP endpoint through a secure tunnel if your Claude setup requires a public HTTPS endpoint.

For example, with ngrok:

```bash
ngrok config add-authtoken <TOKEN>
ngrok http 8000
```

Use the HTTPS MCP endpoint:

```text
https://YOUR-NGROK-DOMAIN/mcp
```

Then add it as an MCP connector in the Claude environment you are using.

Do not expose a production database directly to the public internet.

## Available MCP primitives

### Tools

#### `list_tables`

Returns tables in the public schema.

#### `describe_table`

Returns column names, data types, nullability, and defaults.

#### `query_database`

Runs one read-only `SELECT` or `WITH` query and returns structured rows.

### Resource

#### `postgres://schema`

Provides a compact representation of the database schema so an MCP host can understand the available tables and columns.

## Safety design

This project is intentionally designed as a demo with multiple read-only boundaries.

### 1. Database user

The MCP server connects as:

```text
mcp_readonly
```

That user receives `SELECT` permission only.

### 2. Read-only transaction

Every query is executed inside a PostgreSQL read-only transaction.

### 3. SQL validation

The MCP tool only accepts queries beginning with `SELECT` or `WITH` and blocks common write/administrative operations.

### 4. Row limit

Results are capped by `MAX_ROWS`, defaulting to 50.

### 5. Statement timeout

Queries are limited by `STATEMENT_TIMEOUT_MS`, defaulting to 5000 ms.

These are demo safeguards, not a complete production security model. For production, add authentication/authorization, stronger SQL policy enforcement, auditing, least-privilege schemas, network controls, and carefully designed domain-specific tools.

## Good Claude demo prompts

Try these in the video:

```text
What tables do I have?
```

```text
How many customers are in each country?
```

```text
Which customer generated the most paid revenue?
```

```text
Show me monthly paid revenue for 2026.
```

```text
What is the average paid order value?
```

```text
Find customers who have never placed an order.
```

A stronger demo is:

```text
Analyze the database and tell me the top 3 countries by paid revenue.
Explain how you calculated it and show the SQL query you used.
```

## Architecture

```text
                    Natural language
                           │
                           ▼
                     ┌───────────┐
                     │  Claude   │
                     └─────┬─────┘
                           │ MCP
                           ▼
                ┌─────────────────────┐
                │ Python MCP Server   │
                │                     │
                │ list_tables         │
                │ describe_table      │
                │ query_database      │
                │ postgres://schema   │
                └──────────┬──────────┘
                           │ psycopg
                           ▼
                ┌─────────────────────┐
                │     PostgreSQL      │
                │                     │
                │ customers           │
                │ orders              │
                └─────────────────────┘
```

## Video positioning

The point of this project is not to teach PostgreSQL.

The story is:

> **What happens when an AI assistant can actually understand and query a real database?**

MCP is the bridge that exposes controlled database capabilities to Claude.

## Next project ideas

This can naturally lead into:

1. MCP + PostgreSQL + authentication
2. MCP + PostgreSQL + charts
3. MCP + multiple databases
4. MCP + APIs + PostgreSQL
5. Build an AI Data Analyst with MCP