# SQLite MCP Server

> A read-only **Model Context Protocol (MCP)** server that lets an LLM (like Claude) safely explore and query any SQLite database in natural language.

[![CI](https://github.com/helmi75/mcp-sqlite-server/actions/workflows/ci.yml/badge.svg)](https://github.com/helmi75/mcp-sqlite-server/actions/workflows/ci.yml)
[![Python](https://img.shields.io/badge/python-3.10%2B-blue.svg)](https://www.python.org/)
[![MCP](https://img.shields.io/badge/MCP-compatible-7C3AED.svg)](https://modelcontextprotocol.io/)
[![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)](LICENSE)

---

## What is this?

[MCP](https://modelcontextprotocol.io/) is the open standard that connects LLMs to external tools and data. This server speaks MCP and exposes a **SQLite database** to any MCP client (Claude Desktop, IDEs, custom agents) through three small, safe tools.

Ask **"Which product category generated the most revenue last quarter?"** and the model discovers the schema, writes the SQL itself, runs it through this server, and answers — without you writing a single query.

## Why it's safe by design

Giving an LLM database access is risky if done naively. This server is **read-only on two independent levels**:

1. **The connection** is opened with SQLite's `file:...?mode=ro` URI — the OS-level handle physically cannot write.
2. **Every query is validated** to be a *single* `SELECT`/`WITH` statement before execution. Writes (`INSERT`/`UPDATE`/`DELETE`/`DROP`) and multi-statement injections (`SELECT 1; DROP TABLE ...`) are rejected with a clear error.

Results are also capped (`SQLITE_MAX_ROWS`, default 100) so a careless `SELECT *` can't flood the context window.

## Tools exposed

| Tool | Description |
|------|-------------|
| `list_tables()` | List all user tables. |
| `describe_table(table)` | Show a table's columns, types and keys. |
| `read_query(sql, limit)` | Run a read-only `SELECT` / `WITH` query and return a Markdown table. |

Plus a `schema://database` **resource** exposing the full DDL, so the model can load the whole schema at once.

## Architecture

```
┌────────────────┐   MCP (stdio)   ┌──────────────────────┐   read-only    ┌────────────┐
│  Claude / MCP  │ ◄────────────► │  sqlite-mcp-server   │ ◄───────────► │  SQLite DB │
│     client     │   tool calls    │  list/describe/query │   mode=ro      │  (*.db)    │
└────────────────┘                 └──────────────────────┘                └────────────┘
```

## Quickstart

```bash
# 1. Install
git clone https://github.com/helmi75/mcp-sqlite-server.git
cd mcp-sqlite-server
pip install -e .

# 2. Create the demo e-commerce database (customers, products, orders, order_items)
python scripts/seed_db.py

# 3. Run the server (stdio)
python -m sqlite_mcp_server.server
```

Point it at your own database instead with the `SQLITE_DB_PATH` environment variable.

## Use it with Claude Desktop

Add this to your `claude_desktop_config.json` (see [`examples/`](examples/claude_desktop_config.json)):

```json
{
  "mcpServers": {
    "sqlite-explorer": {
      "command": "python",
      "args": ["-m", "sqlite_mcp_server.server"],
      "env": { "SQLITE_DB_PATH": "/absolute/path/to/demo.db" }
    }
  }
}
```

Restart Claude Desktop, then try:

- *"What tables are in the database?"*
- *"Show me the schema of the orders table."*
- *"Top 3 customers by total spend, with their country."*
- *"Monthly revenue trend for delivered orders."*

## Configuration

| Variable | Default | Description |
|----------|---------|-------------|
| `SQLITE_DB_PATH` | `./demo.db` | Path to the SQLite database to serve. |
| `SQLITE_MAX_ROWS` | `100` | Maximum rows returned by `read_query`. |

## Testing

```bash
pip install -e ".[dev]"
python scripts/seed_db.py
pytest -v
```

The suite covers schema introspection, query execution, the row limit, and — importantly — that **writes and multi-statement injections are rejected** at both the SQL-guard and connection levels. CI runs on Python 3.10–3.12 via GitHub Actions.

## Project structure

```
mcp-sqlite-server/
├── src/sqlite_mcp_server/
│   └── server.py            # MCP server: tools + read-only safety
├── scripts/seed_db.py       # Reproducible demo database
├── tests/test_server.py     # Pytest suite (incl. security tests)
├── examples/                # Claude Desktop config
├── .github/workflows/ci.yml # CI: pytest on 3.10–3.12
└── pyproject.toml
```

## Roadmap

- [ ] `EXPLAIN QUERY PLAN` tool to help the model optimise queries
- [ ] Optional per-table allowlist / column masking
- [ ] PostgreSQL backend behind the same interface

## License

MIT — see [LICENSE](LICENSE).

---

Built by **[Helmi Chiha](https://www.linkedin.com/in/helmi-chiha-8b66ba34/)** — AI / Backend Engineer (RAG · LLM agents · MCP · FastAPI).