Skip to main content
Glama
deenesjakoruzh
ilyassakhanov

MCP SQLite Server (Read-Only)

by ilyassakhanov
OverviewSchemaSourceRelated ServersDiscussionsAdmin
Python
Local

Servidor MCP SQLite (solo lectura)

Un servidor Model Context Protocol listo para producción que proporciona a los agentes de IA un acceso seguro y solo de lectura a una base de datos SQLite (shop.db). Construido con el SDK oficial mcp de Python mediante el transporte stdio.

Características

  • 3 herramientas MCP: list_tables, describe_table, query_database

  • Seguridad de solo lectura con defensa en profundidad: modo de solo lectura por URI de SQLite + PRAGMA query_only + validador SQL + inspección de opcodes de EXPLAIN

  • Validación de consultas: rechaza INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH, consultas de múltiples sentencias (;), comentarios SQL (--, /* */) y PRAGMA de modificación — sin falsos positivos en literales de cadena

  • Paginación: límite de filas por defecto (100), parámetros limit/offset, indicador de salida truncada

  • Registro solo en stderr: todos los registros y tracebacks van a sys.stderr; stdout está reservado exclusivamente para JSON-RPC

  • Tipado completo: mypy --strict sin errores

  • TDD: 105 pruebas que cubren seguridad, capa de base de datos, herramientas MCP, 8 consultas de referencia y la protección de stderr

Related MCP server: shop-mcp

Inicio rápido

Requisitos previos

  • Python 3.10+

  • Un archivo de base de datos SQLite (por defecto: ./shop.db)

Configuración local

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

Configurar

Copia .env.example y establece la ruta de la base de datos:

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

O establece la variable de entorno directamente:

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

Ejecutar el servidor

python -m mcp_server.server

El servidor se comunica a través de stdin/stdout usando el transporte stdio de MCP. No interactúas con él directamente: un cliente MCP (p. ej., Claude Desktop, tu agente de IA) se conecta a él.

Configuraciones del cliente MCP

Python estándar

Añade esto a la configuración de tu cliente MCP (p. ej., el claude_desktop_config.json de Claude Desktop):

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

Docker

Primero construye la imagen:

docker build -t mcp-shop:latest .

A continuación, configura tu cliente 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

Herramientas

list_tables

Enumera todas las tablas y vistas de usuario de la base de datos (excluye las tablas internas sqlite_*).

Parámetros: ninguno

Devuelve:

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

describe_table

Describe el esquema de una tabla: columnas, claves foráneas, número de filas y la sentencia CREATE.

Parámetros:

  • table (cadena, obligatorio): nombre de la tabla a describir.

Devuelve:

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

Ejecuta una consulta SQL de solo lectura con soporte de paginación.

Parámetros:

  • sql (cadena, obligatorio): una única sentencia SQL de solo lectura (SELECT, WITH, EXPLAIN o PRAGMA de solo lectura).

  • limit (entero, opcional): número máximo de filas a devolver. Por defecto: 100. Máximo: 1000.

  • offset (entero, opcional): número de filas a omitir. Por defecto: 0.

Devuelve:

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

Cuando truncated es true, hay más filas disponibles: aumenta offset para obtener la siguiente página.

Seguridad

El servidor implementa defensa en profundidad para garantizar el acceso de solo lectura:

Capa 1: conexión SQLite (modo de solo lectura por URI)

La base de datos se abre con file:<path>?mode=ro, lo que impide escrituras a nivel del motor de SQLite. Además, se establece PRAGMA query_only = ON en cada conexión.

Capa 2: validador de consultas SQL (security.py)

Antes de que cualquier consulta llegue a SQLite, pasa por un validador de varias etapas:

  1. Eliminación de literales de cadena: los literales de cadena ('...', "...") se sustituyen por marcadores de posición para que las palabras clave dentro de los datos (p. ej., un producto llamado «Deleted Item») no provoquen falsos positivos.

  2. Detección de comentarios: los comentarios SQL (--, /* */) se rechazan para prevenir evasiones basadas en comentarios.

  3. Rechazo de múltiples sentencias: se rechaza cualquier punto y coma (;), lo que impide consultas apiladas.

  4. Análisis de palabras clave: la primera palabra clave real de la sentencia debe ser SELECT, WITH, EXPLAIN o PRAGMA. Se bloquean las palabras clave destructivas (INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, REPLACE, TRUNCATE, ATTACH, BLOQUEO, VACUUM, etc.).

  5. Validación de PRAGMA: se permiten los PRAGMA de solo lectura (table_info, database_list, etc.). Se rechaza cualquier PRAGMA con asignación (=) o que esté en la lista de PRAGMA mutantes (journal_mode, synchronous, foreign_keys, etc.).

Capa 3: inspección de opcodes de EXPLAIN

Como defensa final, la consulta se procesa con el analizador de SQLite mediante EXPLAIN <query>. El flujo de opcodes resultante se inspecciona buscando opcodes de escritura (OpenWrite, Insert, Delete, Create, Drop, etc.) y los indicadores de transacción de escritura. Si se encuentra alguno, la consulta se rechaza.

Capa 4: mensajes de error saneados

Todos los errores devueltos al cliente se sanan: se eliminan las rutas del sistema de archivos y los detalles internos para evitar la fuga de información.

Pruebas

Las pruebas solo utilizan bases de datos temporales o en memoria — nunca la base de datos de producción 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

Cobertura de pruebas

Archivo de prueba

Cobertura

tests/test_security.py

76 pruebas: consultas válidas, rechazo de sentencias destructivas, validación de PRAGMA, rechazo de múltiples sentencias, prevención de evasión con comentarios, manejo de literales de cadena

tests/test_db.py

20 pruebas: cumplimiento de solo lectura, listado de tablas, descripción del esquema, paginación, truncamiento, todas las 8 consultas de referencia

tests/test_server.py

9 pruebas: descubrimiento de herramientas MCP, llamadas a herramientas mediante cliente SDK, rechazo de consultas destructivas, paginación, 7 consultas de referencia mediante herramientas, protección de stderr / ausencia de contaminación de stdout

Análisis estático

# Type checking
python -m mypy

# Linting
python -m ruff check src/ tests/

Estructura del proyecto

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

Tareas de referencia

Las herramientas del servidor permiten a un agente de IA realizar estas tareas analíticas (validadas mediante pruebas sin con una base de datos de pruebas controlada):

  1. Descubrimiento de tablas: list_tables + describe_table — listar todas las tablas y describir los esquemas.

  2. Conteo filtrado: query_database con SELECT COUNT(*) FROM customers WHERE country = 'Germany'.

  3. Agregación por país: SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1.

  4. LTV del cliente: unir customers + orders, SUM(total_amount), ordenar por total.

  5. Rendimiento del producto: unir order_items + products, agregar por cantidad e ingresos, LIMIT 5.

  6. Agregación por categoría: recorrer order_items → products → category, agregar ingresos, LIMIT 3.

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

  8. Agregación de pedidos: unir customers + orders, COUNT(o.id), ordenar por número de pedidos.

Configuración

Variable de entorno

Valor por defecto

Descripción

DATABASE_PATH

./shop.db

Ruta al archivo de la base de datos SQLite

ROW_LIMIT

100

Límite de filas por defecto para los resultados de consultas (máximo 1000)

Licencia

Este proyecto se proporciona tal cual, como referencia.Demostración.

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