# Ask Your Database — MCP Server

A local MCP server that lets Claude Desktop or Claude Code answer plain-English
questions against a Postgres database — e.g. *"which customers spent less than
$2,000 in the last year?"* — by calling real tools, not by guessing.

Tested end-to-end: schema discovery, multi-step joins, and a blocked write all
verified against a live Postgres instance.

## What's here

```
mcp-demo/
├── server.py           # MCP server — exposes list_tables, describe_table, query
├── db.py                # connection + safety layer (read-only role, timeout, write-block)
├── seed.sql              # schema + sample data + read-only role setup
├── docker-compose.yml    # spins up Postgres with the seed data pre-loaded
├── requirements.txt       # mcp[cli], psycopg2-binary
└── README.md
```

## 1. Start Postgres

```bash
cd mcp-demo
docker compose up -d
```

This starts Postgres on `localhost:5432`, creates the `demo` database, loads
the sample schema (`customers`, `subscriptions`, `support_tickets`), and
creates a `mcp_readonly` role that can only `SELECT`.

No Docker? Run `psql` against any local Postgres instance and load `seed.sql`
directly: `psql -U postgres -d demo -f seed.sql` (creates the database first
if needed).

## 2. Set up Python

```bash
python3 -m venv venv
source venv/bin/activate        # Windows: venv\Scripts\activate
pip install -r requirements.txt
```

## 3. Point the server at your database

The server reads connection details from environment variables (defaults
shown match `seed.sql` and `docker-compose.yml`, so you likely don't need to
change anything for local testing):

| Variable | Default |
|---|---|
| `PGHOST` | `localhost` |
| `PGPORT` | `5432` |
| `PGDATABASE` | `demo` |
| `PGUSER` | `mcp_readonly` |
| `PGPASSWORD` | `readonlypass` |

## 4. Smoke-test it standalone (optional but recommended)

```bash
export PGHOST=localhost PGPORT=5432 PGDATABASE=demo PGUSER=mcp_readonly PGPASSWORD=readonlypass
python3 server.py
```

It should sit there quietly waiting on stdio — that's correct, it's not
meant to print anything until a client (like Claude Desktop) talks to it.
Ctrl+C to stop.

## 5. Connect it to Claude Desktop

Open Claude Desktop's config file:
- macOS: `~/Library/Application Support/Claude/claude_desktop_config.json`
- Windows: `%APPDATA%\Claude\claude_desktop_config.json`

Add this server (use the **absolute path** to `server.py` on your machine):

```json
{
  "mcpServers": {
    "ask-your-database": {
      "command": "/absolute/path/to/mcp-demo/venv/bin/python3",
      "args": ["/absolute/path/to/mcp-demo/server.py"],
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "demo",
        "PGUSER": "mcp_readonly",
        "PGPASSWORD": "readonlypass"
      }
    }
  }
}
```

Restart Claude Desktop. You should see "ask-your-database" listed under the
🔌 tools/connectors icon in a new chat.

## 6. Connect it to Claude Code

From the project directory:

```bash
claude mcp add ask-your-database \
  --env PGHOST=localhost --env PGPORT=5432 --env PGDATABASE=demo \
  --env PGUSER=mcp_readonly --env PGPASSWORD=readonlypass \
  -- /absolute/path/to/mcp-demo/venv/bin/python3 /absolute/path/to/mcp-demo/server.py
```

## 7. Try it

Ask Claude, in plain English:

> "Which customers churned last month, and what plan were they on?"

> "Which customers spent less than $2,000 total in the last year?"

Claude will call `list_tables` and `describe_table` on its own to learn the
schema (nothing about it is hardcoded in the prompt), then write and run the
SQL itself via the `query` tool.

Try a write, too — it's blocked on purpose:

> "Delete all customers with status 'churned'."

## Adapting this to your real database

Swap the seed data for your actual schema, and point `PGHOST`/`PGDATABASE` at
your real Postgres instance — **using a read-only role**, same as here. Never
point this server at a production database with a role that can write.
`db.py`'s query logic doesn't need to change at all.

## Troubleshooting

- **Server doesn't show up in Claude Desktop** — check the path in
  `claude_desktop_config.json` is absolute, not relative, and that you
  restarted the app after editing the config.
- **`permission denied` on every query, even simple ones** — check the
  `mcp_readonly` role's grants ran (last block of `seed.sql`); rerun that
  block if you dropped and recreated the database.
- **Connection refused** — confirm Postgres is actually running
  (`docker compose ps`) and the port matches `PGPORT`.