askql 0.20.0

Safe natural-language-to-SQL: validated, read-only queries across many SQL engines.

askql — Safe Natural-Language → SQL

Translate plain-English questions into validated, read-only SELECT queries and run them against a relational database. Designed for QA / dev environments only, with defense-in-depth guardrails so an AI agent can never modify data or leak PII.

⚠️ Non-production by design. This system executes only read-only SELECT statements through a least-privilege DB user. Do not point it at a production database without the Phase-3 approval workflow (see ARCHITECTURE.md).

---

How it works

question ─▶ compress (pick ~10 relevant tables) ─▶ LLM writes SQL
        ─▶ validate (AST guardrails)  ─▶ execute (read-only, rollback, timeout)
        ─▶ markdown/CSV + audit log

Five defense layers: AST validation → read-only DB user → connection hygiene → output caps → audit trail. See ARCHITECTURE.md for the full design and the security hardening applied on top of the source playbook.

Credentials: use whatever your org gives you — the tool runs with any user. A read-only user is a recommended nice-to-have (defense in depth), not a requirement; if the connected user can write, you get a one-line advisory, never a block.

---

Quick start

# 1. Install (Python 3.11+). Pick your driver extra: [postgres] / [oracle] / [mssql] / [jdbc]
pip install "askql[postgres]"               # in this repo: pip install -e ".[postgres,dev]"

# 2. Scaffold a project in an empty dir — no T2S_HOME needed; askql auto-discovers it from here
cd my-askql-project
askql init                       # creates config/, docs/, .env + a .askql marker
#   edit .env                    # DB_USER / DB_PASSWORD / DB_CONNECTION (host:port/service)
#   edit config/databases.yaml + config/settings.yaml   # dialect + schemas
askql doctor                     # verify connectivity

# 3. Build the metadata pipeline (one command)
askql scrape --schemas public --build-graph     # or: python scripts/scrape_schema.py …

# 4. Ask a question (the orchestrator runs compress→validate→execute)
askql ask "show me the 10 newest active accounts"

# …or drive the steps manually:
python scripts/compress_metadata.py --question "active accounts" --graph docs/schema-graph.json
python scripts/validate_sql.py build/query.sql --max-rows 100
python scripts/execute_sql.py build/query.sql --limit 50

No database handy? Everything except execute runs fully offline, and the executor has an offline render mode (--rows-json) used by the test suite.

Verify your setup any time with the built-in health check:

python scripts/doctor.py        # config, credentials, transport, live connectivity, graph

---

Run in Docker (zero local install)

The image bundles Python and a JRE, so the universal JDBC transport works without installing Java or native drivers on your machine. Mount your config, .env, and vendor JDBC jars at runtime:

docker build -t askql .

# health check
docker run --rm --env-file .env \
  -v "$PWD/config:/app/config" -v "$PWD/jdbc-drivers:/app/jdbc-drivers" askql doctor

# or via compose
docker compose run --rm askql doctor
docker compose run --rm askql execute build/query.sql --limit 50

Vendor JDBC jars aren't baked into the image (licensing) — drop them in jdbc-drivers/ and they're mounted in. This is the "usable by all" path: one image, any database engine/version.

---

REST API (connectivity-as-a-service)

Run askql as a service so clients (a web UI, a Slack bot, scripts, other teams) hold no drivers, JVM, or credentials — they just call HTTP. The service applies the same validator, RBAC, read-only execution, and audit.

pip install -e ".[api,postgres,jdbc]"
export T2S_API_KEYS="devkey:alice@corp"       # identity drives RBAC; omit + ALLOW_OPEN for dev
askql-api                                         # or: uvicorn askql.api:app --port 8000
#   or containerized:  docker compose up api

Method & path	Auth	Purpose
GET /health	public	liveness
GET /api/v1/databases	public	registry names (no creds/conn strings)
POST /api/v1/validate	required	{sql, database?} → guardrail check
POST /api/v1/compress	required	{question} → focused schema slice
POST /api/v1/query	required	{sql, database?, max_rows?, format?} → execute read-only
POST /api/v1/ask	required	{question, database?} → compress→LLM→validate→retry→execute (needs ANTHROPIC_API_KEY)

curl -s -X POST localhost:8000/api/v1/query -H "X-API-Key: devkey" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT first_name, salary FROM HR.EMPLOYEES WHERE ROWNUM <= 3","database":"oracle-prod"}'
# → {"ok":true,"columns":["FIRST_NAME","SALARY"],"rows":[["Steven",24000], ...],"latency_ms":104}

Auth: T2S_API_KEYS="key1:alice@corp,key2:bob@corp" maps a key → identity (→ role in config/access-control.yaml). Writing endpoints refuse to run unless keys are set (or T2S_API_ALLOW_OPEN=true for local dev). A non-SELECT returns 400 before any DB work.

Choosing the model (provider-agnostic / BYOM)

/ask (and askql ask) generates SQL through a pluggable provider — you bring your own model. The safety pipeline is identical regardless of provider; only T2S_LLM_PROVIDER changes. With nothing configured, generation falls back to the BYO-LLM / IDE lane (your Copilot / Claude Code agent does it — no backend model, no key).

T2S_LLM_PROVIDER	What it uses	Config
anthropic (auto)	Anthropic API	ANTHROPIC_API_KEY · pip install '.[llm]'
bedrock	Claude on AWS	AWS creds/role · '.[llm-aws]' · T2S_LLM_MODEL=anthropic.claude-…
vertex	Claude on GCP	GCP creds · '.[llm-vertex]'
openai	OpenAI GPT	OPENAI_API_KEY · '.[llm-openai]'
azure-openai	Azure OpenAI	AZURE_OPENAI_API_KEY/_ENDPOINT/OPENAI_API_VERSION · '.[llm-openai]'
openai-compatible	any self-hosted / OSS (vLLM, Ollama, LM Studio, OpenRouter, GitHub Models)	T2S_LLM_BASE_URL=… + T2S_LLM_MODEL=… · '.[llm-openai]'
custom	your own gateway	T2S_LLM_FACTORY=module:callable
unset / none	BYO-LLM — IDE agent (Copilot/Claude Code)	nothing

Copilot note: Copilot has no server-side completions API, so it can't power the /ask service — but it is the model in the IDE lane (the agent runs the scripts/skill with its own model). For a token-based API in the GitHub/MS ecosystem, use Azure OpenAI or "GitHub Models" via the openai-compatible provider.

---

MCP server (safe DB tools for AI agents)

Expose the schema + safe execution to an MCP client (Claude Desktop, Cursor, …) so an agent can explore the database and run read-only, validated, audited SQL — using its own model, no LLM key in askql. Tools: list_tables, describe_table, compress_schema, validate_sql, query, check_freshness, and ask (end-to-end NL→SQL→result, when a server-side LLM is configured).

pip install "askql[mcp]"          # optional extra — a plain `pip install askql` never pulls it
askql-mcp                          # stdio server; point your MCP client's command at this

It's bound to one database + identity at startup (T2S_DATABASE / T2S_MCP_USER) and runs every query through the same validator + RBAC + read-only executor + audit as the CLI/API. Because the mcp SDK is an opt-in extra imported lazily, orgs that disallow MCP can ignore it entirely — it has zero effect on the base install or import askql.

Tip — value hints: build the graph with askql scrape --build-graph --sample-values to profile coded/enum columns (e.g. STATUS ∈ {'A','C','X'}). Those hints flow into describe_table / compress_schema, so the agent writes correct SQL on coded columns first try.

---

Use as a library

askql is a clean, typed, importable package (py.typed) — build it into your own app. The public API is everything in askql.__all__; heavy/optional deps (DB drivers, LLM SDKs, FastAPI) load lazily, so import askql is light.

from askql import validate, compress, execute_sql_text, ask, Settings, load_graph

s = Settings(dialect="postgres", max_rows=100)

# 1) Guardrail check (pure, no DB)
r = validate("SELECT id, name FROM app.users LIMIT 10", settings=s)
assert r.ok, r.errors

# 2) Pick relevant tables for a question
slice_ = compress(load_graph("schema-graph.json"), "active users this week", max_tables=8)

# 3) Full NL->SQL (compress -> LLM -> validate -> retry -> execute), provider via env
result = ask("how many active users this week", settings=s)   # needs an LLM provider configured

Everything is config-injectable (pass Settings / DatabaseEntry / a custom SqlGenerator) — no checked-out repo required. For pip-installed use, set T2S_HOME (config/data root) and/or T2S_DATA_DIR (writable dir for the audit log + caches) so nothing is written next to the installed package. Packaging/publishing details: SHIPPING.md.

Repository map

Path	What it is
src/askql/	The library: validator, compressor, executor, drivers, scraper
scripts/	Thin CLI wrappers (the commands the agent/humans call)
config/	settings.yaml, sensitive-columns.yaml, databases.yaml
docs/domain-rules.md	Business logic hints the LLM reads
tests/	Offline unit tests (validator / compressor / graph)
.claude/	Claude Code skill + permission settings
CLAUDE.md	Operating instructions for the AI agent
QUICKSTART.md	Install + first run in your org (≈15 min)
SHIPPING.md	How to package, ship, and install (wheel / index / Docker)
PB/	The original source playbook (reference)

---

Safety rules (non-negotiable)

• Only SELECT (incl. UNION/INTERSECT/EXCEPT). No DDL/DML, no procedural code.
• No SELECT * — explicit columns only (COUNT(*) is fine).
• A row limit is required and capped at max_rows.
• No system schemas, no sensitive columns (SSN, PASSWORD, …), no dangerous functions.
• Every execution rolls back, times out, and is audited.

These aren't just asserted — they're measured. askql eval runs an adversarial red-team corpus through the validator and exits non-zero on any escape (an attack it failed to block), so safety is a provable CI gate. askql eval --accuracy --cases f.json scores generated SQL by execution-match against gold result sets. Both are dialect-agnostic (Oracle / SQL Server / Postgres / Redshift / MySQL / Snowflake / BigQuery).

See CLAUDE.md for the agent workflow and tests/ for the enforced behavior.