Skip to main content
Glama
deenesjakoruzh
ilyassakhanov

MCP SQLite Server (Read-Only)

by ilyassakhanov
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

MCP SQLite 服务器（只读）

一个生产就绪的 Model Context Protocol 服务器，为 AI 代理提供对 SQLite 数据库（shop.db）的安全、只读访问。基于官方 mcp Python SDK 构建，使用 stdio 传输。

功能特性

  • 3 个 MCP 工具：list_tables、describe_table、query_database

  • 纵深防御式只读安全：SQLite URI 只读模式 + PRAGMA query_only + SQL 验证器 + EXPLAIN 操作码检查

  • 查询验证：拒绝 INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH、多语句查询（;）、SQL 注释（--、/* */）以及修改性 PRAGMA——且不会对字符串字面量产生误报

  • 分页：默认行数限制（100）、limit/offset 参数、截断输出标志

  • 仅 stderr 日志：所有日志/回溯信息输出到 sys.stderr；stdout 专用于 JSON-RPC

  • 完整类型注解：mypy --strict 零错误

  • TDD：105 个测试，涵盖安全性、数据库层、MCP 工具、8 个基准查询以及 stderr 保护

Related MCP server: shop-mcp

快速开始

前置条件

  • Python 3.10+

  • 一个 SQLite 数据库文件（默认：./shop.db）

本地设置

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

配置

复制 .env.example 并设置数据库路径：

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

或者直接设置环境变量：

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

运行服务器

python -m mcp_server.server

服务器通过 stdin/stdout 使用 MCP stdio 传输进行通信。你不需要直接与它交互——MCP 客户端（例如 Claude Desktop、你的 AI 代理）会连接到它。

MCP 客户端配置

标准 Python

将此配置添加到你的 MCP 客户端配置中（例如 Claude Desktop 的 claude_desktop_config.json）：

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

Docker

首先构建镜像：

docker build -t mcp-shop:latest .

然后配置你的 MCP 客户端：

{
  "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

工具

list_tables

列出数据库中的所有用户表和视图（排除内部的 sqlite_* 表）。

参数：无

返回：

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

describe_table

描述表的模式：列、外键、行数以及 CREATE 语句。

参数：

  • table（字符串，必填）：要描述的表名。

返回：

{
  "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

执行带分页支持的只读 SQL 查询。

参数：

  • sql（字符串，必填）：单条只读 SQL 语句（SELECT、WITH、EXPLAIN 或只读 PRAGMA）。

  • limit（整数，可选）：返回的最大行数。默认值：100。最大值：1000。

  • offset（整数，可选）：跳过的行数。默认值：0。

返回：

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

当 truncated 为 true 时，表示还有更多行可用——增加 offset 以获取下一页。

安全性

服务器实现了纵深防御以保证只读访问：

第 1 层：SQLite 连接（URI 只读模式）

数据库以 file:<path>?mode=ro 方式打开，这会在 SQLite 引擎层面阻止写入。此外，每个连接都会设置 PRAGMA query_only = ON。

第 2 层：SQL 查询验证器（security.py）

在任何查询到达 SQLite 之前，它都会经过一个多阶段验证器：

  1. 字符串字面量剥离：字符串字面量（'...'、"..."）会被替换为占位符，这样数据中的关键字（例如名为 "Deleted Item" 的产品）不会触发误报。

  2. 注释检测：SQL 注释（--、/* */）会被拒绝，以防止基于注释的绕过。

  3. 多语句拒绝：任何分号（;）都会被拒绝，防止堆叠查询。

  4. 关键字分析：第一条实际语句关键字必须是 SELECT、WITH、EXPLAIN 或 PRAGMA。破坏性关键字（INSERT、UPDATE、DELETE、DROP、ALTER、CREATE、REPLACE、TRUNCATE、ATTACH、DETACH、VACUUM 等）会被阻止。

  5. PRAGMA 验证：只读 PRAGMA（table_info、database_list 等）被允许。任何带有赋值（=）的 PRAGMA 或位于可变 PRAGMA 黑名单（journal_mode、synchronous、foreign_keys 等）中的 PRAGMA 都会被拒绝。

第 3 层：EXPLAIN 操作码检查

作为最后一道防线，查询会通过 EXPLAIN <query> 经过 SQLite 自身的解析器。生成的操作码流会被检查是否存在写操作码（OpenWrite、Insert、Delete、Create、Drop 等）以及写事务标志。如果发现任何此类操作码，查询将被拒绝。

第 4 层：净化后的错误消息

返回给客户端的所有错误消息都经过净化——文件系统路径和内部细节会被剥离，以防止信息泄露。

测试

测试仅使用临时/内存数据库——绝不使用生产环境的 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

测试覆盖率

测试文件

覆盖率

tests/test_security.py

76 个测试：有效查询、破坏性语句拒绝、PRAGMA 验证、多语句拒绝、注释绕过防护、字符串字面量处理

tests/test_db.py

20 个测试：只读强制、表列出、模式描述、分页、截断、全部 8 个基准查询

tests/test_server.py

9 个测试：MCP 工具发现、通过 SDK 客户端调用工具、破坏性查询拒绝、分页、通过工具执行的 7 个基准查询、stderr/无 stdout 污染保护

静态分析

# Type checking
python -m mypy

# Linting
python -m ruff check src/ tests/

项目结构

.
├── .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

基准任务

服务器的工具使 AI 代理能够执行以下分析任务（已通过针对受控夹具数据库的测试验证）：

  1. 表发现：list_tables + describe_table——列出所有表并描述模式。

  2. 过滤计数：使用 SELECT COUNT(*) FROM customers WHERE country = 'Germany' 进行 query_database。

  3. 国家聚合：SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1。

  4. 客户生命周期价值：连接 customers + orders，SUM(total_amount)，按总额排序。

  5. 产品表现：连接 order_items + products，按数量和收入聚合，LIMIT 5。

  6. 类别聚合：遍历 order_items → products → category，聚合收入，LIMIT 3。

  7. 日期过滤：SUM(total_amount) WHERE substr(order_date,1,4) = '2025'。

  8. 订单聚合：连接 customers + orders，COUNT(o.id)，按数量排序。

配置

环境变量

默认值

描述

DATABASE_PATH

./shop.db

SQLite 数据库文件的路径

ROW_LIMIT

100

查询结果的默认行数限制（最大 1000）

许可证

本项目按原样提供，仅用于演示目的。

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.

ParametersJSON Schema
NameRequiredDescriptionDefault
tableYesName of the table to describe.

TDQS

A4.3/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

No annotations are provided, so the description carries the full burden of behavioral disclosure. It discloses that the operation is read-only and details the return structure (JSON with specific keys). It does not mention error handling, permission requirements, or side effects, but for a read-only introspection tool these are minor. The description adds value by describing what information is returned, beyond what annotations would provide.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is a single, dense sentence that front-loads the primary purpose and then enumerates the exact components and return keys. Every phrase adds information—no filler or redundancy. It is concise yet comprehensive, structuring the behavior clearly.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

Given there is no output schema, the description explicitly lists the return keys ('table', 'columns', 'foreign_keys', 'row_count', 'sql') and details column attributes. This fully equips an agent to interpret the result. It also covers the read-only nature and the scope (schema description). For a single-parameter introspection tool, nothing essential is missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100% for the single parameter, with the schema saying 'Name of the table to describe.' The description adds no additional meaning beyond that—it doesn't explain how to obtain valid table names (e.g., via list_tables) or any format constraints. Since the schema already fully documents the parameter, the description's contribution is minimal, matching the baseline of 3.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description states a specific verb ('Describe') and resource ('a table') with clear detail on what is covered: columns with type/notnull/default/PK, foreign keys, row count, and the CREATE statement. It is unambiguous and distinct from siblings like list_tables (which presumably lists table names) and query_database (which executes queries). The purpose is immediately clear.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description implicitly defines when to use it: when you need schema metadata for a specific table. It states it is 'Read-only', which implies it is safe for inspection. However, it does not explicitly contrast with list_tables or query_database, nor mention any exclusions (e.g., when to avoid it). Since the usage context is clear but alternatives are not named, a score of 4 is appropriate.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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.

ParametersJSON Schema
NameRequiredDescriptionDefault

No parameters

TDQS

A4.5/5.0
Behavior4/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries the burden. It explicitly states 'This is a read-only operation,' disclosing it has no side effects. It also discloses the exclusion of internal tables and the exact return format. This is good behavioral disclosure for a simple list operation.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

Two sentences with no fluff. Purpose is front-loaded, return format is given, and the read-only note is appended. Every sentence earns its place.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a simple list tool with no params and no output schema, the description fully covers what the agent needs: the scope (user tables/views), the exclusion of internal tables, and the exact JSON return shape. Nothing missing.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

There are zero parameters, so the schema is trivially covered at 100%. Per the baseline for 0 params, the description doesn't need to add parameter semantics, and it doesn't. No gaps.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description clearly states it lists all user tables and views, excluding internal sqlite_* tables. This specific verb+resource combination distinguishes it from siblings like describe_table (specific table) and query_database (run queries).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description clearly implies when to use it: to get an overview of all tables/views. However, it does not explicitly mention alternatives or when not to use it, but the contrast with siblings is obvious enough. Lacks explicit exclusions.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

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}.

ParametersJSON Schema
NameRequiredDescriptionDefault
sqlYesA single read-only SQL statement.
limitNoMaximum rows to return (default 100).
offsetNoNumber of rows to skip for pagination.

TDQS

A4.5/5.0
Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

With no annotations provided, the description carries full responsibility for disclosing behavior, and it does so thoroughly. It states the read-only nature, rejection of destructive statements, pagination behavior (default limit of 100, max 1000, offset support), and signals when more rows exist (truncated flag). The return format is fully specified, which is exceptional given the absence of annotations.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness5/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The description is three sentences long, front-loaded with the core purpose and restrictions, then pagination, then output format. Every sentence contributes essential information with zero redundancy or fluff. It is structured so the most critical constraints (read-only, rejected statements) appear first.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a SQL query tool with no output schema and no annotations, the description is remarkably complete. It explains the allowed statements, the rejection rules, pagination mechanics, and the exact JSON response structure. An agent has everything required to call the tool correctly and interpret results. Error handling isn't mentioned, but that is a minor omission given the breadth of what is covered.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters3/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is 100%—all three parameters have descriptive text in the schema. The description adds context around pagination (use limit/offset) but does not introduce new semantic information beyond what the schema already provides. The default limit and max are already in the schema, so the description's added value is limited to reinforcing the pagination workflow.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

The description states a specific verb ('Execute') and resource ('read-only SQL query') and enumerates the allowed statement types (SELECT, WITH, EXPLAIN, read-only PRAGMA). It clearly distinguishes itself from sibling tools by focusing on arbitrary query execution rather than metadata listing, so an agent can tell it apart immediately.

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description makes clear the tool is for read-only queries and explicitly lists what is rejected (destructive statements, multi-statement, comments). It does not name sibling tools or give explicit 'when to use vs. alternatives' guidance, but the context is unambiguous—if you need to run a SELECT or similar, use this. The exclusion criteria are, however, implied rather than spelled out.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Tool Schema Changelog

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

  1. 3 tool updatesv1.0.0
    • First observeddescribe_table
    • First observedlist_tables
    • First observedquery_database
Deploy Server
Shareable, User-Owned Context BackendPeople, applications, and agents read, write, and coordinate together in one context lake. Reach it over MCP, a CLI, a Python SDK, or REST.fulcradynamics.comAd

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

ActivitySlowing
ResponsivenessNo issues

Resources

Related MCP Connectors

Related MCP Servers

  • mcp-to-db

    jorgepvenegas
    F
    license
    Not graded
    quality
    D
    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.
    -

  • shop-mcp

    stalexsm
    F
    license
    A
    quality
    C
    maintenance
    Enables AI agents to safely explore and query a SQLite database in read-only mode, allowing them to inspect schema and run analytical SQL queries without risking data modification.
    3
    -

  • sql-guard-mcp

    egoring
    A
    license
    Not graded
    quality
    B
    maintenance
    Enables AI agents to safely query and explore SQLite databases through read-only, guard-protected tools that block writes, sensitive table access, and runaway queries.
    1
    MIT

  • shop-analytics-mcp

    yuzislav
    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