Skip to main content
Glama
deenesjakoruzh
stalexsm

shop-mcp

by stalexsm
OverviewSchemaSourceRelated ServersScoreDiscussionsAdmin
Python
Local

shop-mcp — Read-Only SQLite MCP Server

An MCP server in Python that provides an AI agent (e.g., Pi) with safe read-only access to the SQLite database shop.db via stdio.

The agent independently explores the database schema, writes SQL queries, and solves analytical tasks. The server contains no ready-made answers — only tools for exploring and executing read-only queries.

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

  • The shop.db database file (already located in the project root)

Related MCP server: safe-sql-mcp

2. Installation

uv sync

uv will create a virtual environment and install the dependencies. There is no need to create a venv manually.

3. Database configuration

The database path is not hardcoded and is configured through an environment variable.

Option A — environment variable (absolute path):

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

Option B — no configuration (fallback): if SHOP_DB_PATH is not set, the server uses shop.db from the project root.

You can also copy .env.example to .env and specify the values there (the server reads .env from the project root; environment variables take precedence):

cp .env.example .env

4. Run MCP locally

uv run python -m shop_mcp.server

The server works over stdio and expects the MCP protocol on stdin/stdout — there is no need to start it separately; the client (Pi) launches it. The manual run above is useful only for debugging.

Invalid configuration (for example, a missing database file) terminates the process with a clear message on stderr.

5. Connect MCP to Pi

Pi connects MCP servers through the pi-mcp-adapter package and reads the configuration from .mcp.json in the project root. Such a file is already included in the repository:

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

For another machine, set cwd to the absolute path to the project directory (or replace it with env and the SHOP_DB_PATH variable):

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

There is no need to run a separate HTTP server or manually keep python server.py running in the terminal: Pi itself starts the process via stdio (lazily, on first access to the tools).

If the adapter is not installed yet:

pi install npm:pi-mcp-adapter

Then restart Pi in the project directory. The server tools will appear in the /mcp panel.

6. Available tools

list_tables

List of database tables with a brief description and row counts. A starting point for schema exploration. SQL is not required.

describe_table

Structure of a single table: columns (name, type, nullable, primary_key, default) and foreign keys in the form orders.customer_id -> customers.id. A nonexistent table gives a clear error with the list of available tables.

read_query

Executes a single read-only SQL query (SELECT or WITH ... SELECT).

Parameters:

  • sql (required) — the query text;

  • max_rows (optional) — the requested row limit; the server-side hard limit MAX_RESULT_ROWS (default 1000) cannot be exceeded.

Supported SQLite analytics: JOIN, LEFT JOIN, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET, COUNT/SUM/AVG/MIN/MAX, DISTINCT, CASE, CTE.

The result is structured JSON:

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

truncated: true means that due to the limit, only part of the rows was returned — refine the query (LIMIT, WHERE, aggregation) and do not consider the data complete.

7. Security model

Three independent levels of protection:

  1. SQL validation — exactly one statement starting with SELECT/WITH is allowed. INSERT, UPDATE, DELETE, REPLACE INTO, DROP, ALTER, CREATE, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA, and other modifying operations are prohibited. Multi-statement queries (SELECT ...; DELETE ...) are rejected in full. The validator understands string literals, comments, and quoted identifiers, so 'DELETE' inside a string is not considered a violation.

  2. Connection authorizer — anything that is not a read (SELECT / read from a table / function call) is rejected at the query preparation stage.

  3. mode=ro — the SQLite file is opened in read-only mode; even bypassing the first two levels, physical writes are impossible.

Errors are returned to the agent in a clear form (Database query failed: no such column: foo) — without a traceback, file system paths, or implementation details.

shop.db is a read-only source of truth: the server does not modify either the content or the structure of the file. This is verified by the integrity test (checksum + row counts before/after all attempts at destructive operations).

8. Example questions

Ask these questions to the Pi agent — it will call list_tables, describe_table, and read_query on its own:

  • 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?

Business logic reference (the agent derives it from the tool descriptions; the server does not encode answers):

  • revenue for products/categories is calculated as SUM(order_items.quantity * order_items.unit_price);

  • orders with status cancelled are not counted;

  • revenue by year is calculated based on orders.order_date; if there are no orders — the correct answer is 0.

Question about countries

How many customers are from Germany?

This question cannot be answered reliably: the customers table has no country field (only first_name, last_name, email, phone, created_at). The server provides the agent with reliable schema information, and the agent must report that the required data is absent from the database, rather than inferring the country from the email/phone or guessing.

9. Testing

uv run pytest

Test suite (66):

  • tests/test_database.py — read-only connection, table discovery, foreign keys, closing connections;

  • tests/test_security.py — all prohibited operations (section 24 of the specification), multi-statement, database integrity test;

  • tests/test_tools.py — integration tests of MCP tools through a real client session (in-memory transport), including error handling;

  • tests/test_analytics.py — analytical scenarios (section 27) cross-checked against an independent SQLite source, result sie limits.

The tests do not modify shop.db (the integrity test checks the file checksum).

10. Troubleshooting

Symptom

Cause and solution

Configuration error: Database file not found

SHOP_DB_PATH points to a non-existent file. Specify an absolute path or place shop.db in the project root.

Tools not visible in Pi

Make sure .mcp.json is in the project root, cwd points to the project directory, pi-mcp-adapter is installed (pi install npm:pi-mcp-adapter), and restart Pi.

Multiple SQL statements are not allowed

Only one statement is allowed in a single read_query call; split the query into multiple calls.

Only read-only queries are allowed

The query does not start with SELECT/WITH or contains DML/DDL. Rewrite the query as a SELECT.

Incomplete result (truncated: true)

The row limit has been triggered. Add LIMIT/WHERE/aggregation and do not try to increase it — the hard limit is set by the server.

I want a different row limit

Set MAX_RESULT_ROWS in the environment (the server will be restarted by Pi automatically on the next startup).

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.

ParametersJSON Schema
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.

ParametersJSON Schema
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.

ParametersJSON Schema
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. Dates show when Glama detected each change.

  1. 3 tool updatesv0.1.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedread_query
Install Server
RayrunBuild, deploy, and operate MCP servers without becoming an infrastructure team.ray.runAd

TDQS

A4.7/5.0
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

Unclaimed servers have limited discoverability.

Looking for Admin?

If you are the server author, to access and configure the admin panel.

Related MCP Connectors

Related MCP Servers

  • safe-sql-mcp

    ahmedabdirahman1-max
    F
    license
    Not graded
    quality
    C
    maintenance
    Enables read-only SQL database access for AI assistants, allowing schema exploration and safe query execution without risk of data modification.
    -

  • sqlite-analyst

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

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/stalexsm/shop-mcp'

If you have feedback or need assistance with the MCP directory API, please join our Discord server