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: sqlite-analyst

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

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