Plain-English questions to safe SQL, with two independent layers between the model and the data
Ask a database a question in plain English and get safe SQL, the right chart and a live dashboard
Summary
- Problem
- Teams have the numbers they need in a database, but getting them out means waiting for someone who writes SQL. Letting a model write SQL directly risks queries that change data or read what they shouldn't.
- My role
- Sole developer
- Team
- Personal project
- Status
- Shipped
- Timeline
- September 2026
- Stack
- Python, FastAPI, Gemini, sqlglot, SQLite, React, TypeScript, Tailwind CSS
- Next
- Measure accuracy on a held-out question set.
What shipped
- Built a plain-English-to-SQL service that runs only read-only SELECTs on the chosen dataset, enforced by a sqlglot AST guard and a separate read-only SQLite authorizer.
- Built charts and live dashboards from each answer's result shape, with a TTL cache that skips the model call for repeated questions.
No measured results yet. This page shows the problem, the plan and the design.
On this page
Context and constraints
The goal is to let anyone type a question such as “revenue by month for the last 12 months” and get back SQL that is proven read-only before it runs, real data from a read-only connection with a time budget and a row cap, a chart chosen from the result’s shape, and plain-English highlights computed from the numbers rather than written by the model. People can also upload their own CSV files and query them the same way.
Architecture
The FastAPI service profiles the selected dataset’s schema and sends it with the question to Gemini, which returns a typed answer. The SQL guard parses the query with sqlglot and allows only a single SELECT, WITH or set operation on the dataset’s own tables, rejecting writes, PRAGMA, ATTACH, dangerous functions and schema-qualified names anywhere in the tree. The executor runs it on a read-only connection with PRAGMA query_only and an authorizer that permits only reads. A TTL cache with in-flight deduplication removes the model call for repeated questions, and a chart engine picks a line, bar, area or pie chart or a KPI card from the result’s shape.
Request flows
- A question becomes a chart: React app → FastAPI query service → Gemini (JSON schema output)
- Generated SQL is checked before it runs: FastAPI query service → SQL guard (sqlglot AST) → Read-only executor (authorizer, time budget) → Dataset SQLite files
Engineering decisions
How should the model return its answer?
- Options
- Free text parsed with regexes
- A JSON schema response
- Chose
- A JSON schema response of {answerable, sql, explanation}
- Trade-off
- The output is tied to one provider's structured-output support.
- Evidence
- The model can say a question is unanswerable instead of producing a guess, and there is no free text to parse.
How is generated SQL kept from changing or leaking data?
- Options
- Trust the prompt
- A static check alone
- A static AST guard and a runtime authorizer
- Chose
- Two independent layers, a sqlglot AST allow-list and the SQLite authorizer on a read-only connection
- Trade-off
- Some valid analytical SQL is refused, such as table-valued functions and schema-qualified names.
- Evidence
- The static guard can be wrong without data being exposed, because the authorizer separately allows only reads of the dataset's own tables.
What stops a runaway query?
- Options
- A database timeout
- A progress-handler time budget and a row cap
- Chose
- A progress-handler time budget (3 s by default) and a row cap with a truncated flag
- Trade-off
- Long legitimate queries are cut off.
- Evidence
- Recursive CTEs and other runaway queries are interrupted inside SQLite rather than holding a worker.
Evaluation
SQL accuracy has not been measured yet; an evaluation harness that scores it on a benchmark set of questions is the next step in the repository’s roadmap. Until it exists, this page makes no accuracy claim. The safety layers are tested directly: the backend tests include attack cases against the SQL guard and the executor sandbox.
Deployment and operations
The test suite covers the backend with pytest (including the guard’s attack cases), the front end with Vitest and every view in a real browser with Playwright. The API adds a per-IP sliding-window rate limit, upload size and shape caps, security headers and request IDs, and never leaks internal errors. The client ID scopes data but is not authentication; production use needs an identity provider in front. A live demo is public.
Results
What’s done: the full question-to-chart path, the two safety layers, CSV uploads, pinned dashboards that re-run on live data, share links and exports. What isn’t: any measurement of how often the generated SQL is correct.
Code highlight
The static guard on generated SQL
The query is checked as a syntax tree, not as text, so a write hidden inside a CTE or a subquery is found wherever it sits.
def check(sql: str, allowed_tables: set[str]) -> CheckedSQL:
sql = strip_fences(sql)
if not sql:
raise UnsafeSQLError("The generated query is empty.")
try:
statements = [s for s in sqlglot.parse(sql, read="sqlite") if s is not None]
except ParseError as exc:
raise UnsafeSQLError(f"The generated SQL could not be parsed: {_first_line(exc)}") from exc
if len(statements) != 1:
raise UnsafeSQLError("Only a single statement is allowed.")
tree = statements[0]
if not isinstance(tree, exp.Query):
raise UnsafeSQLError("Only read-only SELECT queries are allowed.")
if any(isinstance(node, WRITE_NODES) for node in tree.walk()):
raise UnsafeSQLError("Only read-only SELECT queries are allowed.")
for fn in tree.find_all(exp.Anonymous, exp.Func):
name = (fn.name if isinstance(fn, exp.Anonymous) else fn.sql_name()).lower()
if name in BLOCKED_FUNCTIONS:
raise UnsafeSQLError(f"The function {name}() is not allowed.")What I'd do next
Score SQL accuracy on a benchmark question set, then add read-only Postgres and MySQL connectors, which are next on the roadmap.
Talk about this project
I'm happy to walk through any decision here in more depth.