mysql-mcp-demo
A small, heavily-commented MCP server for MySQL that demonstrates all threeModel 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 aworkshop on building MCP servers, and every file is written as teachingmaterial: one primitive per file, comments that explain why rather thanwhat, and a demo database with deliberate flaws so the examples find somethingreal.
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 runningreal queries, and it can change data and schema. It is locked to a singlethrowaway demo database, and the controls that make that safe are inexecution.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 istools. 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 tooland schema://table/{name}/ddl is a resource — the same bytes, reached twoways, because "the model fetches it when it needs it" and "the human attaches itbefore 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 twodependencies, creates the demo database, and verifies the server end to end. Itstops 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-tabledemo database.
Tools — the model calls these
Split across two files by blast radius, not by subsystem. That is adeliberate design choice worth copying: it keeps the risky surface small andobvious 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? Structuredtools are safer — arguments are typed and values are bound, so the model neverwrites SQL text and cannot produce something malformed. But they only do whatyou anticipated. A general SQL door handles the long tail: window functions,an ALTER you did not foresee. Real servers ship both, and the walkthroughshould 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 aclient can show it in a picker. A templated resource has {placeholders}and appears in resources/templates/list instead — there is no fixed list, sothe 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 forpeople, 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 ahuman could have attached once — and users get no discoverable entry points.
- Resources for things that need arguments the model picks. If the modeldecides the parameter, it is a tool.
- Prompts that do work. A prompt returns text. If you find yourselfquerying the database inside a prompt, you wanted a tool.
The demo database
mcp_demo, six tables, deliberately imperfect so the examples find realproblems:
| 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 thedemo: 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 clearestthing 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 hasseparate Tools, Resources, and Prompts tabs, which is the mostconvincing way to show all three: none of it is our code, so if the Inspectordrives 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 themenu, which is where prompts finally become visible.
--desktopmust be run from Terminal.app, not from inside Claude Desktop.Claude Desktop holds its config in memory and rewrites the file from thatcopy, so an edit made while it is running is silently discarded. The scriptquits the app, edits, and relaunches — which would kill the session youlaunched it from.
Code walkthrough order
For presenting, this order builds up cleanly:
server.py— 10 lines. The whole architecture in one screen.database.py— plain MySQL, no MCP. Establishes that MCP is a thin layerover code you already have. Stop onsafe_identifierand explain why tablenames cannot be bound parameters.tools.py— the decorator, and how the docstring is the prompt themodel reads.resources.py— static vs templated URIs, and whyget_table_ddlisdeliberately duplicated as a resource.prompts.py— that a prompt returns text, and that the text tells themodel which tools to use.examples/explore_server.py— the client side, showing what actuallycrosses the wire.
Going further
This server is scoped to one database to keep the examples short. To take itfurther:
- Multiple schemas — take
schemaas a tool argument rather than readingMYSQL_DEMO_SCHEMA. Add an allowlist so an agent cannot reach production. - Query execution — a
run_querytool. Doable, but it changes the securitystory completely: the server then needs credentials that read your tables, andresults enter the model's context. EnforceSELECT-only, inject aLIMIT, anduse 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_tablehits the database on every call. A short TTLcache 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 aworkshop — showing how to build write capability safely is more useful thanpretending 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 thefilesystem, 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 bypattern alone, because a.b in SQL is usually alias.column (SELECT c.NAME FROM CUSTOMERS c), not schema.table. Rejecting every dotted name breaksordinary joins — which is exactly the bug the first version had. So it compareseach qualifier against the actual list of databases on the server: a realdatabase 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 beyonda demo, connect as a MySQL user whose grant covers only the schema you intend toexpose. If the credentials cannot reach production, neither can aprompt-injection or a model mistake.
Two more things worth stating plainly:
- Table names cannot be bound parameters.
SHOW CREATE TABLE %sis notvalid SQL, so identifiers must be interpolated — a genuine injection sink.database.safe_identifieris what makes it safe, and it is the single mostimportant function in the project. - The connecting MySQL user is the real boundary. Give it a read-only
GRANTscoped to the schemas you mean to expose. The code's read-only-ness isdefense in depth, not the defense.
License
MIT — see LICENSE.
