A PostgreSQL query tool for Claude Desktop that runs read-only SQL, inspects execution plans, and reads table schemas.
Run read-only SQL (execute_query) — execute a single SELECT/WITH/VALUES/TABLE statement and get rows back as JSON; writes, DDL, and multi-statement input are rejected.
Inspect execution plans (explain_query) — get the EXPLAIN plan for a query, or set analyze=true to run EXPLAIN ANALYZE (which actually executes the query) with buffers.
Read table structure (get_table_schema) — list a table's columns and indexes, optionally for a non-public schema.
Note: the README describes six tools, but the live server schema exposes only these three — list_tables, get_slow_queries, and the AI-powered analyze_query optimization pipeline (SQL-Surgeon) are not present in this schema.
Safety continues to come from the underlying client: single statement only, query-only, READ ONLY transaction, and a least-privilege role if configured.
Provides tools for executing SQL queries, obtaining query execution plans, and inspecting table schemas in a PostgreSQL database.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type @ followed by the MCP server name and your instructions, e.g., "@postgres-mcp show me the schema of the users table"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
An MCP server that connects Claude Desktop to a PostgreSQL database, exposing both direct query tools and an AI-powered query optimization pipeline built on SQL-Surgeon.
This project wraps two layers of capability into a single MCP server:
Layer 1 — Direct database tools: Claude can execute SQL, inspect execution plans, and query table schemas against a live PostgreSQL database.
Layer 2 — AI query optimization pipeline: Claude can invoke a multi-step LangGraph agent (SQL-Surgeon) that analyzes a slow query, identifies performance bottlenecks, generates optimization advice, self-reviews the advice for quality, and optionally benchmarks the result in a sandbox schema.
The MCP interface means Claude decides which tool to use based on the user's question — no manual tool selection needed.
Related MCP server: @yawlabs/postgres-mcp
Claude Desktop
│
│ MCP protocol
▼
server.py ← tool registration + MCP entry point
│
tools.py ← tool logic
│
┌───┴──────────────────────────────────┐
│ │
db.py sql_surgeon.agent.graph
(Layer 1: direct tools) (Layer 2: LangGraph pipeline, from the
│ sql-surgeon package — see below)
├── execute_query │
├── explain_query preprocess_sql → rewrite_sql → run_explain
├── list_tables │
├── get_table_schema identify_issues → generate_advice
└── get_slow_queries │ ▲
review_advice ───────┘ retry loop (max 2x)
│
generate_benchmark_schema (optional)The pipeline is not copied into this repo. It comes from the SQL-Surgeon project as the sql-surgeon package (backend/ of that repo), pinned to a release tag in pyproject.toml. Fixes made in SQL-Surgeon reach this server by bumping that tag.
Tool | Parameters | Description |
|
| Run one read-only query (SELECT / WITH / VALUES / TABLE) and return JSON rows. Writes, DDL and multiple statements are rejected |
|
| Get the execution plan of one read-only query; |
|
| List all tables in a schema |
|
| List columns, types, nullability, defaults, and indexes |
|
| Return the slowest queries by mean execution time from |
|
| Run full SQL-Surgeon optimization pipeline; returns issues, advice, warnings (SQL anti-patterns, tables without a primary key), index recommendations with reasons, optimized SQL, and optional benchmark |
analyze_query invokes the SQL-Surgeon LangGraph graph (7 nodes):
preprocess_sql — rewrites comma-style joins (FROM a, b WHERE a.id = b.id) into explicit JOIN ... ON
rewrite_sql — flags SQL anti-patterns (e.g. SELECT *) and drops redundant DISTINCT
run_explain — executes EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) in a read-only, time-limited transaction; fetches column definitions and existing indexes for every table in the query; flags tables without a primary key
identify_issues — sends the execution plan + DDL to Gemini 2.5 Pro; returns a JSON array of identified bottlenecks (missing indexes, sequential scans, row count misestimation, etc.)
generate_advice — generates specific optimization recommendations and a complete optimized SQL script (index DDL + rewritten query)
review_advice — a second LLM call acting as a senior DBA reviewer; returns pass or retry with feedback; a retry goes back to generate_advice, up to 2 times
generate_benchmark_schema (optional) — copies the query's tables into a temporary schema, applies the suggested DDL (only CREATE INDEX / CREATE EXTENSION / ANALYZE on the copies), and re-runs EXPLAIN to compare plans. Runs in one transaction that is always rolled back
src/sql_surgeon_mcp/
server.py # MCP entry point, tool registrations
tools.py # Tool logic; calls db.py and the sql_surgeon pipeline
db.py # Connection helper (reads DATABASE_URL)
tests/
test_tools.py # Unit tests with mocked DB connections
examples/
claude_desktop_config.jsonPython 3.10+
uv — brew install uv
A running PostgreSQL instance
A Google API key (Gemini 2.5 Pro) for analyze_query
git clone https://github.com/RachelHuangZW/sql-surgeon-mcp
cd sql-surgeon-mcp
uv syncCreate .env in the project root:
DATABASE_URL=postgresql://user:password@localhost:5432/dbname
GOOGLE_API_KEY=your-google-api-key
# Recommended: a SELECT-only role for SQL that comes from Claude (see Security below)
SURGEON_READONLY_DATABASE_URL=postgresql://sql_surgeon_readonly:password@localhost:5432/dbnameEdit ~/Library/Application Support/Claude/claude_desktop_config.json:
{
"mcpServers": {
"sql-surgeon-mcp": {
"command": "uv",
"args": [
"run",
"--directory", "/path/to/sql-surgeon-mcp",
"--env-file", "/path/to/sql-surgeon-mcp/.env",
"python", "-m", "sql_surgeon_mcp.server"
]
}
}
}Fully quit and reopen Claude Desktop after saving.
Open Claude Desktop and ask:
"What MCP tools do you have available?"
Claude should list all six tools.
uv sync --group dev
uv run pytestTests use mocked database connections and do not require a live PostgreSQL instance.
The pipeline (sql_surgeon) is a dependency, pinned to a tag of the SQL-Surgeon repo:
In SQL-Surgeon: commit the change, then git tag vX.Y.Z && git push origin main --tags
Here: change tag = "..." under [tool.uv.sources] in pyproject.toml to the new tag
uv lock --upgrade-package sql-surgeon && uv sync, then uv run pytest
To try an unreleased SQL-Surgeon change locally, temporarily point the source at your checkout instead of the tag:
sql-surgeon = { path = "../SQL-Surgeon/backend", editable = true } — and switch back before committing.
MCP framework: FastMCP
Agent framework: LangGraph
LLM: Gemini 2.5 Pro via langchain-google-genai
Database: PostgreSQL via psycopg2
Package manager: uv
This project was built with Claude Code as a pair-programming partner. I designed the architecture (two-layer tool exposure, separation of db.py vs db_client.py), made all technical decisions (MCP framework choice, LangGraph integration approach, security boundaries), and iterated on implementation with AI assistance. Every design decision documented in this README reflects my own thinking about MCP server design and enterprise database tool exposure.
SQL written by Claude never changes your data. execute_query, explain_query and analyze_query all go through the SQL-Surgeon DB client:
One statement only. Input such as SELECT 1; COMMIT; DELETE ... is rejected before it reaches the database.
Queries only. The statement must start with SELECT, WITH, VALUES or TABLE.
READ ONLY transaction, never committed. This also blocks data-modifying CTEs and SELECT ... FOR UPDATE.
statement_timeout / lock_timeout. Defaults are 5s / 2s. Set SURGEON_STATEMENT_TIMEOUT_MS / SURGEON_LOCK_TIMEOUT_MS to change them.
Least-privilege role. If SURGEON_READONLY_DATABASE_URL is set, these queries run as that role instead of DATABASE_URL. Create the role with scripts/setup_security_role.sql from SQL-Surgeon.
Two things still use DATABASE_URL directly:
The sandbox benchmark, because it needs to create a schema. Only allowlisted index DDL runs there, and the transaction is always rolled back.
The server's own fixed catalog queries (list_tables, get_table_schema, get_slow_queries).
Setting the read-only role is still recommended. The checks above are the first line of defense; the role is the backstop.
Execute a SQL query against the connected PostgreSQL database and return results as JSON.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations exist, so the description must disclose behavioral traits. It mentions JSON output but fails to indicate whether the query can modify data (read-only vs write), what side effects occur, or any authorization requirements. This is insufficient for a potentially destructive operation.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is concise (one sentence) and front-loaded with the core action. However, it lacks structure (e.g., no sections or warnings) and could be slightly more informative without losing brevity.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the presence of an output schema and the potential risks of SQL execution, the description is incomplete. It does not clarify read-only status, transaction behavior, error handling, or limitations. A more thorough description is needed for safe and correct agent use.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, yet the description adds no meaning beyond the parameter name and type. There is no guidance on SQL format, statement constraints, or parameterization, leaving the agent to infer from the name alone.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the tool executes a SQL query against a PostgreSQL database and returns JSON results. It uses specific verbs and resources, and the sibling tools (explain_query, get_table_schema) suggest distinct purposes.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No explicit when-to-use or when-not-to-use guidance is provided. The description implies usage for executing SQL queries, but does not contrast with siblings like explain_query or get_table_schema.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Return the EXPLAIN execution plan for a SQL query. Set analyze=True to run EXPLAIN ANALYZE (this actually executes the query).
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | ||
| analyze | No |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
No annotations are provided, so the description carries the full burden. It discloses the critical behavioral trait that setting analyze=True actually executes the query, but does not discuss other traits like idempotency or potential side effects.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is extremely concise with two sentences containing no fluff. Every word adds value.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the simple tool with only two parameters and existence of an output schema, the description is largely complete. It could optionally mention what the plan looks like or any limitations, but the output schema likely covers that.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
With 0% schema description coverage, the description compensates well by explaining the 'sql' parameter implicitly and the 'analyze' parameter explicitly ('Set analyze=True to run EXPLAIN ANALYZE'). For two parameters, this adds meaningful context beyond the schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states it returns the EXPLAIN execution plan for a SQL query, which distinguishes it from sibling tools like execute_query (executes the query) and get_table_schema (retrieves schema).
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description implies usage by mentioning the analyze flag, but it does not explicitly state when to use this tool versus execute_query or provide exclusions. It could add guidance like 'Use when you need the query plan instead of actual results.'
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Get columns and indexes for a PostgreSQL table.
| Name | Required | Description | Default |
|---|---|---|---|
| schema | No | public | |
| table_name | Yes |
| Name | Required | Description |
|---|---|---|
| result | Yes |
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
With no annotations, the description carries the full burden of behavioral disclosure. It only states what is retrieved (columns and indexes) but does not mention safety (read-only), required permissions, or limitations (e.g., no constraints or foreign keys).
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single, efficient sentence—'Get columns and indexes for a PostgreSQL table.'—with no filler or redundancy. It is front-loaded and earns its place.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
Given the tool's simplicity, the output schema exists (so return values need not be described), but the description omits important context like default behavior of the 'schema' parameter and the requirement that the table must exist. It is minimally complete.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 0%, and the description adds no information beyond the parameter names. It does not explain that 'schema' defaults to 'public' or that 'table_name' is case-sensitive, providing no semantic value over the input schema.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the verb 'Get' and the resource 'columns and indexes for a PostgreSQL table'. It distinguishes from siblings 'execute_query' (execute SQL) and 'explain_query' (show query plan) by specifying exactly what artifact is retrieved.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
No guidance is provided on when to use this tool versus alternatives like 'execute_query' or 'explain_query'. There is no mention of prerequisites (e.g., table must exist) or context in which this tool is preferred.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
v0.1.0execute_queryexplain_queryget_table_schemaScored across 3 tools
Each tool serves a distinct purpose: executing queries, explaining plans, and retrieving table schema. No overlap exists.
All tools follow a consistent verb_noun snake_case pattern (execute_query, explain_query, get_table_schema) with clear, descriptive names.
Three tools is slightly low for a database server, but the scope is focused on query execution and schema inspection, which is reasonable. Could benefit from additional tools like listing tables.
Core query execution and schema retrieval are covered, but missing tools to list tables or databases, manage transactions, or modify data directly. Agents would need to rely on raw SQL for many operations.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Query Postgres, MySQL, SQL Server, Oracle, BigQuery, ClickHouse and Redshift from your AI client.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
Text-to-SQL MCP server: read-only queries on PostgreSQL, MySQL and SQL Server with your real schema
