# mcp-sqlite-explorer

A **Model Context Protocol (MCP) server** that safely exposes a local **SQLite** database to Claude and any other MCP client. Point Claude Desktop (or Claude Code) at a `.db` file and ask questions about your data in plain English — Claude discovers the schema and runs **read-only** queries through this server.

Built with the official [`@modelcontextprotocol/server`](https://www.npmjs.com/package/@modelcontextprotocol/server) TypeScript SDK (v2).

---

## What it exposes

| Kind | Name | Purpose |
|------|------|---------|
| Tool | `list_tables` | List every table and view |
| Tool | `describe_table` | Columns, types and keys for one table |
| Tool | `query` | Run a **read-only** SQL query (SELECT / WITH … SELECT) and get JSON rows |
| Resource | `schema://database` | The full schema as text, for context |
| Prompt | `explore-table` | A ready-made "profile this table" request |

### Safety — read-only by design

Three layers stop any write reaching your database:

1. The connection is opened **read-only** (`better-sqlite3` `{ readonly: true }`) — writes fail at the SQLite level.
2. A textual guard rejects anything that isn't a single `SELECT` / `WITH … SELECT` (blocks `INSERT/UPDATE/DELETE/DROP/PRAGMA`, multiple statements, and writes smuggled into a CTE).
3. Each prepared statement is checked with better-sqlite3's `statement.readonly` before it runs.

Results are capped (default 100 rows, max 1000) so a huge table can't flood the context.

## Requirements

- Node.js 20+

## Setup

```bash
npm install
npm run seed     # creates data/sample.db (customers, products, orders, order_items)
npm run build    # compile to dist/
```

## Use it with Claude Desktop

Add this to your `claude_desktop_config.json` (use absolute paths):

```json
{
  "mcpServers": {
    "sqlite-explorer": {
      "command": "node",
      "args": [
        "/absolute/path/to/mcp-sqlite-explorer/dist/index.js",
        "/absolute/path/to/your-database.db"
      ]
    }
  }
}
```

Restart Claude Desktop, then ask things like *"What tables are in the database?"* or *"Which product category has the most orders?"* — Claude will call `list_tables`, `describe_table` and `query` as needed.

With **Claude Code**:

```bash
claude mcp add sqlite-explorer -- node /absolute/path/to/dist/index.js /absolute/path/to/your.db
```

The database path can also be given via the `MCP_SQLITE_DB` environment variable; it defaults to `data/sample.db`.

## Quick check (no client needed)

You can drive the server over stdio directly to confirm it works:

```bash
printf '%s\n' \
  '{"jsonrpc":"2.0","id":1,"method":"initialize","params":{"protocolVersion":"2024-11-05","capabilities":{},"clientInfo":{"name":"c","version":"1"}}}' \
  '{"jsonrpc":"2.0","method":"notifications/initialized"}' \
  '{"jsonrpc":"2.0","id":2,"method":"tools/list"}' \
  | node dist/index.js data/sample.db
```

## Scripts

```bash
npm run dev       # run from source with tsx
npm run seed      # (re)create the sample database
npm run build     # compile to dist/
npm start         # run the compiled server
npm run typecheck # tsc --noEmit
npm test          # vitest
```

## Project structure

```
src/
  sql-safety.ts   Pure read-only query guard + identifier check
  db.ts           Read-only SQLite wrapper (list/describe/query/schema)
  server.ts       MCP server: tools, resource, prompt
  index.ts        stdio entry point (logs to stderr only)
  seed.ts         Builds the sample database
```

> **stdio note:** the MCP stdio transport uses **stdout** for the JSON-RPC protocol, so the server logs only to **stderr** — writing to stdout would corrupt the stream.

## Tests

```bash
npm test
```

The suite covers the read-only guard (write statements, multi-statement, CTE-smuggled writes, comment handling) and the database wrapper (listing, describing, querying, row-limit truncation, and write rejection) against an in-memory database.

## License

Released under the [MIT License](LICENSE).