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

Text-to-SQL is one of the most impactful applications of large language models. It lets non-technical team members query databases using plain English, and helps data analysts prototype complex queries faster. Claude excels at this task because it can understand context, interpret nuanced requests, and generate accurate SQL statements.

In this guide, you'll learn how to build a production-ready Text-to-SQL system using Claude. We'll start with a basic prompt, then layer in advanced techniques like few-shot learning, 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 code, let's look at why this capability is so valuable:

• Accessibility: Business analysts, product managers, and executives can query databases without knowing SQL syntax.
• Efficiency: Data professionals can prototype queries in seconds instead of minutes.
• Integration: Chatbots and internal tools can offer natural language database interfaces.
• Complexity: Claude can generate multi-join queries, subqueries, and aggregations that would take humans significant time to write.

Setting Up Your Environment

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

import sqlite3
Create an in-memory SQLite database
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, 95000, '2020-03-15'),
    (2, 'Bob', 2, 72000, '2021-07-01'),
    (3, 'Charlie', 1, 88000, '2019-11-20'),
    (4, 'Diana', 3, 65000, '2022-01-10')
])
conn.commit()

Step 1: Creating a Basic Text-to-SQL Prompt

A good prompt needs three things: clear instructions, the user's query, and the database schema. Let's build a simple function that generates this prompt.

def get_schema_info(cursor):
    """Extract schema information from the database."""
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    
    schema = []
    for table in tables:
        table_name = table[0]
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        col_info = [f"{col[1]} ({col[2]})" for col in columns]
        schema.append(f"Table: {table_name}\nColumns: {', '.join(col_info)}")
    
    return "\n\n".join(schema)
def basic_prompt(user_query, schema):
    return f"""You are a SQL expert. Given the database schema below, convert the user's natural language query into a valid SQL query.
Database Schema:
{schema}
User Query: {user_query}
SQL Query:"""
Example usage
schema = get_schema_info(cursor)
prompt = basic_prompt("Show me all employees in Engineering", schema)
print(prompt)

Now let's call Claude with this prompt:

import anthropic
client = anthropic.Anthropic()
response = client.messages.create(
    model="claude-3-sonnet-20241022",
    max_tokens=200,
    messages=[{"role": "user", "content": prompt}]
)
sql_query = response.content[0].text
print("Generated SQL:", sql_query)
Execute and verify
cursor.execute(sql_query)
results = cursor.fetchall()
print("Results:", results)

Step 2: Improving with Few-Shot Examples

A basic prompt works, but adding examples (few-shot learning) dramatically improves accuracy. Let's modify our prompt function to include examples.

def improved_prompt(user_query, schema):
    examples = """
Example 1:
User: List all departments
SQL: SELECT * FROM departments;
Example 2:
User: Show employee names and their department names
SQL: SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id;
Example 3:
User: Find the average salary by department
SQL: 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. Given the database schema below, convert the user's natural language query into a valid SQL query.
{schema}
Here are some examples:
{examples}
User Query: {user_query}
SQL Query:"""

Pro Tip: Including a few rows of actual data in your prompt can give Claude even more context about the data structure and content, leading to better results.

Step 3: Chain-of-Thought Prompting

For complex queries, chain-of-thought (CoT) prompting helps Claude reason step-by-step. This is especially useful for queries involving multiple joins, subqueries, or aggregations.

def cot_prompt(user_query, schema):
    return f"""You are a SQL expert. Given the database schema below, convert the user's natural language query into a valid SQL query.
Database Schema:
{schema}
First, think step-by-step about what tables and columns are needed. Then, write the SQL query.
<reasoning>
Identify the tables involved based on the user's request.
Determine which columns are needed.
Consider any filters, joins, or aggregations.
Write the final SQL query.
</reasoning>

User Query: {user_query}
SQL Query:"""

Step 4: RAG for Complex Database Schemas

When your database has dozens or hundreds of tables, you can't fit the entire schema in a prompt. Retrieval Augmented Generation (RAG) solves this by dynamically fetching only the relevant schema information.

First, create embeddings of your schema using VoyageAI:

import voyageai
vo = voyageai.Client()
def embed_schema_tables(cursor):
    """Create embeddings for each table in the database."""
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    tables = cursor.fetchall()
    
    table_descriptions = []
    for table in tables:
        table_name = table[0]
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        col_info = [f"{col[1]} ({col[2]})" for col in columns]
        description = f"Table {table_name} has columns: {', '.join(col_info)}"
        table_descriptions.append((table_name, description))
    
    # Create embeddings
    texts = [desc for _, desc in table_descriptions]
    embeddings = vo.embed(texts, model="voyage-2").embeddings
    
    return list(zip(table_descriptions, embeddings))
def retrieve_relevant_schema(user_query, table_embeddings, top_k=2):
    """Retrieve the most relevant tables for a given query."""
    query_embedding = vo.embed([user_query], model="voyage-2").embeddings[0]
    
    # Simple cosine similarity (you'd use a proper vector DB in production)
    similarities = []
    for (table_name, description), emb in table_embeddings:
        similarity = sum(a*b for a,b in zip(query_embedding, emb))
        similarities.append((similarity, table_name, description))
    
    similarities.sort(reverse=True)
    return [desc for _, _, desc in similarities[:top_k]]

Now integrate RAG into your prompt:

def rag_prompt(user_query, table_embeddings):
    relevant_schema = retrieve_relevant_schema(user_query, table_embeddings)
    schema_str = "\n\n".join(relevant_schema)
    
    return f"""You are a SQL expert. Based on the relevant database schema below, convert the user's query into SQL.
Relevant Schema:
{schema_str}
User Query: {user_query}
SQL Query:"""

Step 5: Query Self-Improvement Loop

One of Claude's most powerful capabilities is the ability to execute its own SQL, analyze the results, and fix errors. This self-improvement loop handles syntax errors, logic mistakes, and edge cases.

def self_improving_query(user_query, schema, max_iterations=3):
    """Generate SQL, execute it, and improve if needed."""
    prompt = improved_prompt(user_query, schema)
    
    for i in range(max_iterations):
        response = client.messages.create(
            model="claude-3-sonnet-20241022",
            max_tokens=300,
            messages=[{"role": "user", "content": prompt}]
        )
        
        sql_query = response.content[0].text
        
        try:
            cursor.execute(sql_query)
            results = cursor.fetchall()
            
            # Check if results make sense
            if results:
                return sql_query, results
            else:
                # No results - maybe the query is wrong
                prompt = f"""The previous SQL query returned no results. Please fix it.
Original query: {user_query}
Previous SQL: {sql_query}
Schema: {schema}
Improved SQL:"""
        except Exception as e:
            # SQL error - ask Claude to fix it
            prompt = f"""The SQL query had an error: {str(e)}
Original query: {user_query}
Previous SQL: {sql_query}
Schema: {schema}
Fixed SQL:"""
    
    return None, []

This loop is incredibly useful for production systems where you can't manually review every query. Claude becomes a self-correcting SQL generator.

Key Takeaways

• Start simple, then iterate: Begin with a basic prompt, then add few-shot examples, chain-of-thought, and finally RAG as your schema grows.
• Use RAG for large schemas: Don't cram your entire database schema into a prompt. Retrieve only the relevant tables based on the user's query.
• Implement self-correction: Claude can execute its own SQL, catch errors, and fix them. This dramatically improves reliability in production.
• Include data samples: Adding a few rows of real data in your prompt helps Claude understand the data structure and produce more accurate queries.
• Always validate: Even with self-improvement, always validate SQL output before running it against production databases. Consider using a read-only replica for safety.

Text-to-SQL with Claude is a powerful capability that can democratize data access across your organization. By following the techniques in this guide, you'll build a system that's accurate, scalable, and self-correcting.