# Northwind PostgreSQL MCP Server

[![MIT License](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)

A read-only MCP (Model Context Protocol) server that connects AI models to the [Northwind](https://github.com/pthom/northwind_psql) sample PostgreSQL database. Ask natural language questions and the model will query the database on your behalf.

---

## What is this?

This project lets an AI model act as a data analyst over the Northwind database — a classic sample dataset covering customers, orders, products, employees, and suppliers. The model can list tables, inspect schemas, run SQL queries, and generate charts, all through a secure read-only connection.

---

## Prerequisites

| Tool | Version | Download |
|------|---------|----------|
| Docker Desktop | Latest | https://www.docker.com/products/docker-desktop |
| Node.js (for Claude Code only) | 18+ | https://nodejs.org |
| Python (for Claude Code / Desktop only) | 3.11+ | https://www.python.org/downloads |

---

## Setup

### 1. Clone the repository

```bash
git clone <your-repo-url>
cd postgres_mcp
```

### 2. Set up your environment file

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

Edit `.env` and fill in your Anthropic API key:
```
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/northwind
ANTHROPIC_API_KEY=sk-ant-...
```

### 3. Start all services

Docker Desktop must be running (look for the whale icon in your system tray).

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

On first run this will:
- Pull the `postgres:16` and `open-webui` images
- Download `northwind.sql` from GitHub and initialise the database
- Start the MCP server and Open WebUI

Verify everything is ready:
```bash
docker compose ps
```
All services should show `running` and `db` should show `healthy`.

---

## Connecting to a model

### Option A — Open WebUI + Ollama (browser-based, no API key needed)

Open WebUI gives you a browser chat interface powered by a local Ollama model. No API key or cloud service required.

**1. Make sure Ollama is running** with at least one model pulled:

```bash
ollama pull llama3
ollama serve
```

**2. Start all services:**

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

**3. Seed the Northwind Analyst preset** (first time only):

```bash
docker exec postgres_mcp-open-webui-1 python3 /app/backend/webui-init.py
```

This creates a **Northwind Analyst** model preset with a built-in system prompt that automatically uses matplotlib for all chart and graph requests.

The MCP tool server (`http://mcp:8000/mcp`), the Ollama connection (`http://host.docker.internal:11434`) and Jupyter code execution are configured by environment variables in `docker-compose.yml`, so they need no seeding.

**4. Open the UI:**

Go to [http://openwebui.localhost](http://openwebui.localhost) and create an admin account on first launch.

Select **Northwind Analyst** from the model dropdown to use the preset with matplotlib charts enabled by default. You can also select any other Ollama model directly.

**5. Verify connections:**

- Go to **Admin Settings → Connections** — Ollama should show as connected at `http://host.docker.internal:11434`
- Go to **Settings → Tools** — `postgres-mcp` should be listed and connected at `http://mcp:8000/mcp`

**6. Enable chart generation (Jupyter code execution):**

Go to **Admin Settings → Code Execution** and configure:

- **Enable Code Execution** → on
- **Code Execution Engine** → `Jupyter (Legacy)`
- **Jupyter URL** → `http://jupyter:8888`
- **Jupyter Auth** → `Token`
- **Token** → `open-webui`

Save. The model can now execute Python and return real matplotlib chart images inline.

You can now pick any Ollama model and chat — it will query the Northwind database through the MCP tools.

> **Tip:** Models that generate better charts: `qwen2.5-coder:7b`, `deepseek-coder-v2`, `phi4`. Pull with `ollama pull <model>`.

---

### Option B — Claude Code CLI

Install Claude Code if you have not already:
```bash
npm install -g @anthropic-ai/claude-code
```

Start the database:
```bash
docker compose up -d db
```

Register the MCP server:
```bash
claude mcp add postgres-northwind \
  --env DATABASE_URL=postgresql://postgres:postgres@localhost:5432/northwind \
  python /full/path/to/postgres_mcp/server.py
```

Replace `/full/path/to/postgres_mcp` with the actual path on your machine.

Verify it was registered:
```bash
claude mcp list
```

Start Claude Code and try it out:
```bash
claude
```
Then ask: *"List the tables in my database"*

---

### Option C — Claude Desktop

Open your Claude Desktop config file:

- **Windows**: `C:\Users\<username>\AppData\Roaming\Claude\claude_desktop_config.json`
- **macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`

Add the following inside `"mcpServers"`:

```json
{
  "mcpServers": {
    "postgres-northwind": {
      "command": "python",
      "args": ["C:\\full\\path\\to\\postgres_mcp\\server.py"],
      "env": {
        "DATABASE_URL": "postgresql://postgres:postgres@localhost:5432/northwind"
      }
    }
  }
}
```

Start the database first:
```bash
docker compose up -d db
```

Restart Claude Desktop. The MCP tools will appear automatically in new conversations.

---

## Available tools

Once connected, the model has access to these tools:

| Tool | Description |
|------|-------------|
| `list_tables` | Lists all tables in the database |
| `describe_table` | Shows columns, types, and primary keys for a table |
| `sample_table` | Returns the first N rows of a table (max 100) |
| `query` | Runs any SELECT or WITH query (results capped at 500 rows) |
| `get_schema` | Full schema overview — all tables and columns in one call |

### Example prompts

- *"List all tables in the Northwind database"*
- *"How many customers are there, and which countries do they come from?"*
- *"Show me the top 10 best-selling products"*
- *"Show monthly revenue for 1997 as a pie chart using matplotlib"*

![Monthly Revenue](img/monthly-revenue-pie-chart.png)

- *"Who are our top 20 customers by total spend? Show a horizontal bar chart."*

![Top 20 Customers](img/top20_customers.png)

---

## Testing

Unit and security tests run against a local Postgres and need nothing else:

```bash
pip install -e ".[test]"
pytest tests/
```

The end-to-end suite drives the real stack in a browser. It is opt-in via a
marker so the command above stays fast:

```bash
pip install -e ".[e2e]"
playwright install chromium
docker compose up -d
pytest tests/ -m e2e
```

It verifies that Open WebUI's tool server config is valid, that Open WebUI can
reach the MCP server and list its tools, that the Northwind Analyst preset
carries the matplotlib system prompt, and that the admin integrations page
renders. Screenshots land in `artifacts/screenshots/`.

The `@chart` scenarios send real prompts and wait for a rendered chart:

```bash
pytest tests/ -m "e2e and chart"
```

These need a model capable enough to follow the system prompt and call the MCP
tools — `llama3` or larger. CI runs them against a 1.5B model on a CPU-only
runner, where they are not expected to pass, so they never fail the build.

To run the suite against an instance that already has an account, point it at
those credentials:

```bash
E2E_ADMIN_EMAIL=you@example.com E2E_ADMIN_PASSWORD=... pytest tests/ -m e2e
```

---

## Security

All database access is **read-only**, enforced at two levels:

1. **Application level** — the `query` tool rejects any SQL that does not start with `SELECT` or `WITH`
2. **Database level** — every connection is opened with `readonly=True`, so PostgreSQL itself will reject any write attempt even if the application check were bypassed

Table and column names supplied by users are validated against a strict identifier pattern before being used in queries, preventing SQL injection.

---

## Stopping

```bash
docker compose down
```

To also delete stored data (resets the database and Open WebUI config):
```bash
docker compose down -v
```

> After `down -v`, re-run the webui-init step on next startup to restore the Northwind Analyst model preset. Connection settings come from `docker-compose.yml` and are restored automatically.

---

## Troubleshooting

**`docker compose up` fails with "cannot find the file specified"**
Docker Desktop is not running. Open it from the Start menu and wait for the whale icon in the system tray.

**Open WebUI shows "Trouble accessing Ollama"**
Ollama is not running. Start it with `ollama serve` in a terminal.

**Open WebUI MCP connection fails**
The tool URL must use the Docker service name, not `localhost`:
```
http://mcp:8000/mcp
```
Using `localhost` inside Docker will not work — `mcp` is the correct hostname.

**`pip install -e .` fails with a hatchling error**
```bash
pip install hatchling
pip install -e .
```

**Port 5432 is already in use**
Another PostgreSQL instance is running locally. Change the port in `docker-compose.yml`:
```yaml
ports:
  - "5433:5432"
```
Then update `DATABASE_URL` in `.env` to use port `5433`.

**`psycopg2` installation fails on Windows**
Use the binary build:
```bash
pip install psycopg2-binary
```

---

## License

This project is released under the [MIT License](LICENSE). You are free to use, modify, and distribute it for personal or commercial purposes.