SQL MCP

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

It is a local reimplementation of the SqlDBM MCP Server concept: an AI can discover projects, read the full data model as structured JSON, retrieve DDL, inspect revision history, and generate migration scripts between revisions or environments — while never being able to read a single row or 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 invents plausible 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 a documentation problem for a security one. Now it can read PII and write to production, 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 is read-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

Exposes	Cannot
Databases, schemas, tables, views	Read a single row of data — ever
Columns: type, nullability, identity, default, comment	Create, modify, or delete anything
Indexes, primary keys, foreign keys	Return an unfiltered model (a query expression is required)
Revision history, environments, alter scripts	Reach any schema the connecting MySQL user cannot already see

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

---

Requirements

• macOS or Linux
• Python 3.10+ (developed on 3.12)
• MySQL 8.x running locally, reachable as a user that can read information_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, installs dependencies, creates the demo databases, and verifies the server over a real MCP session. It is safe to re-run, and it stops with a specific message at the first 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 the Homebrew 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 over it, so PORT=5051 bash scripts/run_dashboard.sh still works.

Why a file rather than exported variables: the MCP SDK deliberately does not pass a parent process's arbitrary environment through to a spawned server — it inherits only a small safe-list. Settings exported by a shell script therefore never reach the server. Reading .env.local inside the server means the same configuration applies no matter what launches it: a script, the dashboard, 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 the connection. On a laptop that also holds real databases that is the wrong default — 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:

Variable	Effect
MYSQL_SCHEMA_ALLOWLIST	Only these schemas are reachable
MYSQL_SCHEMA_DENYLIST	Everything except these
both empty	Every non-system schema (original behaviour)

The filter applies to discovery and direct access alike — a hidden schema cannot 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 your Claude Desktop config entry, or in scripts/_common.sh.

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

---

The demo databases

scripts/setup_db.sh creates two independent playgrounds. Both are safe to drop, 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:

Table	Notable
CUSTOMERS	AUTO_INCREMENT PK, UNIQUE index on EMAIL, a column COMMENT, and EMAIL/PHONE for the PII scan
ORDERS	A real FOREIGN KEY to CUSTOMERS, plus DEFAULT values

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 environment suffix. The two sides are deliberately out of sync, reproducing the kinds of drift 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 the Inspector 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 that exact 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 over stdio to server.py. The MCP Activity console pinned to the bottom of the page shows every call live, with arguments and timing.

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

The Query Console is the best place to feel the protocol: type a JMESPath expression, 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 in sequence. 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 this folder as the working directory and approve the server once. Safe to run from inside 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 loads claude_desktop_config.json into memory at startup and rewrites the whole file from that copy whenever preferences change. An edit made while the app is running gets silently discarded on the next flush. The only reliable order is quit → edit → relaunch, which is what the script does. It backs up your config first, preserves any servers already registered, validates the JSON, and smoke-tests the server before relaunching.

One more detail worth knowing: the entry is registered as a single bash -c "cd <proj> && exec <venv>/bin/python server.py". Claude Desktop's stdio schema defines command, args, and env — but not cwd, so the directory 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.

Area	Tools
Discovery	get_projects
Model query (filtered)	get_project_latest_revision, get_project_revision, get_schema_guide
DDL retrieval	get_project_latest_ddl, get_project_ddl, get_project_object_latest_ddl, get_project_object_ddl
Revision history	get_project_revisions
Environments & migration	get_project_environments, get_project_alter_script, get_project_compare_alter_script
Demo extras	get_project_sensitive_columns, get_project_inferred_relationships, create_schema_snapshot

The surface is deliberately small. Every tool description and JSON Schema is spent 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 and comments against common PII and secret indicators. Pattern-matching only, not a formal classification field: treat results as a lead, not a verdict.
• get_project_inferred_relationships — guesses relationships from naming convention (REQUEST_ID → REQUEST_DETAILS) for schemas that declare no real foreign 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-time snapshots into snapshots/ so there is something to diff against later.

---

Filtered model queries

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

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 renamed or moved freely. After moving it, re-run scripts/setup.sh (a virtualenv bakes absolute 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 app overwriting 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 user cannot read information_schema.

A JMESPath query returns null — you probably hit the tables.* projection gotcha 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 an agent reads a comment containing instructions, treat that as data, never as a command. This is an open problem across the whole MCP ecosystem, not a property of this server.
• Pin the version and log every call. A changed tool description silently changes model behaviour, because descriptions are what the model reads when deciding which tool to use. Review them on upgrade.

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