Skip to content
Back to search

pg-lens-mcp

Read-only PostgreSQL explainer MCP server (TypeScript) — explains queries, suggests indexes, never writes

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/tansayhuan1999--pg-lens-mcp/badge.svg)](/listings/tansayhuan1999--pg-lens-mcp)

HTML

<a href="/listings/tansayhuan1999--pg-lens-mcp"><img src="/api/listings/tansayhuan1999--pg-lens-mcp/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

# 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](docs/claude-desktop.md).

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md · README · 162 lines
  1. # pg-lens-mcp
  2. A **read-only PostgreSQL explainer** MCP server, written in TypeScript.
  3. Point an AI client (Claude Code, Cursor, Claude Desktop) at a Postgres
  4. database and ask questions like *"what tables are here?"*, *"what does the
  5. orders table look like?"* — and, from Evening 2, *"why is this query slow?"*
  6. The server can **never write to the database**. That is a design choice,
  7. not a missing feature — see *Safety* below.
  8. > All six tools are built and verified end-to-end against a live seeded
  9. > database (below), with unit + integration tests and two layers of evals.
  10. ## Tools
  11. | Tool | What it does |
  12. | --- | --- |
  13. | `list_tables` | Tables in the database (system schemas excluded), with approximate row counts |
  14. | `describe_table` | Columns and types, primary key, and existing indexes for one table |
  15. | `explain_query` | EXPLAIN (FORMAT JSON) for a SELECT, summarised: cost, sequential scans, index scans. Planned, never executed |
  16. | `suggest_indexes` | Reads an explain plan and suggests an index, with reasoning — never applies it |
  17. | `table_stats` | Table/index sizes, live/dead rows, sequential vs index scan counts |
  18. | `index_health` | **v0.2:** Global index view across all schemas — flags unused and redundant indexes, with sizes and wasted bytes. Never drops anything |
  19. ### What's new in v0.2 — `index_health`
  20. Missing indexes are only half the problem: unused and duplicate indexes tax
  21. every INSERT/UPDATE and waste storage. `index_health` reads
  22. `pg_stat_user_indexes` and the `pg_index` catalog and reports every index in
  23. the database with a verdict:
  24. - `unused` — never scanned (`idx_scan = 0`)
  25. - `redundant` — its columns are a left-prefix of a wider index on the same table
  26. - `ok` — otherwise
  27. Primary-key and unique indexes are **never flagged** — they enforce
  28. integrity even if they are never scanned for lookups. Like `suggest_indexes`,
  29. it only *suggests* a `DROP INDEX` statement for review; pg-lens never runs one.
  30. ## Verified end-to-end (live, seeded shop DB)
  31. Query: `SELECT * FROM orders WHERE customer_id = 42` (20,000 orders)
  32. - **Before:** plan cost **417**, sequential scan on `orders`
  33. - `suggest_indexes` → `CREATE INDEX idx_orders_customer_id ON public.orders (customer_id);`
  34. - **After** a human applies that index (the tool never does): cost
  35. **157.46**, sequential scan gone, Bitmap Index Scan on the new index,
  36. and `suggest_indexes` now reports nothing to suggest
  37. ## Safety — defence in depth
  38. Most Postgres MCP servers expose a generic "run any SQL" tool. pg-lens
  39. deliberately does not. Three layers enforce read-only access:
  40. 1. **SQL guard** (`src/sql-guard.ts`) — a single statement only, starting
  41. with SELECT / WITH / EXPLAIN; write and DDL keywords rejected;
  42. `EXPLAIN ANALYZE` rejected because it *executes* the query, while plain
  43. EXPLAIN only plans it.
  44. 2. **Read-only transactions** (`src/db.ts`) — every query runs inside
  45. `BEGIN READ ONLY` with a 5-second statement timeout, so Postgres itself
  46. refuses writes even if a statement slipped past the guard.
  47. 3. **Read-only database user** (recommended setup) — the server only needs
  48. a user that cannot write in the first place.
  49. ## Quick start
  50. ```bash
  51. # 1. Start the sample database (a small "shop" schema, seeded)
  52. docker compose up -d
  53. # 2. Install and run the server
  54. npm install
  55. cp .env.example .env # sets DATABASE_URL for the sample DB
  56. npm start
  57. ```
  58. The sample schema contains a deliberate flaw: `orders.customer_id` has no
  59. index, so orders-by-customer queries do a sequential scan. That is the
  60. slow query the demo diagnoses.
  61. ### Use it from Claude Code
  62. ```bash
  63. claude mcp add pg-lens --env DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop -- npx tsx src/index.ts
  64. ```
  65. ### Use it from Claude Desktop (free plan works)
  66. Build the single-file bundle once (`npm run bundle`), then add to
  67. `~/Library/Application Support/Claude/claude_desktop_config.json`:
  68. ```json
  69. {
  70. "mcpServers": {
  71. "pg-lens": {
  72. "command": "node",
  73. "args": ["/path/to/pg-lens-mcp/dist/pg-lens.mjs"],
  74. "env": {
  75. "DATABASE_URL": "postgres://pglens:pglens@localhost:5433/shop"
  76. }
  77. }
  78. }
  79. }
  80. ```
  81. Full walkthrough: [docs/claude-desktop.md](docs/claude-desktop.md).
  82. ## Evals
  83. Two layers, both run against the seeded database:
  84. - **Tool-level evals** — `evals/cases.json` holds 13 fixed cases: a
  85. question, the tool that should answer it, and the expected diagnosis
  86. (e.g. "orders by customer must seq-scan, and the suggestion must be
  87. `CREATE INDEX idx_orders_customer_id …`"; "a DELETE must be rejected").
  88. ```bash
  89. DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval
  90. # 13/13 eval cases passed
  91. ```
  92. - **Protocol evals** — `evals/protocol-evals.mjs` spawns the server and
  93. speaks raw MCP over stdio, exactly as a client does: 14 cases covering
  94. every tool plus the safety rejections.
  95. ```bash
  96. DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm run eval:protocol
  97. # 14/14 evals passed
  98. ```
  99. This is the eval-first check: the tools are scored on known questions
  100. instead of only looking right in a demo.
  101. ## Development
  102. ```bash
  103. npm test # vitest — SQL guard, plan parser, tool logic (no database needed)
  104. npm run typecheck
  105. DATABASE_URL=postgres://pglens:pglens@localhost:5433/shop npm test
  106. # also runs the integration tests against a real seeded
  107. # Postgres (docker compose up -d first); CI does the same
  108. # with a Postgres service — see .github/workflows/ci.yml
  109. ```
  110. Built on the official MCP TypeScript SDK **v2**
  111. (`@modelcontextprotocol/server` 2.4.0). Most tutorials online still use
  112. the v1 package (`@modelcontextprotocol/sdk`); this project starts on v2
  113. deliberately.
  114. ## Layout
  115. - `src/server.ts` — MCP server and tool registration
  116. - `src/tools.ts` — tool logic (unit-testable without a database)
  117. - `src/plan.ts` — EXPLAIN plan parser and index-suggestion logic
  118. - `src/sql-guard.ts` — read-only SQL guard
  119. - `src/db.ts` — connection pool + read-only transaction wrapper
  120. - `scripts/build-bundle.mjs` — esbuild JS-API bundle build (no shell quoting)
  121. - `db/init.sql` — sample shop schema + seed data
  122. - `test/` — vitest tests
  123. - `evals/` — fixed eval cases (tool-level + protocol-level)
  124. - `docs/` — client setup walkthroughs

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.