# mcp-sqlite-explorer

[![CI](https://github.com/KaryawanSurga/mcp-sqlite-explorer/actions/workflows/ci.yml/badge.svg)](https://github.com/KaryawanSurga/mcp-sqlite-explorer/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/License-MIT-blue.svg)](LICENSE)
[![Node](https://img.shields.io/badge/node-%3E%3D22.0.0-brightgreen.svg)](https://nodejs.org)

> **Zero-dependency, read-only SQLite explorer and query sandbox for AI agents and developers.**

Inspect databases, extract relational schemas, run bounded read-only queries, and generate token-dense database summaries for Claude, Cursor, and MCP-compatible coding agents — without risking accidental data modification or context window flooding.

---

## ✨ Features

- **🛡️ Multi-Layer Read-Only Safety**:
  - AST-level query validator blocks all mutating statements (`INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `ATTACH`, mutating `PRAGMA`s).
  - Multi-query injection detection (disallows statement chaining via `;`).
  - Native SQLite engine-level read-only mode (`PRAGMA query_only = ON;` and `readOnly: true`).
- **⚡ Zero Native Dependencies**:
  - Built entirely on Node 22+ native `node:sqlite`.
  - No `node-gyp`, no Python runtime requirement, no C++ compilation errors on Windows, macOS, or Linux.
- **🧠 Token-Optimized AI Context Summaries**:
  - `sqlite_summarize_database` produces high-density schema maps (tables, row counts, PKs, foreign keys, indexes) formatted specifically to conserve LLM context budgets.
- **📊 Bounded Query Sandbox**:
  - Enforces pagination (`limit` & `offset`) with default cap (50 rows, max 500) and truncation indicators.
  - Formats results into GitHub-flavored markdown tables, compact TSV, or JSONL.
- **🔍 Query Plan Explainer**:
  - Inspect SQLite index utilization and execution plans with `EXPLAIN QUERY PLAN`.
- **💻 Standalone CLI**:
  - Dual-mode: works as both an MCP stdio server and a standalone terminal utility.

---

## 🚀 Quick Start

### 1. Claude Desktop Configuration

Add `mcp-sqlite-explorer` to your `claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "sqlite-explorer": {
      "command": "npx",
      "args": ["-y", "mcp-sqlite-explorer", "serve", "--db", "/path/to/database.sqlite"]
    }
  }
}
```

### 2. Standalone CLI Usage

```bash
# List all tables and views with row counts
npx mcp-sqlite-explorer tables ./mydb.sqlite

# Describe table columns, primary keys, and foreign keys
npx mcp-sqlite-explorer describe ./mydb.sqlite users

# Run a safe read-only query
npx mcp-sqlite-explorer query ./mydb.sqlite "SELECT id, name FROM users LIMIT 10"

# Generate agent-ready database summary
npx mcp-sqlite-explorer summary ./mydb.sqlite

# Inspect query plan
npx mcp-sqlite-explorer explain ./mydb.sqlite "SELECT * FROM posts WHERE user_id = 1"
```

---

## 🛠️ MCP Tools Reference

| Tool | Description | Key Parameters |
| --- | --- | --- |
| `sqlite_list_tables` | List user tables and views with row and column counts | `db_path`, `include_views` |
| `sqlite_describe_table` | Full schema detail: columns, types, PKs, FKs, indexes, DDL | `db_path`, `table` |
| `sqlite_read_query` | Execute read-only query (`SELECT`, `WITH`) with pagination | `db_path`, `query`, `limit`, `offset`, `format` |
| `sqlite_explain_query` | Inspect SQLite execution plan | `db_path`, `query` |
| `sqlite_summarize_database` | Token-dense database overview for LLM context | `db_path` |

---

## 🔒 Security Model

1. **Strict SQL Whitelist**: Only `SELECT`, `WITH ... SELECT`, `EXPLAIN`, and safe read-only PRAGMAs (`table_info`, `database_list`, `index_list`, `foreign_key_list`) are permitted.
2. **Comment Stripping**: SQL comments (`--` and `/* */`) are parsed and removed prior to analysis to prevent comment-cloaked attacks.
3. **No Database Writes**: Any attempt to write or mutate triggers an immediate `UnsafeQueryError` before reaching SQLite.

---

## 🧪 Testing

```bash
npm test          # Run Vitest test suite (35+ unit & e2e tests)
npm run typecheck # Validate TypeScript types
npm run build     # Compile TypeScript to dist/
```

---

## 📄 License

MIT © [KaryawanSurga](https://github.com/KaryawanSurga)