Skip to main content
Glama

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

Related MCP server: postgres-mcp

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

# 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

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:

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

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

    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.

    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

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

Available Tools

5 tools
describe_tableDescribe tableA

Describe one table: columns and types, primary key, and existing indexes.

Inputs: table (required), schema.

explain_queryExplain queryA

Run EXPLAIN (FORMAT JSON) for a SELECT query and return a plain summary: total cost, node types, sequential scans, and index scans. The query is planned, never executed, and writes are rejected.

Inputs: query (required).

list_tablesList tablesA

List the tables in the database (excluding system schemas), with approximate row counts.

Inputs: schema.

suggest_indexesSuggest indexesA

Analyse a SELECT query's plan and suggest indexes for sequential scans, with reasoning and the CREATE INDEX statement. Suggestions only — this tool never creates an index.

Inputs: query (required).

table_statsTable statsB

Statistics for one table: live/dead rows, sequential vs index scan counts, and table/index sizes.

Inputs: table (required), schema.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    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

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      B
      quality
      D
      maintenance
      A lightweight Postgres MCP server for safe database exploration and query analysis, read-only by default, with multi-database support.
      4
      4
      MIT
    • A
      license
      A
      quality
      D
      maintenance
      A self-hostable PostgreSQL MCP server for exploring database schemas and running guarded read/write queries with selectable access modes (readonly, readwrite, admin), plus a dry-run confirm workflow for safety.
      14
      1
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      A read-only MCP server for PostgreSQL that enables safe database introspection and querying via natural language.
      MIT
    • A
      license
      Not graded
      quality
      D
      maintenance
      Read-only MCP server for PostgreSQL, enabling schema discovery, table metadata, and safe SELECT queries via READ ONLY transactions.
      11 npm
      MIT