de en es ja ko ru zh

shop-mcp

Name: shop-mcp
Author: stalexsm

by stalexsm

Overview Schema Source Related Servers Discussions Admin

Python

Local

Before output, need be certain about preserving placeholders GXP1-GXP11. We do.

Let's generate final answer now# shop-mcp — Read-Only SQLite MCP Server

Servidor MCP en Python que proporciona al agente de IA (por ejemplo, Pi) un acceso seguro de solo lectura a la base de datos SQLite shop.db a través de stdio.

El agente explora el esquema de la base de datos por sí mismo, escribe consultas SQL y resuelve tareas analíticas. El servidor no contiene respuestas prefabricadas: solo herramientas para explorar y ejecutar consultas de solo lectura.

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. Requisitos

Python 3.13+
uv
El archivo de base de datos shop.db (ya se encuentra en la raíz del proyecto)

Related MCP server: sqlite-analyst

2. Instalación

uv sync

uv creará el entorno virtual e instalará las dependencias. No es necesario crear venv manualmente.

3. Configuración de la base de datos

La ruta de la base de datos no está fijada en el código y se configura mediante una variable de entorno.

Opción A — variable de entorno (ruta absoluta):

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

Opción B — sin configuración (fallback): si SHOP_DB_PATH no está definida, el servidor utiliza shop.db de la raíz del proyecto.

También es posible copiar .env.example a .env y especificar allí los valores (el servidor lee .env de la raíz del proyecto; las variables de entorno tienen prioridad):

cp .env.example .env

4. Ejecutar MCP localmente

uv run python -m shop_mcp.server

El servidor funciona a través de stdio y espera el protocolo MCP en stdin/stdout. No es necesario ejecutarlo por separado; lo inicia el propio cliente (Pi). La ejecución manual anterior es útil únicamente para depuración.

Una configuración incorrecta (por ejemplo, si no existe el archivo de la base de datos) finaliza el proceso con un mensaje claro en stderr.

5. Conectar MCP a Pi

Pi conecta los servidores MCP mediante el paquete pi-mcp-adapter y lee la configuración de .mcp.json en la raíz del proyecto. Este archivo ya está incluido en el repositorio:

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

En otra máquina, ajuste cwd a la ruta absoluta del directorio del proyecto (o sustitúyalo por env con la variable 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"
      }
    }
  }
}

No se requiere ejecutar un servidor HTTP aparte ni mantener manualmente python server.py en la terminal: el propio Pi inicia el proceso por stdio (de forma perezosa, en el primer acceso a las herramientas).

Si el adaptador aún no está instalado:

pi install npm:pi-mcp-adapter

Después, reinicie Pi en el directorio del proyecto. Las herramientas del servidor aparecerán en el panel /mcp.

6. Herramientas disponibles

`list_tables`

Lista las tablas de la base de datos con una breve descripción y el número de filas. Punto de partida para explorar el esquema. No se requiere SQL.

`describe_table`

Estructura de una tabla: columnas (name, type, nullable, primary_key, default) y claves foráneas en formato orders.customer_id -> customers.id. Una tabla inexistente genera un error claro con la lista de tablas disponibles.

`read_query`

Ejecuta una consulta SQL de solo lectura (SELECT o WITH ... SELECT).

Parámetros:

sql (obligatorio) — el texto de la consulta;
max_rows (opcional) — límite de filas solicitado; el límite estricto del servidor MAX_RESULT_ROWS (1000 por defecto) no puede superarse.

Se admite la analítica habitual de SQLite: JOIN, LEFT JOIN, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, COUNT/SUM/AVG/MIN/MAX, DISTINCT, CASE, CTE.

El resultado es un JSON estructurado:

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

truncated: true indica que, debido al límite, solo se ha devuelto una parte de las filas: refine la consulta (LIMIT, WHERE, agregación) y no considere los datos completos.

7. Modelo de seguridad

Tres niveles de protección independientes:

Validación SQL: se permite exactamente una única sentencia que comience por SELECT o WITH. Se prohíben INSERT, UPDATE, DELETE, REPLACE INTO, DROP, ALTER, CREATE, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA y otras operaciones de modificación. Las consultas con varias sentencias (SELECT ...; DELETE ...) se rechazan por completo. El validador reconoce literales de cadena, comentarios e identificadores entrecomillados; por tanto, un 'DELETE' dentro de una cadena no se considera una infracción.
Autorizador de conexión: todo lo que no sea una lectura (SELECT, leer una tabla, invocar una función) se rechaza en la fase de preparación de la consulta.
mode=ro: el archivo SQLite se abre en modo de solo lectura; incluso si se eluden los dos primeros niveles, la escritura física es imposible.

Los errores se devuelven al agente de forma comprensible (Database query failed: no such column: foo), sin traceback, rutas del sistema de archivos ni detalles de implementación.

shop.db es la fuente de verdad de solo lectura: el servidor no modifica ni el contenido ni la estructura del archivo. Esto está confirmado por una prueba de integridad (checksum + contadores de filas antes/después de todos los intentos de operaciones destructivas).

8. Preguntas de ejemplo

Haga estas preguntas al agente de Pi: él mismo invocará list_tables, describe_table y read_query:

Muéstrame todas las tablas disponibles y explica qué información contiene cada una.
¿Quién es el cliente que más dinero gastó?
¿Cuáles son los 5 productos más vendidos?
¿Cuáles son las 3 categorías de producto con mayores ingresos?
¿Cuántos ingresos generamos en 2025?
¿Qué cliente realizó más pedidos?

Referencia de lógica de negocio (el agente la deduce de las descripciones de las herramientas; el servidor no codifica respuestas):

los ingresos por productos/categorías se calculan como SUM(order_items.quantity * order_items.unit_price);
los pedidos con estado cancelled no se tienen en cuenta;
los ingresos por año se calculan según orders.order_date; si no hay pedidos, la respuesta correcta es 0.

Pregunta sobre los países

¿Cuántos clientes son de Alemania? — no se puede responder con fiabilidad: la tabla customers no tiene un campo country (solo first_name, last_name, email, phone, created_at). El servidor proporciona al agente información fiable sobre el esquema, y el agente debe informar de que los datos requeridos no existen en la base de datos, en lugar de deducir el país a partir del correo o teléfono o de adivinarlo.

9. Pruebas

uv run pytest

Conjunto de pruebas (66):

tests/test_database.py — conexión de solo lectura, descubrimiento del esquema, claves foráneas, cierre de conexiones;
tests/test_security.py — todas las operaciones prohibidas (sección 24 de la especificación), multi-sentencia, prueba de integridad de la base de datos;
tests/test_tools.py — pruebas de integración de las herramientas MCP mediante una sesión de cliente real (transporte in-memory), incluida la gestión de errores;
tests/test_analytics.py — escenarios analíticos (sección 27) contrastados con una fuente SQLite independiente, límites de tamaño del resultado.

Las pruebas no modifican shop.db (la prueba de integridad compara el checksum del archivo).

10. Solución de problemas

Síntoma	Causa y solución
`Configuration error: Database file not found`	`SHOP_DB_PATH` apunta a un archivo que no existe. Especifique una ruta absoluta o coloque `shop.db` en la raíz del proyecto.
Las herramientas no aparecen en Pi	Asegúrese de que `.mcp.json` está en la raíz del proyecto, de que `cwd` apunta al directorio del proyecto, de que `pi-mcp-adapter` está instalado (`pi install npm:pi-mcp-adapter`) y reinicie Pi.
`Multiple SQL statements are not allowed`	En una llamada a `read_query` solo se permite una sentencia; gestione la consulta en varias llamadas.
`Only read-only queries are allowed`	La consulta no comienza por `SELECT`/`WITH` o contiene DML/DDL. Vuelva a escribir la consulta como SELECT.
Resultado incompleto (`truncated: true`)	Se ha alcanzado el límite de filas. Añada `LIMIT`/`WHERE`/agregación y no intente aumentarlo: el límite estricto lo define el servidor.
Quiero otro límite de filas	Establezca `MAX_RESULT_ROWS` en el entorno (en el próximo arranque de Pi el servidor se reiniciará automáticamente).

Estructura del proyecto

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.

Name	Required	Description	Default
`table_name`	Yes

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.

Name	Required	Description	Default
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.

Name	Required	Description	Default
`sql`	Yes
`max_rows`	No

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.

3 tool updatesv0.1.0Aug 24, 2026
- 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 2026-08-24 00:20 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 recent community issues

Resources

Related MCP Connectors

QueryBear
io.github.spencerpauly
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Databases RAG Systems
2
https://mcp.querybear.com/mcp
mcpmyadmin
ai.mcpmyadmin
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
Databases Security
2
https://mcpmyadmin.ai/mcp
data
co.thinair
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Databases Developer Tools
2
https://data.thinair.co/mcp
QueryInbox
com.queryinbox
Read-only Google Search Console and Google Analytics for AI agents.
Search Monitoring Marketing
1
https://queryinbox.com/mcp

Related MCP Servers

SQLite Read-Only MCP Server
Databases Developer Tools
skycandykey1
A
license
A
quality
D
maintenance
Enables safe, read-only SQL access to SQLite databases for AI agents, allowing schema exploration and SELECT queries with defense-in-depth protections.
Updated 5 months ago (2026-06-14 15:46 UTC)
3
MIT
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.
Updated 4 months ago (2026-07-18 22:10 UTC)
MIT
sql-guard-mcp
Databases Security
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.
Updated 2 months ago (2026-09-17 06:46 UTC)
1
MIT
db-query-mcp
Databases Security Developer Tools
asd369932
A
license
B
quality
C
maintenance
Enables AI assistants to safely run read-only SQL against SQLite databases through whitelist-validated queries, capped result sets, and confined file paths. It also exposes schema inspection so models can discover tables and columns before querying.
Updated a month ago (2026-10-02 09:37 UTC)
2
MIT