Text-to-SQL prompt engineering can turn a natural-language question into a useful database query, but a convincing query is not necessarily a correct or safe one. This guide provides a reusable schema-context prompt, database-dialect examples, a validation workflow, read-only safeguards, and a regression checklist for building more reliable natural language to SQL features.
Overview
An LLM needs more than a question such as “Which products sold best last quarter?” to generate dependable SQL. It needs the relevant schema, relationships, business definitions, SQL dialect, output constraints, and a clear instruction to ask for clarification when the request is ambiguous.
The safest design treats an LLM-generated query as a proposed program rather than a final answer. The model can help interpret intent and draft SQL, while deterministic application code validates the statement, limits access, applies timeouts, and controls execution. A second step can explain the results, but it should not silently change the query or invent missing data.
A practical text-to-SQL workflow is:
- Classify the user’s request and identify the intended data domain.
- Retrieve only the schema descriptions and business rules needed for that request.
- Generate SQL in the target database dialect.
- Parse and validate the SQL before execution.
- Run it through a restricted, read-only database connection.
- Check execution errors, row limits, and result plausibility.
- Present the query, assumptions, and answer in a transparent format.
For related implementation patterns, see the LLM Evaluation Test Suite, the guide to LLM function calling and tool use, and the structured output prompt guide.
Checklist by scenario
1. Build a schema-context prompt
Give the model a compact, curated representation of the database instead of sending an unrestricted catalog dump. Include table names, column names and types, primary keys, foreign keys, allowed joins, important filters, and definitions for terms such as “active customer,” “net revenue,” or “completed order.” Exclude credentials, sensitive values, and unnecessary tables.
Use a prompt structure like this:
System:
You generate read-only SQL for the specified database dialect.
Use only the tables and columns in the supplied schema.
Never generate INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, or administrative statements.
If the request is ambiguous or the schema is insufficient, ask a clarification question.
Do not guess business definitions. State assumptions explicitly.
Return JSON with: status, sql, assumptions, clarification_question.
Database dialect: [PostgreSQL | MySQL | SQL Server | SQLite | other]
Schema context:
[table and column definitions]
Relationships:
[approved foreign-key relationships and join rules]
Business definitions:
[metric and status definitions]
Query limits:
[maximum rows, permitted date range, timeout expectations]
User request:
[plain-language question]
Keep schema retrieval separate from user input where possible. Clearly label each section so an instruction embedded in a table description or user message cannot be mistaken for a higher-priority system rule.
2. Handle database dialects explicitly
SQL is not one uniform language. Date functions, identifier quoting, pagination, string operations, and type conversion can differ between PostgreSQL, MySQL, SQL Server, and SQLite. Put the dialect in the prompt and test generated queries against the same engine that will execute them.
- PostgreSQL: specify whether date arithmetic, case-sensitive identifiers, and interval expressions are permitted.
- MySQL: define the expected date functions, identifier conventions, and handling of strict grouping behavior.
- SQL Server: clarify pagination syntax, date functions, and identifier quoting.
- SQLite: account for its different type behavior and date-handling approach.
Do not ask one prompt to produce portable SQL unless portability is genuinely required. A dialect-specific prompt is usually easier to validate and regression-test.
3. Add a validation and execution layer
Prompt instructions are useful controls, but they should not be the only controls. Before execution, parse the generated SQL with a dialect-appropriate parser or a carefully maintained validation layer. Confirm that the statement is a single permitted read operation, references approved tables, uses allowed columns, and does not contain comments or additional statements that bypass simple checks.
Execute through a database role with read-only permissions. Add a maximum runtime, row limit, resource limit where available, and an application-level cancellation path. Consider using a reporting replica or a curated analytics layer rather than an operational database. Log the user request, schema version, generated SQL, validation outcome, execution result, and error category without logging secrets or unnecessary personal data.
4. Test common question types
Build a small golden set of natural-language questions with expected query properties, not just expected text. The tests should cover simple filters, aggregations, multi-table joins, date ranges, top-N requests, missing values, and questions that should be refused or clarified. The prompt injection prevention checklist is useful when user text, retrieved schema descriptions, or metadata may contain untrusted instructions.
What to double-check
| Strategy | Useful for | Double-check |
|---|---|---|
| Schema-in-context prompting | Small, stable schemas | Stale definitions, missing relationships, context size |
| Schema retrieval | Large or frequently changing databases | Retrieval relevance, access boundaries, schema versioning |
| Few-shot examples | Recurring query patterns and business terminology | Examples that encourage unsafe joins or outdated rules |
| SQL parser validation | Blocking unsupported syntax and statements | Parser dialect alignment and bypass cases |
| Dry-run or explain step | Cost and plan inspection | Whether the database still permits expensive work |
| Read-only execution | Reducing write risk | Role privileges, views, functions, and connector behavior |
| Result verification | Detecting empty, huge, or implausible outputs | Business-specific thresholds and false alarms |
Pay particular attention to joins. An LLM may select a syntactically valid relationship that duplicates rows or joins on a descriptive field rather than a key. Document approved join paths and, where practical, expose reporting views with known grain. Every metric should have a defined unit of analysis: order, order line, customer, session, or another business entity.
Dates also need explicit rules. Clarify whether “last month” means a calendar month, a rolling period, or a reporting period; whether timestamps are stored in UTC; and whether the end date is inclusive or exclusive. For NULL values, specify whether missing data should be excluded, grouped separately, or treated as zero. These decisions often affect accuracy more than the wording of the prompt.
Common mistakes
- Providing only table names: Without keys, grain, and definitions, the model may choose an incorrect join or metric.
- Trusting valid syntax: A query can compile and still answer a different question. Compare its tables, filters, grouping, and aggregation with the requested intent.
- Using unrestricted database credentials: The LLM should never determine its own privileges. Enforce permissions outside the prompt.
- Relying on string matching for safety: Checking whether text begins with
SELECTdoes not replace parsing, statement controls, and a restricted connection. - Hiding uncertainty: If “revenue” or “customer” has multiple meanings, the application should show the assumption or ask a clarification question.
- Returning only a number: Show the interpreted question, relevant filters, time range, and optionally the generated SQL so users can review the result.
- Testing only easy examples: Include permissions, ambiguous requests, NULLs, date boundaries, duplicate-producing joins, and requests for unavailable fields.
- Ignoring prompt injection: Treat user text, retrieved documentation, table comments, and metadata as untrusted content. Separate data from instructions and apply validation after generation.
When the system needs clarification or refuses a request, make that a structured outcome rather than an improvised conversational response. This makes it easier for the application to route the request, request missing details, and measure failure modes.
When to revisit
Review a text-to-SQL prompt and its validation rules whenever the schema changes, a reporting view is replaced, a business metric is redefined, or a new database dialect or connector is introduced. A column rename can break generation; a new relationship can create duplicate rows; and a changed permission model can alter what the application should expose.
Before seasonal planning cycles or recurring reporting periods, run the test matrix against date boundaries, expected volumes, and high-use questions. When workflows or tools change, verify model output formats, API behavior, parser compatibility, connector error handling, and read-only enforcement. Do not assume that a prompt update alone preserves behavior.
Keep a versioned evaluation set with cases for:
- single-table filters and aggregations;
- approved and disallowed joins;
- calendar and rolling date ranges;
- NULL and zero handling;
- ambiguous metric or entity names;
- missing permissions and unavailable columns;
- injection attempts and multi-statement requests;
- large-result and expensive-query conditions.
For each change, compare not only whether SQL executes, but whether it uses the intended tables, preserves the expected grain, obeys limits, and communicates uncertainty. A dependable natural language to SQL feature is maintained through prompt engineering, schema management, deterministic validation, restricted execution, and ongoing evaluation—not through a single perfect prompt.