# mysql-mcp-demo

A small, heavily-commented **MCP server for MySQL** that demonstrates all three
Model Context Protocol primitives — **tools**, **resources**, and **prompts** —
in about 1,100 lines of Python.

This repository exists to be **read**, not just run. It is the companion to a
workshop on building MCP servers, and every file is written as teaching
material: one primitive per file, comments that explain *why* rather than
*what*, and a demo database with deliberate flaws so the examples find something
real.

```
mcp_server/
├── database.py    read-only introspection — the only file not about MCP
├── execution.py   running queries and writes, plus every safety control
├── tools.py       6 TOOLS   — inspect structure (cannot read or change a row)
├── data_tools.py  6 TOOLS   — read rows, and INSERT / UPDATE / DELETE / ALTER
├── resources.py   4 RESOURCES + 2 templates — content the APPLICATION attaches
├── prompts.py     6 PROMPTS — workflows the USER invokes
└── server.py      wires them together (about 10 meaningful lines)
```

The server is **read-write**: it answers questions about the data by running
real queries, and it can change data and schema. It is locked to a single
throwaway demo database, and the controls that make that safe are in
`execution.py` and explained below — that design is itself part of the lesson.

---

## The one idea worth taking away

Most MCP tutorials only cover tools, which leaves people thinking MCP *is*
tools. It is three primitives, and they differ by **who is in control**:

| Primitive | Who decides | When it happens | Analogy |
|---|---|---|---|
| **Tool** | the **model** | mid-conversation, autonomously | a function the model may call |
| **Resource** | the **application** | up front, chosen by a human | a file you attach |
| **Prompt** | the **user** | explicitly, from a menu | a saved expert question |

Same data can appear as more than one. In this repo `get_table_ddl` is a tool
*and* `schema://table/{name}/ddl` is a resource — the same bytes, reached two
ways, because "the model fetches it when it needs it" and "the human attaches it
before starting" are genuinely different needs.

---

## Quick start

```bash
git clone https://github.com/Khushboo-Mishra/mysql-mcp-demo.git
cd mysql-mcp-demo
bash scripts/setup.sh
```

`setup.sh` checks prerequisites, creates the virtualenv, installs the two
dependencies, creates the demo database, and verifies the server end to end. It
stops with a specific message at the first thing that is missing.

Then see all three primitives in one pass:

```bash
bash scripts/run_explorer.sh
```

### Requirements

- **Python 3.10+**
- **MySQL 8.x** running locally (`brew services start mysql`)
- **Node.js** — optional, only for the MCP Inspector

Defaults to `root` on `127.0.0.1:3306` with no password — the Homebrew default,
so most people change nothing. Otherwise export `MYSQL_USER`, `MYSQL_PASSWORD`,
`MYSQL_HOST`, `MYSQL_PORT`.

---

## What gets built

**12 tools, 4 resources + 2 URI templates, and 6 prompts**, over a six-table
demo database.

### Tools — the model calls these

Split across two files **by blast radius**, not by subsystem. That is a
deliberate design choice worth copying: it keeps the risky surface small and
obvious to anyone reviewing the server or writing its database GRANT.

`tools.py` — inspect structure. Cannot read a row, cannot change anything.

| Tool | Purpose |
|---|---|
| `list_tables` | every table and view, with row estimates |
| `describe_table(table)` | columns, types, keys, indexes, foreign keys |
| `get_table_ddl(table)` | the exact `CREATE TABLE` |
| `list_relationships` | every declared foreign key |
| `find_sensitive_columns` | columns whose *name* suggests PII or secrets |
| `search_columns(keyword)` | find a column when you forget which table it is in |

`data_tools.py` — read rows and change data. This is the half with consequences.

| Tool | Purpose |
|---|---|
| `run_query(sql, limit)` | run a SELECT and get the rows — this is what answers data questions |
| `execute_statement(sql)` | INSERT / UPDATE / DELETE / CREATE / ALTER / DROP / TRUNCATE |
| `insert_row(table, values)` | structured insert, values sent as bound parameters |
| `update_rows(table, changes, where)` | structured update, `where` required |
| `delete_rows(table, where)` | structured delete, `where` required |
| `show_audit_log(limit)` | every statement the server has executed |

**Why both a general `execute_statement` and structured wrappers?** Structured
tools are safer — arguments are typed and values are bound, so the model never
writes SQL text and cannot produce something malformed. But they only do what
you anticipated. A general SQL door handles the long tail: window functions,
an `ALTER` you did not foresee. Real servers ship both, and the walkthrough
should say why.

### Resources — the application attaches these

| URI | Type | Contents |
|---|---|---|
| `schema://tables` | JSON | table inventory |
| `schema://ddl` | SQL | DDL for the whole schema |
| `schema://relationships` | JSON | all foreign keys |
| `schema://overview` | Markdown | human-readable summary |
| `schema://table/{name}` | JSON | one table — **templated** |
| `schema://table/{name}/ddl` | SQL | one table's DDL — **templated** |

A **static** resource has a fixed URI and appears in `resources/list`, so a
client can show it in a picker. A **templated** resource has `{placeholders}`
and appears in `resources/templates/list` instead — there is no fixed list, so
the client fills in the blank.

### Prompts — the user invokes these

| Prompt | Arguments | What it does |
|---|---|---|
| `audit_schema` | — | five-step health check: keys, relationships, PII, naming |
| `explain_table` | `table` | explains one table in plain language |
| `ask_data` | `question` | writes the query, **runs it**, and answers in plain language |
| `modify_data` | `request` | preview → confirm → apply → verify, for changes |
| `document_schema` | — | generates reference documentation |
| `onboarding_tour` | `role` | a guided first look, tailored to a role |

---

## Deciding: tool, resource, or prompt?

The question people get stuck on. Work through it in this order.

**1. Does it perform an action, or fetch something the model chooses?**
→ **Tool.** Anything the model should be able to decide to do on its own.

**2. Is it a document a human would sensibly attach before starting?**
→ **Resource.** Reference material, whole-schema context, anything stable.

**3. Is it a task someone repeats, where the *way you ask* is the expertise?**
→ **Prompt.** Ship the good question instead of expecting rediscovery.

Two heuristics that resolve most remaining doubt:

> **Who initiates?** Model → tool. Application → resource. User → prompt.

> **Would you want this in a menu?** If yes, it is a prompt. Menus are for
> people, and only prompts are surfaced to people as commands.

### Worked examples from this repo

| Feature | Choice | Why |
|---|---|---|
| Fetch one table's structure | **tool** | the model needs it mid-reasoning, unpredictably |
| Whole-schema DDL | **both** | tool for the model; resource for a human to attach up front |
| Schema audit | **prompt** | a repeatable task where knowing *what to ask* is the value |
| Search for a column | **tool** | takes an argument the model chooses at call time |
| Markdown overview | **resource** | passive reference, no decision required |

### Where people get it wrong

- **Everything as tools.** Works, but the model burns calls fetching context a
  human could have attached once — and users get no discoverable entry points.
- **Resources for things that need arguments the model picks.** If the model
  decides the parameter, it is a tool.
- **Prompts that do work.** A prompt returns *text*. If you find yourself
  querying the database inside a prompt, you wanted a tool.

---

## The demo database

`mcp_demo`, six tables, deliberately imperfect so the examples find real
problems:

| Table | Deliberate flaw |
|---|---|
| `CUSTOMERS` | `EMAIL`, `PHONE` — the sensitive-column scan fires |
| `PRODUCTS` | `SKU` is `UNIQUE` but not the PK — a natural key worth discussing |
| `ORDERS` | (clean — the reference example) |
| `ORDER_ITEMS` | `PRODUCT_ID` looks like a foreign key but **has no constraint** |
| `AUDIT_LOG` | **no primary key at all** |
| `legacy_notes` | `snake_case` while everything else is `UPPER_CASE` |

Run `audit_schema` against it and every one of those should surface. That is the
demo: the tools find genuine problems, not toy ones.

---

## Running it

### The explorer — every primitive in one pass

```bash
bash scripts/run_explorer.sh
```

Prints the `initialize` handshake, then lists and exercises tools, resources
(static *and* templated), and prompts. Best first thing to run, and the clearest
thing to show on a terminal during a talk.

### The MCP Inspector — Anthropic's own client

```bash
bash scripts/run_inspector.sh
```

Open the printed `http://localhost:6274?...` URL — the token is required. It has
separate **Tools**, **Resources**, and **Prompts** tabs, which is the most
convincing way to show all three: none of it is our code, so if the Inspector
drives the server, the server is genuinely spec-compliant.

Suggested tour: **Tools** → `describe_table` with `ORDERS`; **Resources** →
`schema://overview`; **Prompts** → `audit_schema`.

### Claude Desktop / Claude Code

```bash
bash scripts/install_claude.sh              # Claude Code
bash scripts/install_claude.sh --desktop    # also Claude Desktop
```

Then ask: *"Audit this database"* — or use the `audit_schema` prompt from the
menu, which is where prompts finally become visible.

> **`--desktop` must be run from Terminal.app, not from inside Claude Desktop.**
> Claude Desktop holds its config in memory and rewrites the file from that
> copy, so an edit made while it is running is silently discarded. The script
> quits the app, edits, and relaunches — which would kill the session you
> launched it from.

---

## Code walkthrough order

For presenting, this order builds up cleanly:

1. **`server.py`** — 10 lines. The whole architecture in one screen.
2. **`database.py`** — plain MySQL, no MCP. Establishes that MCP is a thin layer
   over code you already have. Stop on `safe_identifier` and explain why table
   names cannot be bound parameters.
3. **`tools.py`** — the decorator, and how the docstring *is* the prompt the
   model reads.
4. **`resources.py`** — static vs templated URIs, and why `get_table_ddl` is
   deliberately duplicated as a resource.
5. **`prompts.py`** — that a prompt returns text, and that the text tells the
   model which tools to use.
6. **`examples/explore_server.py`** — the client side, showing what actually
   crosses the wire.

---

## Going further

This server is scoped to one database to keep the examples short. To take it
further:

- **Multiple schemas** — take `schema` as a tool argument rather than reading
  `MYSQL_DEMO_SCHEMA`. Add an allowlist so an agent cannot reach production.
- **Query execution** — a `run_query` tool. Doable, but it changes the security
  story completely: the server then needs credentials that read your tables, and
  results enter the model's context. Enforce `SELECT`-only, inject a `LIMIT`, and
  use a read-only database user.
- **Remote transport** — `mcp.run(transport="streamable-http")`. Same tools,
  same code, different pipe. Add authentication before exposing it.
- **Caching** — `describe_table` hits the database on every call. A short TTL
  cache is worth it once a model starts calling it in a loop.

---

## Security notes

This server **can change your data**. That is a deliberate choice for a
workshop — showing how to build write capability *safely* is more useful than
pretending the question never comes up — but it means the controls matter.

### The five controls, all in `execution.py`

| Control | What it stops |
|---|---|
| **Schema lock** | every statement runs on a connection pinned to the demo database; a reference to any other database is refused |
| **One statement per call** | a second statement cannot ride along on a legitimate one |
| **Separate read/write doors** | `run_query` refuses to write and `execute_statement` refuses to read, so neither can be talked into the other's job |
| **Row cap** | a broad `SELECT` cannot flood the model's context |
| **Audit log** | every statement is recorded and readable via `show_audit_log` |

A denylist also refuses statements that would escape the schema lock, reach the
filesystem, or change server-wide state — privilege changes, user management,
file import/export, and database-level operations.

One subtlety worth showing in a walkthrough: the schema lock cannot work by
pattern alone, because `a.b` in SQL is usually `alias.column` (`SELECT c.NAME
FROM CUSTOMERS c`), not `schema.table`. Rejecting every dotted name breaks
ordinary joins — which is exactly the bug the first version had. So it compares
each qualifier against the **actual list of databases on the server**: a real
database name is refused, a table alias passes untouched.

### Point it at a restricted user

The controls above are defense in depth, **not** the defense. In anything beyond
a demo, connect as a MySQL user whose grant covers only the schema you intend to
expose. If the credentials cannot reach production, neither can a
prompt-injection or a model mistake.

### Two more things worth stating plainly:

- **Table names cannot be bound parameters.** `SHOW CREATE TABLE %s` is not
  valid SQL, so identifiers must be interpolated — a genuine injection sink.
  `database.safe_identifier` is what makes it safe, and it is the single most
  important function in the project.
- **The connecting MySQL user is the real boundary.** Give it a read-only
  `GRANT` scoped to the schemas you mean to expose. The code's read-only-ness is
  defense in depth, not the defense.

---

## License

MIT — see [LICENSE](LICENSE).