quarry

# Quarry

> **The database workbench built for the AI era** — one kernel, many faces (CLI / GUI / MCP / agent skill).

[![CI](https://github.com/yiminspace/quarry/actions/workflows/ci.yml/badge.svg)](https://github.com/yiminspace/quarry/actions/workflows/ci.yml)
[![Coverage ≥95%](https://img.shields.io/badge/coverage-%E2%89%A595%25-brightgreen)](TESTING.md)
[![PyPI](https://img.shields.io/pypi/v/quarry-db)](https://pypi.org/project/quarry-db/)
[![Python 3.11+](https://img.shields.io/badge/python-3.11%2B-blue)](https://pypi.org/project/quarry-db/)
[![License: MIT](https://img.shields.io/badge/license-MIT-green)](LICENSE)

[中文文档 →](README.zh-CN.md) · [Website →](https://yiminspace.github.io/quarry/)

![Quarry demo](site/assets/demo.svg)

Every database tool you know — DBeaver, TablePlus, pgAdmin — assumes a *human* at the keyboard. But increasingly, the entity running your queries is an **AI agent**, and agents need different guarantees:

- **Results a machine can parse**, not a screen a human can read
- **Shared query safety policies**, with explicit authorization by entry point
- **Deterministic error contracts** (stable exit codes), not stack traces to scrape
- **Configuration as files**, not clicks — so it can be versioned, diffed, and shared with agents

Quarry inverts the traditional design: it is a **query kernel with an agent-safe contract first**, and the human faces (CLI, GUI) are thin shells grown from the same kernel. Whether a query comes from a person in the browser, a script in CI, or Claude running a skill, it uses shared query policies. CLI formats render rows; the GUI, MCP and Python API expose a structured QueryResult. See the [interface and support contract](COMPATIBILITY.md) for the exact boundaries.

## Philosophy

1. **One core, many faces.** Connection management, query execution, schema introspection, and safety rails live in an importable kernel (`quarry.core`). The CLI (`qy`), the GUI, the MCP server, and agent skills are thin shells. Fix a bug once, every face gets it.

2. **Read-only by default; escalation is explicit and graduated.** CLI writes require `--write`; prod additionally needs confirmation or `--yes`. MCP requires server and per-call authorization, plus `confirm_prod` for prod. GUI queries are read-only; Python callers obtain authorization before passing `allow_write=True`. Read queries without an outer LIMIT default to a 500-row cap; `--max-rows 0` explicitly disables it. PostgreSQL/MySQL query execution also uses database read-only transactions unless writes are authorized; SQLite opens existing files in read-only mode.

3. **A contract machines can trust.** GUI/MCP/Python queries return `{columns, rows, rowCount, truncated, elapsedMs, engine, sql, downloadBytes, sizeIsEstimated}`. CLI JSON remains an array of rows; diagnostics and truncation notices go to stderr. Exit codes are stable API: `0` ok, `2` connection error, `3` SQL error, `8` safety block. CLI argument syntax errors also use `2`; other commands have their own codes (for example `ping` returns `1` on failure). GUI/MCP/Python report structured errors.

4. **Workspace as code.** A workspace is just a directory: `connections.toml` + `queries/**/*.sql` (named queries with `-- @meta` headers). Share query files and credential-free templates through *your* repo; keep actual connection credentials local.

5. **Nearly zero dependencies.** The base package and GUI use Python 3.11+ stdlib, including `sqlite3` for SQLite. PostgreSQL uses system `psql`, Redis needs `redis-cli` 6+, SSH uses system `ssh`, and MySQL uses the optional `quarry-db[mysql]` dependencies. No Electron or cloud service; the optional `qy up` keeper runs in the background.

## Install

```bash
pipx install quarry-db        # or: pip install quarry-db
qy --help
```

SQLite uses Python's built-in `sqlite3`, with no server, Docker container or `sqlite3` CLI required. PostgreSQL uses the system `psql` binary; MySQL needs `pip install "quarry-db[mysql]"`.

## Quickstart

```bash
mkdir my-workspace && cd my-workspace
cat > connections.toml <<'EOF'
[shop]
url    = "postgresql://user:pass@localhost:5432/shop"
engine = "postgres"
env    = "dev"
EOF

qy connections                       # list connections
qy exec shop --sql "select * from customers"
qy schema shop customers             # table structure (\d+)
qy gui                               # browser data grid
```

### SQLite files

Point a connection at an **existing** SQLite file on the machine running `qy`:

```toml
[notes_local]
engine = "sqlite"
url = "sqlite:///data/notes-local.db"
db = "notes"
env = "local"

[notes_prod]
engine = "sqlite"
url = "sqlite:////srv/notes/notes.db"
db = "notes"
env = "prod"
production = true
```

`sqlite:///data/notes-local.db` resolves relative to the workspace containing
that connection; `sqlite:////srv/notes/notes.db` is an absolute path. Plain file
paths are also accepted when `engine = "sqlite"` is explicit. A missing file is
an error, including for authorized writes: Quarry never creates an empty
database because a connection path was mistyped.

```bash
qy connections test notes --env local
qy exec notes --env local --sql "SELECT * FROM notes ORDER BY id"
qy schema notes notes --env local
qy gui
```

The CLI, GUI, MCP and Python API share table/view browsing, schema inspection,
parameters, query limits and exports. The GUI supports paging and SQLite's
`EXPLAIN QUERY PLAN`. Queries default to read-only; writes use the same explicit
authorization as other SQL engines, and the GUI stays read-only.

`local`, `dev` and `prod` are labels; separate files provide data isolation.
Two environments pointing at one file access the same data. Set
`production = true` explicitly for production protection and Manual mode by
default. SQLite does not use `qy local up`, Docker or SSH tunnels; remote files,
`:memory:` databases, SQLite URI options and attached databases are outside
this first implementation. If your application uses PostgreSQL in production,
keep PostgreSQL locally too for matching SQL and data types.

## Workspace

A workspace directory is the source of connections + queries:

```
my-workspace/
├── connections.toml      # [key] url / engine / env / group / notes
└── queries/<db>/*.sql    # named queries (with -- @meta headers)
```

Resolution order: `--workspace PATH` → `~/.config/quarry/config.toml` → current directory.

See [COMPATIBILITY.md](COMPATIBILITY.md) for lossless number/string representations, write support by entry point, stable interfaces and tested environment boundaries.

## CLI reference

| Command | Purpose |
|---------|---------|
| `qy connections [list\|add\|set\|remove\|test]` | Manage connections |
| `qy ping <db>\|--all [--timeout N] [--format text\|json]` | Reachability probe (ok/fail + latency; exit 1 if any fail) |
| `qy exec <db> --sql "..." [--format json\|ndjson\|csv\|table] [--timeout N]` | Run ad-hoc SQL |
| `qy speedtest <db> [--env dev] [--bytes N] [--runs N]` | Benchmark the current PostgreSQL/MySQL tunnel path |
| `qy schema <db> <table>` | Live table structure |
| `qy run <name> [k=v ...]` | Run a saved named query |
| `qy save <name> --db X --sql "..."` | Save a named query |
| `qy list / describe / validate / fingerprint / audit` | Manage named queries |
| `qy workspace list/add/remove` | Manage aggregated workspaces |
| `qy up/down/status [--format text\|json]` | Workspace tunnel keep-alive keeper |
| `qy local up/down/status/sync [--engine postgres\|redis\|neptune\|all]` | Local dev services (see below) |
| `qy gui` | Launch the local GUI |
| `qy mcp [--write]` | Serve the MCP face over stdio (for AI agents) |

## MCP (the agent-native face)

`qy mcp` speaks the Model Context Protocol over stdio — pure stdlib, no SDK dependency. Agents get six tools (`list_connections`, `list_tables`, `describe_table`, `exec_sql`, `list_saved_queries`, `run_saved_query`) with the exact same kernel rails: read-only unless the server was started with `--write` *and* the call passes `write: true`; a prod env additionally requires `confirm_prod: true`.

```bash
# Claude Code
claude mcp add quarry -- qy mcp --workspace ~/my-workspace
```

```json
{ "mcpServers": { "quarry": { "command": "qy", "args": ["mcp", "--workspace", "/path/to/workspace"] } } }
```

Published in the [MCP Registry](https://registry.modelcontextprotocol.io/v0.1/servers/io.github.yiminspace%2Fquarry/versions/latest)
as `io.github.yiminspace/quarry`.
The former personal-namespace listing still needs administrator-assisted migration
or deprecation; use the organization entry above. Direct `qy mcp` configuration is unchanged.

<!-- mcp-name: io.github.yiminspace/quarry -->

## Safety rails (the AI-native moat)

- **CLI read-only default**: writes/DDL blocked with exit code `8`; `--write` to allow
- **Automatic row cap**: read queries without an outer LIMIT default to 500 rows; raise with `--max-rows N`, disable with `--max-rows 0` (utility/locking queries are not rewritten; Redis caps after receipt)
- **CLI prod protection**: all envs default read-only → dev needs `--write` → prod needs `--write` *plus* an interactive confirmation (`--yes` for automation)
- **Query exit codes**: `0` success (rows optional), `1` usage, `2` connection or CLI argument syntax, `3` execution, `8` safety block; other commands have their own documented codes

## Timeouts

Query execution and connection establishment (including SSH tunnel setup) are capped independently, so an unreachable host fails fast instead of eating the whole query budget:

- **Connect timeout**: 15s, fixed — bounds tunnel/dial only.
- **Execute timeout**: 300s default for the CLI/GUI, 120s for MCP (agents should converge faster).

The effective execute timeout is resolved in priority order:

1. `--timeout N` (CLI, on `qy exec`/`qy run`)
2. `QUARRY_TIMEOUT` env var
3. the connection's `timeout` field in `connections.toml` (set via `qy connections add/set --timeout N`)
4. the default above

```toml
[shop_prod]
url     = "postgresql://