# sqlite-mcp-server

An [MCP](https://modelcontextprotocol.io) (Model Context Protocol) server
that gives an AI assistant safe, structured access to a SQLite database.

Point it at any `.db` file and an MCP-compatible client (Claude Desktop,
Claude Code, etc.) can inspect the schema and query it directly.

## Why this one

- **Read-only by default.** The assistant can `SELECT` all it wants, but
  can't `INSERT`/`UPDATE`/`DELETE`/`DROP` anything unless you explicitly
  turn writes on with an environment variable.
- **Every statement is checked before it runs** — not just trusted
  blindly. Stacked statements (`SELECT 1; DROP TABLE users;`) are
  rejected outright.
- **Results are capped** (1000 rows by default) so a `SELECT *` on a huge
  table can't flood the assistant's context window.
- **Schema-aware.** The assistant can list tables, describe columns, and
  read the full schema before it ever writes a query — fewer guesses,
  fewer mistakes.

## Install

```bash
git clone <this-repo-url>
cd sqlite-mcp-server
pip install -r requirements.txt
```

## Try it immediately with sample data

```bash
python3 seed_sample_db.py
```

This creates `./data/app.db` with two small tables (`customers`, `orders`)
so you can test the server without hooking it up to a real database first.

## Run it standalone

```bash
python3 server.py
```

It talks MCP over stdio, so it's meant to be launched by an MCP client,
not run interactively on its own.

## Connect it to Claude Desktop

Add this to your Claude Desktop MCP config (see
`claude_desktop_config.example.json` in this repo for the full snippet),
replacing the paths with the absolute path to where you cloned this repo:

```json
{
  "mcpServers": {
    "sqlite-explorer": {
      "command": "python3",
      "args": ["/absolute/path/to/sqlite-mcp-server/server.py"],
      "env": {
        "MCP_SQLITE_DB_PATH": "/absolute/path/to/sqlite-mcp-server/data/app.db"
      }
    }
  }
}
```

Restart Claude Desktop, and the assistant will have `list_tables`,
`describe_table`, `query`, and `execute` tools available for that
database.

## Configuration

Set these as environment variables (see `.env.example`):

| Variable | Default | Meaning |
|---|---|---|
| `MCP_SQLITE_DB_PATH` | `./data/app.db` | Path to the SQLite file. Created automatically if missing. |
| `MCP_ALLOW_WRITE` | `false` | Set to `true` to allow the `execute` tool to run write statements. |
| `MCP_MAX_ROWS` | `1000` | Maximum rows a single `query` call can return. |

## Tools exposed

| Tool | Description |
|---|---|
| `list_tables()` | List every table in the database. |
| `describe_table(table)` | Columns, types, nullability, primary keys, and indexes for one table. |
| `query(sql, params?)` | Run a read-only `SELECT`/`PRAGMA`/`EXPLAIN`/`WITH` statement. |
| `execute(sql, params?)` | Run a write statement. Only works if `MCP_ALLOW_WRITE=true`. |

Plus a resource, `schema://database`, exposing the full `CREATE TABLE`
statements for the database, so the assistant can read the whole schema
at a glance.

## Safety model

- `query()` only accepts `SELECT`/`PRAGMA`/`EXPLAIN`/`WITH`. Anything else
  is rejected with an error before it touches the database.
- `execute()` is a hard no-op — it raises `PermissionError` — unless the
  server was started with `MCP_ALLOW_WRITE=true`. This means an assistant
  can never modify or destroy data unless a human operator deliberately
  opted in.
- Multiple statements in one call (`SELECT 1; DROP TABLE users;`) are
  rejected outright, whether writes are enabled or not.
- Known limitation: the multi-statement check is a simple semicolon scan,
  not a full SQL parser, so a semicolon inside a string literal
  (`SELECT ';' AS x`) will also be (harmlessly) rejected. Being overly
  cautious here was a deliberate trade-off over a more permissive parser
  that could be tricked into missing a real stacked statement.

## Development

The core SQL-safety logic lives in `sql_guard.py`, which has zero
dependency on the `mcp` package, so it can be tested without installing
the full SDK:

```bash
python3 -m unittest discover -s tests -v
```

## License

MIT — see `LICENSE`.