Sql Generation

Text-to-SQL Generation

What is Text-to-SQL?
Converting natural language questions into SQL queries that can be executed against databases.

Basic Approach

def text_to_sql(question: str, schema: str) -> str:
    return llm.generate(f"""
Given this database schema:
{schema}

Convert this question to SQL:
{question}

SQL query (only output the query):
    """)

Schema Representation

schema = """
Tables:
- users (id, name, email, created_at)
- orders (id, user_id, total, status, created_at)
- products (id, name, price, category)
- order_items (order_id, product_id, quantity)

Relationships:
- orders.user_id -> users.id
- order_items.order_id -> orders.id
- order_items.product_id -> products.id
"""

Advanced Text-to-SQL

With Examples

def text_to_sql_few_shot(question: str, schema: str, examples: list) -> str:
    examples_text = "
".join([
        f"Q: {e['question']}
SQL: {e['sql']}"
        for e in examples
    ])

    return llm.generate(f"""
Schema: {schema}

Examples:
{examples_text}

Q: {question}
SQL:
    """)

Multi-Step for Complex Queries

def complex_text_to_sql(question: str, schema: str) -> str:
    # Step 1: Decompose
    steps = llm.generate(f"Break down: {question}")

    # Step 2: Generate sub-queries
    sub_queries = [text_to_sql(step, schema) for step in steps]

    # Step 3: Combine
    return llm.generate(f"Combine these queries: {sub_queries}")

SQL Validation

def validate_and_fix(sql: str, schema: str, error: str) -> str:
    return llm.generate(f"""
This SQL query has an error:
{sql}

Error: {error}
Schema: {schema}

Fixed query:
    """)

Security Considerations
| Risk | Mitigation |
|------|------------|
| SQL injection | Parameterized queries |
| Data exposure | Limit schema to allowed tables |
| Destructive queries | Read-only permissions |
| Resource abuse | Query timeout/limits |

Use Cases
| Use Case | Example |
|----------|---------|
| Analytics | "Show sales by region last quarter" |
| Reporting | "Top 10 customers by revenue" |
| Data exploration | "How many orders per category?" |

Tools and Frameworks
| Tool | Features |
|------|----------|
| LangChain SQL Agent | Multi-step, error correction |
| Vanna.ai | RAG-based, learns from examples |
| Defog | Fine-tuned models |