mcp-server-playground

A hands-on MCP (Model Context Protocol) server built over a SQLite mock database, for learning how an AI client discovers and calls your own tools.

The dataset is a fictional general online store — 1,200 customers, 1,358 SKUs across 540 distinct products, 25,000 orders, 61,835 line items and 4,000 reviews, spanning 2024-01 to 2026-07.

Ten categories, deliberately spanning an order of magnitude in price:

家電  食品  日用品  ファッション  書籍  インテリア  スポーツ  文具  ペット用品  コスメ
      ¥160 (パスタソース) … ¥36,480 (ハンディクリーナー 上位モデル)

The data is shaped rather than uniformly random, so aggregating it yields findings instead of noise:

• Seasonality. December runs ~1.65× a normal month, February ~0.8×.
• Growth. Volume trends up ~1.8% per month across the whole period.
• Concentration. A minority of SKUs and a minority of customers account for most of the volume, so rankings and customer segments are not flat.
• Brands. 9 brands × 60 product types, each brand with its own price positioning. sales_summary groups by brand, so "which brand sells best" is answerable — and the answer is not the obvious one: プレミアムセレクト leads on revenue while デイリープラス takes 70% more orders to reach last place.
• Price vs volume. 日用品 has the most orders but the least revenue; 家電 is the reverse. Ranking by revenue and by units gives different answers, which is exactly the distinction top_products exposes.

Why this stack

Choice	Reason
TypeScript on Node ≥24.10	Node's type stripping runs .ts files directly — no build step, no dist/
node:sqlite	SQLite is built into Node; no native module to compile
Three transports	stdio, Streamable HTTP, and Streamable HTTP with OAuth 2.1
Few dependencies	@modelcontextprotocol/sdk and zod; express only for the OAuth server

src/server.ts defines what the server exposes; src/stdio.ts and src/http.ts define how it is reached. The tool definitions know nothing about either transport, which is why supporting both cost one extra file.

src/server.ts        tools, resources, prompts  (transport-agnostic)
src/db.ts            connections + read-only guard
src/seed.ts          deterministic mock data
src/stdio.ts         entry point: stdin/stdout
src/http.ts          entry point: Streamable HTTP
src/http-oauth.ts    entry point: Streamable HTTP + OAuth 2.1
src/auth-provider.ts in-memory authorization server for the demo
src/session-store.ts HTTP session bookkeeping: idle timeout + cap

Node 24.10 is the floor because of DatabaseSync.setAuthorizer(), which the read-only connection depends on. Type stripping itself only needs 23.6.

Three entry points, one server.ts. Adding OAuth did not change a single tool definition — that separation is the whole point of the layout.

Quick start

npm install
npm run tools        # print everything the server advertises
npm run smoke        # stdio: acts as an MCP client and exercises everything
npm run smoke:http   # Streamable HTTP: same, plus two concurrent sessions
npm run smoke:oauth  # OAuth 2.1: the whole authorization flow, step by step

npm run tools shows exactly what the model receives — name, description and argument schema, nothing more. If a tool reads as ambiguous there, it reads as ambiguous to the model too, which makes it the fastest way to review a tool definition you just wrote.

npm run smoke is the fastest way to see the whole protocol in action: it spawns the server exactly like a real client does, lists the capabilities, calls every tool, reads both resources, fetches a prompt, and finally proves that a restart wipes the database back to its initial state.

There is no separate setup step — the server builds data/shop.db itself on start. npm run seed exists only for rebuilding the file by hand, e.g. to poke at it with the sqlite3 CLI.

A fresh database on every start

The server deletes and rebuilds data/shop.db each time it starts, so every session begins from the same known state and nothing a previous session wrote survives. The seed uses a fixed PRNG value, so "rebuilt" means the identical dataset every time — you can memorise a number from one session and still recognise it in the next.

Rebuilding ~93,000 rows costs about 250ms (all inserts run in one transaction), which is invisible next to process startup.

playground-shop: database reset in 249ms — customers=1200  products=1358  orders=25000  order_items=61835  reviews=4000

To keep data between runs instead, set MCP_SQLITE_PERSIST=1:

{
  "mcpServers": {
    "playground-shop": {
      "command": "node",
      "args": ["src/stdio.ts"],
      "env": { "MCP_SQLITE_PERSIST": "1" }
    }
  }
}

One consequence worth knowing: because each client spawns its own server process, opening MCP Inspector while Claude Code is already connected resets the database underneath Claude Code's session. Run one client at a time, or use MCP_SQLITE_PERSIST=1 when you deliberately want them to share state.

What the server exposes

MCP has three kinds of capability. This server implements all three.

Tools — actions the model chooses to call

Tool	Purpose
list_tables	Every table with its row count. The natural entry point.
describe_table	Column definitions plus three sample rows.
search_products	Keyword, brand, category, price range and stock filters.
search_reviews	Full-text search over review bodies, backed by SQLite FTS5.
customer_orders	Resolve a customer by id/email/name, return orders with items joined.
sales_summary	Revenue aggregated by month, category, brand, prefecture or status.
top_products	Ranking by revenue, units sold or average rating.
add_review	The one tool that writes. Inserts a review row.

There is no raw-SQL tool, on purpose. An earlier version had one, and it is worth understanding why it is gone — see Why no raw-SQL tool.

The two text searches (search_products.keyword, customer_orders.name) split the query on whitespace and require every term, rather than matching the whole string. Word order stops mattering, which matters here because product names are <brand> <product>（<size>） and a model has no reason to guess that order:

"匠工房 加湿器"  →  匠工房 超音波加湿器（標準）
"加湿器 匠工房"  →  same result
"匠工房超音波"   →  no match — terms are matched individually, not fuzzily

% and _ are escaped, so a query of % finds nothing rather than everything.

Results are ordered by sort, which defaults to relevance. There is no real relevance score behind LIKE, so it approximates one with name length: the shortest matching name is the plainest product. That matters because ordering by price put the answer to "find me a ballpoint pen" at ¥1,660 for a premium 5-pack, when a ¥200 single pen also matched:

sort=relevance   グッドデイ ゲルインクボールペン（1個）        ¥200
sort=price_desc  プレミアムセレクト ゲルインクボールペン（5個セット） ¥1,660

Full-text search with FTS5

search_reviews searches review bodies, which LIKE over 4,000 rows would scan linearly. It is backed by an FTS5 virtual table, and getting that working for Japanese took two non-obvious decisions.

The default tokenizer is useless for Japanese. FTS5's unicode61 splits on whitespace and punctuation. A Japanese sentence has neither, so the whole sentence becomes one token and nothing short of an exact match ever hits:

unicode61, indexing "音が静かで満足しています"
  MATCH '静か'   →  0 rows

tokenize='trigram' indexes every 3-character window instead, which has no notion of word boundaries and therefore does not need one:

trigram, same text
  MATCH '音が静か'  →  1 row
  MATCH '静か'      →  0 rows   ← still nothing

Trigram has a hard floor of three characters. Two-character queries — 静か, 破損, 満足, all perfectly ordinary Japanese words — cannot match, ever. The tool therefore routes each term by length: three or more characters go to the index, shorter ones fall back to a LIKE scan. Mixed queries use both, and the response says which path ran:

"動作音が大きくて"  →  1 review(s) via full-text index (動作音が大きくて)
"静か"             →  1 review(s) via LIKE scan
"洗濯 縮み"         →  1 review(s) via LIKE scan

The index earns its place even at this size — measured over 4,000 reviews:

FTS5 MATCH    0.059 ms/query
LIKE %...%    0.574 ms/query     ~10x slower

Two implementation notes worth knowing if you add FTS5 to your own server:

• The authorizer needs PRAGMA data_version. FTS5 reads it internally to validate its cache. Without it every FTS query fails with a bare authorization denied and nothing indicates which pragma was refused.
• Shadow tables are hidden from list_tables. An FTS5 table brings five companions (_data, _idx, _content, _docsize, _config). They are storage internals; listing them would waste the model's context and invite it to query them directly.

Writes stay in sync through triggers, so a review added via add_review is immediately findable by search_reviews.

Query terms are quoted before being handed to FTS5, so review text containing FTS5 operators is treated as literal text rather than syntax.

Resources — context the client reads, not calls

• schema://database — full DDL for every table.
• table://{table}/sample — first five rows as JSON. A URI template: the server also advertises one concrete URI per table and supports completion, so a client can offer table://orders/sample as an autocomplete suggestion.

Prompts — user-triggered templates

• monthly_report (month) — draft a sales report for one month.
• product_deep_dive (keyword) — investigate one product end to end.

In Claude Code these appear as /mcp__playground-shop__monthly_report.

Why no raw-SQL tool

A run_sql tool taking a SELECT string is the obvious way to make a database useful to a model, and it was the first tool this project had. Removing it was the single biggest security improvement, so the reasoning is worth recording.

Guarding it took three layers, and measurement showed each one mattered:

Attack	Stopped by	Why
SELECT 1; DROP TABLE reviews	SQLite itself	prepare() compiles only the first statement
DELETE FROM reviews	all three layers	write, in every sense
/* hi */ DELETE FROM ...	regex	but it also rejected valid commented queries
ATTACH DATABASE '/etc/passwd'	regex + authorizer	would expose other database files
PRAGMA database_list	regex + authorizer	leaks absolute filesystem paths
SELECT readfile('/etc/passwd')	nothing — luck	the function simply does not exist in node:sqlite
unbounded WITH RECURSIVE	authorizer only	hung the server for as long as it was left running

Two attacks had no answer at all:

• SELECT * FROM customers reads every row. It is a valid query, so no layer has grounds to refuse it. Allowing raw SQL over real data means allowing the model to read all of that data.
• A big enough cross join blocks the process. DatabaseSync is synchronous, so a slow query freezes the server. Measured: worker_threads + terminate() does not help, because the loop is inside SQLite's C code and never returns control to V8. Only child_process + SIGKILL actually stops it — which is a lot of machinery to bolt onto one tool.

The purpose-built tools need none of this. Their input is typed arguments, the SQL is written by hand in server.ts, and business rules (excluding cancelled orders, sensible defaults) are enforced in one place instead of hoped for. They also cost fewer tokens and cannot produce a syntax error.

The trade-off is real: questions no tool covers now go unanswered instead of being improvised. That is the intended behaviour — the server's instructions tell the model to say so rather than guess.

Safety model

The database still gets defence in depth, because a bug in a hand-written query should fail loudly rather than corrupt data.

A SQLite authorizer. The read-only connection installs setAuthorizer(), which SQLite consults for every operation the parser attempts. Only SQLITE_SELECT, SQLITE_READ and SQLITE_FUNCTION pass, plus PRAGMA table_info for describe_table. SQLITE_RECURSIVE is denied, so an unbounded recursive CTE can never start.

A read-only connection. readOnly: true means a write fails even if the authorizer were misconfigured. add_review uses a separate writable connection.

Tool annotations. readOnlyHint: true on every query tool and readOnlyHint: false on add_review — that is the signal a client uses to decide whether to ask the user for confirmation.

Still not covered: no authentication on the HTTP transport (see the Streamable HTTP section), and the seeded data is fictional, so nothing here is tuned for real personal data. For that, SQLITE_IGNORE in the authorizer can mask individual columns — returning NULL for, say, customers.email even under SELECT * and in WHERE clauses.

Connecting a client

Claude Code — project scope (recommended)

.mcp.json in this repository already declares the server. Start Claude Code from this directory and approve the server when prompted:

cd ~/projects/mcp-server-playground
claude

Then /mcp shows the connection status and the available tools.

Claude Code — available everywhere

claude mcp add playground-shop --scope user -- node ~/projects/mcp-server-playground/src/stdio.ts

Use an absolute path: the client sets its own working directory.

Claude Code — one-off, non-interactive

claude -p "2026年6月のカテゴリ別売上を教えて" \
  --mcp-config ~/projects/mcp-server-playground/.mcp.json \
  --allowedTools "mcp__playground-shop__sales_summary,mcp__playground-shop__top_products"

Claude Desktop

Add to ~/Library/Application Support/Claude/claude_desktop_config.json:

{
  "mcpServers": {
    "playground-shop": {
      "command": "node",
      "args": ["/Users/YOU/projects/mcp-server-playground/src/stdio.ts"]
    }
  }
}

Streamable HTTP

Start the server yourself and let clients connect over the network:

npm run start:http
# playground-shop MCP server listening on http://127.0.0.1:4871/mcp

claude mcp add --transport http playground-shop http://127.0.0.1:4871/mcp

Or non-interactively:

claude -p "レビュー平均評価の高い商品トップ3は？" \
  --mcp-config '{"mcpServers":{"playground-shop":{"type":"http","url":"http://127.0.0.1:4871/mcp"}}}' \
  --allowedTools "mcp__playground-shop__top_products"

How it differs from stdio:

	stdio	Streamable HTTP
Process	One per client, spawned by the client	One long-lived server, started by you
Clients	Exactly one	Many, each with its own session
Addressing	A command line	A URL
Lifetime	Dies with the client	Runs until you stop it
Database reset	Every client launch	Once per server start — clients share the data

Endpoints: POST /mcp for JSON-RPC, GET /mcp for the server-to-client SSE stream, DELETE /mcp to end a session, plus a non-MCP GET /health that reports the active session count.

Sessions are stateful: initialize returns an mcp-session-id header that later requests must carry, and each session gets its own McpServer instance. DNS rebinding protection is on, so the Host header must match the address the server is listening on — a browser page cannot quietly reach your local server.

There is no authentication on this entry point. It binds to 127.0.0.1 deliberately. For an authenticated server, see the next section.

Environment: MCP_HTTP_PORT (default 4871), MCP_HTTP_HOST (default 127.0.0.1).

Sessions are bounded. A plain Map<sessionId, transport> is enough to make the protocol work, but clients disconnect without sending DELETE /mcp — a crashed process, a closed laptop — and each abandoned session pins an McpServer, a transport and any open SSE stream for the life of the process. src/session-store.ts adds an idle timeout (30 min) and a cap (100 sessions, past which new ones get a 503). Reads refresh the timer, so an active session is never swept.

Streamable HTTP with OAuth 2.1

npm run start:oauth
# playground-shop MCP server (OAuth) listening on http://127.0.0.1:4872/mcp

claude mcp add --transport http playground-shop http://127.0.0.1:4872/mcp

Claude Code then runs the whole flow itself — discovery, dynamic client registration, opening a browser for the consent screen, token exchange and refresh. You never paste a client id or a token anywhere.

npm run smoke:oauth walks the same flow by hand, one labelled step at a time, which is the fastest way to see what is actually happening:

1.  unauthenticated request  → 401 + WWW-Authenticate: Bearer …, resource_metadata="…"
2.  GET /.well-known/oauth-protected-resource/mcp   → which server guards this API
3.  GET /.well-known/oauth-authorization-server     → where /authorize, /token, /register are
4.  POST /register                                  → dynamic client registration (RFC 7591)
5.  GET /authorize?…&code_challenge=…               → consent screen
6.  user approves                                   → redirect back with ?code=…
7.  POST /token with the PKCE verifier              → access + refresh token
8.  bad token                                       → 401 invalid_token
9.  MCP client with the access token                → tools/list, tools/call work
10. read-only token calls add_review                → rejected, missing playground-shop:write
11. full-scope token calls add_review               → succeeds
12. POST /token grant_type=refresh_token            → new access token

Scopes. playground-shop:read is required for any MCP request; add_review additionally requires playground-shop:write. The check lives in server.ts and reads extra.authInfo, which the transport populates. Under stdio and the plain HTTP server authInfo is undefined and the tool stays open — the check applies exactly where there are scopes to check, without making the tool definition transport-aware.

Environment: MCP_HTTP_PORT (default 4872), MCP_HTTP_HOST (default 127.0.0.1).

Not production ready

The authorization server is a teaching implementation:

• Everything is in memory. Restart the process and every registered client and issued token is gone.
• No user authentication. The consent screen has no login — anyone who can reach /authorize can approve. A real server authenticates a user first and binds the token to them.
• Tokens are opaque random strings in a Map, not signed JWTs, so they cannot be verified by anything but this process.
• Plain HTTP on localhost. OAuth 2.1 requires TLS everywhere but loopback.
• Anyone may register. Dynamic client registration is unauthenticated and unthrottled beyond the SDK's default rate limiting.

What it does demonstrate faithfully is the protocol: discovery, PKCE, single-use authorization codes, scope enforcement and refresh.

MCP Inspector — the GUI debugger

npm run inspect

Opens a browser UI where you can see the raw JSON-RPC traffic, call tools with arbitrary arguments and inspect errors. This is the tool to reach for while developing a new capability.

Things to try

1. Ask "先月いちばん売れた商品は？" and watch which tool the model picks.
2. Ask something no tool covers ("都道府県ごとの平均客単価は？") and watch what the model does when it cannot answer. This is the cost of dropping raw SQL — decide for yourself whether a prefecture_stats tool is worth adding.
3. Ask it to delete all reviews, and watch it discover there is no such tool.
4. Ask it to post a 5-star review, and see the client ask for confirmation because of readOnlyHint: false. Restart the client and the review is gone.
5. Delete a tool description in src/server.ts, restart, and observe how much worse the tool selection gets. Descriptions are the model's only documentation.

Environment

• MCP_SQLITE_DB — override the database path. Defaults to data/shop.db resolved relative to the project root, not the working directory.
• MCP_SQLITE_PERSIST=1 — skip the reset and reuse the existing database. Still seeds it if the file does not exist yet.

Gotchas worth knowing

• stdout belongs to the protocol. A stray console.log in server code corrupts the JSON-RPC stream and the client drops the connection. Log to stderr instead — src/stdio.ts does.
• The client controls the working directory. Always resolve file paths from import.meta.dirname, as src/db.ts does.
• Tool descriptions are the interface. The model sees only the name, description and JSON schema. A vague description is a broken tool.