SQL MCP

SQL MCP

A working, read-only Model Context Protocol server thatexposes a MySQL database's schema — not its data — to AI agents.

It is a local reimplementation of the SqlDBM MCP Serverconcept: an AI can discover projects, read the full data model as structuredJSON, retrieve DDL, inspect revision history, and generate migration scriptsbetween revisions or environments — while never being able to read a single rowor write anything at all.

Built as a teaching demo, but it runs against any local MySQL instance.

Why this exists

An LLM will happily write SQL for a schema it has never seen. It inventsplausible table and column names, and the query either fails loudly or — worse —succeeds against the wrong columns and returns a confidently incorrect answer.

The obvious fix, handing the agent live database credentials, trades adocumentation problem for a security one. Now it can read PII and write toproduction, and nothing in the transcript tells you which it did.

This server takes the third path: give the agent the model — tables,columns, types, keys, relationships, history — over a protocol that isread-only by construction and has no access to row data at all.

Claude / Cursor / any MCP client
            │  JSON-RPC 2.0 over stdio
            ▼
        server.py            ← 15 MCP tools
            │
     introspect.py           ← SELECT + SHOW only
            │
     information_schema      ← never table contents

What it exposes, and what it cannot

Every query the server issues is a SELECT against information_schema or aSHOW CREATE TABLE. There is no write path to disable, because none was everimplemented.

Requirements

• macOS or Linux
• Python 3.10+ (developed on 3.12)
• MySQL 8.x running locally, reachable as a user that can readinformation_schema
• Node.js — optional, only for the MCP Inspector

Quick start

git clone <repo-url>
cd sql-mcp
bash scripts/bootstrap.sh

That one command checks your prerequisites, creates the virtualenv, installsdependencies, creates the demo databases, and verifies the server over a realMCP session. It is safe to re-run, and it stops with a specific message at thefirst missing piece rather than failing three steps later.

Then pick any of these:

bash scripts/run_demo_client.sh   # scripted walkthrough in the terminal
bash scripts/run_dashboard.sh     # web UI at http://127.0.0.1:5050
bash scripts/run_inspector.sh     # the official MCP Inspector (needs node)

Configuration

MySQL defaults to root on 127.0.0.1:3306 with no password — which is theHomebrew default, so most people need to change nothing.

If yours differs, bootstrap.sh creates a .env.local on first run; edit it:

MYSQL_USER=me
MYSQL_PASSWORD=secret

.env.local is gitignored, and real environment variables take precedence overit, so PORT=5051 bash scripts/run_dashboard.sh still works.

Why a file rather than exported variables: the MCP SDK deliberately doesnot pass a parent process's arbitrary environment through to a spawned server— it inherits only a small safe-list. Settings exported by a shell scripttherefore never reach the server. Reading .env.local inside the server meansthe same configuration applies no matter what launches it: a script, thedashboard, the Inspector, Claude Code, or Claude Desktop.

The same variables (MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD)are read by the server itself at runtime.

Scoping which schemas are visible

The server would otherwise discover every non-system schema on theconnection. On a laptop that also holds real databases that is the wrongdefault — they would appear in the project list mid-demo, on a shared screen.

So the scripts and both installers scope the server to the demo schemas:

MYSQL_SCHEMA_ALLOWLIST=schema_mcp_demo,shop_demo_dev,shop_demo_local

Two variables control this, and the allowlist wins if both are set:

The filter applies to discovery and direct access alike — a hidden schemacannot be reached by naming it explicitly, so an agent cannot guess its way in.To widen the scope, edit MYSQL_SCHEMA_ALLOWLIST in .mcp.json, in yourClaude Desktop config entry, or in scripts/_common.sh.

Snapshots are excluded by .gitignore. A snapshot file contains the completeCREATE TABLE output for whatever schema it captured, so a snapshot of a realdatabase is effectively a dump of that database's structure — never commit orshare one.

The demo databases

scripts/setup_db.sh creates two independent playgrounds. Both are safe todrop, edit, and recreate — nothing else depends on them.

schema_mcp_demo — the main sandbox

Two tables chosen so that every feature has something to show:

shop_demo_dev + shop_demo_local — the drift playground

Discovered as one project named shop_demo with two environments,because the server groups schemas sharing a prefix before a known environmentsuffix. The two sides are deliberately out of sync, reproducing the kinds ofdrift that accumulate in real deployments:

• a table in dev only (PRICE_HISTORY) and one in local only (LEGACY_IMPORT_STAGING)
• a column added in dev but never applied (DISCONTINUED)
• a renamed column (SHIP_NOTES vs SHIPNOTES)
• inconsistent identifier case (ID vs id)
• a widened type (VARCHAR(120) vs VARCHAR(50))
• a nullability change on SHIPMENT.CARRIER

Ask for an alter script between them and all six show up.

The three ways to run it

1. MCP Inspector — the most convincing

Anthropic's official MCP debugging client. None of it is our code, so if theInspector can drive the server, the server is genuinely spec-compliant.

bash scripts/run_inspector.sh

It prints a http://localhost:6274?MCP_INSPECTOR_API_TOKEN=… URL — open thatexact URL, the token is required. Then:

1. Toggle the server to Connected
2. Open the Tools tab — all 15 tools, with forms generated from their JSON Schemas
3. Run get_project_latest_revision with project = schema_mcp_demo, query = keys(tables)
4. Expand a message in the right-hand panel to see the raw JSON-RPC

2. Web dashboard — what a product on top of MCP looks like

bash scripts/run_dashboard.sh          # http://127.0.0.1:5050
PORT=8080 bash scripts/run_dashboard.sh

The dashboard is itself an MCP client — it does not read MySQL directly.Flask calls mcp_bridge.call_tool(...), which sends a real tools/call overstdio to server.py. The MCP Activity console pinned to the bottom of thepage shows every call live, with arguments and timing.

Tabs: Overview · Tables & Columns · DDL · Query Console · SensitiveColumns · Inferred Relationships · Revisions · Compare / Alter Script.

The Query Console is the best place to feel the protocol: type a JMESPathexpression, press Run as MCP tool call, and watch it appear in the console.

3. Scripted client — the developer surface

bash scripts/run_demo_client.sh                # schema_mcp_demo
bash scripts/run_demo_client.sh shop_demo      # the drifted project

Opens a real MCP session, lists the advertised tools, and calls each one insequence. This is the code an agent developer actually writes.

Connecting it to Claude

Claude Code

bash scripts/install_claude_code.sh

Writes a project-scoped .mcp.json. Open a Claude Code session with thisfolder as the working directory and approve the server once. Safe to run frominside a Claude Code session.

With the standalone claude CLI, the equivalent is:

claude mcp add local-schema-mcp --scope project \
  -- "$PWD/venv/bin/python" server.py

Claude Desktop

bash scripts/install_claude_desktop.sh

Run this from Terminal.app, not from Claude Code inside Claude Desktop.The script quits Claude Desktop, which would kill the session you launched it from.

Why it has to quit the app: Claude Desktop loadsclaude_desktop_config.json into memory at startup and rewrites the whole filefrom that copy whenever preferences change. An edit made while the app isrunning gets silently discarded on the next flush. The only reliable order isquit → edit → relaunch, which is what the script does. It backs up yourconfig first, preserves any servers already registered, validates the JSON, andsmoke-tests the server before relaunching.

One more detail worth knowing: the entry is registered as a singlebash -c "cd <proj> && exec <venv>/bin/python server.py". Claude Desktop'sstdio schema defines command, args, and env — but not cwd, so thedirectory change has to live inside the command itself.

If the connector does not appear, check:

tail -50 ~/Library/Logs/Claude/mcp-server-local-schema-mcp.log

If that file does not exist at all, the app never tried to launch the server —which means the config edit did not stick.

The tool surface

Twelve tools mirroring the SqlDBM server, plus three clearly-marked extras.

The surface is deliberately small. Every tool description and JSON Schema isspent from the model's context budget, so a tight API is a design requirement,not a limitation.

Why the extras exist

• get_project_sensitive_columns — pattern-matches column names andcomments against common PII and secret indicators. Pattern-matching only, nota formal classification field: treat results as a lead, not a verdict.
• get_project_inferred_relationships — guesses relationships from namingconvention (REQUEST_ID → REQUEST_DETAILS) for schemas that declare no realforeign keys, which is extremely common in practice.
• create_schema_snapshot — a live MySQL database has no revision history.SqlDBM gets that for free from its own model editor; here you capture point-in-timesnapshots into snapshots/ so there is something to diff against later.

Filtered model queries

The model-query tools require a query expression. A full enterprise modelcan exceed any context window, so an unfiltered request is not permitted.SqlDBM uses JQ; this implementation usesJMESPath. Call get_schema_guide for the fullreference — it ships with the model shape and a categorized list of workingqueries.

The one gotcha

tables.* is a projection and will not flatten for filtering:

tables.*.columns.* | [] | [?identity==`true`]      → null   ✗
values(tables)[].columns.* | [] | [?identity==`true`] → works ✓

To filter across all tables, start from values(tables)[].

Queries worth knowing

keys(tables)                                   # every table name
length(keys(tables))                           # table count
length(tables.*.column_order[])                # total column count
tables.ORDERS                                  # one table, in full
tables.ORDERS.columns | keys(@)                # just its column names
tables.*.primary_key                           # PK columns per table

# every foreign key, as readable triples
values(tables)[].foreign_keys[].[column, references_table, references_column]

# column-level filters — note the values(tables)[] prefix
values(tables)[].columns.* | [] | [?identity==`true`]     # auto-increment
values(tables)[].columns.* | [] | [?nullable==`false`]    # NOT NULL
values(tables)[].columns.* | [] | [?default!=`null`]      # has a default
values(tables)[].indexes.* | [] | [?unique==`true`]       # unique indexes

Questions to ask an agent

Discovery

• What database projects can you see?
• What environments does shop_demo have?
• How many tables and columns are in schema_mcp_demo?

Schema Q&A

• Describe the ORDERS table.
• What are the foreign key relationships in schema_mcp_demo?
• Which columns are auto-increment?
• Show me the DDL for PRODUCT in shop_demo dev.

The ones that land

• Are there columns that look like they hold PII or secrets?
• Generate an alter script to bring shop_demo local in line with dev.
• shop_demo local has no foreign keys — infer the relationships from naming.
• Compare revision 1 and revision 5 of schema_mcp_demo.

Multi-step

• Audit schema_mcp_demo: list the tables, find sensitive columns, and tell me which lack a primary key.
• Snapshot schema_mcp_demo labeled "before demo", then tell me what changed since revision 1.
• I need to join customers to their orders — what columns do I have to work with?

Proving the guardrails — these should be refused, which is the point

• How many rows are in CUSTOMERS? → it has no row access
• Drop the ORDERS table. → no write path exists

Layout

SQL MCP/
├── server.py              MCP server — the 15 tool definitions
├── introspect.py          MySQL introspection, model building, diffing
├── demo_client.py         scripted MCP client walkthrough
├── .mcp.json              Claude Code registration (generated)
├── snapshots/             captured revisions, one JSON per revision
├── webapp/
│   ├── app.py             Flask JSON API — calls MCP, never MySQL
│   ├── mcp_bridge.py      persistent MCP client session + call log
│   ├── templates/
│   └── static/
└── scripts/
    ├── _common.sh                 shared path/MySQL helpers
    ├── setup.sh                   create venv, install dependencies
    ├── setup_db.sh                create both demo databases
    ├── create_demo_db.sql         schema_mcp_demo
    ├── create_drift_demo.sql      shop_demo_dev / shop_demo_local
    ├── run_dashboard.sh           launch the web UI
    ├── run_inspector.sh           launch the MCP Inspector
    ├── run_demo_client.sh         run the scripted walkthrough
    ├── install_claude_code.sh     register with Claude Code
    └── install_claude_desktop.sh  register with Claude Desktop

Every script resolves paths from its own location, so the folder can be renamedor moved freely. After moving it, re-run scripts/setup.sh (a virtualenv bakesabsolute paths into bin/activate) and re-run whichever installer you use.

Troubleshooting

cannot connect to MySQL — is it running? brew services start mysql.If your setup needs a password, prefix the command with MYSQL_PASSWORD=….

Connector missing in Claude Desktop — see the note above about the appoverwriting its own config. Check for~/Library/Logs/Claude/mcp-server-local-schema-mcp.log; if it does not exist,the server was never launched.

Server starts but every tool errors — MySQL is down, or the configured usercannot read information_schema.

A JMESPath query returns null — you probably hit the tables.* projectiongotcha above. Start from values(tables)[].

Port already in use — PORT=5051 bash scripts/run_dashboard.sh.

Security notes

Read-only and metadata-only are enforced by construction, not by configuration —but two things are worth stating plainly for anyone deploying this beyond a demo:

• Prompt injection is unsolved. A column comment is untrusted input. If anagent reads a comment containing instructions, treat that as data, never as acommand. This is an open problem across the whole MCP ecosystem, not aproperty of this server.
• Pin the version and log every call. A changed tool description silentlychanges model behaviour, because descriptions are what the model reads whendeciding which tool to use. Review them on upgrade.

The connecting MySQL user is the real security boundary. Give it a read-onlygrant scoped to the schemas you intend to expose; the server never widensaccess beyond what that user can already see.