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 that demonstrates all three Model Context Protocol primitives (tools, resources, and prompts) over a semantic layer, rather than over a raw database schema.

This repository exists to be read, not just run. If you have seen MCP mentioned and want to understand what building a governed server actually involves, this is a complete, working example small enough to read in one sitting: one primitive per file, comments that explain why rather than what, and an NYU academic database behind a semantic layer that hides it.

mcp_server/
├── semantic.py    the ENTITY REGISTRY: one source of truth for what exists
├── database.py    the only file that knows SQL; every governance control
├── entities.py    10 TOOLS      business questions, plus one governed write
├── resources.py   4 RESOURCES   content the APPLICATION attaches (+2 templates)
├── prompts.py     6 PROMPTS     workflows the USER invokes
├── errors.py      turning expected failures into messages a model can act on
└── server.py      wires them together, about ten meaningful lines

The idea this repository is built to demonstrate

Do not hand a model your database schema. Give it business entities.

A model that can list your tables and read your columns is a model that has seen your PII column names, has to write its own joins, and breaks the day you rename something. The alternative is a semantic layer:

  Model / Client  (Claude Desktop, Claude Code, any MCP client)
                           │
                           │  business entities only: no SQL, no schema
                           ▼
  ┌──────────────────────────────────────────────────────────┐
  │  SQL-MCP-101 SERVER                                      │
  │    semantic.py    entity registry, the single source     │
  │    entities.py    business tools, no SQL accepted        │
  └────────────────────────┬─────────────────────────────────┘
                           │
                           │  translates internally
                           ▼
  ┌──────────────────────────────────────────────────────────┐
  │  MySQL: nyu_demo                                         │
  │    v_semantic_course_catalog, v_semantic_*  (readable)   │
  │    students, enrollments, employees ...     (hidden)     │
  └──────────────────────────────────────────────────────────┘

A model connected to this server never learns that a students table exists, that it has a passport_num column, or that answering "how many international graduates are in Computer Science?" needs a three-way join.

Related MCP server: mysql-mcp-server

The other 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:

The same content can appear as more than one. Here get_courses is a tool and nyu://semantic/course-catalog is a resource: the same answer, reached two 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 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

• Ollama: optional, only for the UI's Chat panel

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

What gets built

10 tools, 4 resources + 2 URI templates, and 6 prompts, over seven semantic views that hide nine raw tables.

Tools: the model calls these

entities.py: business questions. Every argument is a business word. Not one of them is a table, a column, or a query.

One tool can change data, registered separately so the write surface stays small and obvious:

There is deliberately no run_query and no execute_statement. A caller cannot express a statement the server did not design, so there is nothing to inject into and no schema to know.

Resources: the application attaches these

resources.py: business vocabulary, not schema dumps.

Prompts: the user invokes these

prompts.py: saved expert questions. Note that none of them names a view, a table or a column: they name tools and let the semantic layer decide the rest.

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.

• Schema leaking through any of the three. A tool that takes a table name, a resource that publishes column types, or a prompt that names a view. The last is the easiest to write by accident, and it breaks the day that view is renamed. Name the tool and let the semantic layer decide the rest.

The demo database

nyu_demo has two layers, and the split between them is what the server enforces.

Nine raw tables hold the real data: colleges, departments, courses, students, employees, enrollments, publications, rewards, finalists. students carries ssn, passport_num and visa_type; employees carries salary. The MCP server never reads any of them directly and never names one to a model.

Seven semantic views are the only objects the server may query. Each is pre-joined, pre-aggregated and PII-free:

Refactor a raw table and you fix one view. Nothing the model knows changes.

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 setup works 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:

A live Activity strip along the bottom shows the real JSON-RPC underneath, tools/call, resources/read, prompts/get, so the protocol is visible the whole 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, and nothing 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 has separate Tools, Resources and Prompts tabs. None of it is our code, so if the Inspector drives the server, the server is spec-compliant.

Try get_courses with Computer Science, read nyu://semantic/entities, and expand the analyze_department prompt.

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 already registered, validates the JSON, smoke-tests the exact launch command, and relaunches the app.

Then ask "Review the Physics department", or pick analyze_department from the prompt menu. Asking for student names and email addresses is the quickest way to see the semantic layer refuse something.

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

Reading the code

The repo is meant to be read. This order builds up without forward references:

Every file opens with a docstring explaining why it is shaped the way it is.

Going further

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

• More entities: add a row to ENTITIES in semantic.py and a matching v_semantic_* view. The tool, the catalogue entry and the dictionary all follow from that one definition.

• Do not add a raw SQL tool. It is the obvious next step and it undoes the design: the server would need credentials that read your tables, the model would need the schema to write against, and results would enter its context unfiltered. If a question cannot be expressed as an entity plus filters, add the entity.

• Remote transport: mcp.run(transport="streamable-http"). Same tools, same code, different pipe. Add authentication before exposing it.

• Caching: every entity tool 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, though only in one specific way: it can open, waitlist or close enrolment on a single course. That is deliberate. "Can an agent write to my database?" is the question every team asks, and a narrow working example is more useful than avoiding the subject.

The six controls

1. Semantic layer only. Every query targets a v_semantic_* view drawn from the registry in semantic.py. A view not on the allowlist is refused before a connection is opened, so the raw tables are unreachable rather than merely undocumented.

2. No SQL from callers, ever. There is no run_query and no execute_statement. Callers name an entity and pass filters; the statement is built in database.py from the registry, with values bound as parameters.

3. Filters are an allowlist. Filter names map to columns through the registry, and filters with a fixed value set reject anything else by name. A caller cannot reach a column nobody chose to expose.

4. PII guard at startup. verify_semantic_layer() reads every exposed view at boot and raises if one has grown a forbidden field, so a careless view edit is a startup crash rather than a quiet leak months later.

5. Row cap. A ceiling on rows returned, with truncation reported rather than hidden.

6. Audit log of intent. Every call is recorded as the business action and its parameters, never as SQL, so the log itself is safe to show a user.

The one write, set_course_enrollment_status, changes a single field on a single course, validates the status against a fixed set, and verifies the result afterwards.

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 granted SELECT on the semantic views only. If the credentials cannot reach a raw table, neither can a prompt injection or a model mistake.

Two more things worth stating plainly

• The MySQL grant is the real boundary. Grant SELECT on the v_semantic_* views and nothing else. Then a bug in this code cannot reach a raw table, because the credentials cannot.

• The views are a security control, not a convenience. A column left out of a view is not hidden by policy, it is absent from the result. No query against that view returns it, whatever the model asks for.

License

MIT, see LICENSE.