de en es ja ko ru zh

MCP SQLite Server (Read-Only)

Name: MCP SQLite Server (Read-Only)
Author: ilyassakhanov

by ilyassakhanov

Overview Schema Source Related Servers Discussions Admin

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: セキュリティ、DB レイヤー、MCP ツール、8 つのベンチマーククエリ、stderr ガードをカバーする 105 のテスト

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

サーバーは MCP stdio トランスポートを使用して stdin/stdout で通信します。直接操作する必要はありません — 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 に到達する前に、多段階のバリデーターを通過します:

文字列リテラルの除去: 文字列リテラル（'...'、"..."）はプレースホルダーに置き換えられ、データ内のキーワード（例: "Deleted Item" という製品名）が誤検出を引き起こさないようにします。
コメント検出: SQL コメント（--、/* */）は拒否され、コメントベースのバイパスを防ぎます。
複文の拒否: セミコロン（;）は拒否され、スタッククエリを防ぎます。
キーワード分析: 最初の実ステートメントキーワードは SELECT、WITH、EXPLAIN、または PRAGMA でなければなりません。破壊的なキーワード（INSERT、UPDATE、DELETE、DROP、ALTER、CREATE、REPLACE、TRUNCATE、ATTACH、DETACH、VACUUM など）はブロックされます。
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/no-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 エージェントは次の分析タスクを実行できます（制御されたフィクスチャデータベースに対するテストで検証済み）:

テーブル発見: list_tables + describe_table — すべてのテーブルを一覧表示し、スキーマを説明します。
フィルタリングされたカウント: SELECT COUNT(*) FROM customers WHERE country = 'Germany' を使用した query_database。
国別集計: SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1。
顧客 LTV: customers + orders を結合し、SUM(total_amount)、合計で並べ替え。
製品パフォーマンス: order_items + products を結合し、数量と売上で集計、LIMIT 5。
カテゴリ集計: order_items → products → category を辿り、売上を集計、LIMIT 3。
日付フィルタリング: SUM(total_amount) WHERE substr(order_date,1,4) = '2025'。
注文集計: 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

Name	Required	Description	Default
`table`	Yes	Name 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

Name	Required	Description	Default
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

Name	Required	Description
`sql`	Yes	A single read-only SQL statement.
`limit`	No	Maximum rows to return (default 100).
`offset`	No	Number 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.

3 tool updatesv1.0.0Aug 23, 2026
- 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 2026-08-23 23:40 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

GitHub Repository

Need Help?

Related Servers

Related MCP Connectors

dataOAuth
co.thinair
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Databases Developer Tools
2
wgong-sqlite-mcp-server
ai.smithery
Explore, query, and inspect SQLite databases with ease. List tables, preview results, and view det…
Databases Developer Tools
21
QueryBearOAuth
io.github.spencerpauly
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Databases RAG Systems
2
Plato's Kallipolis A
io.github.handongzhen101-jpg
Read-only MCP tools for AI agent discovery, structured resources, and NIULAI information.
Agent Orchestration AI & Machine Learning
61

Related MCP Servers

mcp-to-db
Databases RAG Systems
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.
Updated 3 months ago (2026-07-17 04:31 UTC)
-
shop-mcp
Databases
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.
Updated 2 months ago (2026-08-23 15:32 UTC)
3
-
sql-guard-mcp
Databases Security
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.
Updated 24 days ago (2026-09-17 06:46 UTC)
1
MIT
shop-analytics-mcp
Databases Developer Tools
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.
Updated a month ago (2026-08-31 13:52 UTC)
MIT

MCP SQLite サーバー（読み取り専用）

特徴

クイックスタート

前提条件

ローカルセットアップ

設定

サーバーの実行

MCP クライアント設定

標準 Python

Docker

Docker Compose

ツール

list_tables

describe_table

query_database

セキュリティ

レイヤー 1: SQLite 接続（URI 読み取り専用モード）

レイヤー 2: SQL クエリバリデーター（security.py）

レイヤー 3: EXPLAIN オペコード検査

レイヤー 4: サニタイズされたエラーメッセージ

テスト

テストカバレッジ

静的解析

プロジェクト構造

ベンチマークタスク

設定

ライセンス

Available Tools

Tool Schema Changelog

Maintenance

Resources

Related MCP Connectors

Related MCP Servers

`list_tables`

`describe_table`

`query_database`

レイヤー 2: SQL クエリバリデーター（`security.py`）