What Is Text-to-SQL and Why Build It?
Text-to-SQL systems translate natural language questions into SQL queries that run against your database, returning answers grounded in your actual data. Instead of requiring users to write SQL or waiting for an analyst to run a report, they ask “What were the top 10 products by revenue last quarter?” and get an immediate, accurate answer. For business intelligence, internal analytics tools, and data exploration interfaces, text-to-SQL is one of the highest-value LLM applications available — it directly removes the bottleneck between business questions and data answers.
Building a production-grade text-to-SQL system requires more than just asking an LLM to write SQL. You need to provide accurate schema information, handle ambiguous queries gracefully, validate generated SQL before execution, prevent SQL injection, and deal with the inevitable cases where the model generates incorrect or inefficient queries. This guide covers all of these concerns.
The Basic Architecture
A text-to-SQL system has four main components. A schema provider that gives the LLM accurate, up-to-date information about your tables, columns, and relationships. A prompt that instructs the model to generate valid SQL for your specific database dialect. A validator that checks the generated SQL before execution. And an executor that runs the query and formats the results for the user.
import anthropic, psycopg2, re
from dataclasses import dataclass
client = anthropic.Anthropic()
conn = psycopg2.connect("postgresql://user:password@localhost:5432/analytics")
@dataclass
class QueryResult:
sql: str
rows: list
columns: list[str]
error: str = ""
TEXT_TO_SQL_PROMPT = """You are a SQL expert. Convert the user's question to a PostgreSQL query.
Database schema:
{schema}
Rules:
- Generate only SELECT queries (no INSERT, UPDATE, DELETE, DROP)
- Use table and column names exactly as shown in the schema
- Limit results to 100 rows unless the user specifies otherwise
- Return only the SQL query, no explanation or markdown
User question: {question}
SQL query:"""Schema Injection: The Key to Accuracy
The single most important factor in text-to-SQL quality is how well you describe the schema to the model. Providing table and column names is necessary but not sufficient — you also need to convey what the data means, what values columns can contain, and how tables relate to each other.
def get_schema_description(tables: list[str] = None) -> str:
"""Generate a natural-language-enhanced schema description."""
schema_parts = []
with conn.cursor() as cur:
# Get table list
if not tables:
cur.execute("""SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' ORDER BY table_name""")
tables = [r[0] for r in cur.fetchall()]
for table in tables:
# Get columns with types and descriptions
cur.execute("""
SELECT c.column_name, c.data_type, c.is_nullable,
pgd.description
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_statio_all_tables st
ON st.relname = c.table_name
LEFT JOIN pg_catalog.pg_description pgd
ON pgd.objoid = st.relid
AND pgd.objsubid = c.ordinal_position
WHERE c.table_name = %s AND c.table_schema = 'public'
ORDER BY c.ordinal_position
""", (table,))
columns = cur.fetchall()
col_descriptions = []
for col_name, data_type, nullable, description in columns:
desc = f" - {col_name} ({data_type})"
if description:
desc += f": {description}"
col_descriptions.append(desc)
schema_parts.append(f"Table: {table}
Columns:
" + "
".join(col_descriptions))
return "
".join(schema_parts)
schema = get_schema_description(tables=["orders", "products", "customers", "order_items"])
print(schema)Add PostgreSQL column comments to your database tables — these comments are retrieved by the schema function and injected into the prompt, giving the model domain context it cannot infer from column names alone. “revenue DECIMAL — Total revenue in USD after discounts and refunds applied” is far more useful to the model than just “revenue DECIMAL”.
Generating and Validating SQL
After building the schema description, generate SQL using the LLM and validate it before execution. The validator must allowlist operations — only SELECT — and block dangerous keywords like DROP, DELETE, and INSERT. Always set a statement timeout to prevent expensive queries from locking up your database, and hard-limit results to 100 rows regardless of what the model generates.
When the model generates invalid SQL, feed the database error back for self-correction. Claude is remarkably effective at fixing its own SQL mistakes when given the error message — a retry loop with up to three attempts handles the majority of generation errors without surfacing them to the user.
Using LangChain SQL Agent
LangChain provides a pre-built SQL agent that handles schema discovery, query generation, validation, and error correction automatically. Connect it to your database with a read-only user, specify which tables to expose in the schema, and optionally include sample rows to help the model understand data formats. The agent’s multi-step approach — query, check result, refine if needed — produces higher accuracy than single-shot generation on complex questions:
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import create_sql_agent
from langchain_anthropic import ChatAnthropic
db = SQLDatabase.from_uri(
"postgresql://readonly_user:password@localhost:5432/analytics",
include_tables=["orders", "products", "customers"],
sample_rows_in_table_info=3
)
llm = ChatAnthropic(model="claude-sonnet-4-6", temperature=0)
agent = create_sql_agent(llm=llm, db=db, agent_type="tool-calling", verbose=True)
result = agent.invoke({"input": "What were the top 5 products by revenue in Q2 2026?"})
print(result["output"])Security: Non-Negotiable Controls
Text-to-SQL is one of the highest-risk LLM applications from a security standpoint. Several controls are mandatory for production. Read-only database user: Create a PostgreSQL user with SELECT-only permissions. Even if SQL validation fails and a destructive query reaches the database, the user cannot execute it. Schema allowlisting: Only expose tables the system needs. Do not include tables containing secrets, audit logs, or excessive PII in the schema prompt. Statement timeouts: Set a 10-second statement timeout to prevent expensive queries from blocking your database. SQL validation: Blocklist INSERT, UPDATE, DELETE, DROP, TRUNCATE, EXEC, GRANT, REVOKE before execution. Full audit logging: Log every generated query with user ID, natural language question, and row count returned. This is both a security requirement and an invaluable debugging tool.
Handling Ambiguous Questions
“Show me the best customers” is ambiguous — best by revenue, order count, or recency? Rather than guessing, detect ambiguity before generating SQL and return clarifying questions to the user. Run a quick pre-check with a cheap model: does this question require assumptions to answer? If yes, what clarification is needed? A text-to-SQL system that admits uncertainty and asks for clarification is more trustworthy than one that always generates a query — even when the query answers a slightly different question than intended.
Evaluating Text-to-SQL Quality
Measure three things: execution accuracy (does the generated SQL run without error?), result correctness (does the returned data answer the question correctly?), and query efficiency (does the SQL use indexes, avoid full table scans, and return results quickly?). Build a golden dataset of 50–100 question-answer pairs with known correct SQL. Run your system against it weekly and track metrics over time. Execution accuracy above 90% is achievable for well-constrained schemas. Result correctness is harder to measure automatically — human review of a sample of results is the most reliable approach. Efficiency monitoring via query plan analysis catches cases where the model generates technically correct but catastrophically slow SQL that bypasses your indexes.
Production Architecture Recommendations
For a production text-to-SQL system handling business users: cache the schema description and refresh it hourly rather than regenerating per request — schema changes are infrequent but schema generation is slow. Pre-select the relevant tables based on keyword matching between the question and table names before injecting the full schema — a 5-table schema produces better SQL than a 50-table schema where most tables are irrelevant to the question. Rate-limit queries per user to prevent abuse. Surface the generated SQL to power users for transparency and verification. And implement a feedback mechanism where users can flag incorrect answers — this feedback drives the golden dataset that measures and improves system quality over time.
Self-Correcting SQL Generation
When the model generates invalid SQL, feed the database error back for self-correction. Claude and GPT-4o are remarkably effective at fixing SQL errors when given the error message from the database. A retry loop of up to three attempts handles the vast majority of generation errors without surfacing them to the user:
def query_with_retry(question, schema, max_retries=3):
sql = generate_sql(question, schema)
result = execute_query(sql)
for _ in range(max_retries - 1):
if not result.error:
break
fix_prompt = (f"Fix this SQL error.
Question: {question}
"
f"SQL: {sql}
Error: {result.error}
Return corrected SQL only:")
resp = client.messages.create(model="claude-sonnet-4-6", max_tokens=512,
messages=[{"role":"user","content":fix_prompt}])
sql = resp.content[0].text.strip()
result = execute_query(sql)
return resultTrack retry rates in production — a high retry rate (above 15–20%) indicates the schema description is insufficient or the prompt needs refinement. Most well-tuned text-to-SQL systems achieve first-attempt execution accuracy above 85% on their target domain with appropriate schema enrichment and a capable model.
Combining Text-to-SQL with RAG
Text-to-SQL and RAG are complementary rather than competing approaches and many production analytics applications use both. Text-to-SQL answers quantitative questions: “How many orders did we receive last month?” RAG answers qualitative questions: “What does our returns policy say about damaged goods?” A router that classifies incoming questions and directs them to the appropriate system — or uses both for questions that span structured data and documents — delivers a more complete analytics experience than either approach alone. Build the routing as a simple LLM classification step: given the question and a brief description of each system’s capabilities, ask the model which to use. This classification is cheap and reliable, and it means users get accurate answers regardless of whether their question is about data in a database or information in a document.
Improving Accuracy with Few-Shot Examples
Few-shot examples in the prompt are one of the most effective ways to improve text-to-SQL accuracy on a specific schema. Show the model two or three question-SQL pairs from your actual database — ideally covering the types of queries most commonly asked — and accuracy improves measurably compared to zero-shot generation. Select examples that demonstrate the specific patterns users tend to ask: date range filtering, multi-table joins, aggregations with GROUP BY, and any domain-specific conventions like how your date columns are stored or which columns should be used for revenue calculations. Keep examples concise and representative rather than comprehensive — two excellent examples outperform ten mediocre ones.
Restricting Schema to Relevant Tables
A full enterprise database schema can span hundreds of tables. Injecting the complete schema into every prompt is expensive (many thousands of tokens) and degrades accuracy (the model becomes confused by irrelevant tables). Use semantic search to select only the relevant tables for each query: embed table names and descriptions, embed the incoming question, and retrieve the top 5–10 most semantically similar tables. Inject only those tables’ schemas into the prompt. This retrieval-augmented schema selection reduces input tokens by 80–90% for large schemas and significantly improves the model’s ability to write correct joins and filters, because it is reasoning about a focused subset rather than an overwhelming catalogue of all available data.