Text-to-SQL

Text-to-SQL converts natural language questions into executable database queries. A user asks "What was revenue last quarter by region?" and the system generates the correct SQL, executes it, and returns results. It is one of the most requested enterprise AI use cases because it democratises data access without requiring SQL literacy.

The challenge is not generating SQL - LLMs do that well. The challenge is generating correct SQL that respects your schema, business logic, and security boundaries.

Architecture

Schema Grounding

The LLM needs to know your database structure to generate correct SQL. This is the most critical step.

What to include in context

schema_context = """
Database: analytics_prod
Schema: gold

Tables:
- orders (order_id, customer_id, order_date, delivered_date, status, order_value, refund_amount, region)
  - status values: pending, confirmed, shipped, delivered, cancelled
  - Use delivered_date for revenue calculations, not order_date
  - 1 row per order line item

- customers (customer_id, name, segment, created_date, country)
  - segment values: enterprise, mid-market, smb

- products (product_id, name, category, unit_price, active)

Relationships:
- orders.customer_id -> customers.customer_id
- orders.product_id -> products.product_id

Business rules:
- "Revenue" means SUM(order_value - COALESCE(refund_amount, 0)) WHERE status = 'delivered'
- "Last quarter" means the most recent complete calendar quarter
- "Active customers" means customers with at least one delivered order in the last 90 days
"""

Schema selection for large databases

With hundreds of tables, you cannot fit the entire schema in context. Use retrieval:

Query Generation

def generate_sql(question: str, schema: str, examples: list[dict]) -> str:
    messages = [
        {"role": "system", "content": f"""You are a SQL expert. Generate a single SQL query 
to answer the user's question.

Rules:
- Use only tables and columns from the schema provided
- Generate SELECT queries only (never INSERT, UPDATE, DELETE, DROP)
- Use explicit column names (never SELECT *)
- Include appropriate WHERE clauses for date ranges
- Use CTEs for complex queries (readable over clever)

Schema:
{schema}"""},
    ]
    
    # Add few-shot examples
    for ex in examples:
        messages.append({"role": "user", "content": ex["question"]})
        messages.append({"role": "assistant", "content": ex["sql"]})
    
    messages.append({"role": "user", "content": question})
    
    response = client.chat.completions.create(
        model="gpt-4o", messages=messages, temperature=0
    )
    return response.choices[0].message.content

Validation

Never execute LLM-generated SQL without validation.

import sqlparse

def validate_sql(sql: str, allowed_tables: set[str]) -> tuple[bool, str]:
    """Validate generated SQL before execution."""
    
    # Parse SQL
    parsed = sqlparse.parse(sql)
    if not parsed:
        return False, "Could not parse SQL"
    
    statement = parsed[0]
    
    # Only allow SELECT
    if statement.get_type() != "SELECT":
        return False, f"Only SELECT allowed, got {statement.get_type()}"
    
    # Check tables against allowlist
    tables_used = extract_tables(sql)
    unauthorized = tables_used - allowed_tables
    if unauthorized:
        return False, f"Unauthorized tables: {unauthorized}"
    
    # Reject dangerous patterns
    dangerous = ["DROP", "DELETE", "INSERT", "UPDATE", "ALTER", "EXEC", "--", ";"]
    sql_upper = sql.upper()
    for pattern in dangerous:
        if pattern in sql_upper and pattern not in ("--",):  # Allow inline comments
            return False, f"Dangerous pattern detected: {pattern}"
    
    return True, "Valid"

Production Patterns

Verified queries (for critical metrics)

Pre-approve SQL for common business questions. Return these verbatim instead of generating.

VERIFIED_QUERIES = {
    "monthly revenue": """
        SELECT DATE_TRUNC('month', delivered_date) AS month,
               SUM(order_value - COALESCE(refund_amount, 0)) AS net_revenue
        FROM gold.orders
        WHERE status = 'delivered'
        GROUP BY 1 ORDER BY 1
    """,
    "active customers by segment": """
        SELECT c.segment, COUNT(DISTINCT c.customer_id) AS active_customers
        FROM gold.customers c
        JOIN gold.orders o ON c.customer_id = o.customer_id
        WHERE o.status = 'delivered' AND o.delivered_date >= CURRENT_DATE - 90
        GROUP BY 1
    """
}

def answer_question(question: str):
    # Check verified queries first
    for key, sql in VERIFIED_QUERIES.items():
        if is_similar(question, key, threshold=0.9):
            return execute_and_format(sql, verified=True)
    
    # Fall back to generation
    sql = generate_sql(question, schema, examples)
    valid, reason = validate_sql(sql, ALLOWED_TABLES)
    if not valid:
        return f"Could not generate a safe query: {reason}"
    return execute_and_format(sql, verified=False)

Self-correction

When generated SQL fails, feed the error back to the LLM for correction.

def generate_with_retry(question: str, max_attempts: int = 3) -> str:
    sql = generate_sql(question, schema, examples)
    
    for attempt in range(max_attempts):
        valid, reason = validate_sql(sql, ALLOWED_TABLES)
        if not valid:
            sql = fix_sql(sql, error=reason)
            continue
        
        try:
            results = execute_query(sql)
            return format_results(results, sql)
        except DatabaseError as e:
            sql = fix_sql(sql, error=str(e))
    
    return "Could not generate a valid query after multiple attempts."

Platform Implementations

Key Takeaways

• Schema grounding determines accuracy. Invest in table/column descriptions and business rule documentation.
• Validate every generated query before execution. Allowlist tables, reject mutations, check syntax.
• Use verified queries for critical business metrics. Do not let the LLM generate SQL for board-level KPIs.
• Self-correction (feed errors back to LLM) handles 80% of generation failures without human intervention.
• Security is non-negotiable: read-only connections, parameterised execution, row-level access controls.