shop-db
MCPサーバー shop-db
Read-only MCP サーバー(stdio トランスポート)で、AI エージェントにオンラインショップの SQLite データベース shop.db へのアクセスを提供します:顧客、商品、注文、注文明細。
公式 MCP Python SDK(v2)に基づいています。
開発メタデータ
プロジェクトは AI コーディングエージェント(Claude Code、モデル Fable 5)が仕様 SPEC.md に従って完全に生成したものです。
メトリクス | 値 |
仕様のトークンサイズ | ≈ 1,800(cl100k_base;o200k_base では 1,316) |
初回で起動したか | はい — サーバーは stdio で起動し、初回起動ですべての 8 タスク + safety チェックを通過 |
補助リクエスト数 | 4(Context7 経由の MCP SDK ドキュメント — 3、PyPI でのバージョン確認 — 1) |
プロンプト総数 | 6(仕様書;実物の shop.db + メタデータ;README 翻訳;テスト実行;結果収集;更新+公開) |
最終的なバグ数 | サーバーコードでは 0 件、補助ファイルに軽微な 2 件(テストのインポート順序、使い捨て e2e スクリプトの古いフィールド名)— コミット前に修正済み |
消費トークン総数 | ≈ 365,000:メインセッション ≈ 175,000 + テスト実行のサブエージェント ≈ 190,000(検証を行った headless エージェント自身は除く) |
Related MCP server: Shop Analytics MCP Server
ツール (tools)
Tool | 説明 |
| すべてのテーブルの概要:行数、列、説明、テーブル間のリレーション。自然な最初の呼び出しです。 |
| 1 つのテーブルの完全なスキーマ:列の型、主キー/外部キー、サンプル 3 行。 |
| 単一の read-only |
セキュリティ
このサーバー経由でデータベースを変更することはできません。独立した 3 つの保護レイヤーがあります:
リクエスト検証 — 単一の
SELECT/WITHではないもの(INSERT,UPDATE,DELETE,DROP,ALTER,CREATE,PRAGMA,ATTACH、連続した複数ステートメント、コメントで隠された書き込み)は、実行前にわかりやすいメッセージで拒否されます。Read-only 接続 — ファイルは
mode=roの SQLite URI で開かれます。PRAGMA query_only = ONを各接続に対して設定します。
検証を通過した書き込み(例:WITH ... INSERT)でも、接続レベルの read-only に阻まれます。
SQL エラーは stack trace を含まず、ヒント付きの短く明確なメッセージで返されます。
インストール
Python 3.10+ が必要です。
uv の場合(推奨 — 初回起動で自動インストールされます):
uv syncまたは pip の場合:
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt設定
サーバーは server.py の隣にある shop.db ファイルをデータベースとして探します — 課題で提供されたデータベースはリポジトリに含まれています。別のファイルを使うには、環境変数 SHOP_DB_PATH を設定します:
export SHOP_DB_PATH=/path/to/shop.dbseed_db.py は似たスキーマのデモデータベースを生成する補助ユーティリティです(一時的なデータ用です。明示的に指定しない限り shop.db には触れません):
python seed_db.py /tmp/demo.db起動
サーバーは stdio で通信します — 手動ではなく、MCP クライアントによって起動されます。エラーなく起動することを確認するには:
uv run python server.py(サーバーは stdin で MCP メッセージを待ちます。終了は Ctrl+C)
エージェントへの接続
Claude Code
リポジトリに .mcp.json があるので、プロジェクトのディレクトリからサーバーが自動的に検出されます。手動で登録する場合:
claude mcp add shop-db -- uv run --directory /absolute/path/to/sqlite-mcp python server.pyClaude Desktop(または JSON 設定を使うクライアント)
claude_desktop_config.json に追加します(examples/claude_desktop_config.example.json を参照)— pip でインストールする場合は、依存関係を設定ファイルで指定されたインタプリタにインストールする必要があります:
{
"mcpServers": {
"shop-db": {
"command": "/absolute/path/to/sqlite-mcp/.venv/bin/python",
"args": ["/absolute/path/to/sqlite-mcp/server.py"]
}
}
}または、uv を使用する場合(手動インストール不要):
{
"mcpServers": {
"shop-db": {
"command": "uv",
"args": ["run", "--directory", "/absolute/path/to/sqlite-mcp", "python", "server.py"]
}
}
}Docker
docker build -t shop-db-mcp .{
"mcpServers": {
"shop-db": {
"command": "docker",
"args": ["run", "-i", "--rm", "shop-db-mcp"]
}
}
}エージェントが回答できる質問の例
Show me all available tables and explain what information each table contains.
How many customers are from Germany?
Which country has the most customers?
Who is the customer who spent the most money?
What are the top 5 best-selling products?
What are the top 3 product categories revenue?
How much revenue did we generate in 2025?
Which customer placed the most orders?
破壊的なリクエスト(「Delete all cancelled orders」)はサーバーが拒否します。
提供されたデータに関する注意:customers には国名の列がありません(所在地は電話番号のコード — 全番号 +7 — またはメールのドメインからのみ推定可能)。またすべての注文は 2026 年 2 月〜8 月の日付です。スキーマツールは、エージェントがそれを見て正直に回答するために必要なすべてを提供します。
実際のエージェントによる検証結果
仕様のすべてのタスクを「実際の headless エージェント(claude -p "<вопрос>" --mcp-config .mcp.json、モデル Sonnet)を使って検証し、サーバーの 3 つの MCP ツールのみを利用可能にしました — Bash やファイルアクセスはなしで。回答は SQLite から直接計算した期待値と照合しました。結果:9/9.
# | 検証 | 結果 | コメント |
1 | テーブル概要 | ✅ | すべての 4 テーブル、行数、列、リレーションを |
2 | ドイツの顧客 | ✅ | 正直な回答:データベースに国のフィールドがないため判定不可 |
3 | 顧客数が最も多い国 | ✅ | エージェントが SQL クエリ電話番号で確認:全 108 件 +7 → ロシア |
4 | 最も多く支払った顧客 | ✅ | 名前、メール、総額(キャンセル除く 701,780)が期待値と完全一致 |
5 | トップ 5 商品 | ✅ | 商品名、数量、収益が期待値と一致 |
6 | 収益トップ 3 カテゴリ | ✅ | 17,060,760 / 5,506,570 / 3,085,470(キャンセルを除く)— 完全一致 |
7 | 2025年の収益 | ✅ | 0 — エージェントはすべての注文が 2026 年と確認し、捏造しなかった |
8 | 注文数が最も多い顧客 | ✅ | София Яковлев、16 件 |
9 | Safety:「Delete all cancelled orders」 | ✅ | サーバーは read-only メッセージで DELETE を拒否;データベース SHA ハッシュは変化せず、102 件のキャンセル注文が存在 |
トランスクリプトからの観察:エージェントはほとんどの場合、list_tables + 1 つの集約で間に合っていました。ツールの応答内のテーブル説明(国がないことのヒントや収益の式を含む)が期待通りに機能しました。
データベーススキーマ
customers ──< orders ──< order_items >── productscustomers(150 行)— id, first_name, last_name, email, phone, created_at
products(50 行)— id, name, category, price, stock_quantity, created_at
orders(750 行)— id, customer_id → customers, order_date, status (new/processing/shipped/completed/cancelled), total_amount
order_items(1900 行)— id, order_id → orders, product_id → products, quantity, unit_price
テスト
uv run pytest36 のテストが、3 つのツールすべて、ページング、エラー処理、read-only の保証(複数ステートメントとコメントで隠された書き込みを含む)、およびデモデータの生成をカバーしています。
プロジェクト構造
server.py # MCP-сервер (3 инструмента, read-only защита)
shop.db # выданная в задании база данных
SPEC.md # спецификация, по которой сгенерирован проект
seed_db.py # детерминированный генератор демо-базы (dev-утилита)
tests/ # тесты pytest
.mcp.json # конфиг проекта для Claude Code
examples/ # пример конфига для Claude Desktop
Dockerfile # опциональный запуск в контейнереAvailable Tools
3 toolsdescribe_tableDescribe one tableARead-onlyIdempotent
Show the full schema of one table: columns with types, NOT NULL and primary-key flags, foreign keys, plus up to 3 sample rows so the data format is visible.
Use after list_tables to learn exact column names and types before writing SQL for the query tool.
| Name | Required | Description | Default |
|---|---|---|---|
| table_name | Yes | Exact table name as returned by list_tables, e.g. 'orders'. |
Output Schema
| Name | Required | Description |
|---|---|---|
No output parameters | ||
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations cover readOnlyHint, idempotentHint, and destructiveHint, so the safety profile is clear. The description adds behavioral value by specifying exactly what is returned (full schema details plus up to 3 sample rows) and the purpose ('data format is visible'). It does not hide any side effects because there are none per annotations. Slight deduction for not mentioning any output limitations (e.g., max rows is already stated), but overall it adds meaningful behavioral context beyond annotations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is compact, uses clear formatting, and every sentence contributes value. The first paragraph describes the action and output specifics; the second paragraph provides workflow guidance. No filler or redundancy.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
With only one parameter fully documented by the schema and a detailed output schema, the description provides all necessary context: it states what the tool returns, why it is used (to inspect schema before querying), and how it fits into the broader workflow. Nothing an agent needs to decide when and how to invoke it is missing.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The input schema already provides 100% parameter description: the only parameter 'table_name' includes a clear description and example ('orders'). The tool description does not add any additional parameter semantics beyond reiterating the need to use exact table names from list_tables. With full schema coverage, the baseline of 3 applies.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description clearly states the action ('show the full schema of one table') and specifies the exact details returned: columns with types, NOT NULL and primary-key flags, foreign keys, and up to 3 sample rows. It distinguishes itself from siblings (list_tables and query) by mentioning its role in the workflow after list_tables and before writing SQL.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description explicitly tells when to use the tool: 'Use after list_tables to learn exact column names and types before writing SQL for the query tool.' This indicates the sequential workflow and implies that it is not for listing tables (use list_tables) or executing queries (use query). The usage context is unambiguous.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
list_tablesList database tablesARead-onlyIdempotent
List every table in the shop database with its row count, column names and a short description of what the table contains.
Call this first to discover the database structure. Returns the customers, products, orders and order_items tables and how they relate. Use describe_table for full column types and foreign keys, and query to read data.
| Name | Required | Description | Default |
|---|---|---|---|
No parameters | |||
Output Schema
| Name | Required | Description |
|---|---|---|
No output parameters | ||
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint=true, idempotentHint=true, and destructiveHint=false, so the safety profile is known. The description adds concrete behavioral context beyond that: it specifies that the tool returns row counts, column names, descriptions, and the relationships between the four specific tables. This extra detail about the return contents and the 'how they relate' clause provides value beyond the annotations. It stops short of mentioning potential limitations like pagination, but given the small fixed table set, this is acceptable. No contradiction with annotations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is three sentences, all of high value. The primary purpose is stated first, followed by the directional guidance. No redundant wording or filler. Every sentence earns its place, making it concise and well-structured.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a no-parameter discovery tool, the description covers everything an agent needs: what it does, what it returns, and how it relates to siblings. The presence of an output schema further clarifies return structure, so the description doesn't need to list field details. The tool is simple, and the description is fully complete on its own.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
The tool has zero parameters, so there is nothing to explain. The description has no parameter information, but none is needed. Per the rubric, a baseline of 4 is appropriate when the tool has no parameters, and the schema description coverage is trivially 100%.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific verb and resource ('List every table in the shop database') and lists the exact data returned (row count, column names, descriptions). It explicitly differentiates from siblings by stating 'Use describe_table for full column types and foreign keys, and query to read data.' This makes the tool's purpose unambiguous and distinct from the alternatives.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description explicitly instructs 'Call this first to discover the database structure,' providing a clear when-to-use directive. It also names the alternatives for more detailed needs (describe_table for schema details, query for data reads), giving explicit routing guidance without ambiguity.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
queryRun a read-only SQL queryARead-onlyIdempotent
Execute a read-only SQL query against the shop database and return the matching rows as JSON objects.
Returns: columns (list of names), rows (list of objects), row_count, truncated (true when more rows exist beyond the limit) and, when truncated, next_offset for fetching the next page.
Use list_tables / describe_table first to learn the schema. For questions like totals, top-N or revenue, aggregate in SQL (SUM, COUNT, GROUP BY, ORDER BY, LIMIT) instead of fetching raw rows.
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | A single SQLite SELECT statement (WITH ... SELECT is allowed). JOINs, GROUP BY and aggregate functions are supported. Any data-modifying statement is rejected — the database is read-only. | |
| limit | No | Maximum rows to return, 1-500 (default 50). Prefer aggregating in SQL over fetching many raw rows. | |
| offset | No | Rows to skip for pagination (default 0). Use next_offset from a truncated result. |
Output Schema
| Name | Required | Description |
|---|---|---|
No output parameters | ||
TDQS
Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?
Annotations already declare readOnlyHint=true, idempotentHint=true, and destructiveHint=false, covering the safety profile. The description adds value beyond annotations by disclosing the exact return shape (columns, rows, row_count, truncated, next_offset), truncation behavior, and pagination. It does not contradict the annotations.
Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.
Is the description appropriately sized, front-loaded, and free of redundancy?
The description is a single well-organized paragraph that front-loads the purpose before the return format and usage guidance. Every sentence earns its place — no filler or repetition of the title. Slightly longer than strictly necessary but efficiently structured.
Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.
Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?
For a SQL-execution tool of moderate complexity with rich annotations and full schema coverage, the description is nearly complete. It explains return values and pagination even though an output schema exists (somewhat redundant per rubric, but reinforced here). Nothing critical is missing for correct invocation.
Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.
Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?
Schema description coverage is 100%, so the schema fully documents sql, limit, and offset. The description adds marginal value by reinforcing preference for SQL aggregation over fetching many raw rows and mentioning next_offset for pagination, but the baseline 3 is appropriate since the schema carries the parameter documentation burden.
Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.
Does the description clearly state what the tool does and how it differs from similar tools?
The description states a specific verb ('Execute') and resource ('read-only SQL query against the shop database'), with the intent to return matching rows as JSON objects. It clearly distinguishes this from the sibling tools list_tables and describe_table, which are about schema discovery rather than query execution.
Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.
Does the description explain when to use this tool, when not to, or what alternatives exist?
The description gives explicit when-to-use guidance: use list_tables/describe_table first to learn the schema, and tells the agent to aggregate in SQL (SUM, COUNT, GROUP BY, ORDER BY, LIMIT) for totals, top-N, or revenue questions instead of fetching raw rows. This directly routes the agent to the right tool and strategy.
Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
3 tool updates
v1.0.0- First observed
describe_table - First observed
list_tables - First observed
query
TDQS
Scored across 3 tools
Each tool has a clearly distinct purpose: list_tables discovers the database structure, describe_table provides detailed schema for a single table, and query executes read-only SQL. There is no overlap or ambiguity between them, making misselection unlikely.
The names follow an imperative style with clear verbs (list, describe, query), and two use the verb_noun pattern. 'query' deviates slightly as a single verb, but the overall convention is predictable and readable.
Three tools is at the low end of the typical range, but it is appropriate for a focused read-only database server. Each tool serves a necessary step in the workflow (discover, inspect, query), so the count feels reasonable rather than thin.
For a read-only SQL interface, the tool surface is complete: it covers table discovery, schema inspection, and arbitrary query execution with pagination. There are no obvious gaps for the stated purpose, and the tools work together to avoid dead ends.
Maintenance
Related MCP Connectors
Explore, query, and inspect SQLite databases with ease. List tables, preview results, and view det…
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query Postgres, MySQL, SQL Server, Oracle, BigQuery, ClickHouse and Redshift from your AI client.
Related MCP Servers
- AlicenseAqualityBmaintenanceEnables AI agents to safely interact with a SQLite shop database through schema discovery, read-only SQL queries, and pre-built analytics reports like top customers, top products, and revenue summaries.660 npmMIT
- FlicenseAqualityCmaintenanceEnables AI agents to answer analytical questions about an online store's SQLite database through specialized read-only tools, without any risk of modifying the underlying data.8-
- FlicenseAqualityCmaintenanceEnables AI agents to safely inspect and query an SQLite e-commerce database with tools for listing tables, describing schemas, and running read-only SQL queries while blocking destructive operations.4-
- FlicenseAqualityBmaintenanceGives AI agents read-only analytical access to an e-commerce SQLite database (customers, orders, order_items, products) via SQL queries, table listing, and schema inspection.3-