Metadata-Version: 2.4
Name: askql
Version: 0.12.0
Summary: Safe natural-language-to-SQL: validated, read-only queries across many SQL engines.
Author: askql maintainers
License: MIT
Project-URL: Homepage, https://github.com/TestAutomationArchitect/askql
Project-URL: Source, https://github.com/TestAutomationArchitect/askql
Project-URL: Changelog, https://github.com/TestAutomationArchitect/askql/blob/main/CHANGELOG.md
Keywords: sql,nl2sql,text-to-sql,llm,guardrails,read-only,database
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Classifier: Typing :: Typed
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlglot>=23.0
Requires-Dist: pyyaml>=6.0
Provides-Extra: postgres
Requires-Dist: psycopg[binary]>=3.1; extra == "postgres"
Provides-Extra: redshift
Requires-Dist: psycopg2-binary>=2.9; extra == "redshift"
Provides-Extra: oracle
Requires-Dist: python-oracledb>=2.0; extra == "oracle"
Provides-Extra: mssql
Requires-Dist: pymssql>=2.2; extra == "mssql"
Provides-Extra: tsql
Requires-Dist: pymssql>=2.2; extra == "tsql"
Provides-Extra: jdbc
Requires-Dist: JayDeBeApi>=1.2; extra == "jdbc"
Requires-Dist: JPype1>=1.5; extra == "jdbc"
Provides-Extra: llm
Requires-Dist: anthropic>=0.40; extra == "llm"
Provides-Extra: llm-aws
Requires-Dist: anthropic[bedrock]>=0.40; extra == "llm-aws"
Provides-Extra: llm-vertex
Requires-Dist: anthropic[vertex]>=0.40; extra == "llm-vertex"
Provides-Extra: llm-openai
Requires-Dist: openai>=1.40; extra == "llm-openai"
Provides-Extra: embeddings
Requires-Dist: sentence-transformers>=2.2; extra == "embeddings"
Provides-Extra: api
Requires-Dist: fastapi>=0.110; extra == "api"
Requires-Dist: uvicorn[standard]>=0.27; extra == "api"
Provides-Extra: mcp
Requires-Dist: mcp>=1.2; extra == "mcp"
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == "dev"
Requires-Dist: ruff>=0.5; extra == "dev"
Requires-Dist: mypy>=1.10; extra == "dev"
Requires-Dist: httpx>=0.27; extra == "dev"
Dynamic: license-file

# 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](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](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

```bash
# 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:

```bash
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:

```bash
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.

```bash
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`) |

```bash
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).

```bash
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.

```python
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](SHIPPING.md).

## Repository map

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