Building a Robust Text-to-SQL System with Claude: A Practical Guide

Text-to-SQL is one of the most transformative applications of large language models. It bridges the gap between natural language and structured data, allowing anyone in an organization to query databases without knowing SQL syntax. Claude excels at this task thanks to its strong reasoning capabilities, context understanding, and ability to generate syntactically correct SQL.

In this guide, you'll learn how to build a production-ready Text-to-SQL system using Claude. We'll start with basic prompting, then progressively add sophistication: few-shot examples, chain-of-thought reasoning, Retrieval Augmented Generation (RAG) for large schemas, and a self-improvement loop that lets Claude fix its own mistakes.

Why Text-to-SQL Matters

Before diving into the implementation, let's understand why this capability is so valuable:

• Democratizing data access: Non-technical team members can ask questions like "What were our top-selling products last quarter?" without waiting for a data analyst.
• Accelerating analytics: Data professionals can prototype complex queries in seconds instead of minutes.
• Enabling conversational interfaces: Chatbots and virtual assistants can answer data-driven questions in real time.
• Handling complexity: Claude can generate queries with multiple JOINs, subqueries, window functions, and aggregations that would take humans significant time to write correctly.

Setting Up Your Environment

First, let's create a test SQLite database with two tables: employees and departments. This will serve as our sandbox throughout the guide.

import sqlite3
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
Create departments table
cursor.execute("""
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    location TEXT
)
""")
Create employees table
cursor.execute("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary REAL,
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
)
""")
Insert sample data
cursor.executemany("INSERT INTO departments VALUES (?, ?, ?)", [
    (1, "Engineering", "New York"),
    (2, "Marketing", "San Francisco"),
    (3, "Sales", "Chicago")
])
cursor.executemany("INSERT INTO employees VALUES (?, ?, ?, ?, ?)", [
    (1, "Alice", 1, 120000, "2020-01-15"),
    (2, "Bob", 1, 95000, "2021-03-01"),
    (3, "Charlie", 2, 80000, "2019-06-10"),
    (4, "Diana", 3, 110000, "2022-11-20")
])
conn.commit()

Creating a Basic Text-to-SQL Prompt

A good Text-to-SQL prompt needs three elements: clear instructions, the user's natural language query, and the database schema. Here's a minimal implementation:

def get_schema(cursor):
    """Extract schema information from the database."""
    cursor.execute("SELECT sql FROM sqlite_master WHERE type='table'")
    return "\n".join(row[0] for row in cursor.fetchall())
def basic_prompt(user_query, schema):
    return f"""You are a SQL expert. Given the following database schema, convert the user's natural language query into a valid SQL query.
Database Schema:
{schema}
User Query: {user_query}
SQL Query:"""
schema = get_schema(cursor)
prompt = basic_prompt("Show me all employees in Engineering", schema)
print(prompt)

This will output a prompt that Claude can use to generate the SQL. The key insight is that Claude needs to understand the table structure, column names, and relationships to write accurate queries.

Improving with Few-Shot Examples

Basic prompting works, but you'll get better results by including examples. This technique, called few-shot learning, shows Claude what good input-output pairs look like:

def few_shot_prompt(user_query, schema):
    examples = """
Example 1:
User Query: List all departments
SQL Query: SELECT * FROM departments;
Example 2:
User Query: Find employees hired after 2020
SQL Query: SELECT * FROM employees WHERE hire_date > '2020-01-01';
Example 3:
User Query: What is the average salary by department?
SQL Query: SELECT d.name, AVG(e.salary) FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.name;
"""
    return f"""You are a SQL expert. Convert natural language queries to SQL.
Database Schema:
{schema}
{examples}
User Query: {user_query}
SQL Query:"""

Pro tip: Include a few rows of actual data alongside the schema. This gives Claude context about data formats and values, which helps with queries involving specific dates, names, or numeric ranges.

Chain-of-Thought Prompting for Complex Queries

For complex queries involving multiple joins, subqueries, or aggregations, chain-of-thought (CoT) prompting helps Claude reason step-by-step. Using XML tags to structure the reasoning process is particularly effective:

def cot_prompt(user_query, schema):
    return f"""You are a SQL expert. Convert the user's query into SQL by reasoning step-by-step.
Database Schema:
{schema}
User Query: {user_query}
<reasoning>
Let me break down this query:
What tables do I need?
What columns should I select?
What conditions (WHERE clauses) are needed?
Do I need aggregation (GROUP BY, HAVING)?
What's the correct JOIN condition?
</reasoning>

<sql>
-- Write the SQL query here
</sql>"""

This structured approach forces Claude to think through the problem before generating SQL, reducing errors on complex queries.

Implementing RAG for Large Database Schemas

Real-world databases can have hundreds of tables. Including the entire schema in every prompt is impractical and confuses the model. Retrieval Augmented Generation (RAG) solves this by dynamically fetching only the relevant schema information.

Here's how to implement a simple RAG system using VoyageAI embeddings:

import voyageai
from typing import List, Dict
class SchemaRetriever:
    def __init__(self, api_key: str):
        self.client = voyageai.Client(api_key=api_key)
        self.table_embeddings = {}
        self.table_descriptions = {}
    
    def index_schema(self, tables: List[Dict]):
        """Index table schemas with descriptions."""
        for table in tables:
            description = f"Table: {table['name']}\nColumns: {', '.join(table['columns'])}"
            self.table_descriptions[table['name']] = description
            response = self.client.embed([description], model="voyage-2")
            self.table_embeddings[table['name']] = response.embeddings[0]
    
    def retrieve_relevant_tables(self, query: str, top_k: int = 3) -> str:
        """Get the most relevant table schemas for a query."""
        query_embedding = self.client.embed([query], model="voyage-2").embeddings[0]
        
        # Calculate cosine similarity
        similarities = {}
        for table_name, embedding in self.table_embeddings.items():
            similarity = sum(a*b for a,b in zip(query_embedding, embedding))
            similarities[table_name] = similarity
        
        # Return top-k table schemas
        top_tables = sorted(similarities, key=similarities.get, reverse=True)[:top_k]
        return "\n\n".join(self.table_descriptions[t] for t in top_tables)
Usage
retriever = SchemaRetriever(api_key="your-voyageai-api-key")
tables = [
    {"name": "employees", "columns": ["id", "name", "department_id", "salary", "hire_date"]},
    {"name": "departments", "columns": ["id", "name", "location"]},
    # Add more tables...
]
retriever.index_schema(tables)
user_query = "Show me the highest paid employee in each department"
relevant_schema = retriever.retrieve_relevant_tables(user_query)
prompt = f"""Schema:\n{relevant_schema}\n\nQuery: {user_query}\nSQL:"""

This approach scales to hundreds of tables and keeps your prompts focused and efficient.

Building a Self-Improvement Loop

One of Claude's most powerful capabilities is the ability to evaluate and improve its own outputs. Here's a self-improvement loop that executes the SQL, catches errors, and retries:

def text_to_sql_with_self_improvement(user_query, schema, conn, max_attempts=3):
    """Generate SQL, execute it, and improve if there are errors."""
    
    # Initial generation
    prompt = f"""Generate SQL for: {user_query}\n\nSchema: {schema}"""
    sql = claude_complete(prompt)  # Your Claude API call here
    
    for attempt in range(max_attempts):
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            results = cursor.fetchall()
            
            # Optional: Validate results make sense
            validation_prompt = f"""
            User asked: {user_query}
            Generated SQL: {sql}
            Results: {results}
            
            Does this SQL correctly answer the user's question? If not, fix it.
            """
            
            if "yes" in claude_complete(validation_prompt).lower():
                return sql, results
            
        except Exception as e:
            error_msg = str(e)
            fix_prompt = f"""
            The following SQL query produced an error:
            {sql}
            
            Error: {error_msg}
            
            User's original query: {user_query}
            Schema: {schema}
            
            Please fix the SQL query.
            """
            sql = claude_complete(fix_prompt)
    
    return sql, None

This loop handles:

• Syntax errors: Claude can fix typos, missing commas, or incorrect keywords
• Logic errors: If results don't match the query intent, Claude can revise
• Schema mismatches: If a column name is wrong, Claude can correct it

Evaluating Your Text-to-SQL System

Evaluation is critical for production systems. Here's a framework:

• Execution accuracy: Does the generated SQL run without errors?
• Result correctness: Does the output match expected results?
• Semantic equivalence: Is the SQL logically equivalent to a reference query?
• Edge case handling: How does the system handle ambiguous queries, empty results, or unusual filters?

Create a test suite with diverse queries:

test_cases = [
    {
        "query": "List all employees",
        "expected_sql": "SELECT * FROM employees",
        "expected_results": [(1, "Alice", 1, 120000, "2020-01-15"), ...]
    },
    {
        "query": "Average salary by department",
        "expected_sql": "SELECT d.name, AVG(e.salary) FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.name"
    },
    # Add more test cases...
]

Key Takeaways

• Start simple, then iterate: Begin with basic prompting, then add few-shot examples, chain-of-thought reasoning, and finally RAG and self-improvement loops as complexity grows.
• Include schema context: Always provide Claude with the database schema. For large databases, use RAG to dynamically retrieve only the relevant tables.
• Use chain-of-thought for complex queries: Structured reasoning with XML tags helps Claude handle multi-step SQL generation more accurately.
• Implement self-improvement loops: Let Claude execute its SQL, catch errors, and fix them. This dramatically improves reliability in production.
• Test rigorously: Build a comprehensive test suite covering execution accuracy, result correctness, and edge cases before deploying to production.