Back to Details

text-to-sql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese
Mode: Cognitive/Prompt-Driven — No standalone utility script; use via agent context.
模式：认知/提示驱动 — 无独立实用脚本；需通过Agent上下文调用。

Text-to-SQL Skill

Text-to-SQL 技能

Identity

技能说明

Text-to-SQL - Converts natural language queries to SQL using database schema context and query patterns.
Text-to-SQL - 借助数据库架构上下文和查询模式，将自然语言查询转换为SQL语句。

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
  • 查询生成：将自然语言转换为SQL语句
  • 架构感知：利用数据库架构生成精准查询语句
  • 查询优化：生成经过优化的SQL查询语句
  • 参数化查询：生成安全的参数化查询语句

Usage

使用方法

Basic SQL Generation

基础SQL生成

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
适用场景:
  • 通过自然语言生成数据库查询语句
  • 数据分析需求
  • 报表查询需求
  • 临时数据库查询需求
调用方式:
"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"
执行逻辑:
  • 分析自然语言查询内容
  • 参考数据库架构
  • 生成SQL查询语句
  • 验证查询语句语法
  • 返回参数化查询语句

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
架构集成:
  • 加载数据库架构
  • 理解表间关系
  • 运用列类型与约束
  • 处理关联查询与聚合操作
查询优化:
  • 生成高效查询语句
  • 运用合适的索引
  • 优化关联查询
  • 减少数据传输量
安全性:
  • 参数化查询（防止SQL注入）
  • 验证查询语句语法
  • 基于示例数据测试
  • 错误处理

Best Practices

最佳实践

  1. Schema Context: Provide complete database schema
  2. Query Validation: Validate SQL before execution
  3. Parameterization: Always use parameterized queries
  4. Testing: Test queries on sample data
  5. Optimization: Review query performance
  1. 架构上下文：提供完整的数据库架构
  2. 查询验证：执行前验证SQL语句
  3. 参数化：始终使用参数化查询
  4. 测试：基于示例数据测试查询语句
  5. 优化：检查查询性能

Integration

集成方式

With Database Architect

与Database Architect集成

Text-to-SQL uses schema from database-architect:
  • Table definitions
  • Relationships
  • Constraints
  • Indexes
Text-to-SQL 调用database-architect提供的架构信息：
  • 表定义
  • 表间关系
  • 约束条件
  • 索引

With Developer

与Developer集成

Text-to-SQL generates queries for developers:
  • Query templates
  • Parameterized queries
  • Query optimization
  • Error handling
Text-to-SQL 为开发者生成查询语句：
  • 查询模板
  • 参数化查询语句
  • 查询优化
  • 错误处理

Examples

示例

Example 1: Simple Query

示例1：简单查询

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
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

示例2：复杂查询

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
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
).
基于Claude Cookbooks的模式，Text-to-SQL的评估包含以下内容：
语法验证:
  • SQL语法正确性
  • 架构合规性
  • 查询结构验证
功能测试:
  • 在测试数据库上执行查询
  • 结果正确性
  • 性能验证
与Promptfoo集成:
  • 多种提示词变体（基础型、少样本型、思维链型、RAG型）
  • 温度参数调优
  • 模型对比（Haiku与Sonnet）
评估配置: 为你的评估环境创建一个promptfoo配置文件（例如：
text_to_sql_config.yaml
）。

Running Evaluations

运行评估

bash
undefined
bash
undefined

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

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

npx promptfoo@latest eval -c text_to_sql_config.yaml
undefined
npx promptfoo@latest eval -c text_to_sql_config.yaml
undefined

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
  • 语法准确率：拥有有效SQL语法的查询占比
  • 功能正确率：返回正确结果的查询占比
  • 架构合规率：使用正确架构的查询占比
  • 性能：查询执行时间与优化程度

Best Practices from Cookbooks

Cookbooks中的最佳实践

1. Provide Schema Context

1. 提供架构上下文

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

2. 使用少样本示例

Provide examples of similar queries:
  • Simple queries
  • Complex queries with joins
  • Aggregation queries
  • Subquery patterns
提供相似查询的示例：
  • 简单查询
  • 带关联的复杂查询
  • 聚合查询
  • 子查询模式

3. Chain-of-Thought for Complex Queries

3. 复杂查询使用思维链

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
对于复杂查询，使用思维链推理：
  • 将查询拆分为多个步骤
  • 确定所需的表
  • 规划关联与聚合操作
  • 逐步生成SQL语句

4. RAG for Schema Understanding

4. 使用RAG理解架构

Use RAG to retrieve relevant schema information:
  • Find relevant tables for query
  • Understand relationships
  • Get column details
  • Retrieve query patterns
使用RAG检索相关架构信息：
  • 找到与查询相关的表
  • 理解表间关系
  • 获取列详情
  • 检索查询模式

Related Skills

相关技能

  • classifier: Classify database queries
  • database-architect: Use for schema design
  • developer: Generate query code
  • classifier：对数据库查询进行分类
  • database-architect：用于架构设计
  • developer：生成查询代码

Related Documentation

相关文档