Northwind PostgreSQL Server

MIT License

A read-only MCP (Model Context Protocol) server that connects AI models to the Northwind 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

git clone <your-repo-url>
cd postgres_mcp

2. Set up your environment file

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).

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:

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:

ollama pull llama3
ollama serve

2. Start all services:

docker compose up -d

3. Seed the Open WebUI config (first time only — sets up the MCP connection automatically):

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

4. Open the UI:

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

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:

npm install -g @anthropic-ai/claude-code

Start the database:

docker compose up -d db

Register the MCP server:

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:

claude mcp list

Start Claude Code and try it out:

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":

{
  "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:

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"

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

---

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

docker compose down

To also delete stored data (resets the database and Open WebUI config):

docker compose down -v

After down -v, re-run the webui-init step on next startup to restore the MCP connection config.

---

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

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:

ports:
  - "5433:5432"

Then update DATABASE_URL in .env to use port 5433.

psycopg2 installation fails on Windows Use the binary build:

pip install psycopg2-binary

---

License

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