pg-lens-mcp
# pg-lens-mcp
A **read-only PostgreSQL explainer** MCP server, written in TypeScript.
Point an AI client (Claude Code, Cursor, Claude Desktop) at a Postgres
database and ask questions like *"what tables are here?"*, *"what does the
orders table look like?"* — and, from Evening 2, *"why is this query slow?"*
The server can **never write to the database**. That is a design choice,
not a missing feature — see *Safety* below.
> All five tools are built and verified end-to-end against a live seeded
> database (below), with unit + integration tests and two layers of evals.
## Tools
| Tool | What it does |
| --- | --- |
| `list_tables` | Tables in the database (system schemas excluded), with approximate row counts |
| `describe_table` | Columns and types, primary key, and existing indexes for one table |
| `explain_query` | EXPLAIN (FORMAT JSON) for a SELECT, summarised: cost, sequential scans, index scans. Planned, never executed |
| `suggest_indexes` | Reads an explain plan and suggests an index, with reasoning — never applies it |
| `table_stats` | Table/index sizes, live/dead rows, sequential vs index scan counts |
## Verified end-to-end (live, seeded shop DB)
Query: `SELECT * FROM orders WHERE customer_id = 42` (20,000 orders)
- **Before:** plan cost **417**, sequential scan on `orders`
- `suggest_indexes` → `CREATE INDEX idx_orders_customer_id ON public.orders (customer_id);`
- **After** a human applies that index (the tool never does): cost
**157.46**, sequential scan gone, Bitmap Index Scan on the new index,
and `suggest_indexes` now reports nothing to suggest
## Safety — defence in depth
Most Postgres MCP servers expose a generic "run any SQL" tool. pg-lens
deliberately does not. Three layers enforce read-only access:
1. **SQL guard** (`src/sql-guard.ts`) — a single statement only, starting
with SELECT / WITH / EXPLAIN; write and DDL keywords rejected;
`EXPLAIN ANALYZE` rejected because it *executes* the query, while plain
EXPLAIN only plans it.
2. **Read-only transactions** (`src/db.ts`) — every query runs inside
`BEGIN READ ONLY` with a 5-second statement timeout, so Postgres itself
refuses writes even if a statement slipped past the guard.
3. **Read-only database user** (recommended setup) — the server only needs
a user that cannot write in the first place.
## Quick start
```bash
# 1. Start the sample database (a small "shop" schema, seeded)
docker compose up -d
# 2. Install and run the server
npm install
cp .env.example .env # sets DATABASE_URL for the sample DB
npm start
```
The sample schema contains a deliberate flaw: `orders.customer_id` has no
index, so orders-by-customer queries do a sequential scan. That is the
slow query the demo diagnoses.
### Use it from Claude Code
```bash
claude mcp add pg-lens --env DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop -- npx tsx src/index.ts
```
### Use it from Claude Desktop (free plan works)
Build the single-file bundle once (`npm run bundle`), then add to
`~/Library/Application Support/Claude/claude_desktop_config.json`:
```json
{
"mcpServers": {
"pg-lens": {
"command": "node",
"args": ["/path/to/pg-lens-mcp/dist/pg-lens.mjs"],
"env": {
"DATABASE_URL": "postgres://pglens:pglens@localhost:5433/shop"
}
}
}
}
```
Full walkthrough: [docs/claude-desktop.md](docs/claude-desktop.md).
## Evals
Two layers, both run against the seeded database:
- **Tool-level evals** — `evals/cases.json` holds 11 fixed cases: a
question, the tool that should answer it, and the expected diagnosis
(e.g. "orders by customer must seq-scan, and the suggestion must be
`CREATE INDEX idx_orders_customer_id …`"; "a DELETE must be rejected").
```bash
DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval
# 11/11 eval cases passed
```
- **Protocol evals** — `evals/protocol-evals.mjs` spawns the server and
speaks raw MCP over stdio, exactly as a client does: 12 cases covering
every tool plus the safety rejections.
```bash
DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval:protocol
# 12/12 evals passed
```
This is the eval-first check: the tools are scored on known questions
instead of only looking right in a demo.
## Development
```bash
npm test # vitest — SQL guard, plan parser, tool logic (no database needed)
npm run typecheck
DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm test
# also runs the integration tests against a real seeded
# Postgres (docker compose up -d first); CI does the same
# with a Postgres service — see .github/workflows/ci.yml
```
Built on the official MCP TypeScript SDK **v2**
(`@modelcontextprotocol/server` 2.4.0). Most tutorials online still use
the v1 package (`@modelcontextprotocol/sdk`); this project starts on v2
deliberately.
## Layout
- `src/server.ts` — MCP server and tool registration
- `src/tools.ts` — tool logic (unit-testable without a database)
- `src/plan.ts` — EXPLAIN plan parser and index-suggestion logic
- `src/sql-guard.ts` — read-only SQL guard
- `src/db.ts` — connection pool + read-only transaction wrapper
- `db/init.sql` — sample shop schema + seed data
- `test/` — vitest tests
- `evals/` — fixed eval cases (tool-level + protocol-level)
- `docs/` — client setup walkthroughs
TDQS
Scored across 5 tools
Each tool targets a distinct step in a query-tuning workflow: enumeration, schema inspection, plan explanation, index suggestion, and table runtime stats. The only mild overlap is between describe_table and table_stats, which both operate on a single table, but their descriptions clearly separate schema structure from runtime statistics.
Four of five names follow a clean verb_noun snake_case pattern (list_tables, describe_table, explain_query, suggest_indexes). table_stats deviates as noun_noun, but the overall convention remains predictable and readable.
Five tools is well-scoped for a read-only Postgres diagnostics server; each tool earns its place in the list → describe → explain → suggest chain. No redundant or filler tools.
The core workflow of discovering tables, inspecting structure, analysing plans, and proposing indexes is fully covered. Minor gaps exist: no tool to enumerate schemas/views or list all indexes globally (e.g. unused or duplicate index detection), and no pg_stat_statements-style slow-query discovery.