AI-generated SQL can turn a natural-language request into a useful starting point, but production systems need more than a plausible-looking query. This guide presents a repeatable text-to-SQL workflow for validating, formatting, costing, testing, and safely reviewing LLM-generated queries before they reach a database.
Overview
A safe text-to-SQL implementation treats the language model as a query proposal component, not as a database administrator. The model should receive a controlled schema description, produce structured output, and pass the result through deterministic checks before execution.
The central rule is simple: never rely on the model alone to enforce permissions, prevent data modification, or estimate resource usage. Database roles, application-layer validation, query timeouts, and human review should provide the actual safety boundary.
Natural-language request
|
v
[Scope request and identify user permissions]
|
v
[Generate SQL with dialect and schema context]
|
v
[Parse, format, and lint]
|
v
[Reject unsafe statements and unknown objects]
|
v
[Estimate cost with EXPLAIN or an equivalent plan tool]
|
v
[Run read-only query with limits and timeout]
|
v
[Return results, explanation, and audit record]
This workflow separates concerns that are often mixed together. Prompt engineering helps the model understand the schema and user intent. A SQL parser checks syntax and statement types. A formatter makes the query reviewable. The database evaluates the execution plan. Authorization controls what the caller can actually access.
For related implementation work, pair this process with a prompt versioning workflow, an LLM evaluation harness, and an API reliability strategy.
How to estimate
Before enabling execution, estimate whether a generated query is valid, safe, and affordable for the intended workload. A useful review has four separate scores or decisions:
- Semantic fit: Does the query answer the user’s request, including filters, grouping, date ranges, and requested measures?
- Structural validity: Does it parse under the selected database dialect, and does it reference approved tables, columns, functions, and relationships?
- Safety: Is it read-only, scoped to the caller’s permissions, protected against excessive result sizes, and free of unexpected statements?
- Resource cost: Does its execution plan fit the application’s latency, memory, and concurrency limits?
Do not reduce all four decisions to a single confidence value. A query can be syntactically valid but semantically wrong, or semantically useful but too expensive to run. Keep the checks independent so a failure is explainable and actionable.
Database-dialect comparison
| Concern | PostgreSQL | MySQL | SQL Server | SQLite |
|---|---|---|---|---|
| Identifier quoting | Usually double quotes | Backticks are common | Brackets or double quotes, depending on settings | Double quotes are supported |
| Result limiting | LIMIT | LIMIT | TOP or OFFSET/FETCH | LIMIT |
| Date and string functions | Distinct function and casting syntax | Distinct date and JSON functions | Distinct date, conversion, and pagination syntax | Smaller built-in function set |
| Plan inspection | EXPLAIN and related options | EXPLAIN and related options | Execution-plan tools and SET SHOWPLAN patterns | EXPLAIN QUERY PLAN |
Use this table as a planning reminder, not as a complete compatibility matrix. Pass the exact dialect, server version, schema conventions, and approved SQL features to the generation step. A formatter or ORM abstraction does not automatically make SQL portable.
Inputs and assumptions
A text-to-SQL system is only as reliable as the context and controls around it. Define these inputs before writing the prompt or tool schema:
- Schema context: Include approved tables, columns, types, primary keys, foreign keys, business definitions, and known join paths. Exclude secrets and unrelated tables.
- User scope: Represent tenant, department, account, or row-level restrictions outside the model when possible. Treat model-generated filters as supplemental, not authoritative.
- Dialect: State the target engine and relevant version. Include approved pagination, date, cast, and aggregation patterns.
- Output contract: Request structured fields such as
sql,parameters,assumptions,tables_used, andneeds_clarification. - Limits: Set maximum rows, execution time, scanned data, joins, nesting depth, and concurrency according to the application’s workload.
- Ambiguity policy: Require clarification when terms such as “recent,” “active,” “revenue,” or “customer” have multiple valid definitions.
Read-only and allowlist patterns
Use a dedicated database role that cannot insert, update, delete, alter, drop, truncate, grant, or execute unapproved procedures. At the application layer, parse the returned statement and allow only the statement forms required by the feature. A conservative read-only policy might permit one SELECT statement, approved common table expressions, and approved functions while rejecting comments, multiple statements, data-definition commands, and vendor-specific administrative functions.
An allowlist should cover more than table names. Validate schemas, columns, joins, functions, sort expressions, operators, and parameter types. Bind user values as parameters rather than interpolating them into SQL. If the system cannot determine whether a generated object is approved, stop and request review.
SQL formatting and linting checklist
- Parse the query using a dialect-aware parser before formatting.
- Format keywords, aliases, joins, filters, and subqueries consistently so reviewers can inspect them.
- Reject unknown tables, columns, aliases, and functions.
- Check for missing join predicates, accidental Cartesian joins, and duplicate-producing relationships.
- Check aggregation rules, grouping columns, type conversions, and null behavior.
- Require an explicit limit for interactive result views unless the use case proves it unnecessary.
- Reject unbounded wildcard selection when sensitive or high-volume columns are present.
- Log the original request, prompt version, generated SQL, normalized SQL, validation results, and execution outcome.
Worked examples
Example 1: a valid reporting request
Request: “Show monthly order counts for the current year by region, excluding cancelled orders.”
Before execution, confirm which date defines an order month, whether region belongs to the order or customer record, and how cancellation is represented. The generated query should use the approved relationship, bind the year as a parameter, group by the same month expression it selects, and return a bounded result. Review the plan for a full scan or an unexpectedly large join before running it.
Example 2: an ambiguous request
Request: “Show our best customers recently.”
This should not execute immediately. “Best” could mean order count, revenue, margin, retention, or another measure; “recently” could mean a rolling period or a calendar interval. Return a clarification question asking for the metric, time range, and ranking limit. Clarification is safer than allowing the model to silently choose business logic.
Example 3: a permissions failure
Request: “List employee compensation by name.”
If the caller lacks access to compensation data, the request must fail before query execution. Do not depend on a prompt instruction such as “never reveal private information.” Enforce the restriction through database permissions, a column or view allowlist, row-level controls where applicable, and an application authorization check.
Reusable test templates
| Test area | Test request | Expected review |
|---|---|---|
| Join correctness | “Compare order count with customer count by month.” | Detect the approved join path and prevent duplicate multiplication. |
| Permissions | “Show records from another tenant.” | Reject or enforce the caller’s tenant scope independently of generated SQL. |
| Nulls | “Find accounts without a recorded phone number.” | Use null-aware predicates rather than equality with a null value. |
| Ambiguity | “Show recent high-value users.” | Ask for a time window, value metric, and user definition. |
| Cost | “Return every event with all fields.” | Require a narrower projection, pagination, or an explicit approval path. |
For cost review, inspect the estimated plan first, then use a controlled execution path where available. Record planning time, execution time, rows returned, rows examined or scanned when exposed by the engine, temporary work, and timeout status. Compare these values with a baseline query for the same task. A query that is acceptable in a development dataset may become unsuitable after data volume, indexes, or join relationships change.
When to recalculate
Revisit the workflow whenever its inputs change. Re-run validation and performance tests after a schema migration, index change, database-engine upgrade, driver or ORM upgrade, model change, prompt change, formatter or linter configuration change, permission change, or new tenant boundary. Also recalculate after a query begins timing out, returns unexpected duplicates, consumes more resources, or receives new types of natural-language requests.
Maintain a small regression set in source control. Include representative joins, permission boundaries, null-heavy data, ambiguous terminology, large result requests, and dialect-specific syntax. Run it on a regular schedule and whenever the supported schema changes. Keep expected outcomes focused on safety and meaning: allowed or rejected, clarification required, tables permitted, parameters present, and acceptable plan characteristics.
A practical release checklist is:
- Refresh the schema summary and approved-object allowlist.
- Confirm the database role remains read-only and narrowly scoped.
- Run parser, formatter, linter, permission, and injection-resistance tests.
- Review execution plans for representative and worst-case queries.
- Compare latency, result size, and resource measures with the current baseline.
- Record the model, prompt, dialect, driver, ORM, formatter, and schema versions.
- Publish only after failures have a clear rejection or clarification path.
Safe text-to-SQL is therefore an ongoing review process rather than a one-time prompt. Keep generation flexible, but make parsing, authorization, formatting, cost checks, and database permissions deterministic. That division gives developers a repeatable way to validate AI-generated queries as schemas and workloads evolve.