QueryPilot MCP
# QueryPilot MCP
[](https://github.com/m-shahmeer-kz/querypilot-mcp/actions/workflows/tests.yml)
[](LICENSE)
**[Safety report and real session ↗](https://m-shahmeer-kz.github.io/querypilot-mcp/)**
An open-source **MCP server** that lets AI agents (Claude, Cursor, VS Code Copilot, Codex, Gemini CLI ...) **safely explore and query your local data files**: CSV, Parquet, Excel and SQLite. It uses **DuckDB** for fast SQL and the official **MCP Python SDK**. Free and fully local: no cloud, no API keys, no downloads at query time.
Point it at one folder. The agent can look at the tables and run `SELECT` queries. It **cannot** change data, write files, read anything outside that folder, or reach the network, and every call is written to a log.
## Tools the agent gets
| Tool | What it does |
|---|---|
| `list_tables` | every table found in the folder, with the file it came from |
| `describe_table` | columns, types and statistics (rows, min, max, average, distinct values, % missing) |
| `sample_rows` | a few example rows (max 50) |
| `run_query` | **one read-only `SELECT`**, max **1,000 rows**, stopped after **30 s** |
| `get_definitions` | your business terms and column meanings from an optional `semantic.yaml` |
All five tools are marked `readOnlyHint` so the agent's app can show they change nothing.
## Measured results
Real data: the Chinook music-store database (SQLite, 11 tables), the Titanic passenger list (CSV, 1,309 rows), Birmingham car-park occupancy (Parquet, 35,717 rows) and an Excel workbook ([`benchmarks/run_benchmarks.py`](benchmarks/run_benchmarks.py), [`results.json`](benchmarks/results.json)).
| Check | Result |
|---|---|
| Unsafe queries blocked (writes, DDL, file read/write, path traversal, network, extensions, settings, dynamic SQL; 9 kinds) | **60 / 60** |
| Secret file outside the folder leaked | **0** |
| Files created or changed in the data folder | **0** |
| Real analytical questions wrongly blocked | **0 / 41** |
| Answers equal to the reference (sqlite3 on the original file, or pandas) | **41 / 41** |
| Row cap on a 35,717-row table | **1,000** returned, `truncated: true` |
| Runaway query with the default 30 s limit | stopped at **30.0 s**, server kept working |
| SQL guard time per query (median) | **0.9 ms** |
| MCP round trip over stdio, `run_query` (median, 30 calls) | **5.4 ms** |
**Why two safety layers?** The benchmark also switched the SQL guard **off** and attacked DuckDB's own lock-down alone: it stopped **42 / 60**. The other 18 ran, including **2 `COPY` commands that wrote new files into the data folder** (DuckDB's "allowed folder" setting allows writing there too). With the guard on, 60 / 60 are blocked. And in the Docker image the folder is mounted read-only, so the same `COPY` fails at the operating-system level too (`Read-only file system`).
## Install
Needs Python 3.11+.
```bash
pip install "git+https://github.com/m-shahmeer-kz/querypilot-mcp"
querypilot-mcp --data-dir /path/to/your/data # quick check: it waits for an agent (Ctrl+C to stop)
```
Or from a clone (for development):
```bash
git clone https://github.com/m-shahmeer-kz/querypilot-mcp && cd querypilot-mcp
python -m venv .venv && source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -e ".[dev]"
pytest # 120 tests
querypilot-mcp --data-dir examples/data # try it on the example data
```
Find the full path of the command for the configs below with `which querypilot-mcp` (macOS/Linux) or `where querypilot-mcp` (Windows). Desktop apps often do not see your terminal's `PATH`, so use the **full path**.
### Options
| Flag | Env variable | Default |
|---|---|---|
| `--data-dir` | `QUERYPILOT_DATA_DIR` | required: the only folder the agent can see |
| `--log-file` | `QUERYPILOT_LOG_FILE` | `~/.querypilot/queries.jsonl` (must be outside the data folder) |
| `--semantic` | `QUERYPILOT_SEMANTIC` | `<data-dir>/semantic.yaml` if it exists |
| `--max-rows` | `QUERYPILOT_MAX_ROWS` | 1000 (can be lowered, not raised) |
| `--timeout` | `QUERYPILOT_TIMEOUT` | 30 seconds (can be lowered, not raised) |
| `--transport` | | `stdio`; or `streamable-http` with `--host`/`--port` |
## Connect it to your agent
In every example, replace `/full/path/to/querypilot-mcp` and `/path/to/your/data`.
### Claude Code
```bash
claude mcp add querypilot -- /full/path/to/querypilot-mcp --data-dir /path/to/your/data
claude mcp list # should show: querypilot ... ✓ Connected
```
(Tested: `claude mcp list` reported `✓ Connected`.)
### Claude Desktop
Settings → Developer → Edit Config, then add to `claude_desktop_config.json` and restart Claude:
```json
{
"mcpServers": {
"querypilot": {
"command": "/full/path/to/querypilot-mcp",
"args": ["--data-dir", "/path/to/your/data"]
}
}
}
```
### Cursor
`~/.cursor/mcp.json` (all projects) or `.cursor/mcp.json` (one project):
```json
{
"mcpServers": {
"querypilot": {
"command": "/full/path/to/querypilot-mcp",
"args": ["--data-dir", "/path/to/your/data"]
}
}
}
```
### VS Code (GitHub Copilot agent mode)
`.vscode/mcp.json` in your project (note: the key is `servers`, not `mcpServers`):
```json
{
"servers": {
"querypilot": {
"type": "stdio",
"command": "/full/path/to/querypilot-mcp",
"args": ["--data-dir", "${workspaceFolder}/data"]
}
}
}
```
### OpenAI Codex CLI
```bash
codex mcp add querypilot -- /full/path/to/querypilot-mcp --data-dir /path/to/your/data
```
or in `~/.codex/config.toml`:
```toml
[mcp_servers.querypilot]
command = "/full/path/to/querypilot-mcp"
args = ["--data-dir", "/path/to/your/data"]
```
### Gemini CLI
`~/.gemini/settings.json` (or `.gemini/settings.json` in a project):
```json
{
"mcpServers": {
"querypilot": {
"command": "/full/path/to/querypilot-mcp",
"args": ["--data-dir", "/path/to/your/data"]
}
}
}
```
### With Docker (any agent)
```bash
docker build -t querypilot-mcp .
```
Then use `docker` as the command. `:ro` mounts your folder **read-only**:
```json
{
"mcpServers": {
"querypilot": {
"command": "docker",
"args": ["run", "-i", "--rm", "-v", "/path/to/your/data:/data:ro", "querypilot-mcp"]
}
}
}
```
Use `-i` (keeps stdin open) and never `-d`. The image runs as a non-root user.
## semantic.yaml (optional)
Agents write good SQL but do not know what **your** words mean. Put a `semantic.yaml` in the data folder (full example: [`examples/data/semantic.yaml`](examples/data/semantic.yaml)):
```yaml
terms:
revenue:
description: Money from sold tracks (unit price x quantity), before tax.
sql: SUM(UnitPrice * Quantity) -- table chinook_invoice_line
synonyms: [sales, turnover]
tables:
titanic:
description: One row per Titanic passenger.
columns:
pclass: Ticket class (1 = first, 2 = second, 3 = third).
```
`get_definitions("sales")` then returns the `revenue` entry, and column notes also appear in `describe_table`. The file is read with `yaml.safe_load`, so it can never run code.
## How the safety works
```
agent ──MCP──▶ tool ──▶ ① SQL guard ──▶ ② locked DuckDB ──▶ ③ limits ──▶ result
│ │
└──────────── ④ audit log (every call) ◀──┘
```
1. **SQL guard** ([`sql_guard.py`](src/querypilot/sql_guard.py)). DuckDB's own parser reads the query (no fragile text matching). It must be exactly **one** statement of type `SELECT`; every table must be one of QueryPilot's tables or a CTE; table functions are allow-listed (`range`, `generate_series`, `unnest` only), so `read_csv`, `read_text`, `glob`, `query()` ... are refused; `getenv` and `current_setting` are refused.
2. **Locked DuckDB** ([`engine.py`](src/querypilot/engine.py)). The catalog is opened **read-only**, file access is switched off except the data folder, extension install/auto-load is off (no network), and the configuration is **locked** so a query cannot switch any of this back on.
3. **Limits**. At most 1,000 rows come back (`truncated` tells the agent there were more) and a timer interrupts any query after 30 s.
4. **Audit log** ([`audit.py`](src/querypilot/audit.py)). One JSON line per call: time, tool, SQL, `ok` / `blocked` / `error` / `timeout`, reason, rows, milliseconds.
**Sandbox** ([`sandbox.py`](src/querypilot/sandbox.py)): only files inside the data folder are loaded, hidden files are skipped, and a symlink pointing outside the folder is ignored. QueryPilot never writes inside the data folder (the log must live elsewhere).
**File types**: CSV/TSV and Parquet become live DuckDB views (nothing copied). Excel sheets are read with pandas + openpyxl, and SQLite tables with Python's `sqlite3` in read-only mode (`?mode=ro`). Table names are simple: `chinook.sqlite` → `chinook_invoice_line`, sheet `Costs` of `budget.xlsx` → `budget_costs`.
## Project structure
```
querypilot-mcp/
├── src/querypilot/
│ ├── cli.py # querypilot-mcp command
│ ├── server.py # the 5 MCP tools
│ ├── engine.py # locked DuckDB, row cap, timeout
│ ├── sql_guard.py # SELECT-only allow-list check
│ ├── catalog.py # CSV / Parquet / Excel / SQLite -> tables
│ ├── sandbox.py # one-folder sandbox, safe table names
│ ├── semantic.py # semantic.yaml glossary
│ ├── audit.py # JSONL query log
│ └── config.py # settings and hard limits
├── tests/ # 120 pytest tests (guard, engine, sandbox, log, MCP end-to-end)
├── benchmarks/ # attack + accuracy benchmark, results.json
├── examples/data/ # real example data + semantic.yaml
├── scripts/ # Docker smoke test, demo session recorder
├── docs/ # GitHub Pages report
├── Dockerfile
└── pyproject.toml
```
## Tests
```bash
pytest # 120 tests, about 11 s
python benchmarks/run_benchmarks.py # about 1 minute (includes one real 30 s timeout)
```
The tests cover: 64 attack queries and 24 normal queries on the guard; read-only and locked settings even **without** the guard; secret file and symlink outside the folder; row cap and timeout; audit log of blocked calls; config and `semantic.yaml` errors; and a full **end-to-end** run that starts the server over stdio and talks to it with the official MCP client. CI runs them on Python 3.11 and 3.13, and builds the Docker image and tests it the same way.
## Honest limits
- DuckDB's short `PIVOT t ON ...` statement is refused (DuckDB reports it as several statements). The SQL-standard form `SELECT * FROM t PIVOT (count(*) FOR col IN (...))` works.
- Excel and SQLite files are read once at start-up; restart the server to see changes. CSV and Parquet are always live.
- Memory use is not capped by QueryPilot; a very large join can still use a lot of RAM before the 30 s timeout.
- Tested with mcp 2.3.0, DuckDB 2.0.0, Python 3.11 / 3.12 / 3.13. The Docker image was tested locally on an Ubuntu 24.04 + Python 3.12 base and in CI on the default `python:3.12-slim` base.
## Data credits
Chinook database © Luis Rocha (MIT licence) · Titanic `titanic3` from Vanderbilt Biostatistics · Parking Birmingham from the UCI Machine Learning Repository (Open Government Licence v3.0). `chinook_staff.xlsx` and `birmingham_parking.parquet` are format copies made by [`examples/make_example_data.py`](examples/make_example_data.py).
## License
MIT © Muhammad Shahmeer
TDQS
Scored across 5 tools
Each tool targets a distinct stage of the data exploration workflow: discovering tables, inspecting schema/stats, sampling rows, retrieving glossary definitions, and executing SQL. The only mild overlap is between describe_table and get_definitions, but one gives schema/statistics while the other gives business semantics, so an agent can easily choose correctly.
All tool names follow a clear verb_noun pattern (list_tables, describe_table, sample_rows, get_definitions, run_query). The wording is predictable and readable, with no mixed casing or inconsistent verb styles.
Five tools is well-scoped for a read-only DuckDB query assistant. Every tool adds a necessary capability, and there is no redundant or filler tool.
The surface covers the core read-only lifecycle: discover tables, inspect schemas, sample data, understand business terms, and run constrained SELECT queries. Minor gaps exist around searching for columns by name, inspecting relationships/foreign keys, or explaining query plans, but agents can work around these using describe_table and run_query.