How do you build a text-to-SQL feature that lets users ask questions of a database safely and accurately?

Give the model the schema and business definitions it needs, have it write a query, and then never trust the query: run it with a read-only role that can see only allowed tables and rows, with timeouts and row limits, after validating it in code. Show users the query or a plain explanation of it, and measure accuracy on a set of real questions with known answers.

Why it is hard

• Accuracy: the model must know which tables, joins and definitions to use. "Revenue" might mean gross or net, with or without refunds. Wrong but plausible numbers are worse than an error.
• Security: model-written SQL is untrusted output (OWASP LLM05). A user, or text injected into data the model reads, could ask for other tables, other customers' rows, or a DROP TABLE.
• Cost: a careless query can scan a whole warehouse.

Accuracy

1. Curated schema context: only the tables and columns needed, with descriptions, units, and how tables join.
2. A semantic layer: define metrics (revenue, active users) once, and have the model use them rather than re-deriving them each time.
3. Examples: pairs of real questions and correct SQL, chosen by similarity to the question.
4. Self-check: run the query, look at the result's shape (empty? suspicious totals?), and let the model fix errors once.
5. Clarify: if a question is ambiguous ("last quarter": calendar or fiscal?), ask.

Safety

• Read-only database role with access to approved schemas or views only. No write permission at all.
• Row-level security so each user sees only their own organisation's data, enforced by the database, not the prompt.
• Validate before running: parse the SQL, allow only SELECT, check tables against an allow-list, add a LIMIT.
• Timeouts and cost limits per query.
• Log questions, queries and results for audit.

An alternative design

For narrow use cases, skip free-form SQL: let the model fill parameters (metric, filters, date range) through structured output, and run queries your team wrote. Less flexible, much safer and easier to test.

Evaluate

A set of real questions with correct answers. Compare results, not SQL text, since many different queries are correct. Track accuracy per question type (joins, time ranges, ratios), and keep the questions that failed.

Show your work

Display the query or a plain summary ("Sum of net revenue from orders in Q3, excluding refunds"), so users can spot a wrong interpretation.

A good summary: "The model drafts, the database enforces: read-only, row-level security, limits, and validation before anything runs."