Skip to main content
Glama
README.md
# 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

A3.7/5.0

Scored across 5 tools

Disambiguation4/5

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.

Naming Consistency4/5

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.

Tool Count5/5

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.

Completeness4/5

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.

Maintenance

ActivityMaintained
ResponsivenessNo recent community issues