# SQL MCP

A working, **read-only** [Model Context Protocol](https://modelcontextprotocol.io) server that
exposes a MySQL database's *schema* — not its data — to AI agents.

It is a local reimplementation of the [SqlDBM MCP Server](https://mcp.sqldbm.com)
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
- **[Ollama](https://ollama.com)** — optional, only for the SQL Chat tab
  (`brew install ollama && ollama pull qwen2.5:7b`)

---

## Quick start

```bash
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
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:

```bash
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
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
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** · **SQL Chat** ·
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.

#### SQL Chat — plain English to a runnable query

The one tab that uses an LLM. Ask a question; you get SQL back.

```
your question
    -> the app fetches the schema through the MCP server (a real tools/call)
    -> an LLM turns schema + question into SQL
    -> THE APP runs it, guarded
    -> results render in your browser
```

**The model never sees the results.** It writes the query and stops. You click
Run, the app executes it, and the rows appear on the page — they are never sent
back to the LLM. That keeps the project's central claim true end to end: no
row data reaches a model, ever.

Execution is deliberately in the app, not in the MCP server. The server stays
schema-only, which is what makes it safe to publish and share.

**Default backend is Ollama** — local, free, no API key, nothing leaves your
machine:

```bash
brew install ollama
ollama serve
ollama pull qwen2.5:7b
```

`qwen2.5:7b` is the recommended model; it reliably produces correct joins from a
schema. Set `ANTHROPIC_API_KEY` instead and SQL Chat switches to the Claude API
automatically — no code change. Configure either in `.env.local`.

Every execution is guarded:

| Guard | Effect |
|---|---|
| `SELECT` / `WITH` only | anything else is rejected before it reaches MySQL |
| Single statement | no `;`-chaining; checked after stripping comments |
| Row cap | `LIMIT 500` injected when the query has none |
| Time cap | `MAX_EXECUTION_TIME` so nothing table-scans a warehouse |
| Schema scope | runs against the selected project only, honouring the allowlist |
| Audit log | every executed statement recorded, at `/api/chat/audit` |

None of that replaces pointing this at a MySQL user with a read-only `GRANT` —
it is defense in depth, not the defense.

### 3. Scripted client — the developer surface

```bash
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
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:

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

### Claude Desktop

```bash
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:

```bash
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](https://jmespath.org). 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

```jmespath
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
├── requirements.txt       Python dependencies
├── .env.example           config template — copy to .env.local
├── .env.local             your local settings (gitignored, created by bootstrap)
├── .mcp.json              Claude Code registration (gitignored, generated)
├── .gitignore             excludes venv, snapshots, and local config
├── LICENSE                MIT
├── snapshots/             captured revisions (gitignored — see the warning above)
├── webapp/
│   ├── app.py             Flask JSON API — calls MCP, never MySQL
│   ├── mcp_bridge.py      persistent MCP client session + call log
│   ├── sql_chat.py        SQL Chat: LLM backends + guarded query execution
│   ├── templates/
│   │   └── index.html
│   └── static/
│       ├── app.js
│       └── style.css
└── scripts/
    ├── _common.sh                 shared path/MySQL helpers, .env.local loader
    ├── bootstrap.sh               one command: prereqs → venv → databases → verify
    ├── 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`.

**SQL Chat says it cannot reach Ollama** — start the server with `ollama serve`
(it does not run on its own after install). Confirm with
`curl localhost:11434/api/tags`.

**SQL Chat says the model is not installed** — `ollama pull qwen2.5:7b`, or point
`OLLAMA_MODEL` in `.env.local` at a model you already have. `ollama list` shows
them. Not every model supports this well; `qwen2.5:7b` is the tested default.

**SQL Chat is slow on the first question** — a cold model load takes a few
seconds. Later questions run in about two. Raise `SQL_CHAT_LLM_TIMEOUT_S` if you
are on a larger model.

**The generated SQL is wrong** — the model only sees the schema, never your data,
so it cannot know that a status column holds `'PENDING'` rather than `'pending'`.
Edit the query before running it; that is what the editable SQL box is for.

---

## 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.