shop-mcp
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+
The database file
shop.db(already located in the project root)
Related MCP server: sqlite-analyst
2. Installation
uv syncuv 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 1000Option 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 .env4. Run MCP locally
uv run python -m shop_mcp.serverThe 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-adapterThen 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:
SQL validation — exactly one statement is allowed, starting with
SELECT/WITH. Allowed areSELECTandWITHonly; prohibited areINSERT,UPDATE,DELETE,REPLACE INTO,DROP,ALTER,CREATE,ATTACH,DETACH,VACUUM,REINDEX,PRAGMA, and other modifying operations. Multi-statement queries (SELECT ...; DELETE ...) are entirely rejected.Connection authorizer — anything that is not a read operation (SELECT / table read / function call) is rejected at the query preparation stage.
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
cancelledare excluded;revenue by year is calculated by
orders.order_date; if there are no orders — the correct answer is0.
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 pytestTest 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
Symptom | Cause and solution |
|
|
Tools are not visible in Pi | Make sure |
| A single |
| The query does not start with |
Result is incomplete ( | The row limit was reached. Add |
I want a different row limit | Set the |
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.pyAvailable Tools
3 toolsdescribe_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.
Inputs: table_name (required).
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.
Inputs: no named parameters.
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.
Inputs: sql (required), max_rows.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 3 tools
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.
All tool names follow a consistent verb_noun snake_case pattern: list_tables, describe_table, read_query. This is uniform and predictable.
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.
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.
Related MCP Connectors
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Explore, query, and inspect SQLite databases with ease. List tables, preview results, and view det…
Query 40 databases from Claude, ChatGPT, or Cursor — on any device. Read-only, encrypted, audited.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseAqualityDmaintenanceEnables safe, read-only SQL access to SQLite databases for AI agents, allowing schema exploration and SELECT queries with defense-in-depth protections.3MIT
- AlicenseNot gradedqualityDmaintenanceEnables AI assistants to explore and query SQLite databases through read-only tools, with defense-in-depth sandboxing preventing any data modifications.MIT
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to safely query and explore SQLite databases through read-only, guard-protected tools that block writes, sensitive table access, and runaway queries.1MIT
- AlicenseBqualityCmaintenanceEnables 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.2MIT