# SQL-MCP-101

**New to MCP?** Start with the [interactive tutorial](https://khushboo-mishra.github.io/SQL-MCP-101/), 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.

---

## 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**:

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

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

```bash
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
bash scripts/run_explorer.sh
```

The only thing that has to stay running is MySQL. There is no backend service to
start: the MCP server is not a daemon, and every client launches its own copy
over stdio. To get the data layer up before a demo, without opening a client:

```bash
bash scripts/run_backend.sh
```

```bash
bash scripts/run_backend.sh status
```

`start` brings MySQL up if it is down, verifies the semantic views, and smoke
tests the server the way a client would. `status` reports without changing
anything, and `stop` shuts MySQL down again.

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

Install the dependencies:

```bash
pip install mcp PyMySQL
```

`scripts/setup.sh` already runs this inside the project virtualenv, so use the
command above only if you are setting things up by hand.

### Python dependencies

Two, and that is the whole stack:

| Package | Role | Verified on |
|---|---|---|
| [`mcp`](https://pypi.org/project/mcp/) `>=2.0.0` | The official Anthropic MCP SDK. Provides `MCPServer` and the `@mcp.tool()` / `@mcp.resource()` / `@mcp.prompt()` decorators used throughout. | 2.1.1, speaking protocol `2025-11-25` |
| [`PyMySQL`](https://pypi.org/project/PyMySQL/) `>=1.1.0` | Pure-Python MySQL driver. The only thing that ever opens a database connection. | 1.2.0 |

**A note on FastMCP, because the naming causes real confusion.** This project
does **not** use the third-party [`fastmcp`](https://pypi.org/project/fastmcp/)
package, and that package is not a dependency. It uses the official SDK's
decorator-based server:

```python
from mcp.server import MCPServer          # mcp_server/server.py
```

The ergonomics are the ones FastMCP popularised: write a plain Python function,
and its docstring becomes the description the model reads while its type hints
become the JSON Schema. That API was absorbed into the official SDK, where it
lived for a long time as `mcp.server.fastmcp.FastMCP`. Most tutorials still
show that import. In the 2.x SDK the class was renamed to `MCPServer` under
`mcp.server.mcpserver`, and `mcp.server.fastmcp` no longer exists, so the older
import raises `ModuleNotFoundError`.

Same decorators, same idea, different name and import path. If you are
following a tutorial written against `FastMCP`, substitute `MCPServer` and the
rest carries over unchanged.

---

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

| Tool | Answers |
|---|---|
| `list_entities` | What can I ask about? Returns the business catalogue. |
| `get_courses` | The course catalogue, filtered by department, level, international eligibility, enrolment status. |
| `get_student_headcount` | Student numbers per department, by degree level and residency. Counts only. |
| `get_enrollments` | How many students are on each course, by term. Never a roster. |
| `get_research_output` | Publication counts and citation totals, by department and year. |
| `get_awards` | Awards granted, aggregated by department, year and award name. |
| `get_finalists` | Competition finalists, counted by competition, year and department. |
| `get_staffing` | Staff headcount by department and role. No names, no salaries. |
| `show_activity_log` | What this server has been asked to do, as business intent. |

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

| Tool | Does |
|---|---|
| `set_course_enrollment_status` | Opens, waitlists or closes enrolment on **one** course. One field, three allowed values. |

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.

| URI | Type | Content |
|---|---|---|
| `nyu://semantic/entities` | Markdown | the semantic dictionary, and what is deliberately unavailable |
| `nyu://semantic/catalog` | JSON | the same catalogue, structured |
| `nyu://semantic/course-catalog` | JSON | every course |
| `nyu://semantic/glossary` | Markdown | what the business terms mean |
| `nyu://semantic/entity/{name}` | Markdown | one entity's definition (**templated**) |
| `nyu://semantic/department/{name}` | JSON | one department: courses, headcount, staffing, research (**templated**) |

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

| Prompt | Starts |
|---|---|
| `analyze_department` | A rounded review: size, teaching, demand, staffing, research. |
| `international_eligibility_review` | How much of a department's catalogue is actually open to international students. |
| `ask_about_the_university` | Any business question, grounded in `list_entities` first. |
| `capacity_planning` | Finds pressure points: waitlisted courses with high demand, open courses with none. |
| `research_review` | Compares output across departments, with the caveats stated. |
| `onboarding_tour` | 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 |
|---|---|---|
| Student numbers for a department | **tool** | the model picks the filters mid-reasoning, unpredictably |
| The course catalogue | **both** | tool for the model; resource for a human to attach up front |
| Departmental review | **prompt** | a repeatable task where knowing *what to ask* is the value |
| The business vocabulary | **resource** | passive reference, no decision required |
| Opening or closing enrolment | **tool** | an action, and the only one that writes |

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

| View | Exposes |
|---|---|
| `v_semantic_course_catalog` | course, level, department, college, credits, international eligibility, enrolment status |
| `v_semantic_department_headcount` | student counts by department, degree level, residency |
| `v_semantic_enrollment_summary` | enrolment counts by course and term |
| `v_semantic_research_output` | publications and citations by department and year |
| `v_semantic_award_summary` | awards granted and totals by department and year |
| `v_semantic_finalist_summary` | finalist counts by competition, year, department |
| `v_semantic_department_staffing` | staff headcount by department and role |

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


## 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. 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
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 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](https://ollama.com), free, no API key, and
nothing leaves the machine:

```bash
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
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
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:

| # | File | What it shows |
|---|---|---|
| 1 | `mcp_server/server.py` | The whole architecture on one screen: verify, register three primitives, run. |
| 2 | `mcp_server/semantic.py` | The entity registry. Tools, resources and the catalogue are all generated from it, so they cannot drift apart. |
| 3 | `mcp_server/entities.py` | `@mcp.tool()`, and the idea that does the most work here: **the docstring is the prompt**, written for the model rather than for a human reading the source. |
| 4 | `mcp_server/resources.py` | Static URIs versus templated ones, and why the course catalogue is deliberately both a tool and a resource. |
| 5 | `mcp_server/prompts.py` | Prompts return *text*, not data, and name tools rather than views. |
| 6 | `mcp_server/database.py` | The only file that knows SQL, and all six governance controls. |
| 7 | `examples/explore_server.py` | The other side of the protocol: a minimal client, so you can see what crosses the wire. |

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](LICENSE).