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:

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

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

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

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

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

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

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:

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

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.