MCP SQLite Server (Read-Only)
Provides safe, read-only access to a SQLite database, allowing AI agents to list tables, describe schemas, and execute read-only SQL queries with pagination.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@MCP SQLite Server (Read-Only) List the tables in the database"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
MCP SQLite Server (Read-Only)
A production-ready Model Context Protocol server that provides AI agents with safe, read-only access to a SQLite database (shop.db). Built with the official mcp Python SDK using the stdio transport.
Features
3 MCP tools:
list_tables,describe_table,query_databaseDefense-in-depth read-only safety: SQLite URI read-only mode +
PRAGMA query_only+ SQL validator + EXPLAIN opcode inspectionQuery validation: Rejects
INSERT/UPDATE/DELETE/DROP/ALTER/CREATE/REPLACE/TRUNCATE/ATTACH/DETACH, multi-statement queries (;), SQL comments (--,/* */), and modifyingPRAGMA— without false positives on string literalsPagination: Default row limit (100),
limit/offsetparameters, truncated-output flagStderr-only logging: All logs/tracebacks go to
sys.stderr;stdoutis reserved exclusively for JSON-RPCFull type hints:
mypy --strictcleanTDD: 105 tests covering security, DB layer, MCP tools, 8 benchmark queries, and the stderr guard
Related MCP server: shop-analytics-mcp
Quick Start
Prerequisites
Python 3.10+
A SQLite database file (default:
./shop.db)
Local Setup
python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"Configure
Copy .env.example and set the database path:
cp .env.example .env
# Edit DATABASE_PATH to point to your SQLite fileOr set the environment variable directly:
export DATABASE_PATH=/abs/path/to/shop.dbRun the Server
python -m mcp_server.serverThe server communicates over stdin/stdout using the MCP stdio transport. You don't interact with it directly — an MCP client (e.g., Claude Desktop, your AI agent) connects to it.
MCP Client Configurations
Standard Python
Add this to your MCP client configuration (e.g., Claude Desktop's claude_desktop_config.json):
{
"mcpServers": {
"sqlite-shop": {
"command": "python",
"args": ["-m", "mcp_server.server"],
"env": {
"DATABASE_PATH": "/abs/path/to/shop.db"
}
}
}
}Docker
First build the image:
docker build -t mcp-shop:latest .Then configure your MCP client:
{
"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 -dTools
list_tables
Lists all user tables and views in the database (excludes internal sqlite_* tables).
Parameters: none
Returns:
{
"tables": ["customers", "orders", "order_items", "products"],
"count": 4
}describe_table
Describes the schema of a table: columns, foreign keys, row count, and the CREATE statement.
Parameters:
table(string, required): Name of the table to describe.
Returns:
{
"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
Executes a read-only SQL query with pagination support.
Parameters:
sql(string, required): A single read-only SQL statement (SELECT,WITH,EXPLAIN, or read-onlyPRAGMA).limit(integer, optional): Maximum rows to return. Default: 100. Max: 1000.offset(integer, optional): Number of rows to skip. Default: 0.
Returns:
{
"columns": ["id", "first_name"],
"rows": [{"id": 1, "first_name": "Alice"}, {"id": 2, "first_name": "Bob"}],
"row_count": 2,
"truncated": false,
"limit": 100,
"offset": 0
}When truncated is true, more rows are available — increase offset to fetch the next page.
Security
The server implements defense-in-depth to guarantee read-only access:
Layer 1: SQLite Connection (URI read-only mode)
The database is opened with file:<path>?mode=ro, which prevents writes at the SQLite engine level. Additionally, PRAGMA query_only = ON is set on every connection.
Layer 2: SQL Query Validator (security.py)
Before any query reaches SQLite, it passes through a multi-stage validator:
String literal stripping: String literals (
'...',"...") are replaced with placeholders so keywords inside data (e.g., a product named "Deleted Item") don't trigger false positives.Comment detection: SQL comments (
--,/* */) are rejected to prevent comment-based bypasses.Multi-statement rejection: Any semicolon (
;) is rejected, preventing stacked queries.Keyword analysis: The first real statement keyword must be
SELECT,WITH,EXPLAIN, orPRAGMA. Destructive keywords (INSERT,UPDATE,DELETE,DROP,ALTER,CREATE,REPLACE,TRUNCATE,ATTACH,DETACH,VACUUM, etc.) are blocked.PRAGMA validation: Read-only PRAGMAs (
table_info,database_list, etc.) are allowed. Any PRAGMA with an assignment (=) or in the mutating-PRAGMA blocklist (journal_mode,synchronous,foreign_keys, etc.) is rejected.
Layer 3: EXPLAIN Opcode Inspection
As a final defense, the query is run through SQLite's own parser via EXPLAIN <query>. The resulting opcode stream is inspected for write opcodes (OpenWrite, Insert, Delete, Create, Drop, etc.) and write-transaction flags. If any are found, the query is rejected.
Layer 4: Sanitized Error Messages
All errors returned to the client are sanitized — filesystem paths and internal details are stripped to prevent information leakage.
Testing
Tests use temporary/in-memory databases only — never the production 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.pyTest Coverage
Test File | Coverage |
| 76 tests: valid queries, destructive statement rejection, PRAGMA validation, multi-statement rejection, comment bypass prevention, string literal handling |
| 20 tests: read-only enforcement, table listing, schema description, pagination, truncation, all 8 benchmark queries |
| 9 tests: MCP tool discovery, tool calls via SDK client, destructive query rejection, pagination, 7 benchmark queries via tools, stderr/no-stdout-pollution guard |
Static Analysis
# Type checking
python -m mypy
# Linting
python -m ruff check src/ tests/Project Structure
.
├── .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 testsBenchmark Tasks
The server's tools enable an AI agent to perform these analytical tasks (validated by tests against a controlled fixture database):
Table Discovery:
list_tables+describe_table— list all tables and describe schemas.Filtered Count:
query_databasewithSELECT COUNT(*) FROM customers WHERE country = 'Germany'.Country Aggregation:
SELECT country, COUNT(*) ... GROUP BY country ORDER BY ... DESC LIMIT 1.Customer LTV: Join
customers+orders,SUM(total_amount), order by total.Product Performance: Join
order_items+products, aggregate by quantity and revenue,LIMIT 5.Category Aggregation: Traverse
order_items→products→category, aggregate revenue,LIMIT 3.Date Filtering:
SUM(total_amount) WHERE substr(order_date,1,4) = '2025'.Order Aggregation: Join
customers+orders,COUNT(o.id), order by count.
Configuration
Environment Variable | Default | Description |
|
| Path to the SQLite database file |
|
| Default row limit for query results (max 1000) |
License
This project is provided as-is for demonstration purposes.
Available Tools
3 toolsdescribe_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.
Inputs: table (required).
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.
Inputs: no named parameters.
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}.
Inputs: sql (required), limit, offset.
Tool Schema Changelog
Recent tool additions, removals, and schema changes observed during successful MCP inspections.
Loading tool schema changelog…
TDQS
Scored across 3 tools
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.
All tool names follow the same snake_case verb_noun pattern (list_tables, describe_table, query_database), offering a consistent and predictable naming convention.
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.
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
Related MCP Connectors
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Explore, query, and inspect SQLite databases with ease. List tables, preview results, and view det…
Read-only search of 2,189 AI agent tools (MCP servers, x402 APIs) with daily automated trust scores.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Related MCP Servers
- FlicenseNot gradedqualityCmaintenanceExposes 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.-
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to read-only query a SQLite database, inspect schema and table summaries, and execute SELECT queries with pagination through MCP.MIT
- AlicenseAqualityCmaintenanceEnables AI agents to inspect real databases through MCP with tools for listing tables, describing schemas, sampling rows, running queries, and explaining execution plans, all with engine-enforced read-only access to SQLite and MySQL.512 npmMIT
- AlicenseNot gradedqualityCmaintenanceEnables AI agents to run read-only SQL against SQLite or MySQL databases through MCP tools for listing tables, describing schemas, and querying data. Wraps every call in guardrails such as statement allow-listing, row caps, query timeouts, PII masking, and audit logging.1MIT