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 Server (только чтение)

Готовый к продакшену сервер Model Context Protocol, который предоставляет ИИ-агентам безопасный доступ только для чтения к базе данных SQLite (shop.db). Создан с использованием официального Python SDK mcp с транспортом stdio.

Возможности

3 инструмента MCP: list_tables, describe_table, query_database
Многоуровневая защита только для чтения: режим только для чтения в URI SQLite + 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 с использованием транспорта stdio MCP. Вы не взаимодействуете с ним напрямую — клиент MCP (например, Claude Desktop, ваш ИИ-агент) подключается к нему.

Конфигурации клиента MCP

Стандартный Python

Добавьте это в конфигурацию вашего клиента MCP (например, claude_desktop_config.json у Claude Desktop):

{
  "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 и т.д.) отклоняется.

Уровень 3: Проверка кодов операций EXPLAIN

В качестве последней защиты запрос пропускается через собственный парсер SQLite с помощью EXPLAIN <query>. Полученный поток кодов операций проверяется на наличие записывающих кодов (OpenWrite, Insert, Delete, Create, Drop и т.д.) и флагов записывающих транзакций. Если таковые обнаружены, запрос отклоняется.

Уровень 4: Очищенные сообщения об ошибках

Все ошибки, возвращаемые клиенту, очищаются — пути файловой системы и внутренние детали удаляются для предотвращения утечки информации.

Тестирование

Тесты используют только временные или in-memory базы данных — никогда не производственную 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

Эталонные задачи

Инструменты сервера позволяют ИИ-агенту выполнять следующие аналитические задачи (проверены тестами на контролируемой фикстурной базе данных):

Обнаружение таблиц: list_tables + describe_table — список всех таблиц и описание схем.
Подсчёт с фильтром: query_database с SELECT COUNT(*) FROM customers WHERE country = 'Germany'.
Агрегация по странам: SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1.
Пожизненная ценность клиента: объединение 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 Server (только чтение)

Возможности

Быстрый старт

Предварительные требования

Локальная настройка

Конфигурация

Запуск сервера

Конфигурации клиента 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`)