Skip to main content
Glama
MCP SQLite Server (Read-Only)

MCP SQLite Server (Read-Only)

MCP SQLite Server (Read-Only)

A production-ready Model Context Protocol server that provides AI agents with safe, read-only access to a SQLite database (shop.db). Built with the official mcp Python SDK using the stdio transport.

Features

  • 3 MCP tools: list_tables, describe_table, query_database

  • Defense-in-depth read-only safety: SQLite URI read-only mode + PRAGMA query_only + SQL validator + EXPLAIN opcode inspection

  • Query validation: Rejects INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH, multi-statement queries (;), SQL comments (--, /* */), and modifying PRAGMA — without false positives on string literals

  • Pagination: Default row limit (100), limit/offset parameters, truncated-output flag

  • Stderr-only logging: All logs/tracebacks go to sys.stderr; stdout is reserved exclusively for JSON-RPC

  • Full type hints: mypy --strict clean

  • TDD: 105 tests covering security, DB layer, MCP tools, 8 benchmark queries, and the stderr guard

Related MCP server: shop-analytics-mcp

Quick Start

Prerequisites

  • Python 3.10+

  • A SQLite database file (default: ./shop.db)

Local Setup

python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"

Configure

Copy .env.example and set the database path:

cp .env.example .env
# Edit DATABASE_PATH to point to your SQLite file

Or set the environment variable directly:

export DATABASE_PATH=/abs/path/to/shop.db

Run the Server

python -m mcp_server.server

The server communicates over stdin/stdout using the MCP stdio transport. You don't interact with it directly — an MCP client (e.g., Claude Desktop, your AI agent) connects to it.

MCP Client Configurations

Standard Python

Add this to your MCP client configuration (e.g., Claude Desktop's claude_desktop_config.json):

{
  "mcpServers": {
    "sqlite-shop": {
      "command": "python",
      "args": ["-m", "mcp_server.server"],
      "env": {
        "DATABASE_PATH": "/abs/path/to/shop.db"
      }
    }
  }
}

Docker

First build the image:

docker build -t mcp-shop:latest .

Then configure your MCP client:

{
  "mcpServers": {
    "sqlite-shop": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-v", "/abs/path/to/shop.db:/app/shop.db",
        "-e", "DATABASE_PATH=/app/shop.db",
        "mcp-shop:latest"
      ]
    }
  }
}

Docker Compose

docker compose up -d

Tools

list_tables

Lists all user tables and views in the database (excludes internal sqlite_* tables).

Parameters: none

Returns:

{
  "tables": ["customers", "orders", "order_items", "products"],
  "count": 4
}

describe_table

Describes the schema of a table: columns, foreign keys, row count, and the CREATE statement.

Parameters:

  • table (string, required): Name of the table to describe.

Returns:

{
  "table": "customers",
  "columns": [
    {"cid": 0, "name": "id", "type": "INTEGER", "notnull": 0, "default": null, "pk": 1},
    {"cid": 1, "name": "first_name", "type": "TEXT", "notnull": 1, "default": null, "pk": 0}
  ],
  "foreign_keys": [],
  "row_count": 150,
  "sql": "CREATE TABLE customers (...)"
}

query_database

Executes a read-only SQL query with pagination support.

Parameters:

  • sql (string, required): A single read-only SQL statement (SELECT, WITH, EXPLAIN, or read-only PRAGMA).

  • limit (integer, optional): Maximum rows to return. Default: 100. Max: 1000.

  • offset (integer, optional): Number of rows to skip. Default: 0.

Returns:

{
  "columns": ["id", "first_name"],
  "rows": [{"id": 1, "first_name": "Alice"}, {"id": 2, "first_name": "Bob"}],
  "row_count": 2,
  "truncated": false,
  "limit": 100,
  "offset": 0
}

When truncated is true, more rows are available — increase offset to fetch the next page.

Security

The server implements defense-in-depth to guarantee read-only access:

Layer 1: SQLite Connection (URI read-only mode)

The database is opened with file:<path>?mode=ro, which prevents writes at the SQLite engine level. Additionally, PRAGMA query_only = ON is set on every connection.

Layer 2: SQL Query Validator (security.py)

Before any query reaches SQLite, it passes through a multi-stage validator:

  1. String literal stripping: String literals ('...', "...") are replaced with placeholders so keywords inside data (e.g., a product named "Deleted Item") don't trigger false positives.

  2. Comment detection: SQL comments (--, /* */) are rejected to prevent comment-based bypasses.

  3. Multi-statement rejection: Any semicolon (;) is rejected, preventing stacked queries.

  4. Keyword analysis: The first real statement keyword must be SELECT, WITH, EXPLAIN, or PRAGMA. Destructive keywords (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, REPLACE, TRUNCATE, ATTACH, DETACH, VACUUM, etc.) are blocked.

  5. PRAGMA validation: Read-only PRAGMAs (table_info, database_list, etc.) are allowed. Any PRAGMA with an assignment (=) or in the mutating-PRAGMA blocklist (journal_mode, synchronous, foreign_keys, etc.) is rejected.

Layer 3: EXPLAIN Opcode Inspection

As a final defense, the query is run through SQLite's own parser via EXPLAIN <query>. The resulting opcode stream is inspected for write opcodes (OpenWrite, Insert, Delete, Create, Drop, etc.) and write-transaction flags. If any are found, the query is rejected.

Layer 4: Sanitized Error Messages

All errors returned to the client are sanitized — filesystem paths and internal details are stripped to prevent information leakage.

Testing

Tests use temporary/in-memory databases only — never the production shop.db.

# Run all tests
python -m pytest

# Run with verbose output
python -m pytest -v

# Run a specific test file
python -m pytest tests/test_security.py

Test Coverage

Test File

Coverage

tests/test_security.py

76 tests: valid queries, destructive statement rejection, PRAGMA validation, multi-statement rejection, comment bypass prevention, string literal handling

tests/test_db.py

20 tests: read-only enforcement, table listing, schema description, pagination, truncation, all 8 benchmark queries

tests/test_server.py

9 tests: MCP tool discovery, tool calls via SDK client, destructive query rejection, pagination, 7 benchmark queries via tools, stderr/no-stdout-pollution guard

Static Analysis

# Type checking
python -m mypy

# Linting
python -m ruff check src/ tests/

Project Structure

.
├── .env.example          # Environment variable template
├── Dockerfile            # Docker containerization
├── docker-compose.yml    # Docker Compose config
├── pyproject.toml        # Package config, deps, tool settings
├── README.md             # This file
├── shop.db               # The SQLite database (not included in tests)
├── src/mcp_server/
│   ├── __init__.py
│   ├── config.py         # Configuration (DATABASE_PATH, limits, URI builder)
│   ├── db.py             # Read-only Database class with introspection + query
│   ├── security.py       # SQL validator (multi-layer defense-in-depth)
│   ├── server.py         # MCP server entrypoint (stdio transport)
│   ├── tools.py          # MCP tool definitions and handlers
│   └── py.typed          # PEP 561 marker
└── tests/
    ├── __init__.py
    ├── test_db.py        # Database layer + benchmark tests
    ├── test_security.py  # Query validator tests
    └── test_server.py    # MCP server/tool tests

Benchmark Tasks

The server's tools enable an AI agent to perform these analytical tasks (validated by tests against a controlled fixture database):

  1. Table Discovery: list_tables + describe_table — list all tables and describe schemas.

  2. Filtered Count: query_database with SELECT COUNT(*) FROM customers WHERE country = 'Germany'.

  3. Country Aggregation: SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1.

  4. Customer LTV: Join customers + orders, SUM(total_amount), order by total.

  5. Product Performance: Join order_items + products, aggregate by quantity and revenue, LIMIT 5.

  6. Category Aggregation: Traverse order_items → products → category, aggregate revenue, LIMIT 3.

  7. Date Filtering: SUM(total_amount) WHERE substr(order_date,1,4) = '2025'.

  8. Order Aggregation: Join customers + orders, COUNT(o.id), order by count.

Configuration

Environment Variable

Default

Description

DATABASE_PATH

./shop.db

Path to the SQLite database file

ROW_LIMIT

100

Default row limit for query results (max 1000)

License

This project is provided as-is for demonstration purposes.

Available Tools

3 tools
describe_tableA

Describe the schema of a table: columns (name, type, notnull, default, primary key), foreign keys, row count, and the CREATE statement. Returns JSON with 'table', 'columns', 'foreign_keys', 'row_count', 'sql'. Read-only.

Inputs: table (required).

list_tablesA

List all user tables and views in the database (excludes internal sqlite_* tables). Returns a JSON object: {"tables": ["table1", "table2", ...], "count": N}. This is a read-only operation.

Inputs: no named parameters.

query_databaseA

Execute a read-only SQL query (SELECT / WITH / EXPLAIN / read-only PRAGMA) against the database. Destructive statements (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, etc.), multi-statement queries, and SQL comments are rejected. Results are paginated: a default row limit of 100 is applied (max 1000). Use 'limit' and 'offset' for pagination. If 'truncated' is true, more rows are available. Returns JSON: {"columns": [...], "rows": [{...}], "row_count": N, "truncated": bool, "limit": N, "offset": N}.

Inputs: sql (required), limit, offset.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4.6/5.0

    Scored across 3 tools

    Disambiguation5/5

    Each tool has a clearly distinct purpose: listing tables/views, describing schema details, and executing read-only queries. There is no functional overlap or ambiguity between them.

    Naming Consistency5/5

    All tool names follow the same snake_case verb_noun pattern (list_tables, describe_table, query_database), offering a consistent and predictable naming convention.

    Tool Count5/5

    With only 3 tools, the server is well-scoped for a read-only SQLite interface. Each tool covers a distinct and essential operation, and the count is ideal for the purpose.

    Completeness5/5

    For a read-only SQLite server, the toolset is complete: listing tables, describing schema, and querying data with pagination cover all typical use cases. Even edge cases like EXPLAIN and read-only PRAGMAs are supported via query_database.

    Maintenance

    ActivityInactive
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • F
      license
      Not graded
      quality
      C
      maintenance
      Exposes any SQLite database as read-only MCP tools for AI assistants, enabling listing tables, describing schemas, and running SELECT queries with filtering, ordering, and pagination.
      -
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables AI agents to read-only query a SQLite database, inspect schema and table summaries, and execute SELECT queries with pagination through MCP.
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Enables AI agents to inspect real databases through MCP with tools for listing tables, describing schemas, sampling rows, running queries, and explaining execution plans, all with engine-enforced read-only access to SQLite and MySQL.
      5
      12 npm
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables AI agents to run read-only SQL against SQLite or MySQL databases through MCP tools for listing tables, describing schemas, and querying data. Wraps every call in guardrails such as statement allow-listing, row caps, query timeouts, PII masking, and audit logging.
      1
      MIT