Skip to main content
Glama
deenesjakoruzh
ilyassakhanov

MCP SQLite Server (Read-Only)

by ilyassakhanov
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

MCP SQLite Server (Nur-Lesen)

Ein produktionsreifer Model Context Protocol-Server, der KI-Agenten sicheren, schreibgeschützten Zugriff auf eine SQLite-Datenbank (shop.db) bietet. Erstellt mit dem offiziellen mcp Python SDK unter Verwendung des stdio-Transports.

Funktionen

  • 3 MCP-Tools: list_tables, describe_table, query_database

  • Mehrschichtige Schreibschutz-Sicherheit: SQLite-URI im Nur-Lesen-Modus + PRAGMA query_only + SQL-Validator + EXPLAIN-Opcode-Inspektion

  • Abfragevalidierung: Lehnt INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH, Mehrfachanweisungen (;), SQL-Kommentare (--, /* */) und modifizierende PRAGMA-Anweisungen ab – ohne Fehlalarme bei String-Literalen

  • Paginierung: Standard-Zeilenlimit (100), limit/offset-Parameter, Flag für abgeschnittene Ausgabe

  • Nur-Stderr-Protokollierung: Alle Logs/Tracebacks gehen an sys.stderr; stdout ist ausschließlich für JSON-RPC reserviert

  • Vollständige Typannotationen: mypy --strict sauber

  • TDD: 105 Tests, die Sicherheit, DB-Schicht, MCP-Tools, 8 Benchmark-Abfragen und den Stderr-Schutz abdecken

Related MCP server: shop-mcp

Schnellstart

Voraussetzungen

  • Python 3.10+

  • Eine SQLite-Datenbankdatei (Standard: ./shop.db)

Lokale Einrichtung

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

Konfiguration

Kopieren Sie .env.example und legen Sie den Datenbankpfad fest:

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

Oder setzen Sie die Umgebungsvariable direkt:

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

Server ausführen

python -m mcp_server.server

Der Server kommuniziert über stdin/stdout mit dem MCP-stdio-Transport. Sie interagieren nicht direkt mit ihm – ein MCP-Client (z. B. Claude Desktop, Ihr KI-Agent) verbindet sich mit ihm.

MCP-Client-Konfigurationen

Standard-Python

Fügen Sie dies zu Ihrer MCP-Client-Konfiguration hinzu (z. B. claude_desktop_config.json von Claude Desktop):

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

Docker

Zuerst das Image erstellen:

docker build -t mcp-shop:latest .

Dann Ihren MCP-Client konfigurieren:

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

Listet alle Benutzertabellen und -sichten in der Datenbank auf (schließt interne sqlite_*-Tabellen aus).

Parameter: keine

Rückgabe:

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

describe_table

Beschreibt das Schema einer Tabelle: Spalten, Fremdschlüssel, Zeilenanzahl und die CREATE-Anweisung.

Parameter:

  • table (Zeichenkette, erforderlich): Name der zu beschreibenden Tabelle.

Rückgabe:

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

Führt eine schreibgeschützte SQL-Abfrage mit Paginierungsunterstützung aus.

Parameter:

  • sql (Zeichenkette, erforderlich): Eine einzelne schreibgeschützte SQL-Anweisung (SELECT, WITH, EXPLAIN oder schreibgeschütztes PRAGMA).

  • limit (Ganzzahl, optional): Maximale Anzahl zurückzugebender Zeilen. Standard: 100. Maximum: 1000.

  • offset (Ganzzahl, optional): Anzahl der zu überspringenden Zeilen. Standard: 0.

Rückgabe:

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

Wenn truncated true ist, sind weitere Zeilen verfügbar – erhöhen Sie offset, um die nächste Seite abzurufen.

Sicherheit

Der Server implementiert eine mehrschichtige Verteidigung, um schreibgeschützten Zugriff zu gewährleisten:

Ebene 1: SQLite-Verbindung (URI im Nur-Lesen-Modus)

Die Datenbank wird mit file:<pfad>?mode=ro geöffnet, was Schreibvorgänge auf der SQLite-Engine-Ebene verhindert. Zusätzlich wird bei jeder Verbindung PRAGMA query_only = ON gesetzt.

Ebene 2: SQL-Abfragevalidator (security.py)

Bevor eine Abfrage SQLite erreicht, durchläuft sie einen mehrstufigen Validator:

  1. String-Literal-Entfernung: String-Literale ('...', "...") werden durch Platzhalter ersetzt, damit Schlüsselwörter in Daten (z. B. ein Produkt namens "Deleted Item") keine Fehlalarme auslösen.

  2. Kommentarerkennung: SQL-Kommentare (--, /* */) werden abgelehnt, um kommentarbasierte Umgehungen zu verhindern.

  3. Ablehnung von Mehrfachanweisungen: Jedes Semikolon (;) wird abgelehnt, wodurch gestapelte Abfragen verhindert werden.

  4. Schlüsselwortanalyse: Das erste echte Anweisungsschlüsselwort muss SELECT, WITH, EXPLAIN oder PRAGMA sein. Destruktive Schlüsselwörter (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, REPLACE, TRUNCATE, ATTACH, DETACH, VACUUM usw.) werden blockiert.

  5. PRAGMA-Validierung: Schreibgeschützte PRAGMAs (table_info, database_list usw.) sind erlaubt. Jede PRAGMA mit einer Zuweisung (=) oder in der Blocklist für mutierende PRAGMAs (journal_mode, synchronous, foreign_keys usw.) wird abgelehnt.

Ebene 3: EXPLAIN-Opcode-Inspektion

Als letzte Verteidigung wird die Abfrage über SQLites eigenen Parser mit EXPLAIN <abfrage> ausgeführt. Der resultierende Opcode-Stream wird auf Schreib-Opcodes (OpenWrite, Insert, Delete, Create, Drop usw.) und Schreibtransaktions-Flags untersucht. Wenn solche gefunden werden, wird die Abfrage abgelehnt.

Ebene 4: Bereinigte Fehlermeldungen

Alle an den Client zurückgegebenen Fehler werden bereinigt – Dateisystempfade und interne Details werden entfernt, um Informationslecks zu verhindern.

Tests

Tests verwenden nur temporäre oder In-Memory-Datenbanken – niemals die Produktions-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

Testabdeckung

Testdatei

Abdeckung

tests/test_security.py

76 Tests: gültige Abfragen, Ablehnung destruktiver Anweisungen, PRAGMA-Validierung, Ablehnung von Mehrfachanweisungen, Verhinderung von Kommentar-Bypasses, String-Literal-Behandlung

tests/test_db.py

20 Tests: Schreibschutz-Durchsetzung, Tabellenauflistung, Schemabeschreibung, Paginierung, Abschneidung, alle 8 Benchmark-Abfragen

tests/test_server.py

9 Tests: MCP-Tool-Erkennung, Tool-Aufrufe über SDK-Client, Ablehnung destruktiver Abfragen, Paginierung, 7 Benchmark-Abfragen über Tools, Stderr/kein-Stdout-Verschmutzungs-Schutz

Statische Analyse

# Type checking
python -m mypy

# Linting
python -m ruff check src/ tests/

Projektstruktur

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

Die Tools des Servers ermöglichen einem KI-Agenten die Durchführung dieser analytischen Aufgaben (durch Tests gegen eine kontrollierte Fixture-Datenbank validiert):

  1. Tabellenentdeckung: list_tables + describe_table – alle Tabellen auflisten und Schemas beschreiben.

  2. Gefilterte Zählung: query_database mit SELECT COUNT(*) FROM customers WHERE country = 'Germany'.

  3. Länderaggregation: SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1.

  4. Kunden-LTV: Join customers + orders, SUM(total_amount), nach Gesamtsumme sortieren.

  5. Produktleistung: Join order_items + products, nach Menge und Umsatz aggregieren, LIMIT 5.

  6. Kategorieaggregation: order_items → products → category durchlaufen, Umsatz aggregieren, LIMIT 3.

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

  8. Bestellaggregation: Join customers + orders, COUNT(o.id), nach Anzahl sortieren.

Konfiguration

Umgebungsvariable

Standard

Beschreibung

DATABASE_PATH

./shop.db

Pfad zur SQLite-Datenbankdatei

ROW_LIMIT

100

Standard-Zeilenlimit für Abfrageergebnisse (max. 1000)

Lizenz

Dieses Projekt wird wie besehen zu Demonstrationszwecken bereitgestellt.

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