Skip to main content
Glama
deenesjakoruzh
stalexsm

shop-mcp

by stalexsm
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

shop-mcp — Read-Only SQLite MCP Server

MCP-сервер на Python, предоставляющий AI-агенту (например, Pi) безопасный read-only доступ к базе данных SQLite shop.db через stdio.

Агент самостоятельно исследует схему БД, пишет SQL-запросы и решает аналитические задачи. Сервер не содержит готовых ответов — только инструменты для исследования и выполнения read-only запросов.

AI Agent (Pi)
        │  stdio
        ▼
┌────────────────────┐
│     MCP Server     │   list_tables / describe_table / read_query
└─────────┬──────────┘
          ▼
   SQL validation            ← только один SELECT / WITH ... SELECT
          ▼
   read-only guard           ← connection authorizer
          ▼
   SQLite (mode=ro)          ← файл физически невозможно изменить

1. Requirements

  • Python 3.13+

  • uv

  • Файл базы данных shop.db (уже находится в корне проекта)

Related MCP server: sqlite-analyst

2. Installation

uv sync

uv создаст виртуальное окружение и установит зависимости. Вручную создавать venv не нужно.

3. Database configuration

Путь к базе не захардкожен и настраивается через переменную окружения.

Вариант A — переменная окружения (абсолютный путь):

export SHOP_DB_PATH=/absolute/path/to/shop.db
export MAX_RESULT_ROWS=1000   # опционально, default 1000

Вариант B — без настройки (fallback): если SHOP_DB_PATH не задана, сервер использует shop.db из корня проекта.

Допустимо также скопировать .env.example в .env и указать значения там (сервер читает .env из корня проекта; переменные окружения имеют приоритет):

cp .env.example .env

4. Run MCP locally

uv run python -m shop_mcp.server

Сервер работает через stdio и ожидает MCP-протокол на stdin/stdout — отдельно его запускать не нужно, его запускает сам клиент (Pi). Ручной запуск выше полезен только для отладки.

Некорректная конфигурация (например, отсутствует файл БД) завершает процесс с понятным сообщением в stderr.

5. Connect MCP to Pi

Pi подключает MCP-серверы через пакет pi-mcp-adapter и читает конфигурацию из .mcp.json в корне проекта. Такой файл уже входит в репозиторий:

{
  "mcpServers": {
    "shop": {
      "command": "uv",
      "args": ["run", "python", "-m", "shop_mcp.server"],
      "cwd": "/Users/stalexsm/projects/shop-mcp"
    }
  }
}

Для другой машины поправьте cwd на абсолютный путь к каталогу проекта (или замените на env с переменной SHOP_DB_PATH):

{
  "mcpServers": {
    "shop": {
      "command": "uv",
      "args": ["run", "python", "-m", "shop_mcp.server"],
      "cwd": "/absolute/path/to/shop-mcp",
      "env": {
        "SHOP_DB_PATH": "/absolute/path/to/shop.db",
        "MAX_RESULT_ROWS": "1000"
      }
    }
  }
}

Запускать отдельный HTTP-сервер или вручную держать python server.py в терминале не требуется: Pi сам стартует процесс по stdio (лениво, при первом обращении к инструментам).

Если адаптер ещё не установлен:

pi install npm:pi-mcp-adapter

Затем перезапустите Pi в каталоге проекта. Инструменты сервера появятся в панели /mcp.

6. Available tools

list_tables

Список таблиц БД с кратким описанием и количеством строк. Отправная точка исследования схемы. SQL не требуется.

describe_table

Структура одной таблицы: колонки (name, type, nullable, primary_key, default) и foreign keys в виде orders.customer_id -> customers.id. Несуществующая таблица даёт понятную ошибку со списком доступных таблиц.

read_query

Выполнение одного read-only SQL-запроса (SELECT или WITH ... SELECT).

Параметры:

  • sql (обязательный) — текст запроса;

  • max_rows (опциональный) — запрошенный лимит строк; серверный hard limit MAX_RESULT_ROWS (по умолчанию 1000) не может быть превышен.

Поддерживается обычная аналитика SQLite: JOIN, LEFT JOIN, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, COUNT/SUM/AVG/MIN/MAX, DISTINCT, CASE, CTE.

Результат — структурированный JSON:

{
  "columns": ["name", "revenue"],
  "rows": [["Ноутбук UltraBook 15", 6569270.0]],
  "row_count": 1,
  "truncated": false,
  "execution_time_ms": 0.716
}

truncated: true означает, что из-за лимита возвращена только часть строк — уточните запрос (LIMIT, WHERE, агрегация), не считая данные полными.

7. Security model

Три независимых уровня защиты:

  1. SQL validation — разрешён ровно один statement, начинающийся с SELECT/WITH. Запрещены INSERT, UPDATE, DELETE, REPLACE INTO, DROP, ALTER, CREATE, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA и другие изменяющие операции. Multi-statement запросы (SELECT ...; DELETE ...) отклоняются целиком. Валидатор понимает строковые литералы, комментарии и закавыченные идентификаторы, поэтому 'DELETE' внутри строки не считается нарушением.

  2. Connection authorizer — всё, что не является чтением (SELECT / чтение таблицы / вызов функции), отклоняется на этапе подготовки запроса.

  3. mode=ro — файл SQLite открывается в read-only режиме; даже при обходе первых двух уровней физическая запись невозможна.

Ошибки возвращаются агенту в понятном виде (Database query failed: no such column: foo) — без traceback, путей файловой системы и деталей реализации.

shop.db — read-only source of truth: сервер не изменяет ни содержимое, ни структуру файла. Это зафиксировано тестом целостности (checksum + счётчики строк до/после всех попыток разрушающих операций).

8. Example questions

Задайте эти вопросы агенту Pi — он сам вызовет list_tables, describe_table и read_query:

  • Show me all available tables and explain what information each table contains.

  • Who is the customer who spent the most money?

  • What are the top 5 best-selling products?

  • What are the top 3 product categories by revenue?

  • How much revenue did we generate in 2025?

  • Which customer placed the most orders?

Справка по бизнес-логике (агент выводит это из описаний инструментов, сервер ответов не кодирует):

  • revenue по товарам/категориям считается как SUM(order_items.quantity * order_items.unit_price);

  • заказы со статусом cancelled не учитываются;

  • revenue по годам считается по orders.order_date; если заказов нет — корректный ответ 0.

Вопрос про страны

How many customers are from Germany? — на этот вопрос нельзя достоверно ответить: в таблице customers нет поля country (только first_name, last_name, email, phone, created_at). Сервер отдаёт агенту достоверную информацию о схеме, а агент обязан сообщить, что требуемых данных в БД нет, вместо того чтобы выводить страну из email/телефона или гадать.

9. Testing

uv run pytest

Набор тестов (66):

  • tests/test_database.py — read-only подключение, discovery схемы, foreign keys, закрытие соединений;

  • tests/test_security.py — все запрещённые операции (раздел 24 спецификации), multi-statement, integrity-тест БД;

  • tests/test_tools.py — интеграционные тесты MCP-инструментов через реальную клиентскую сессию (in-memory transport), включая обработку ошибок;

  • tests/test_analytics.py — аналитические сценарии (раздел 27) со сверкой против независимого SQLite-источника, лимиты размера результата.

Тесты не изменяют shop.db (integrity-тест сверяет checksum файла).

10. Troubleshooting

Симптом

Причина и решение

Configuration error: Database file not found

SHOP_DB_PATH указывает на несуществующий файл. Укажите абсолютный путь или положите shop.db в корень проекта.

Инструменты не видны в Pi

Убедитесь, что .mcp.json лежит в корне проекта, cwd указывает на каталог проекта, установлен pi-mcp-adapter (pi install npm:pi-mcp-adapter), и перезапустите Pi.

Multiple SQL statements are not allowed

В одном вызове read_query допускается ровно один statement; разделите запрос на несколько вызовов.

Only read-only queries are allowed

Запрос начинается не с SELECT/WITH либо содержит DML/DDL. Перепишите запрос как SELECT.

Результат неполный (truncated: true)

Сработал лимит строк. Добавьте LIMIT/WHERE/агрегацию или увеличивать его не пытайтесь — hard limit задаёт сервер.

Хочу другой лимит строк

Задайте MAX_RESULT_ROWS в окружении (сервер перезапустится Pi автоматически при следующем старте).

Project layout

shop-mcp/
├── README.md
├── pyproject.toml
├── uv.lock
├── .env.example
├── .gitignore
├── .mcp.json              # конфигурация MCP для Pi
├── shop.db                # read-only source of truth
├── scripts/
│   └── smoke_stdio.py     # ручной smoke-тест через реальный stdio
├── src/shop_mcp/
│   ├── __init__.py
│   ├── server.py          # MCP-инструменты (stdio)
│   ├── database.py        # read-only слой доступа к SQLite
│   ├── security.py        # SQL validation + single-statement guard
│   ├── models.py          # структуры результатов
│   └── config.py          # SHOP_DB_PATH / MAX_RESULT_ROWS
└── tests/
    ├── test_database.py
    ├── test_security.py
    ├── test_tools.py
    └── test_analytics.py

Available Tools

3 tools
describe_tableA

Describe the structure of one table: columns with name, type, nullable and primary_key flags, plus foreign-key relationships such as 'orders.customer_id -> customers.id'. Use this after list_tables to understand the schema before writing SQL. Pass a table name that was returned by list_tables; unknown names produce a clear error.

NameRequiredDescriptionDefault
table_nameYes

TDQS

A4.8/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Since annotations are absent, the description carries full burden. It discloses the return content (columns, flags, foreign keys) and error behavior for invalid input. While it doesn't explicitly state it is read-only, the non-destructive nature is strongly implied by 'describe' and the absence of any mutating language. It could have been 5 if it explicitly said 'performs no data modification', but the current disclosure is sufficient for safe invocation.

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 concise sentences with zero filler: the first states the purpose and output, the second gives usage context, and the third constrains input and defines error behavior. It is front-loaded with the most critical information and every sentence earns its place.

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 simple tool with one parameter and no output schema, the description is complete: it covers what it does, when to use it, how to choose the parameter, and error handling. An agent has everything needed to call it correctly without additional inference. No behavioral annotations exist, but the description compensates fully for their absence.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters5/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema coverage is 0% and the only parameter has no description beyond its title, so the description adds significant value by explaining that the table_name must come from list_tables and that unknown names cause an error. This goes beyond the schema and directly helps the agent select a valid value, making the parameter's semantics fully clear.

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 explicitly states the verb 'describe' and the resource 'structure of one table', then lists the exact components (columns with flags, foreign keys). It clearly distinguishes itself from siblings by positioning it as the follow-up to list_tables and a prerequisite for writing SQL, while read_query is for actual data retrieval.

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?

It gives explicit when-to-use context ('Use this after list_tables to understand the schema before writing SQL') and even instructs that the table name must come from list_tables. It also preempts misuse by stating unknown names produce a clear error, leaving no ambiguity about prerequisites or error handling.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

list_tablesA

List all tables available in the database, with a short description and the number of rows of each table. Use this first when you do not know the schema, before writing any SQL. No SQL is required.

NameRequiredDescriptionDefault

No parameters

TDQS

A4.3/5.0
Behavior3/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are provided, so the description carries the burden of behavioral disclosure. It specifies the output (list of tables with description and row count) and notes 'No SQL is required,' implying a read-only metadata operation. However, it does not explicitly state that the operation is read-only or side-effect-free, nor does it mention any authentication or performance considerations, which are minor for a simple listing tool.

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 two concise sentences. The first sentence states the action and output, and the second provides usage context. No filler words or redundancies exist, and the most important information (what it does) is front-loaded.

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?

Given the tool has no parameters, no output schema, and a simple, self-contained purpose, the description is complete. It tells the agent exactly what it will receive (tables, descriptions, row counts) and when to call it. Nothing critical is missing for effective use.

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, and the schema coverage is trivially 100%. Per the baseline rule for 0-parameter tools, a score of 4 is appropriate. The description adds value by explaining what the tool outputs, which fully compensates for the absence of parameters.

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 tool lists all tables with a short description and row counts. It also differentiates itself from siblings by saying 'Use this first when you do not know the schema, before writing any SQL,' which implies it is a schema-discovery tool distinct from describe_table (specific table details) and read_query (SQL execution).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description explicitly states when to use it ('when you do not know the schema' and 'before writing any SQL'), providing clear contextual guidance. It does not name alternative tools explicitly, but the context strongly implies that after gaining schema knowledge, one would use read_query or describe_table, which is sufficient for minimal guidance.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

read_queryA

Execute a single read-only SQL statement (SELECT or WITH ... SELECT) against the shop database. JOIN, LEFT JOIN, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, DISTINCT, CASE and CTE are supported. If you do not know the schema yet, call list_tables and describe_table first. Rules enforced by the server: exactly one statement per call (multi-statement SQL is rejected), and no data or schema modification (INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA are rejected). The database is opened read-only, so writes are impossible. The result is structured: {columns, rows, row_count, truncated, execution_time_ms}. If truncated is true, only the first row_count rows were returned due to the server-side limit (max_rows, default 1000): refine the query, e.g. add LIMIT, WHERE or aggregation, instead of assuming the data is complete.

NameRequiredDescriptionDefault
sqlYes
max_rowsNo

TDQS

A4.9/5.0
Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations provided, so description carries full burden. It discloses that the database is opened read-only (writes impossible), rejects multi-statement SQL and all DML/DDL/PRAGMA, and details the output structure with truncation semantics. This is a model of behavioral transparency for a tool with no 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 dense but perfectly organized: operation definition, supported features, prerequisite guidance, server rules, result format, and truncation handling. Every sentence adds critical information with no redundancy. Front-loads the core purpose and essential constraints, making it easy to parse.

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?

Covers everything an agent needs to call this tool correctly: allowed SQL types, supported clauses, schema discovery prerequisite, server-side restrictions, read-only guarantee, output format, truncation behavior, max_rows default, and remediation advice. With no output schema, the description provides a complete picture, making it highly complete for a complex SQL execution tool.

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?

Schema description coverage is 0%, so the description must add meaning. It explains the sql parameter implicitly (the SQL statement to execute) and mentions max_rows with its default and effect on truncation. While not a dedicated parameter-by-parameter breakdown, it effectively conveys the purpose and impact of both parameters, going well beyond the bare schema.

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), resource (read-only SQL statement against the shop database), and explicitly enumerates allowed SQL constructs (SELECT, WITH ... SELECT, JOIN, GROUP BY, etc.). This clearly distinguishes it from sibling tools list_tables and describe_table, which are schema introspection rather than data querying.

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?

Provides explicit when-to-use vs. alternatives: 'If you do not know the schema yet, call list_tables and describe_table first.' Also states constraints (one statement per call, no modification statements) and practical advice on handling truncation (refine query with LIMIT/WHERE/aggregation). This leaves no ambiguity about when and how to use the tool.

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 updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedread_query
Deploy Server
One endpoint for every MCP toolRayrun runs every MCP server behind one URL and holds the credentials. Each call is decided per client, tool, and argument, then recorded.ray.runAd

TDQS

A4.7/5.0

Scored across 3 tools

Disambiguation5/5

Each tool serves a distinct, non-overlapping purpose: listing tables, describing a single table's schema, and executing read-only SQL queries. No ambiguity in selecting between them.

Naming Consistency5/5

All tool names follow a consistent verb_noun snake_case pattern: list_tables, describe_table, read_query. This is uniform and predictable.

Tool Count5/5

Three tools are exactly right for this focused read-only database exploration server. Each tool earns its place with no redundancy, and the count falls within the typical well-scoped range.

Completeness5/5

The tool surface covers the full workflow for safe database discovery: discover schema (list_tables), inspect structure (describe_table), and query data (read_query). No obvious gaps for the stated purpose, and write operations are intentionally excluded.

Maintenance

ActivityMaintained
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers

  • sqlite-analyst

    yogaibhh
    A
    license
    Not graded
    quality
    D
    maintenance
    Enables AI assistants to explore and query SQLite databases through read-only tools, with defense-in-depth sandboxing preventing any data modifications.
    MIT

  • sql-guard-mcp

    egoring
    A
    license
    Not graded
    quality
    B
    maintenance
    Enables AI agents to safely query and explore SQLite databases through read-only, guard-protected tools that block writes, sensitive table access, and runaway queries.
    1
    MIT