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

Imagine giving your non-technical team members the ability to ask your database complex questions in plain English—and getting accurate SQL queries back instantly. That's the promise of Text-to-SQL, and Claude is exceptionally well-suited for this task. This guide walks you through building a production-ready Text-to-SQL system, from basic prompting to advanced self-improvement loops.

Why Text-to-SQL Matters

Text-to-SQL bridges the gap between natural language and structured data. Here's why it's transformative:

• Democratizes data access: Non-technical stakeholders can query databases without learning SQL syntax
• Boosts analyst productivity: Data professionals can prototype complex queries in seconds
• Enables intuitive interfaces: Power chatbots and applications with natural language database interaction
• Handles complexity: Claude can generate queries with multiple JOINs, 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 Johnson', 1, 95000, '2020-03-15'),
    (2, 'Bob Smith', 2, 75000, '2021-07-01'),
    (3, 'Charlie Brown', 1, 88000, '2019-11-20'),
    (4, 'Diana Prince', 3, 82000, '2022-01-10')
])
conn.commit()

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

The foundation of any Text-to-SQL system is a well-structured prompt. Your prompt must include:

• Clear instructions for the task
• The user's natural language query
• The database schema so Claude understands the available tables and columns

import anthropic
client = anthropic.Anthropic()
def get_schema(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT sql FROM sqlite_master WHERE type='table'")
    schemas = cursor.fetchall()
    return '\n'.join([s[0] for s in schemas])
def generate_prompt_basic(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}
Generate only the SQL query, nothing else."""
Test it
schema = get_schema(conn)
prompt = generate_prompt_basic("Show me all employees in Engineering", schema)
response = client.messages.create(
    model="claude-3-sonnet-20241022",
    max_tokens=200,
    messages=[{"role": "user", "content": prompt}]
)
print(response.content[0].text)
Output: SELECT * FROM employees WHERE department_id = 1;

Step 2: Improving with Few-Shot Examples

Basic prompts work, but few-shot learning dramatically improves accuracy. By providing examples of input-output pairs, Claude learns the expected format and reasoning pattern.

def generate_prompt_few_shot(user_query, schema):
    examples = """
Example 1:
User: List all departments in San Francisco
SQL: SELECT * FROM departments WHERE location = 'San Francisco';
Example 2:
User: What is the average salary by department?
SQL: SELECT d.name, AVG(e.salary) as avg_salary 
FROM employees e 
JOIN departments d ON e.department_id = d.id 
GROUP BY d.name;
Example 3:
User: Who was hired most recently?
SQL: SELECT name, hire_date FROM employees ORDER BY hire_date DESC LIMIT 1;
"""
    
    return f"""You are a SQL expert. Convert natural language queries to SQL.
Database Schema:
{schema}
{examples}
User: {user_query}
SQL:"""

Pro tip: Including a few sample rows of real data in your prompt can give Claude valuable context about data types and value formats, leading to more accurate queries.

Step 3: Chain-of-Thought Prompting for Complex Queries

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

def generate_prompt_cot(user_query, schema):
    return f"""You are a SQL expert. Convert the user's query to SQL by reasoning step-by-step.
Database Schema:
{schema}
<reasoning>
Identify the tables needed
Determine the columns to select
Identify any JOIN conditions
Apply WHERE filters
Add GROUP BY, ORDER BY, or LIMIT if needed
Construct the final SQL
</reasoning>

User Query: {user_query}
Provide your reasoning in <reasoning> tags, then output the SQL in <sql> tags."""
response = client.messages.create(
    model="claude-3-sonnet-20241022",
    max_tokens=500,
    messages=[{"role": "user", "content": generate_prompt_cot(
        "Which department has the highest average salary?", schema
    )}]
)

Claude will output something like:

<reasoning>
I need the departments table for department names and employees table for salaries
I need to calculate average salary per department
JOIN on department_id
GROUP BY department name
ORDER BY average salary descending
LIMIT 1 to get the highest
</reasoning>

<sql>
SELECT d.name, AVG(e.salary) as avg_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name
ORDER BY avg_salary DESC
LIMIT 1;
</sql>

Step 4: RAG for Complex Database Schemas

When your database has dozens or hundreds of tables, including the entire schema in every prompt is impractical. Retrieval Augmented Generation (RAG) solves this by dynamically fetching only the relevant schema information.

Here's how to implement RAG using VoyageAI embeddings:

import voyageai
class VectorDB:
    def __init__(self):
        self.client = voyageai.Client()
        self.embeddings = []
        self.metadata = []
    
    def add_documents(self, documents, metadata_list):
        response = self.client.embed(documents, model="voyage-2")
        self.embeddings.extend(response.embeddings)
        self.metadata.extend(metadata_list)
    
    def search(self, query, k=3):
        query_embedding = self.client.embed([query], model="voyage-2").embeddings[0]
        # Calculate cosine similarity
        similarities = [
            cosine_similarity(query_embedding, emb) 
            for emb in self.embeddings
        ]
        top_indices = sorted(
            range(len(similarities)), 
            key=lambda i: similarities[i], 
            reverse=True
        )[:k]
        return [self.metadata[i] for i in top_indices]
Index your table schemas
vector_db = VectorDB()
table_descriptions = [
    "employees: id, name, department_id, salary, hire_date - employee records",
    "departments: id, name, location - company departments"
]
vector_db.add_documents(table_descriptions, [
    {"table": "employees", "schema": "CREATE TABLE employees (...)"},
    {"table": "departments", "schema": "CREATE TABLE departments (...)"}
])
def generate_prompt_rag(user_query):
    relevant_schemas = vector_db.search(user_query, k=2)
    schema_context = "\n".join([s["schema"] for s in relevant_schemas])
    
    return f"""Using the relevant schema context, convert the query to SQL.
Relevant Schema:
{schema_context}
User Query: {user_query}
SQL:"""

Step 5: Self-Improvement Loop

The most powerful technique is letting Claude execute its own SQL, analyze the results, and fix errors. This creates a self-correcting system.

def text_to_sql_with_self_improvement(user_query, conn, max_iterations=3):
    schema = get_schema(conn)
    
    for i in range(max_iterations):
        # Generate SQL
        prompt = generate_prompt_cot(user_query, schema)
        response = client.messages.create(
            model="claude-3-sonnet-20241022",
            max_tokens=500,
            messages=[{"role": "user", "content": prompt}]
        )
        
        # Extract SQL from response
        sql = extract_sql(response.content[0].text)
        
        try:
            cursor = conn.cursor()
            cursor.execute(sql)
            results = cursor.fetchall()
            
            # If successful, return results
            if i == 0:
                return sql, results
            
            # Check if results make sense
            validation_prompt = f"""The SQL query:
{sql}
Returned these results:
{results}
Does this correctly answer the user's question: "{user_query}"?
If not, explain what's wrong."""
            
            validation = client.messages.create(
                model="claude-3-sonnet-20241022",
                max_tokens=200,
                messages=[{"role": "user", "content": validation_prompt}]
            )
            
            if "correct" in validation.content[0].text.lower():
                return sql, results
                
        except Exception as e:
            # SQL execution error - ask Claude to fix it
            error_prompt = f"""The SQL query:
{sql}
Produced this error:
{str(e)}
Fix the SQL query."""
            
            response = client.messages.create(
                model="claude-3-sonnet-20241022",
                max_tokens=500,
                messages=[{"role": "user", "content": error_prompt}]
            )
            sql = extract_sql(response.content[0].text)
    
    return sql, "Max iterations reached"

Key Takeaways

• Start simple, then layer complexity: Begin with basic prompts, add few-shot examples, then chain-of-thought reasoning, and finally RAG and self-improvement loops
• Schema context is critical: Always provide Claude with the database schema. For large databases, use RAG to dynamically retrieve relevant schema information
• Self-improvement loops catch errors: Let Claude execute its SQL, analyze results, and fix mistakes automatically—this dramatically improves reliability
• Include sample data: Adding a few rows of real data alongside the schema helps Claude understand data types and value formats
• XML tags improve structured reasoning: Using <reasoning> and <sql> tags helps Claude separate its thought process from the final output, making debugging easier