shop-mcp — Read-Only SQLite MCP Server

A Python MCP server that provides an AI agent (for example, Pi) with safe read-only access to the SQLite database shop.db over stdio.

The agent independently explores the database schema, writes SQL queries, and solves analytical tasks. The server does not contain 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 database file shop.db (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 may also copy .env.example to .env and specify the values there (the server reads .env from the project root; environment variables take priority):

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 launch it separately; the client (Pi) starts it itself. The manual run above is useful only for debugging.

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

5. Connect MCP to Pi

Pi connects MCP servers via 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, adjust cwd to the absolute path of the project directory (or replace it with env containing 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 a terminal: Pi itself starts the process over stdio (lazily, on the first access to the tools).

If the adapter is not yet installed:

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

Lists database tables with a brief description and row count. A starting point for schema exploration. No SQL is required.

describe_table

The structure of one table: columns (name, type, nullable, primary_key, default) and foreign keys in the form orders.customer_id -> customers.id. A non-existent table produces a clear error with a list of available tables.

read_queryeline

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

Parameters:

• sql (required) — the text of the query;

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

Standard SQLite analytics are supported: 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 were returned — refine the query (LIMIT, WHERE, aggregation) and do not treat the result as complete.

7. Security model

Three independent layers of protection:

1. SQL validation — exactly one statement is allowed, starting with SELECT/WITH. Allowed are SELECT and WITH only; prohibited are INSERT, UPDATE, DELETE, REPLACE INTO, DROP, ALTER, CREATE, ATTACH, DETACH, VACUUM, REINDEX, PRAGMA, and other modifying operations. Multi-statement queries (SELECT ...; DELETE ...) are entirely rejected.

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

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

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

shop.db is a read-only source of truth: the server does not alter the file's contents or structure. This is guaranteed by an integrity test (checksum + row counters before/after all destructive operations).

8. Example questions

Ask the Pi agent these questions — it will itself call list_tables, describe_table, and 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?

Payments on business logic (the agent draws this from the tool descriptions; the server does not encode answers):

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

• orders with status cancelled are excluded;

• revenue by year is calculated by 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 reliably answered: the customers table does not have a country column (only first_name, last_name, email, phone, created_at). The server provides the agent with reliable schema information; the agent must state 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_databases.py — read-only connection, schema discovery, foreign keys, connection closure;

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

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

• tests/test_analytics.py — analytics scenarios (section 27 of the specification) with reconciliation against an independent SQLite source, result size limits.

The tests do not alter shop.db (the integrity test compares 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