SQL-MCP-101
New to MCP? Start with the interactive tutorial, a click-through walk through tools, resources and prompts, and how to decide which one a feature should be.
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, plus a browser UI for exploring it.
This repository exists to be read, not just run. If you have seen MCPmentioned and want to understand what building a server actually involves, thisis a complete, working example small enough to read in one sitting: oneprimitive per file, comments that explain why rather than what, and a demodatabase with deliberate flaws so the examples find real problems instead oftoy ones.
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 content the APPLICATION attaches (+2 templates)
├── 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 twodifferent ways, because "the model fetches it when it decides it needs it" and"a human attaches it before starting" are genuinely different needs.
Quick start
git clone https://github.com/Khushboo-Mishra/SQL-MCP-101.git
cd SQL-MCP-101
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
- Ollama: optional, only for the UI's Chat panel
Defaults to root on 127.0.0.1:3306 with no password, which is the Homebrewdefault, 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 back; 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. Most real servers end up shipping both, forexactly that reason.
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 toshow, so the client fills in the blank.
Prompts: the user invokes these
| Prompt | Arguments | What it does |
|---|---|---|
audit_schema |
none | 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 |
none | 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. Run this first, it confirms the setupworks and shows the entire protocol surface in one screenful.
The web UI: all three primitives in a browser
bash scripts/run_ui.sh # http://127.0.0.1:8000
PORT=9000 bash scripts/run_ui.sh
Four panels, one per thing worth showing:
| Panel | What it demonstrates |
|---|---|
| Chat | ask in plain English; every tool the model chose is listed inline above the answer |
| Tools | all 12, grouped by blast radius, each callable from a form |
| Resources | static and templated, readable in place |
| Prompts | expand one to see the text, or send it straight to the chat |
A live Activity strip along the bottom shows the real JSON-RPC underneath,tools/call, resources/read, prompts/get, so the protocol is visible thewhole time.
The page is itself an MCP client: it has no access to MySQL of its own.Everything on screen arrived through the same protocol Claude Desktop uses.
Chat needs a local LLM via Ollama, free, no API key, andnothing leaves the machine:
brew install ollama && ollama serve
ollama pull qwen2.5:7b
Set ANTHROPIC_API_KEY instead and it switches to the Claude API automatically.The Tools, Resources and Prompts panels work with no LLM at all.
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/add_to_claude_desktop.sh # Claude Desktop, run from Terminal.app
bash scripts/install_claude.sh # Claude Code, safe to run anywhere
add_to_claude_desktop.sh backs up your config, preserves any servers alreadyregistered, validates the JSON, smoke-tests the exact launch command, andrelaunches the app. It prints a suggested demo script when it finishes.
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.
Reading the code
Roughly an hour end to end. This order builds up without forward references:
1. mcp_server/server.py: start here. Ten meaningful lines, and the entirearchitecture fits on one screen: create the server, register the threeprimitives, run. Everything else is detail.
2. mcp_server/database.py: ordinary MySQL code with no MCP in it at all.Worth reading early because it shows how thin the MCP layer really is: if youalready have a data-access layer, you are most of the way there.
Look closely at safe_identifier. MySQL will not let you bind a table name asa parameter (SHOW CREATE TABLE %s is not valid SQL), so identifiers have tobe interpolated into the string. That is a genuine injection risk, and that onesmall function is what makes it safe.
3. mcp_server/tools.py: the @mcp.tool() decorator, and the idea thatdoes the most work in the whole project: the docstring is the prompt. It isthe only thing the model reads when deciding whether to call a tool, so it iswritten for the model rather than for a human reading the source.
4. mcp_server/resources.py: static URIs versus templated ones, and whyget_table_ddl exists as both a tool and a resource. That duplication isdeliberate and is the clearest illustration of the who-controls-what idea.
5. mcp_server/prompts.py: prompts return text, not data. The text is aninstruction that usually tells the model which tools to use. Short file, and theone most people have never seen.
6. mcp_server/execution.py: read this once you want to know how writeaccess can be made safe. Five controls, each with a comment explaining what itprevents.
7. examples/explore_server.py: the other side of the protocol. A minimalclient that lists and calls everything, so you can see what actually crosses thewire.
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 deliberate: "can an agent write tomy database?" is the question every team asks, and a working example of how todo it safely is more useful than one that avoids the subject. But it does meanthe 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, because it is an easy mistake to repeat: the schema lock cannotwork by pattern alone. In SQL, a.b is usually alias.column (SELECT c.NAME FROM CUSTOMERS c), not schema.table, so rejecting every dotted name breaksordinary joins, which is exactly the bug the first version of this had. It nowcompares 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 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.
