# Building Text-to-SQL Queries with Claude for Database Operations

> Learn to build text-to-SQL queries with Claude for database operations. Discover automated schema extraction, few-shot prompting, and chain-of-thought reasoning for accurate results.

- Repository: [Anthropic/claude-cookbooks](https://github.com/anthropics/claude-cookbooks)
- Tags: how-to-guide
- Published: 2026-04-14

---

**The Claude Cookbooks repository provides a production-ready workflow in `capabilities/text_to_sql/guide.ipynb` that converts natural language questions into executable SQL using Claude's API, featuring automated schema extraction, few-shot prompting, and chain-of-thought reasoning to ensure accurate database operations.**

Building text-to-SQL queries with Claude streamlines database interactions by allowing non-technical users to query structured data using plain English. The official implementation in the `anthropics/claude-cookbooks` repository demonstrates a robust end-to-end pipeline using SQLite, sophisticated prompt engineering, and deterministic output generation. This article analyzes the source code architecture, extracts the key functions, and provides runnable code samples you can adapt for PostgreSQL, MySQL, or any relational database.

## Setting Up the SQLite Database

The workflow begins with a lightweight data layer defined in `capabilities/text_to_sql/guide.ipynb`. The notebook creates a self-contained SQLite database at `data/data.db` with two relational tables designed to test realistic SQL generation.

The schema consists of:

- **departments** table: `id`, `name`, `location`
- **employees** table: `id`, `name`, `age`, `department_id`, `salary`, `hire_date`

The database initialization code generates 200 synthetic employee records using Python's `random` and `datetime` modules, populating the tables via `cursor.executemany`. This sample data provides the context Claude needs to understand relationships between tables, foreign key constraints, and realistic data distributions.

```python
import os
import sqlite3
import random
from datetime import datetime, timedelta

DATABASE_PATH = "data/data.db"

if not os.path.exists(DATABASE_PATH):
    with sqlite3.connect(DATABASE_PATH) as conn:
        cur = conn.cursor()
        cur.executescript("""
            CREATE TABLE departments (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                location TEXT
            );
            CREATE TABLE employees (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                age INTEGER,
                department_id INTEGER,
                salary REAL,
                hire_date DATE,
                FOREIGN KEY (department_id) REFERENCES departments(id)
            );
        """)
        
        # Populate with sample data

        departments = [
            (1, "HR", "New York"),
            (2, "Engineering", "San Francisco"),
            (3, "Marketing", "Chicago"),
            (4, "Sales", "Los Angeles"),
            (5, "Finance", "Boston"),
        ]
        cur.executemany("INSERT INTO departments VALUES (?,?,?)", departments)
        
        # Generate 200 random employees

        first_names = ["John", "Jane", "Bob", "Alice", "Charlie", "Diana"]
        last_names = ["Smith", "Johnson", "Williams", "Brown", "Davis"]
        
        employees = []
        for i in range(1, 201):
            name = f"{random.choice(first_names)} {random.choice(last_names)}"
            age = random.randint(22, 65)
            dept = random.randint(1, 5)
            salary = round(random.uniform(40000, 200000), 2)
            hire = (datetime.now() - timedelta(days=random.randint(0, 3650))).strftime("%Y-%m-%d")
            employees.append((i, name, age, dept, salary, hire))
        
        cur.executemany("INSERT INTO employees VALUES (?,?,?,?,?,?)", employees)

```

## Extracting Database Schema for Context

Claude requires explicit schema knowledge to generate valid SQL. The notebook implements a `get_schema_info()` function (lines 77-97 in `guide.ipynb`) that introspects the SQLite database using `PRAGMA table_info` queries to build a human-readable schema representation.

This function connects to the database, retrieves all table names from `sqlite_master`, then iterates through each table to capture column names and data types. The output is formatted as structured text enclosed in XML-style tags within the prompt.

```python
def get_schema_info(db_path):
    """Extract schema information from SQLite database for Claude context."""
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    schema_info = []
    
    # Get all tables

    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    
    for (table_name,) in tables:
        cursor.execute(f"PRAGMA table_info({table_name})")
        columns = cursor.fetchall()
        
        table_info = f"Table: {table_name}\n"
        table_info += "\n".join(f"  - {col[1]} ({col[2]})" for col in columns)
        schema_info.append(table_info)
    
    conn.close()
    return "\n\n".join(schema_info)

```

The resulting schema string includes table definitions, column types, and relationships, which the system embeds directly into the prompt's `<schema>` tags. This eliminates hallucinated table names and ensures Claude references the correct foreign key relationships between `employees` and `departments`.

## Prompt Engineering for Accurate SQL Generation

The `guide.ipynb` file demonstrates three distinct prompting strategies, each implemented as separate functions to handle increasing query complexity. All approaches wrap the schema in `<schema>` tags and the user question in `<query>` tags, requesting the final SQL inside `<sql>` tags.

### Basic Prompting

The foundational approach provides minimal instruction plus the schema. This works for simple `SELECT` statements but may produce verbose explanations or inconsistent formatting.

```python
def generate_prompt(schema, user_query):
    """Basic prompt without examples."""
    return f"""Given the following database schema:

<schema>
{schema}
</schema>

Convert this question into a SQL query:
<query>
{user_query}
</query>

Provide only the SQL inside <sql> tags."""

```

### Few-Shot Prompting

For improved reliability, the `generate_prompt_with_examples()` function (lines 54-90) injects concrete input-output pairs demonstrating the exact XML response format. This technique anchors the model to the desired structure and reduces extraneous commentary.

```python
def generate_prompt_with_examples(schema, user_query):
    """Few-shot prompt with concrete examples."""
    examples = """
Example 1:
Question: List all employees in the HR department.
SQL: SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'HR';

Example 2:
Question: What is the average salary of employees in Engineering?
SQL: SELECT AVG(e.salary) FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering';
"""
    
    return f"""You are an expert SQL generator. Given this schema:

<schema>
{schema}
</schema>

Here are examples of questions and their SQL queries:
<examples>
{examples}
</examples>

Now convert this question:
<query>
{user_query}
</query>

Provide only the SQL query inside <sql> tags."""

```

### Chain-of-Thought Reasoning

Complex queries involving multi-table joins, aggregation, and filtering require explicit reasoning. The `generate_prompt_with_cot()` function (lines 84-130) adds a `<thought_process>` tag to the output requirements, forcing Claude to explain its logic before generating the final SQL. This significantly reduces errors on queries like "names and hire dates of employees in Engineering, ordered by salary."

```python
def generate_prompt_with_cot(schema, user_query):
    """Chain-of-thought prompt for complex queries."""
    return f"""Given this database schema:

<schema>
{schema}
</schema>

Convert this question to SQL. First, think through your reasoning step-by-step, then provide the SQL.
<query>
{user_query}
</query>

Format your response as:
<thought_process>
Your reasoning here...
</thought_process>

<sql>
The SQL query here
</sql>"""

```

## Invoking the Claude API

The notebook uses the official `anthropic` Python client (defined in [`pyproject.toml`](https://github.com/anthropics/claude-cookbooks/blob/main/pyproject.toml)) to generate SQL with deterministic output. The `generate_sql()` function (lines 100-107) sets `temperature=0` to ensure reproducible results—a critical requirement for database operations where consistency matters more than creativity.

```python
import os
from anthropic import Anthropic

client = Anthropic(api_key=os.getenv("ANTHROPIC_API_KEY"))
MODEL_NAME = "claude-sonnet-4-6"

def generate_sql(prompt):
    """Generate SQL using Claude API with zero temperature for determinism."""
    response = client.messages.create(
        model=MODEL_NAME,
        max_tokens=1000,
        temperature=0,
        messages=[{"role": "user", "content": prompt}],
    )
    return response.content[0].text.strip()

```

The function deliberately uses `temperature=0` to minimize variance in SQL syntax generation. For production workloads, you might adjust `max_tokens` based on query complexity, though 1000 tokens suffices for most analytical queries against standard relational schemas.

## Parsing and Executing Generated SQL

Raw LLM output requires extraction before execution. The notebook implements simple string splitting (lines 112-122) to isolate the SQL from XML tags or explanatory text.

```python
def extract_sql(result):
    """Extract SQL from between <sql> tags."""
    try:
        sql = result.split("<sql>")[1].split("</sql>")[0].strip()
        return sql
    except IndexError:
        # Handle cases where tags might be missing or malformed

        return result.strip()

# Chain-of-thought extraction (optional)

def extract_thought(result):
    """Extract reasoning if using CoT prompting."""
    if "<thought_process>" in result:
        return result.split("<thought_process>")[1].split("</thought_process>")[0].strip()
    return None

```

Execution uses pandas for immediate DataFrame conversion, providing visual feedback that the generated SQL is both syntactically valid and semantically correct.

```python
import pandas as pd

def run_sql(sql, db_path=DATABASE_PATH):
    """Execute SQL and return results as DataFrame."""
    conn = sqlite3.connect(db_path)
    try:
        result = pd.read_sql_query(sql, conn)
        return result
    finally:
        conn.close()

# Complete workflow example

schema = get_schema_info(DATABASE_PATH)
prompt = generate_prompt_with_cot(schema, "Find the highest paid employee in each department")
raw_response = generate_sql(prompt)
sql_query = extract_sql(raw_response)
results_df = run_sql(sql_query)

print(f"Generated SQL: {sql_query}")
print(results_df.head())

```

## Security and Configuration Best Practices

The implementation enforces security through environment variable isolation. API keys for `ANTHROPIC_API_KEY` and `VOYAGE_API_KEY` are loaded via `os.getenv()` rather than hardcoded strings, preventing credential leakage in version control.

Never execute raw LLM-generated SQL directly on production databases without validation. The notebook’s SQLite implementation provides a sandbox, but production systems should implement:

- **Query whitelisting** for specific operation types (SELECT vs. INSERT/UPDATE/DELETE)
- **Read-only database connections** for analytics workflows
- **SQL parsing libraries** like `sqlparse` to detect potentially destructive operations

## Summary

- The **Claude Cookbooks** repository provides a complete text-to-SQL implementation in `capabilities/text_to_sql/guide.ipynb`, demonstrating production-ready patterns for natural language database queries.
- **Schema extraction** via `get_schema_info()` automatically introspects SQLite databases using `PRAGMA` commands, providing Claude with accurate table and column metadata.
- **Three prompting strategies**—basic, few-shot, and chain-of-thought—offer progressive complexity handling, with CoT significantly improving accuracy on multi-join queries.
- **Deterministic generation** requires setting `temperature=0` in the `client.messages.create()` call to ensure consistent SQL syntax across identical prompts.
- **Post-processing** involves splitting on `<sql>` tags to extract executable queries, followed by pandas `read_sql_query` for result validation.

## Frequently Asked Questions

### What makes Claude effective for text-to-SQL conversion?

Claude excels at text-to-SQL tasks due to its strong performance on structured reasoning and adherence to XML tag formatting. The model accurately interprets database schemas provided in context, handles complex join logic across multiple tables, and respects output format constraints when prompted with examples. According to the source implementation, Claude maintains high accuracy even on aggregation queries involving `GROUP BY` and `ORDER BY` clauses when provided with chain-of-thought reasoning space.

### How does the schema extraction function handle complex databases?

The `get_schema_info()` function in `guide.ipynb` uses SQLite's `sqlite_master` table to discover all user tables dynamically, then executes `PRAGMA table_info()` for each table to retrieve column names, data types, and nullability constraints. This approach works for any SQLite database regardless of table count or naming conventions. For PostgreSQL or MySQL adaptations, you would replace `sqlite_master` with `information_schema.tables` and use database-specific catalog queries while maintaining the same string formatting logic for the prompt context.

### Can this workflow handle database write operations or only SELECT statements?

The current implementation focuses on read-only analytics via `pd.read_sql_query()`, but the architecture supports INSERT, UPDATE, or DELETE operations if you modify the execution layer. However, the notebook explicitly uses SQLite and read-only connections as a safety measure. For production deployments supporting write operations, implement SQL injection prevention through prepared statements, query parsing to block dangerous keywords, and strict permission controls at the database connection level.

### Why is temperature set to zero in the API call?

The `temperature=0` parameter in the `client.messages.create()` call forces Claude to select the highest probability tokens at each generation step, eliminating randomness in SQL syntax generation. This determinism is crucial for database operations where the same natural language question should always produce identical SQL output. Higher temperatures might introduce syntactic variations like optional `AS` keywords or different alias naming conventions that could break downstream automation or validation logic.