Read-Only SQLite Shop Database MCP Server

Read-Only SQLite Shop Database MCP Server

An official Model Context Protocol (MCP) server providing secure, read-only database access to an SQLite e-commerce store (shop.db) for AI agents.

🌟 Key Features

• Standard stdio Transport: Works seamlessly with any MCP client (Claude Desktop, Cursor, Gemini CLI, Antigravity, etc.).
• Multi-layered Read-Only Security:
  • SQLite URI read-only mode (?mode=ro).
  • Strict runtime PRAGMA query_only = ON;.
  • Pre-flight SQL parser rejecting all DDL/DML mutation statements (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, etc.).
  • Blocks SQL injection chains and multi-statement execution.
• LLM-Optimized Tools: Clear descriptions, robust error handling without raw stack traces, and automatic pagination (limit/offset).
• Flexible Path Resolution: Works out of the box with relative paths, DB_PATH environment variable, or --db-path CLI flag.

🗄️ Database Schema

The SQLite database (shop.db) contains the following entities:

customers
    │
    └──< orders
             │
             └──< order_items >── products

• customers: id, first_name, last_name, email, phone, created_at
• products: id, name, category, price, stock_quantity, created_at
• orders: id, customer_id, order_date, status (new, processing, shipped, completed, cancelled), total_amount
• order_items: id, order_id, product_id, quantity, unit_price

🛠️ MCP Tools

🚀 Getting Started

1. Installation

Create a virtual environment and install the required dependencies:

# Create virtual environment
python3 -m venv .venv
source .venv/bin/activate

# Install dependencies
pip install -r requirements.txt

2. Running Locally

Run the MCP server over standard input/output:

python server.py

Or specify a custom database path:

# Using CLI argument
python server.py --db-path /path/to/shop.db

# Or using Environment Variable
DB_PATH=/path/to/shop.db python server.py

3. Running Tests

Run the test suite to verify tool functionality and safety constraints:

python -m unittest discover -s tests -v

🔌 Connecting to AI Agents

Claude Desktop

Add this server to your claude_desktop_config.json (~/Library/Application Support/Claude/claude_desktop_config.json on macOS or %APPDATA%\Claude\claude_desktop_config.json on Windows):

{
  "mcpServers": {
    "shop-database": {
      "command": "/absolute/path/to/.venv/bin/python",
      "args": ["/absolute/path/to/server.py"],
      "env": {
        "DB_PATH": "/absolute/path/to/shop.db"
      }
    }
  }
}

Cursor IDE

In Cursor Settings → Features → MCP:

• Type: command
• Command: /absolute/path/to/.venv/bin/python /absolute/path/to/server.py

Antigravity / Gemini CLI

Add to your MCP configuration file:

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

🔒 Safety & Validation Examples

If an AI agent or prompt attempts a destructive operation, the server immediately rejects the query gracefully:

• Prompt: "Delete all cancelled orders."
• Server Response:

  {
    "error": "Operation rejected: Statement type 'DELETE' is not allowed. Only read-only queries (SELECT, WITH ... SELECT, EXPLAIN) are permitted."
  }
  

📊 Verification Queries

The server enables AI agents to resolve analytical queries such as:

1. Table Discovery: list_tables() and describe_table(table_name="customers")
2. Customer Demographics:

   SELECT count(*) FROM customers WHERE phone LIKE '+7%';
   

3. Customer Who Spent the Most Money:

   SELECT c.first_name, c.last_name, c.email, SUM(o.total_amount) AS total_spent
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   WHERE o.status != 'cancelled'
   GROUP BY c.id
   ORDER BY total_spent DESC
   LIMIT 1;
   

4. Top 5 Best-Selling Products:

   SELECT p.name, SUM(oi.quantity) AS units_sold, SUM(oi.quantity * oi.unit_price) AS revenue
   FROM products p
   JOIN order_items oi ON p.id = oi.product_id
   JOIN orders o ON oi.order_id = o.id
   WHERE o.status != 'cancelled'
   GROUP BY p.id
   ORDER BY units_sold DESC
   LIMIT 5;
   

5. Top 3 Product Categories by Revenue:

   SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
   FROM products p
   JOIN order_items oi ON p.id = oi.product_id
   JOIN orders o ON oi.order_id = o.id
   WHERE o.status != 'cancelled'
   GROUP BY p.category
   ORDER BY revenue DESC
   LIMIT 3;
   

6. Customer With Most Orders:

   SELECT c.first_name, c.last_name, COUNT(o.id) AS order_count
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   GROUP BY c.id
   ORDER BY order_count DESC
   LIMIT 1;