Mode: Cognitive/Prompt-Driven — No standalone utility script; use via agent context.

Text-to-SQL Skill

Identity

Text-to-SQL - Converts natural language queries to SQL using database schema context and query patterns.

Capabilities

• Query Generation: Convert natural language to SQL

• Schema Awareness: Uses database schema for accurate queries

• Query Optimization: Generates optimized SQL queries

• Parameterized Queries: Creates safe, parameterized queries

Usage

Basic SQL Generation

When to Use:

• Database queries from natural language

• Data analysis requests

• Reporting queries

• Ad-hoc database queries

How to Invoke:

"Generate SQL to find all users who signed up in the last month" "Create a query to calculate total revenue by product" "Write SQL to find duplicate records"

What It Does:

• Analyzes natural language query

• References database schema

• Generates SQL query

• Validates query syntax

• Returns parameterized query

Advanced Features

Schema Integration:

• Loads database schema

• Understands table relationships

• Uses column types and constraints

• Handles joins and aggregations

Query Optimization:

• Generates efficient queries

• Uses appropriate indexes

• Optimizes joins

• Minimizes data transfer

Safety:

• Parameterized queries (prevents SQL injection)

• Validates query syntax

• Tests on sample data

• Error handling

Best Practices

• Schema Context: Provide complete database schema

• Query Validation: Validate SQL before execution

• Parameterization: Always use parameterized queries

• Testing: Test queries on sample data

• Optimization: Review query performance

Integration

With Database Architect

Text-to-SQL uses schema from database-architect:

• Table definitions

• Relationships

• Constraints

• Indexes

With Developer

Text-to-SQL generates queries for developers:

• Query templates

• Parameterized queries

• Query optimization

• Error handling

Examples

Example 1: Simple Query

User: "Find all users who signed up in the last month"

Text-to-SQL:

1. Analyzes query
2. References users table schema
3. Generates SQL: SELECT * FROM users WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
4. Returns parameterized query

Example 2: Complex Query

User: "Calculate total revenue by product for Q4"

Text-to-SQL:

1. Analyzes query
2. References orders and products tables
3. Generates SQL: SELECT p.name, SUM(o.total) as revenue FROM orders o JOIN products p ON o.product_id = p.id WHERE o.created_at >= '2024-10-01' AND o.created_at < '2025-01-01' GROUP BY p.id, p.name
4. Returns optimized query

Evaluation

Evaluation Framework

Based on Claude Cookbooks patterns, text-to-SQL evaluation includes:

Syntax Validation:

• SQL syntax correctness

• Schema compliance

• Query structure validation

Functional Testing:

• Query execution on test database

• Result correctness

• Performance validation

Promptfoo Integration:

• Multiple prompt variants (basic, few-shot, chain-of-thought, RAG)

• Temperature sweeps

• Model comparisons (Haiku vs Sonnet)

Evaluation Configuration: Create a promptfoo config file for your evaluation setup (e.g., text_to_sql_config.yaml ).

Running Evaluations

Run text-to-SQL evaluation (create config first)

npx promptfoo@latest eval -c text_to_sql_config.yaml

Evaluation Metrics

• Syntax Accuracy: Percentage of queries with valid SQL syntax

• Functional Correctness: Percentage of queries returning correct results

• Schema Compliance: Percentage of queries using correct schema

• Performance: Query execution time and optimization

Best Practices from Cookbooks

1. Provide Schema Context

Always include complete database schema:

• Table definitions with column types

• Relationships and foreign keys

• Constraints and indexes

• Sample data patterns

2. Use Few-Shot Examples

Provide examples of similar queries:

• Simple queries

• Complex queries with joins

• Aggregation queries

• Subquery patterns

3. Chain-of-Thought for Complex Queries

For complex queries, use chain-of-thought reasoning:

• Break down query into steps

• Identify required tables

• Plan joins and aggregations

• Generate SQL step by step

4. RAG for Schema Understanding

Use RAG to retrieve relevant schema information:

• Find relevant tables for query

• Understand relationships

• Get column details

• Retrieve query patterns

Related Skills

• classifier: Classify database queries

• database-architect: Use for schema design

• developer: Generate query code

Related Documentation

• Classification Patterns - Classification guide

• Evaluation Guide - Comprehensive evaluation

• Claude Cookbooks - Text-to-SQL

Iron Laws

• ALWAYS validate all table and column names against the provided schema before generating SQL

• NEVER use string interpolation for query values — parameterized queries are mandatory without exception

• ALWAYS apply a LIMIT clause (default 100) to SELECT queries unless the user explicitly overrides it

• NEVER execute DROP, DELETE, TRUNCATE, or UPDATE statements without explicit user confirmation

• ALWAYS explain the generated query logic in plain language so the user understands what will execute

Anti-Patterns

Anti-Pattern Why It Fails Correct Approach

String interpolation for values SQL injection vulnerability Use parameterized queries with ? or $N placeholders

No LIMIT clause on SELECT Returns all rows, risk of OOM and timeout Default LIMIT 100 , require explicit user override

Destructive SQL without confirmation Irreversible data loss Gate DROP/DELETE/TRUNCATE behind user confirmation

No schema validation References non-existent tables or columns Validate all identifiers against the provided schema

SELECT * without column list Unpredictable results and performance waste Always specify an explicit column list

Memory Protocol (MANDATORY)

Before starting: Read .claude/context/memory/learnings.md

After completing:

• New pattern -> .claude/context/memory/learnings.md

• Issue found -> .claude/context/memory/issues.md

• Decision made -> .claude/context/memory/decisions.md