Skip to main content
Glama

MCP-Server shop-db

Read-only MCP-Server (stdio-Transport), der einem KI-Agenten Zugriff auf die SQLite-Datenbank des Online-Shops shop.db gibt: Kunden, Produkte, Bestellungen und Bestellpositionen.

Basiert auf dem offiziellen MCP Python SDK (v2).

Entwicklungsmetadaten

Das Projekt wurde vollständig von einem KI-Coding-Agenten (Claude Code, Modell Fable 5) gemäß der Spezifikation SPEC.md erzeugt.

Metrik

Wert

Größe der Spezifikation in Token

≈ 1 800 (cl100k_base; 1 316 in o200k_base)

Beim ersten Mal gestartet

Ja – der Server startete über stdio und bestand alle 8 Aufgaben + Sicherheitsprüfung beim ersten Start

Anzahl der Unterstützungsanfragen

4 (MCP-SDK-Dokumentation über Context7 – 3, Versionsprüfung auf PyPI – 1)

Gesamtzahl der Prompts

6 (Spezifikation; echte shop.db + Metadaten; README-Übersetzung; Testlauf; Ergebnis-Zusammenfassung; Aktualisierung + Veröffentlichung)

Letztendliche Anzahl an Bugs

0 im Servercode; 2 kleine Bugs in Hilfsdateien (Importreihenfolge in einem Test, veralteter Feldname in einem Einweg-E2E-Skript), vor dem Commit behoben

Gesamtzahl der verbrauchten Tokens

≈ 365 000: ≈ 175 000 Hauptsitzung + ≈ 190 000 Sub-Agenten des Testlaufs (ohne die headless-Agenten selbst, die die Prüfung durchliefen)

Related MCP server: Shop Analytics MCP Server

Werkzeuge (Tools)

Tool

Zweck

list_tables

Übersicht aller Tabellen: Zeilenanzahl, Spalten, Beschreibung, Beziehungen zwischen Tabellen. Sinnvoller erster Aufruf.

describe_table(table_name)

Vollständiges Schema einer Tabelle: Spaltentypen, Primär-/Fremdschlüssel, 3 Beispielzeilen.

query(sql, limit=50, offset=0)

Führt ein einzelnes Read-only SELECT (oder WITH ... SELECT) aus. Unterstützt JOIN und Aggregation. Ergebnisse werden paginiert: maximal 500 Zeilen pro Aufruf, mit truncated / next_offset.

Sicherheit

Die Datenbank kann über diesen Server nicht verändert werden. Drei unabhängige Schutzebenen:

  1. Abfragevalidierung – alles, was kein einzelnes SELECT/WITH ist (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, PRAGMA, ATTACH, mehrere Anweisungen hintereinander, ein in einem Kommentar verstecktes Schreiben), wird mit einer klaren Meldung abgelehnt, noch bevor es ausgeführt wird.

  2. Read-only-Verbindung – Die Datei wird über einen SQLite-URI mit mode=ro geöffnet.

  3. PRAGMA query_only = ON für jede Verbindung.

Selbst ein Schreibversuch, der die Validierung passiert (z. B. WITH ... INSERT), scheitert an der Read-only-Verbindung.

SQL-Fehler werden als kurze, verständliche Meldungen mit Hinweisen dargestellt – ohne Stack-Traces.

Installation

Benötigt wird Python 3.10+.

Mit uv (empfohlen – beim ersten Start wird automatisch alles installiert):

uv sync

Oder mit pip:

python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

Konfiguration

Der Server sucht die Datenbank in der Datei shop.db neben server.py – die in der Aufgabe bereitgestellte Datenbank ist im Repository enthalten. Um eine andere Datei zu verwenden, setzen Sie die Umgebungsvariable SHOP_DB_PATH:

export SHOP_DB_PATH=/path/to/shop.db

seed_db.py – ein Hilfsprogramm, das eine Demo-Datenbank mit ähnlichem Schema erzeugt (nur für einmalige Daten nötig; shop.db wird davon nicht verändert, sofern nicht ausdrücklich erbetet):

python seed_db.py /tmp/demo.db

Start

Der Server kommuniziert über stdio – er wird vom MCP-Client gestartet, nicht manuell vom Benutzer. So überprüfen Sie, dass er fehlerfrei startet:

uv run python server.py

(Der Server wartet auf MCP-Nachrichten auf stdin; Beenden mit Ctrl+C)

Anbindung an den Agenten

Claude Code

Im Repository liegt .mcp.json, daher wird der Server aus dem Projektverzeichnis automatisch übernommen. Manuelle Anbindung:

claude mcp add shop-db -- uv run --directory /absolute/path/to/sqlite-mcp python server.py

Claude Desktop (oder conflatt drei beliebigen Client mit JSON-Konfiguration)

Hinzufügen zu claude_desktop_config.json (siehe examples/claude_desktop_config.example.json) – bei der Installation mit pip müssen die Abhängigkeiten in dem Interpreter vorhanden sein, der in der Konfiguration angegeben ist:

{
  "mcpServers": {
    "shop-db": {
      "command": "/absolute/path/to/sqlite-mcp/.venv/bin/python",
      "args": ["/absolute/path/to/sqlite-mcp/server.py"]
    }
  }
}

Oder über uv (manuelle Installation nicht benötigt):

{
  "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"]
    }
  }
}

Beispiel Fragen, die der Agent beherrscht

  • Zeige mir alle verfügbaren Tabellen und erkläre, welche Informationen jede Tabelle enthält.

  • Wie viele Kunden kommen aus Deutschland?

  • Welches Land hat die meisten Kunden?

  • Wer hat als Kunde am meisten Geld ausgegeben?

  • Was sind die 5 meistverkauften Produkte?

  • Was sind die 3 Produktkategorien mit dem höchsten Umsatz?

  • Wie viel Umsatz haben wir im Jahr 2025 erzielt?

  • Welcher Kunde hat die meisten bestellungen aufgegeben?

Destruktive Fragen („Alle abgebrochenen Bestellungen löschen“) läuft auf den Server.

Hinweis zu den bereitgestellten Daten: In customers gibt es keine Spalte in Ländern (lokal) / Lage kann nur anhand der Telefoncodes – alle Nummern +7 – oder E-Mail-Domains geschlossen werden) und alle Bestellungen sind von Februar bis August 2026 datiert. Die Schema-Tools geben dem Agenten alles, um das zu erkennen und ehrlich zu beantworten.

Ergebnisse der Prüfung mit realem Agenten

Alle Aufgaben der Spezifikation wurden mit echten headless-Agenten (claude -p "<вопрос>" --mcp-config .mcp.json, Modell Sonnet) durchgeführt, die nur über die drei MCP-Tools des Servers verfügten – ohne Bash und ohne Dateizugriff. Die Antworten wurden mit Referenzergebnissen abgeglichen, die direkt von SQLite berechnet wurden. Ergebnis: 9/9.

#

Prüfung

Ergebnis

Kommentar

1

Tabellenübersicht

✅

Alle 4 Tabellen, Zeilenanzahl, Spalten und Abhängigkeiten – mit einem einzigen Aufruf von list_tables

2

Kunden aus Deutschland

✅

Ehrliche Antwort: In der Datenbank ist kein Land gegenüber; nicht feststellbar.

3

Land mit den meisten Kunden

✅

Der Agent prüfte die Telefonnummern per SQL-Abfrage: Alle 150 Nummern sind +7 → Russland

4

Kunde mit größten Zahlungen

✅

Name, E-Mail und Betrag (701 780 ohne stornierte) – stimmen exakt mit der Referenz überein

5

Top-5-Produkten

✅

Name, Stück und Umsatz stimmend bis auf astronomy restlos mit der Referenz überein

6

Top-3-Kategorien nach Umsatz

✅

17.060.760 / 5.506.570 / 3.085.470 (ohne stornierte) – exakte Übereinstimmung

7

Umsatz 2025

✅

0 – der Agent stellte fest, dass alle Bestellungen aus 2026 stammen, und erfand nichts dazu

8

Kunde mit den meisten Bestellungen

✅

София Яковлев, 16 Bestellungen

9

Safety: „Alle stornierten Bestellungen löschen“

✅

Ein UPDATE wird mit einer Read-only-Meldung abgelehnt; SHA des Hash – Datenbank unverändert, 102 stornierte Bestellungen vorhanden.

Beobachtung aus den Transscripts: den Agenten reichten fast immer list_tables + einen aggregieren SQL-Abfrage aus – die Tabellenbeschreibungen in den Antworten der Tools (inkl. Hinweis auf eine fehlende Länder-Spalte und Umsatzformel) funktionierten wie gewünscht.

Datenbankschema

customers ──< orders ──< order_items >── products
  • customers (150 Zeilen) – id, first_name, last_name, email, phone, created_at

  • products (50 Zeilen) – id, name, category, price, st_\content, created_at

  • orders (1.750 Zeilen) – id, custom_code → customers, order_date, status (new/processing/shipped/completed/cancelled), total_amount

  • order_items (1900 Zeilen) – id, order_id → orders, product_id → products, quantity, unit_price

Tests

uv run pytest

36 Tests decken alle drei Tools ab, Pagination, Fehlerbehandlung, Read-only-Garantien (einschließlich Mehrfachanweisungen und einen Schreibvorgang, der mit ein Anwenden kommentiert ist) sowie den Demo-Daten-Generator

Projektstrukur

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 tools
describe_tableDescribe one tableA
Read-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.

NameRequiredDescriptionDefault
table_nameYesExact table name as returned by list_tables, e.g. 'orders'.

Output Schema

NameRequiredDescription

No output parameters

TDQS

A4.5/5.0
Behavior4/5

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.

Conciseness5/5

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.

Completeness5/5

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.

Parameters3/5

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.

Purpose5/5

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.

Usage Guidelines5/5

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

NameRequiredDescriptionDefault

No parameters

Output Schema

NameRequiredDescription

No output parameters

TDQS

A4.7/5.0
Behavior4/5

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.

Conciseness5/5

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.

Completeness5/5

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.

Parameters4/5

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.

Purpose5/5

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.

Usage Guidelines5/5

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

NameRequiredDescriptionDefault
sqlYesA 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.
limitNoMaximum rows to return, 1-500 (default 50). Prefer aggregating in SQL over fetching many raw rows.
offsetNoRows to skip for pagination (default 0). Use next_offset from a truncated result.

Output Schema

NameRequiredDescription

No output parameters

TDQS

A4.3/5.0
Behavior4/5

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.

Conciseness4/5

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.

Completeness4/5

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.

Parameters3/5

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.

Purpose5/5

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.

Usage Guidelines5/5

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.

  1. 3 tool updatesv1.0.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedquery

TDQS

A4.4/5.0

Scored across 3 tools

Disambiguation5/5

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.

Naming Consistency4/5

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.

Tool Count4/5

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.

Completeness5/5

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

ActivitySlowing
ResponsivenessNo issues

Related MCP Connectors

Related MCP Servers

  • A
    license
    A
    quality
    B
    maintenance
    Enables 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.
    6
    64 npm
    MIT
  • F
    license
    A
    quality
    C
    maintenance
    Enables 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
    -
  • F
    license
    A
    quality
    C
    maintenance
    Enables 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
    -
  • F
    license
    A
    quality
    B
    maintenance
    Gives 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
    -