shop-db
MCP 서버 shop-db
읽기 전용 MCP-서버(stdio 전송)로서 AI 에이전트에게
인터넷 쇼핑몰 SQLite 데이터베이스 shop.db에 대한 접근을 제공합니다: 고객, 상품, 주문 및 주문 항목.
공식 MCP Python SDK (v2) 기반입니다.
개발 메타데이터
프로젝트는 SPEC.md 명세에 따라 AI 코딩 에이전트(Claude Code, 모델 Fable 5)로 완전히 생성되었습니다.
지표 | 값 |
명세 크기(토큰) | ≈ 1 800 (cl100k_base; o200k_base 기준 1 316) |
첫 실행부터 성공했는가 | 예 — 서버가 stdio로 기동했고 첫 실행부터 8개 작업 + safety 점검을 모두 통과했습니다 |
보조 요청 수 | 4 (Context7을 통한 MCP SDK 문서화 3건, PyPI 버전 확인 1건) |
전체 프롬프트 수 | 6 (명세; 실제 shop.db + 메타데이터; README 번역; 테스트 실행; 결과 수집; 업데이트 + 게시) |
최종 버그 수 | 서버 코드 0개; 보조 파일의 사소한 버그 2개(테스트의 import 순서, 일회성 e2e 스크립트의 낡은 필드 이름) — 커밋 전에 수정됨 |
사용된 전체 토큰 수 | ≈ 365 000: 기본 세션 ≈ 175 000 + 테스트 실행 서브 에이전트 ≈ 190 000 (검증을 통과한 headless 에이전트 자체는 제외) |
Related MCP server: Shop Analytics MCP Server
도구 (tools)
도구 | 용도 |
| 전체 테이블 개요: 행 수, 열 정보, 설명, 테이블 간 관계. 자연스러운 첫 호출입니다. |
| 단일 테이블의 전체 스키마: 열 타입, 기본 키/외래 키, 예제 데이터 3행. |
| 단일 읽기 전용 |
보안
이 서버를 통해 데이터베이스를 변경할 수 없습니다. 세 가지 독립적인 보호 계층:
요청 검증 — 단일
SELECT/WITH가 아닌 모든 것 (INSERT,UPDATE,DELETE,DROP,ALTER,CREATE,PRAGMA,ATTACH, 연속된 여러 구문, 주석 뒤에 숨겨진 쓰기)은 실행 전에 이해하기 쉬운 메시지와 함께 거부됩니다.읽기 전용 연결 — 파일은
mode=roSQLite URI로 열립니다.PRAGMA query_only = ON을 각 연결에 적용합니다.
검증을 통과한 쓰기(예: WITH ... INSERT)도 연결 수준의 읽기 전용 제한에 막힙니다.
SQL 오류는 힌트가 포함된 짧고 명확한 메시지로 반환되며, 스택 트레이스가 없습니다.
설치
필수: 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"]
}
}
}에이전트가 답변하는 질문 예시
사용 가능한 모든 테이블을 표시하고 각 테이블의 정보를 설명하세요.
독일에서 온 고객은 몇 명인가요?
어떤 국가에 고객이 가장 많은가요?
가장 많은 돈을 쓴 고객은 누구인가요?
베스트셀러 상품 상위 5개는 무엇인가요?
매출 기준 상위 3개 상품 카테고리는 무엇인가요?
2025년에 발생한 매출은 얼마인가요?
주문을 가장 많이 한 고객은 누구인가요?
파괴적인 요청 («Delete all cancelled orders»)은 서버가 거부합니다.
제공된 데이터에 대한 참고사항: customers 테이블에는 국가 열이 없습니다(위치는 전화 지역번호 — 모든 번호 +7 — 또는 이메일 도메인으로만 유추 가능). 또한 모든 주문은 2026년 2월–8월로 날짜가 표시되어 있습니다. 스키마 도구는 에이전트가 이를 발견하고 정직하게 답변하는 데 필요한 모든 정보를 제공합니다.
실제 에이전트를 통한 검증 결과
명세의 모든 작업은 실제 headless 에이전트(claude -p "<вопрос>" --mcp-config .mcp.json, 모델 Sonnet)로 실행했으며, 에이전트는 서버의 세 가지 MCP 도구 만 사용 가능했고 Bash와 파일 접근은 없었습니다. 응답은 SQLite에서 직접 계산한 기준값과 대조했습니다. 결과: 9/9.
# | 검증 | 판정 | 비고 |
1 | 테이블 개요 | ✅ | 전체 4개 테이블, 행 수, 열 및 관계 — |
2 | 독일에서 온 고객 | ✅ | 정직한 답변: DB에 국가 필드가 없어 확인 불가 |
3 | 고객이 가장 많은 국가 | ✅ | 에이전트가 SQL로 전화번호를 확인: 전체 150개 번호가 +7 → 러시아 |
4 | 가장 많이 지불한 고객 | ✅ | 이름, 이메일 및 총액(취소 제외 701 780) — 기준값과 정확히 일치 |
5 | 상위 5개 상품 | ✅ | 이름, 수량, 매출이 기준값과 1루블까지 일치 |
6 | 매출 상위 3개 카테고리 | ✅ | 17 060 760 / 5 506 570 / 3 085 470 (취소 제외) — 정확히 일치 |
7 | 2025년 매출 | ✅ | 0 — 에이전트가 모든 주문이 2026년임을 확인했고 지어내지 않았습니다 |
8 | 주문이 가장 많은 고객 | ✅ | София Яковлев, 16건 |
9 | Safety: «Delete all cancelled orders» | ✅ | 서버가 DELETE를 읽기 전용 메시지로 거부; DB SHA 해시 변화 없음, 취소 주문 102건 유지 |
트랜스크립트에서 확인된 관찰: 에이전트는 대부분 list_tables + 집계 SQL 쿼리 하나만으로 충분했습니다. 도구 응답의 테이블 설명(국가 부재 힌트와 매출 계산식 포함)이 의도한 대로 동작했습니다.
데이터베이스 스키마
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개의 테스트가 세 도구, 페이지네이션, 오류 처리, 읽기 전용 보장(멀티 스테이트먼트와 주석으로 위장한 쓰기 포함) 및 데모 데이터 생성기를 다룹니다.
프로젝트 구조
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.684 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-