SQLite Shop MCP Server 🛍️

A secure, high-performance MCP (Model Context Protocol) server in Python for connecting AI agents (Claude Desktop, Cursor, Antigravity, Gemini CLI) to the online store's relational database (shop.db).

The server runs locally via standard input/output (stdio), implements two-level write protection (strict Read-Only), supports auto-pagination, clear error handling for agent self-correction, and is accompanied by 100% test coverage.

🌟 Key Features

1. Multi-level security (Strict Read-Only):

   • Physical level (SQLite Engine): the database is opened via URI file:shop.db?mode=ro. Any write attempt is physically blocked by the SQLite C library (OperationalError: attempt to write a readonly database).

   • Lexical level (AST & Token Validator): queries are analyzed before being passed to the database. Only SELECT, WITH (CTE), and EXPLAIN are allowed. Any destructive operations (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, ATTACH, PRAGMA writable) and query chains separated by semicolons are immediately rejected.

2. Smart tool design (4 Tools):

   • get_database_schema: a complete catalog of all tables, types, primary/foreign keys, row counts, and domain hints.

   • describe_table: detailed schema of a specific table.

   • get_sample_data: preview of table records without writing SQL.

   • execute_query: safe execution of arbitrary SQL with automatic pagination (page, page_size), context overflow protection (up to 1000 rows), and execution time measurement.

3. Friendly error handling (Self-Correction):

   • No raw Python stack traces are exposed.

   • When a query references a non-existent column, the server suggests the list of available columns in the table, allowing the model to self-correct instantly.

4. Portability:

   • No hardcoded absolute paths. The path is resolved automatically relative to the project or via the SHOP_DB_PATH environment variable.

5. Testing and Docker:

   • 51 pytest automated tests (security, database, integration, all 8 tasks from the technical specification).

   • Ready-made Dockerfile and docker-compose.yml.

Related MCP server: Read-Only SQLite Shop Database MCP Server

🏗️ Architecture

[ AI Agent: Claude / Cursor / Antigravity ]
                   │  (stdio JSON-RPC)
                   ▼
           [ server.py ] (MCPServer stdio transport)
                   │
     ┌─────────────┴─────────────┐
     ▼                           ▼
[ src/security.py ]       [ src/db.py ]
(Валидация SQL,           (Подключение в mode=ro,
 защита от инъекций)       пагинация, сбор метрик)
                                 │
                                 ▼
                       [ shop.db (mode=ro) ]

shop.db Database Schema

customers (150 строк)
    │
    └──< orders (750 строк)
             │
             └──< order_items (1900 строк) >── products (50 строк)

🚀 Quick Start

1. Installing Dependencies (Install)

Requires Python 3.10+:

# Клонируйте репозиторий или перейдите в папку проекта
cd HW_MCP

# Установите зависимости
pip install -r requirements.txt

2. Configuration (Configure)

By default, the server looks for shop.db in the project root. If necessary, the path can be overridden via the environment variable:

# Windows (PowerShell)
$env:SHOP_DB_PATH = "C:\path\to\shop.db"

# Linux / macOS
export SHOP_DB_PATH="/path/to/shop.db"

3. Running the Server (Run)

The server runs in stdio mode:

python server.py

🤖 Connecting to AI Agents (Connect to Agent)

Claude Desktop

Add the configuration to the Claude Desktop settings file:

• Windows: %APPDATA%\Claude\claude_desktop_config.json

• macOS: ~/Library/Application Support/Claude/claude_desktop_config.json

{
  "mcpServers": {
    "sqlite-shop": {
      "command": "python",
      "args": [
        "C:\\Users\\user\\OneDrive\\BackToTheFuture\\HW_MCP\\server.py"
      ],
      "env": {
        "PYTHONUNBUFFERED": "1"
      }
    }
  }
}

Cursor

In Cursor, go to Settings > Features > MCP > Add New MCP Server:

• Name: sqlite-shop

• Type: command

• Command: python C:\Users\user\OneDrive\BackToTheFuture\HW_MCP\server.py

Alternatively, create a .cursor/mcp.json file in the root of your project workspace:

{
  "mcpServers": {
    "sqlite-shop": {
      "command": "python",
      "args": ["server.py"]
    }
  }
}

Antigravity / Gemini CLI

Add the section to mcp_config.json:

{
  "mcpServers": {
    "sqlite-shop": {
      "command": "python",
      "args": ["server.py"]
    }
  }
}

🛠️ Tool Descriptions (MCP Tools)

1. get_database_schema

Returns the full structure of all tables, column data types, primary and foreign keys, row counts, and explanatory notes about the data.

2. describe_table(table_name: str)

Returns a detailed schema of columns and constraints of the selected table (customers, products, orders, order_items).

3. get_sample_data(table_name: str, limit: int = 10)

Returns sample rows from the table for a preliminary analysis of the data format.

4. execute_query(query: str, page: int = 1, page_size: int = 50)

Executes a safe read-only SQL query.

• Parameters:

  • query (string, required): SQL query (SELECT, WITH ... SELECT, EXPLAIN).

  • page (int, default: 1): page number.

  • page_size (int, default: 50, max: 1000): number of rows per page.

• Response format:

  {
    "rows": [
      { "id": 1, "first_name": "Арина", "email": "..." }
    ],
    "page": 1,
    "page_size": 50,
    "total_rows_in_page": 50,
    "has_more": true,
    "execution_time_ms": 1.24
  }

📊 Solving the 8 Specification Tasks

All queries are verified against real shop.db data:

Security Check (Safety Requirement)

Agent request:

Delete all cancelled orders.

MCP server response:

{
  "error": true,
  "error_type": "PermissionDenied",
  "message": "PermissionDenied: Modifying or destructive operations are not permitted (read-only server). Statement starts with 'DELETE'."
}

The database remains completely intact.

🧪 Running Automated Tests

The project includes a full test suite based on pytest:

• tests/test_security.py — checks blocking of destructive statements, SQL injections, and query chains.

• tests/test_db.py — checks physical mode=ro, schema, pagination, and error hints.

• tests/test_server.py — integration tests for tool calls and validation of all 8 homework tasks.

pytest tests/ -v

Result:

============================= 51 passed in 0.87s ==============================

🐳 Running in Docker

Build and run the container:

# Сборка образа
docker build -t sqlite-shop-mcp .

# Запуск с монтированием базы
docker run -i --rm -v $(pwd)/shop.db:/app/shop.db:ro sqlite-shop-mcp

Or via docker-compose:

docker-compose run --rm sqlite-shop-mcp

📁 Repository Structure

HW_MCP/
├── .agent/                  # Интеграция с OpenSpec агентами
├── openspec/                # Спецификация требований (OpenSpec living specs & changes)
├── src/
│   ├── __init__.py
│   ├── config.py            # Разрешение путей и настроек SQLite URI
│   ├── security.py          # Валидатор SQL-запросов (Read-Only enforcement)
│   └── db.py                # Слой SQLite (mode=ro, пагинация, сбор схем)
├── tests/
│   ├── test_security.py     # Тесты безопасности SQL
│   ├── test_db.py           # Тесты слоя БД и пагинации
│   └── test_server.py       # Интеграционные тесты 8 аналитических задач
├── Dockerfile               # Контейнеризация сервиса
├── docker-compose.yml
├── mcp_config_example.json  # Примеры конфигов для Claude Desktop, Cursor, Antigravity
├── requirements.txt         # Зависимости Python
├── server.py                # Главная точка входа MCP-сервера
├── shop.db                  # База данных SQLite интернет-магазина
└── README.md                # Полная документация проекта

📜 License

MIT License.