Text2SQL-CoT: Optimizing Large Language Models' Text-to-SQL Conversion via Chain-of-Thought Prompt Engineering

This article introduces the GitHub project text2sql-cot (author: rievanaverilllio, last updated on May 23, 2026). The project optimizes the Text-to-SQL conversion process of large language models using Chain-of-Thought (CoT) prompt engineering technology. By combining SPLADE retrieval, Schema graph indexing, and a query understanding pipeline, it achieves more accurate conversion from natural language to SQL queries.

Core Challenges of Text-to-SQL

Converting natural language questions into executable SQL queries is a long-standing challenge in the database and AI fields. Traditional methods rely on complex rule engines and feature engineering, while large language models (LLMs) generating SQL directly face issues such as insufficient accuracy, difficulty handling complex multi-table associations, and inadequate depth of Schema understanding.

Value of CoT Prompt Engineering

Chain-of-Thought (CoT) technology significantly improves LLMs' performance in complex tasks by guiding the model to reason step by step. The text2sql-cot project builds a complete optimization framework based on this.

Query understanding is the core module of the system, including the following steps:

• Table Pre-selection: LLM analyzes the query and returns relevant tables to reduce subsequent computational overhead;
• SPLADE Retrieval: Encodes the query and Schema elements into sparse vectors to achieve semantic matching and generate candidate columns;
• Metadata Construction: Loads column metadata (type, description, examples) and uses the Schema graph to generate inter-table connection prompts;
• LLM Column Selection and Parsing: Selects columns via structured prompts, and falls back to SPLADE candidates if parsing fails, ensuring robustness.

The offline preprocessing process includes:

• Schema Database Construction: Extracts table structures, primary/foreign key relationships, and builds a structured Schema database;
• Schema Description Vectorization: Converts table/column descriptions into SPLADE-indexable documents;
• Graph Index Construction: Analyzes foreign key relationships to generate a Schema graph, assisting in connection prompt generation.

Inference and Evaluation Framework

The project integrates LLM calls, SPLADE retrieval, and logging (logs support debugging and auditing). Error analysis summarizes common issues to guide targeted optimizations.

Technical Innovations

• Combining sparse retrieval (SPLADE) with semantic expansion;
• Structured CoT prompt engineering to guide step-by-step reasoning;
• Modular design allowing replacement of retrieval models or LLMs;
• Multi-stage fallback mechanisms to ensure system robustness.

text2sql-cot is applicable to:

• Enterprise Data Analysis: Non-technical users query data warehouses via natural language;
• Database Tools: Intelligent query assistance and Schema exploration;
• Data Exploration Platforms: Quickly understanding unfamiliar database structures;
• Educational Tools: Helping SQL learners understand the mapping from natural language to queries.

Project Summary

text2sql-cot effectively improves the accuracy of Text-to-SQL tasks through CoT prompt engineering, hybrid retrieval technology, and structured Schema understanding, providing developers with a reference architecture template.

Future Directions

• Support complex queries (nested, aggregation, window functions);
• Introduce query execution feedback for online learning;
• Extend to multi-turn dialogue scenarios;
• Integrate more LLMs and retrieval models for comparative experiments.