Skip to main content
Glama
deenesjakoruzh
ilyassakhanov

MCP SQLite Server (Read-Only)

by ilyassakhanov
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

MCP SQLite Server (읽기 전용)

프로덕션 준비가 완료된 Model Context Protocol 서버로, AI 에이전트에게 SQLite 데이터베이스(shop.db)에 대한 안전한 읽기 전용 액세스를 제공합니다. 공식 mcp Python SDK와 stdio 전송을 사용하여 구축되었습니다.

기능

  • 3가지 MCP 도구: list_tables, describe_table, query_database

  • 다층 방어(Defense-in-depth) 읽기 전용 안전성: SQLite URI 읽기 전용 모드 + PRAGMA query_only + SQL 검증기 + EXPLAIN opcode 검사

  • 쿼리 검증: INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH, 다중 문 쿼리(;), SQL 주석(--, /* */) 및 수정형 PRAGMA를 거부합니다 — 문자열 리터럴에 대한 오탐(false positive) 없이

  • 페이지네이션: 기본 행 제한(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에 도달하기 전에 다단계 검증기를 통과합니다:

  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 블록리스트(journal_mode, synchronous, foreign_keys 등)에 포함된 PRAGMA는 거부됩니다.

계층 3: EXPLAIN Opcode 검사

최종 방어선으로, 쿼리는 EXPLAIN <query>를 통해 SQLite 자체 파서로 실행됩니다. 결과 opcode 스트림에서 쓰기 opcode(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. 고객 LTV: 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