{
  "markdown": "# QueryPilot\n\n<!-- mcp-name: io.github.nickklos10/querypilot -->\n\n[![PyPI](https://img.shields.io/pypi/v/querypilot.svg)](https://pypi.org/project/querypilot/)\n[![Python](https://img.shields.io/pypi/pyversions/querypilot.svg)](https://pypi.org/project/querypilot/)\n[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)\n[![CI](https://github.com/nickklos10/QueryPilot/actions/workflows/eval.yml/badge.svg)](https://github.com/nickklos10/QueryPilot/actions/workflows/eval.yml)\n\nEval-driven SQL reliability for AI agents.\n\nQueryPilot helps agents safely generate, validate, repair, execute, and regression-test SQL against real fixture databases.\n\n<p align=\"center\"><img src=\"https://raw.githubusercontent.com/nickklos10/QueryPilot/main/docs/assets/eval-report.svg\" alt=\"querypilot eval run terminal report\" width=\"840\"></p>\n\n## Why QueryPilot Exists\n\nRead-only SQL access for agents is becoming a commodity. Tools that let an agent list tables, read schemas, and run validated `SELECT`s already exist. What is much harder — and what QueryPilot focuses on — is making that access *measurably reliable*: proving the SQL the agent generates is correct, safe, fast, and not regressing.\n\nEvery change to QueryPilot, your prompts, or your model can be measured against an execution-truth eval suite. Suites can be authored by hand or auto-generated by replaying your audit log as a regression set, so the same queries that worked in production yesterday have to keep working tomorrow.\n\n## Quick Demo\n\n```bash\npython3 -m venv .venv\n.venv/bin/pip install -e \".[dev,eval]\"\n.venv/bin/querypilot eval init           # scaffold suites/ and .eval/\n.venv/bin/querypilot eval run \\\n    --suite suites/smoke.yaml \\\n    --generator demo \\\n    --report eval-out.json\n.venv/bin/querypilot eval check \\\n    --report eval-out.json \\\n    --baseline .eval/baseline.json \\\n    --threshold 0.9 \\\n    --require-safety 1.0\n```\n\nSample output (abridged — see the full report at the top of this README):\n\n```text\nQueryPilot Eval Report\nSuite:     smoke\nGenerator: demo\n\nOverall\n  ✅  Pass rate                       3 / 3 (100%)\n  ✅  Safety pass rate                0 / 0 (100%)\n  ✅  Correctness                     3 / 3 (100%)\n  ✅  P95 latency                     18 ms\n\n✅ No threshold violations.\n```\n\nThe bundled `suites/smoke.yaml` runs against a tiny SQLite fixture (`tests/fixtures/demo.db`) so the harness works end-to-end without an LLM key. To benchmark a real generator, use `--generator openai` or `--generator anthropic`.\n\n## Audit-Log Replay\n\n`querypilot eval replay` turns a JSONL audit log written by `JSONLAuditSink` into a `BenchmarkSuite` whose gold SQL is the SQL that previously executed. Re-running that suite gates accuracy regressions against your own production traffic — the unique-to-QueryPilot capability the eval positioning rests on.\n\n```bash\nquerypilot eval replay \\\n    --audit-jsonl audit.jsonl \\\n    --fixture-db sqlite:///tests/fixtures/demo.db \\\n    --output suites/replay.yaml\nquerypilot eval run --suite suites/replay.yaml --generator demo --report replay-out.json\n```\n\nConservative defaults: only successful `ask` records, non-empty results, no active access policy. `--include-failures`, `--include-masked`, `--include-empty` relax each filter.\n\n## CI Gate\n\n`querypilot eval check` compares a `SuiteReport` JSON against thresholds and a committed baseline, exiting non-zero on regression. A sample GitHub Actions workflow ships at `.github/workflows/eval.yml`:\n\n```yaml\n- run: querypilot eval run --suite suites/smoke.yaml --generator demo --report eval-out.json\n- run: querypilot eval check --report eval-out.json --baseline .eval/baseline.json --threshold 0.9 --require-safety 1.0\n```\n\nWhen a regression is detected the output explains which cases regressed and how:\n\n```text\nRegression detected.\n\nPass rate:\n  baseline: 96%\n  current:  89%\n\nFailed cases (regression vs. baseline):\n  - monthly_revenue_by_segment (was passing -> now result_mismatch)\n  - top_customers_by_arr      (was passing -> now repair_failed)\n\nLatency:\n  baseline p95: 2100 ms\n  current p95:  3800 ms  (+1700 ms)\n```\n\nRefresh the baseline on `main` after a deliberate change:\n\n```bash\nquerypilot eval run --suite suites/smoke.yaml --generator demo --report .eval/baseline.json\ngit commit -am \"Refresh eval baseline\"\n```\n\n## Authoring a Suite\n\nSuites are YAML or JSON. Each case carries a question, a gold SQL, and the schema/safety expectations for the candidate.\n\n```yaml\nname: saas_revenue_suite\nfixture_db: sqlite:///fixtures/demo.db\nfixture_dialect: sqlite\n\nthresholds:\n  pass_rate: 0.95\n  safety_pass_rate: 1.0\n  correctness_rate: 0.9\n  max_p95_latency_ms: 5000\n  max_avg_cost_usd: 0.01\n\ncomparison:\n  ignore_row_order: true\n  ignore_column_order: true\n  float_tolerance: 0.001\n  normalize_datetimes: true\n\ncases:\n  - id: top_customers_by_revenue\n    question: \"Top customers by revenue\"\n    gold_sql: |\n      SELECT customer_name, revenue\n      FROM customers\n      ORDER BY revenue DESC\n      LIMIT 100\n    expected_tables: [customers]\n    must_include: [\"ORDER BY\", \"LIMIT\"]\n    must_not_contain: [DELETE, UPDATE, DROP]\n    tags: [revenue, ranking]\n\n  - id: blocks_drop_table\n    sql: \"DROP TABLE customers\"\n    should_pass: false\n    expected_failure_kind: validation\n    expected_error_contains: [\"Only SELECT queries are allowed\"]\n    tags: [safety, ddl]\n```\n\nResult-set correctness is scored by **executing both the gold and candidate SQL** against the same fixture database and comparing rows. Order-insensitive by default; auto-flipped to order-sensitive when the gold SQL has a top-level `ORDER BY`.\n\n## Library Usage\n\n```python\nfrom querypilot import QueryPilot\n\nqp = QueryPilot.connect(\n    database_url=\"sqlite:///demo.db\",\n    dialect=\"sqlite\",\n    readonly=True,\n    max_rows=100,\n)\n\nresult = qp.execute_sql(\"SELECT * FROM customers\")\n\nprint(result.sql)\nprint(result.rows)\n```\n\nNatural-language `ask()` works offline for simple demo questions through a deterministic generator:\n\n```python\nanswer = qp.ask(\"Top customers by revenue\")\n\nprint(answer.sql)\nprint(answer.rows)\nprint(answer.validation.risk_level)\n```\n\n## Examples\n\nRunnable, self-contained examples live in [`examples/`](examples/). They all use\nthe bundled demo SQLite fixture, so most need no API key:\n\n| Example | Shows | Key? |\n| --- | --- | --- |\n| [`01_quickstart.py`](examples/01_quickstart.py) | connect, `execute_sql`, offline `ask()`, validation risk level | No |\n| [`02_openai_tool_use.py`](examples/02_openai_tool_use.py) | `as_openai_tools()` in an OpenAI tool-use loop | `OPENAI_API_KEY` |\n| [`03_anthropic_tool_use.py`](examples/03_anthropic_tool_use.py) | `as_anthropic_tools()` in an Anthropic tool-use loop | `ANTHROPIC_API_KEY` |\n| [`04_access_control.py`](examples/04_access_control.py) | blocked columns, row filter, and masking | No |\n| [`05_custom_eval_suite/`](examples/05_custom_eval_suite/) | a custom YAML suite run with `querypilot eval run`/`check` | No |\n| [`06_mcp/`](examples/06_mcp/) | run `querypilot mcp` + a paste-ready Claude MCP config | No |\n\nSee [`examples/README.md`](examples/README.md) for setup and the full index.\n\n## LLM SQL Generation\n\nFor production-style natural-language SQL generation, plug in an LLM generator. QueryPilot still treats model output as an untrusted candidate: it validates, rewrites, and can ask the generator for a repair before execution.\n\nInstall optional provider dependencies:\n\n```bash\n.venv/bin/pip install -e \".[openai]\"\n.venv/bin/pip install -e \".[anthropic]\"\n```\n\nOpenAI:\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.generation import OpenAISQLGenerator\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    generator=OpenAISQLGenerator(model=\"gpt-5.1\"),\n    max_generation_attempts=2,\n)\n```\n\nAnthropic:\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.generation import AnthropicSQLGenerator\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    generator=AnthropicSQLGenerator(model=\"claude-sonnet-4-20250514\"),\n    max_generation_attempts=2,\n)\n```\n\n### Local / open models\n\nAny OpenAI-compatible endpoint — [Ollama](https://ollama.com), vLLM, LM Studio,\nor llama.cpp's server — works through `OpenAICompatibleSQLGenerator`. It reuses\nthe `[openai]` extra (no extra dependency) and talks the Chat Completions API, so\nyou can benchmark open models at **$0**. The API key is optional (local servers\nignore it), and cost reports show `$0` while token counts still flow through when\nthe server returns usage.\n\n```bash\nollama pull llama3.1\n.venv/bin/pip install -e \".[openai]\"\n```\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.generation import OpenAICompatibleSQLGenerator\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    generator=OpenAICompatibleSQLGenerator(\n        model=\"llama3.1\",\n        base_url=\"http://localhost:11434/v1\",  # Ollama's default; omit to use it\n    ),\n    max_generation_attempts=2,\n)\n```\n\nFrom the eval harness, add open models to the benchmark matrix with\n`--generator openai-compatible`:\n\n```bash\nquerypilot eval run \\\n    --suite suites/smoke.yaml \\\n    --generator openai-compatible \\\n    --model llama3.1 \\\n    --base-url http://localhost:11434/v1 \\\n    --report eval-out.json\n```\n\n`--base-url` also reads `$QUERYPILOT_BASE_URL`, and defaults to Ollama's\n`http://localhost:11434/v1` when unset.\n\nThe safety loop is always:\n\n```text\nquestion\n  -> schema-scoped prompt\n  -> model candidate SQL\n  -> QueryPilot validation\n  -> optional repair\n  -> safe execution\n```\n\n## Eval Harness (Library)\n\nThe CLI is a thin wrapper around `run_suite`, which is also usable directly:\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.evals import (\n    BenchmarkCase,\n    BenchmarkSuite,\n    NullCostTracker,\n    build_qp_factory,\n    render_terminal,\n    run_suite,\n)\nfrom querypilot.generation.sql_generator import DemoSQLGenerator\n\nsuite = BenchmarkSuite(\n    name=\"adhoc\",\n    fixture_db=\"sqlite:///tests/fixtures/demo.db\",\n    cases=[\n        BenchmarkCase(\n            id=\"count_customers\",\n            question=\"Count of customers\",\n            gold_sql=\"SELECT COUNT(*) AS count FROM customers\",\n            expected_tables=[\"customers\"],\n        ),\n    ],\n)\n\nqp_factory = build_qp_factory(\n    database_url=\"sqlite:///tests/fixtures/demo.db\",\n    generator=DemoSQLGenerator(),\n)\n\nreport = run_suite(\n    suite,\n    qp_factory=qp_factory,\n    cost_tracker_factory=NullCostTracker,\n)\n\nprint(render_terminal(report, color=False))\n```\n\nThe returned `SuiteReport` is a Pydantic model with `pass_rate`, `safety_pass_rate`, `correctness_rate`, `repair_rate`, `p50_latency_ms`, `p95_latency_ms`, `total_prompt_tokens`, `estimated_cost_usd`, `tag_rollups`, `failure_breakdown`, `threshold_violations`, and the full per-case `case_results` list.\n\n## Safety Engine\n\nQueryPilot validates SQL before execution with:\n\n- `sqlglot` parsing\n- single-statement enforcement\n- SELECT-only read-only policy\n- blocked keyword detection\n- known table checks\n- column checks where feasible\n- allowed/blocked table policy\n- automatic `LIMIT` insertion and max-row capping\n- `SELECT *` warnings or rejection\n- Cartesian join detection\n- structured policy checks\n- query fingerprints\n- risk levels: `low`, `medium`, `high`, `critical`\n\nFor PostgreSQL production use, connect QueryPilot with a dedicated\nleast-privilege role that has only the required schema `USAGE` and table\n`SELECT` grants. QueryPilot requests a read-only transaction and applies a\nstatement timeout, but application validation is not a replacement for\ndatabase permissions.\n\nExample:\n\n```python\nvalidation = qp.validate_sql(\"SELECT * FROM customers\")\n\nprint(validation.valid)\nprint(validation.risk_level)\nprint(validation.query_fingerprint)\nprint(validation.policy_checks)\n```\n\nFor stricter deployments:\n\n```python\nfrom querypilot.core.config import SafetyPolicy\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    safety_policy=SafetyPolicy(\n        allow_select_star=False,\n        reject_cartesian_joins=True,\n    ),\n)\n```\n\n## Agent Tool Adapters\n\nQueryPilot exposes tool schemas without requiring SDK dependencies:\n\n```python\nopenai_tools = qp.as_openai_tools()\nanthropic_tools = qp.as_anthropic_tools()\n```\n\nAvailable tools:\n\n- `ask_database`\n- `search_schema`\n- `validate_sql`\n- `execute_sql`\n\n## FastAPI Server\n\nRun QueryPilot as a local safe SQL gateway:\n\n```bash\n.venv/bin/pip install -e \".[server]\"\nquerypilot serve --database-url sqlite:///demo.db --dialect sqlite --max-rows 100\n```\n\nOr use environment variables:\n\n```bash\nexport QUERYPILOT_DATABASE_URL=sqlite:///demo.db\nexport QUERYPILOT_DIALECT=sqlite\nquerypilot serve\n```\n\nEndpoints:\n\n- `GET /health`\n- `GET /schema`\n- `POST /search-schema`\n- `POST /ask`\n- `POST /generate-sql`\n- `POST /validate-sql`\n- `POST /execute-sql`\n- `POST /evals/run`\n- `GET /audit/recent`\n\nExample:\n\n```bash\ncurl -X POST http://127.0.0.1:8000/validate-sql \\\n  -H \"content-type: application/json\" \\\n  -d '{\"sql\": \"SELECT * FROM customers\"}'\n```\n\n## MCP Server\n\nRun QueryPilot as an MCP-compatible tool server:\n\n```bash\n.venv/bin/pip install -e \".[mcp]\"\nquerypilot mcp --database-url sqlite:///demo.db --dialect sqlite\n```\n\nIf your MCP client launches servers with `uvx`, include the `[mcp]` extra explicitly so the MCP SDK dependency is installed:\n\n```bash\nuvx --from 'querypilot[mcp]' querypilot mcp --database-url sqlite:///demo.db --dialect sqlite\n```\n\nBy default, the MCP command uses stdio transport. For clients that support Streamable HTTP:\n\n```bash\nquerypilot mcp \\\n  --database-url sqlite:///demo.db \\\n  --dialect sqlite \\\n  --transport streamable-http\n```\n\nMCP tools:\n\n- `ask_database`\n- `search_schema`\n- `validate_sql`\n- `execute_sql`\n\n## Audit Trail\n\nQueryPilot records structured audit events for schema search, SQL generation, validation, execution, and full `ask()` flows.\n\nEach audit record can include:\n\n- `audit_id`\n- timestamp\n- operation\n- question\n- original SQL\n- rewritten SQL\n- validation metadata\n- execution status\n- row count\n- execution time\n- error\n- actor/session/application/trace metadata\n\nUse the default in-memory sink:\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.audit import AuditMetadata\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    audit_metadata=AuditMetadata(\n        actor=\"agent-1\",\n        session_id=\"session-1\",\n        app_name=\"analytics-agent\",\n    ),\n)\n\nresult = qp.execute_sql(\"SELECT customer_name FROM customers\")\n\nprint(result.audit_id)\nprint(qp.get_audit_records(limit=10))\n```\n\nOr persist JSONL audit events:\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.audit import JSONLAuditSink\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    audit_sink=JSONLAuditSink(\"querypilot-audit.jsonl\"),\n)\n```\n\n## Access Control\n\nRead-only SQL is necessary but not enough. QueryPilot can also enforce column-level and row-level access policies before execution.\n\n```python\nfrom querypilot import QueryPilot\nfrom querypilot.access import AccessPolicy, MaskingRule\n\nqp = QueryPilot.connect(\n    \"sqlite:///demo.db\",\n    access_policy=AccessPolicy(\n        blocked_columns={\n            \"customers\": [\"email\"],\n        },\n        row_filters={\n            \"customers\": \"tenant_id = 42\",\n        },\n        masking_rules={\n            \"customers\": {\n                \"email\": MaskingRule(mode=\"redact\"),\n            },\n        },\n    ),\n)\n```\n\nWhat this does:\n\n- rejects SQL that selects blocked columns\n- rejects SQL outside an allowlist when `allowed_columns` is configured\n- injects required row filters such as `tenant_id = 42`\n- masks configured result columns after execution\n- records the applied access policy in validation, result, answer, and audit metadata\n\nThe server and MCP runtimes can also receive access policy JSON:\n\n```bash\nquerypilot serve \\\n  --database-url sqlite:///demo.db \\\n  --access-policy-json '{\n    \"row_filters\": {\"customers\": \"tenant_id = 42\"},\n    \"blocked_columns\": {\"customers\": [\"ssn\"]}\n  }'\n```\n\n## Current Scope\n\nShipped:\n\n- installable Python package\n- SQLite connector\n- PostgreSQL connector structure\n- schema introspection\n- SQL validation and rewriting\n- safe read-only execution\n- offline demo SQL generation, OpenAI and Anthropic LLM generators with repair loop\n- column policies, row filters, and result masking\n- in-memory and JSONL audit logging\n- FastAPI server runtime\n- MCP tool server runtime\n- **eval-driven harness**: YAML/JSON suites, execution-truth correctness scoring, safety/repair/latency/cost metrics, per-tag rollups, failure-category breakdown, threshold violations, JSON and screenshot-quality terminal reports\n- **audit-log → regression suite** (`querypilot eval replay`)\n- **CI regression gate** (`querypilot eval check` against a committed baseline) + sample GitHub Actions workflow\n- **`querypilot eval init`** — scaffolds `suites/` and `.eval/` for new projects\n\n## Roadmap\n\nThe eval-driven foundation is shipped. Next pillars:\n\n- **Schema-aware grounded generation** — schema embeddings, retrieval, semantic verification of repaired SQL\n- **EXPLAIN-plan and cost guards** — per-query row/cost budgets, cardinality-based LIMIT policies, plan analysis\n- **Multi-tenant governance** — tenant-scoped row filters, per-actor policy injection, automatic PII detection\n- **Cross-dialect transpilation** — write a suite once, run it against SQLite, Postgres, MySQL\n- **Multi-database connectors** — Snowflake, BigQuery, Redshift\n",
  "bytes": 17404,
  "sha": "08f13abedb2f55fa211271af92273e7943eb86baeb82420709ea5a85bb3f65e8",
  "repo_slug": "nickklos10/querypilot",
  "fonte": "repo",
  "truncated": false,
  "api": "https://agentalog.com/api/listings/mcp_io_github_nickklos10_querypilot_a30d30b4/readme"
}