PaperlayerView exact revision on arXiv

Source-linked AI summary

Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL

Zijin Hong, Zheng Yuan, Qinggang Zhang, Hao Chen, Junnan Dong, Feiran Huang, Xiao Huang

arXiv:2406.08426v8cs.CLcs.AIcs.DB

TL;DR

Text-to-SQL must handle difficult language, schemas, and SQL operations while remaining accurate in complex databases and real-world settings. This survey synthesizes the field’s evolution, benchmarks, metrics, and LLM-based methods through a systematic taxonomy, then identifies remaining challenges and future directions.

  • Problem

    Text-to-SQL remains challenging because accurate SQL generation requires understanding natural-language questions, database schemas, and complex SQL operations.

  • Method

    The survey reviews text-to-SQL evolution, benchmarks and metrics, and LLM-based methods including in-context learning and fine-tuning through a systematic taxonomy.

  • Results

    The survey identifies robustness, real-world deployment, computational efficiency, data privacy, domain adaptation, and extensions as remaining challenges and future research directions.

  • Takeaways & Limitations

    The survey provides a structured account of LLM-based text-to-SQL research and actionable directions for robustness, deployment, efficiency, domain adaptation, and extensions.

Abstract

from arXiv · show

Generating accurate SQL from users' natural language questions (text-to-SQL) remains a long-standing challenge due to the complexities involved in user question understanding, database schema comprehension, and SQL generation. Traditional text-to-SQL systems, which combine human engineering and deep neural networks, have made significant progress. Subsequently, pre-trained language models (PLMs) have been developed for text-to-SQL tasks, achieving promising results. However, as modern databases and user questions grow more complex, PLMs with a limited parameter size often produce incorrect SQL. This necessitates more sophisticated and tailored optimization methods, which restricts the application of PLM-based systems. Recently, large language models (LLMs) have shown significant capabilities in natural language understanding as model scale increases. Thus, integrating LLM-based solutions can bring unique opportunities, improvements, and solutions to text-to-SQL research. In this survey, we provide a comprehensive review of existing LLM-based text-to-SQL studies. Specifically, we offer a brief overview of the technical challenges and evolutionary process of text-to-SQL. Next, we introduce the datasets and metrics designed to evaluate text-to-SQL systems. Subsequently, we present a systematic analysis of recent advances in LLM-based text-to-SQL. Finally, we make a summarization and discuss the remaining challenges in this field and suggest expectations for future research directions. All the related resources of LLM-based, including research papers, benchmarks, and open-source projects, are collected for the community in our repository: https://github.com/DEEP-PolyU/Awesome-LLM-based-Text2SQL.

I. INTRODUCTION

The survey reviews the evolution, evaluation resources, methods, and open challenges of LLM-based text-to-SQL. It organizes recent work through a systematic taxonomy spanning implementation paradigms and research directions.

  • Task and motivation: Text-to-SQL converts natural-language questions into executable SQL queries, enabling database access through a natural-language interface.The survey illustrates this process with an LLM receiving a question and database schema, then generating an executable query.
  • Evolution: The field has evolved from rule-based systems through deep learning and PLMs toward LLM-integrated text-to-SQL approaches.Rule and template design became increasingly difficult as database environments grew more complex, motivating later paradigms.
  • Survey scope: The survey introduces benchmarks and metrics while discussing dataset characteristics, complexity, and evaluation challenges for LLM-based text-to-SQL.
  • Survey scope: It systematically analyzes LLM-based methods, including in-context learning and fine-tuning, with attention to implementation details, strengths, and adaptations.
  • Challenges and expectations: It summarizes advantages and limitations, then discusses challenges in robustness, real-world deployment, efficiency, domain adaptation, privacy, and future extensions.The survey also outlines future research directions and opportunities for improvement.
  • Technical contributions: The survey presents a systematic taxonomy of LLM-based text-to-SQL methods and analyzes trade-offs between LLM-based and traditional approaches.It identifies the taxonomy as the first LLM-based text-to-SQL survey contribution at the time of release.

II. OVERVIEW

Text-to-SQL seeks to make relational databases accessible through natural-language questions, but robust generation remains difficult. Key obstacles include linguistic ambiguity, complex schemas, rare SQL operations, and demanding real-world settings.

  • Task: Text-to-SQL converts natural-language questions into executable SQL queries, potentially democratizing database access for non-specialized users.The paper identifies business intelligence, customer support, and scientific research as potential application domains.
  • Question understanding: Nested clauses, coreferences, ellipses, and linguistic ambiguity make it difficult to map user questions accurately to SQL.
  • Schema comprehension: Accurate generation requires comprehensive schema understanding, but table names, columns, and relationships vary across complex domains.
  • Rare and complex SQL operations: Rare operations such as nested subqueries, outer joins, and window functions are challenging because they occur less frequently in training data.The paper emphasizes generalization across a wide range of SQL operations, including these complex cases.

4) Cross-Domain Generalization:

Text-to-SQL systems face persistent cross-domain generalization challenges as database schemas, vocabulary, and question patterns vary across domains. The survey traces this challenge from earlier neural and PLM approaches toward LLM-based methods while identifying ongoing opportunities for improvement.

  • Cross-Domain Generalization: Models trained on one domain may perform poorly on questions from other domains because vocabulary, schema structure, and question patterns vary.The survey identifies adaptation to new domains with minimal domain-specific training or fine-tuning as a significant challenge.
  • Deep Learning-based Approaches: Deep learning approaches advanced text-to-SQL by learning mappings from natural language questions to SQL queries, but structural errors remain.Reported errors include missing clauses and improper nesting, while rare operations such as nested subqueries and window functions are difficult to generate accurately.
  • LLM-based Implementation: PLMs use pre-trained linguistic and semantic knowledge, whereas LLM-based approaches use prompting or fine-tuning to generate SQL.The survey contrasts fine-tuning off-the-shelf PLMs with prompt engineering for proprietary LLMs and fine-tuning open-source LLMs.
  • LLM-based Implementation: LLM-based text-to-SQL remains an emerging area, with research exploring stronger use of reasoning, domain knowledge, and efficient fine-tuning.The survey anticipates further improvements in LLM-based implementation and generalization.

A. Datasets

Text-to-SQL datasets are organized by how they were created and annotated, while incorporating cross-domain, domain-specific, conversational, robustness, and multilingual settings. These variations target increasingly realistic sources of complexity for development and evaluation.

  • Datasets: Datasets are categorized as original or post-annotated according to whether they were released with new databases or adapted from existing datasets and databases.Table I is ordered by release time and records dataset statistics, settings, and annotations.
  • Cross-domain Dataset: Most datasets use cross-domain databases because real-world applications commonly span multiple domains.Cross-domain datasets contain databases whose background information comes from various domains.
  • Specialized-domain Dataset: Domain-specific benchmarks add specialized knowledge or target sectors such as finance to address domain-specific text-to-SQL challenges.BULL focuses on funds, stocks, and macroeconomic data, while BIRD and Spider-DK add human-curated domain knowledge.
  • Conversational Dataset: Conversational datasets model context-dependent querying through related and unrelated sub-questions or natural-language interactions.SParC decomposes Spider examples into multiple sub-question–SQL pairs, whereas CoSQL simulates conversational interactions.
  • Robustness Dataset: Robustness datasets perturb schemas, tables, or question wording to test performance under polluted or altered database contents.Examples remove schema words, substitute synonyms, or insert misleading columns and rename existing columns.
  • Multilingual Dataset: Multilingual datasets translate text-to-SQL settings into languages including Chinese and Vietnamese while retaining English or bilingual database contents.These datasets expose challenges in word segmentation and cross-lingual matching.

6) Long-context dataset:

Long-context and specialized-domain benchmarks extend text-to-SQL evaluation toward enterprise-scale queries and financial analysis. Their evaluation uses both SQL-content metrics and execution-based measures that assess correctness and, for VES, efficiency.

  • Long-context Dataset: Spider 2.0 presents enterprise-level text-to-SQL problems with queries often exceeding 100 tokens and requiring reasoning across SQL queries and dialects.Its tasks are sourced from diverse production databases and involve multiple intricate operations.
  • Specialized-domain Dataset: BULL supplies a financial-sector benchmark covering funds, stocks, and macroeconomic databases.It addresses the shortage of specialized datasets for domain-specific analysis.
  • Content-based Metrics: Component Matching and Exact Matching evaluate predicted SQL against ground-truth SQL content and structure.CM compares SELECT, WHERE, GROUP BY, ORDER BY, and keyword components using F1, while EM requires the complete query to match exactly.
  • Execution-based Metrics: Execution Accuracy compares the results of predicted and ground-truth SQL queries after execution on the target database.Execution-based metrics assess query correctness through returned results rather than only SQL-form similarity.
  • Execution-based Metrics: Valid Efficiency Score evaluates valid predictions using both result correctness and relative execution efficiency.VES applies an indicator for matching executed results and compares predicted execution time with ground-truth execution time; BIRD averages the efficiency term over 100 runs.

IV. METHODS

LLM-based text-to-SQL methods primarily use in-context learning or fine-tuning, with ICL relying on frozen models and prompt construction. The survey organizes ICL methods into vanilla prompting and four further strategy categories.

  • Current LLM-based text-to-SQL methods mainly rely on in-context learning and fine-tuning paradigms.
  • In-context learning generates SQL with a frozen, off-the-shelf LLM from instructions, user questions, database schema or content, and supplementary knowledge.Supplementary knowledge can include foreign-key relationships, schema linking, and external knowledge.
  • Zero-shot prompting concatenates task instructions, database schema or content, and the user question into the overall input.
  • Few-shot prompting extends the zero-shot input with k examples, each containing schema, question, and gold SQL.
  • The survey groups ICL methods into vanilla prompting, decomposition, prompt optimization, reasoning enhancement, and execution refinement.The representative methods are summarized in Tables II and III.

1) C0-Vanilla Prompting:

Vanilla prompting studies evaluate zero-shot and few-shot prompting without proposing a specialized ICL framework. Their findings emphasize prompt representation, database content, and demonstration selection as important factors in text-to-SQL performance.

  • C0-Vanilla Prompting: Vanilla prompting comprises zero-shot and few-shot strategies that use simple prompting without a specialized ICL framework.
  • Zero-shot studies: Prompt design is critical for zero-shot text-to-SQL performance, while including more database content can reduce overall accuracy.
  • Zero-shot studies: Table structure, content, prompt length, and representation choices are highlighted as crucial factors in effective prompting.
  • Few-shot studies: Few-shot prompting produces solid improvements over zero-shot prompting across multiple datasets and LLMs.
  • Few-shot studies: Few-shot research studies example number and selection, including similarity, diversity, difficulty level, domain alignment, and hybrid sampling strategies.The survey’s ICL methods and datasets are cataloged in Table III.

2) C1-Decomposition:

Decomposition-based methods reduce text-to-SQL complexity by dividing questions or processing into manageable components. Related approaches use module collaboration, metadata conditioning, schema linking, structured reasoning, and execution feedback.

  • C1-Decomposition: Decomposition methods reduce text-to-SQL complexity by dividing tasks or questions into manageable components.
  • C1-Decomposition: Multi-module collaboration assigns stages such as schema linking, SQL generation, and SQL refinement to distinct modules or agents.
  • C1-Decomposition: Metadata-driven methods decompose questions through metadata conditioning and task-aligned schema linking.
  • C2-Prompt Optimization: Prompt optimization improves example selection using semantic similarity, diversity, or domain alignment, including retrieval-augmented strategies.
  • C3-Reasoning Enhancement: Reasoning enhancement uses structured intermediate steps, candidate diversity, and agreement mechanisms to address complex SQL generation.Unlike decomposition, these methods retain single-turn generation while strengthening intrinsic reasoning.
  • C4-Execution Refinement: Execution refinement uses database feedback to regenerate SQL or select among candidates.Methods include error-driven correction, execution-based pruning, voting, and probabilistic verification.

2. Execution-guided

Fine-tuning adapts open-source LLMs for text-to-SQL by tuning model parameters, while architecture and pre-training modifications address SQL structure and domain knowledge. Execution-guided methods use database results to refine or rank generated queries.

  • Execution-guided: Execution-guided methods refine SQL through failed-execution feedback or select candidates using execution results and verification.
  • Fine-tuning: Supervised fine-tuning is a widely used approach for adapting open-source LLMs to domain-specific text-to-SQL data.
  • Fine-tuning: Unlike in-context learning, fine-tuning updates the LLM parameters during training.
  • Fine-tuning: Fine-tuning is foundational for open-source text-to-SQL systems because open-source LLMs have comparatively limited capabilities on complex database content.
  • Enhanced Architecture: Tailored architectures modify the transformer backbone to handle SQL’s structural and syntactic complexities, while specialized decoding can reduce generation latency.
  • Enhanced Architecture: Table IV organizes well-designed fine-tuning methods by category and release time, presenting a representative model when methods use multiple open-source LLMs.
  • Pre-training: Code- and SQL-specific pre-training supplies programming-language or database-related data to improve code and SQL generation capabilities.CodeS uses three stages of incremental pre-training on a hybrid corpus including SQL-related and natural-language-to-code data.

3) Data Augmentation:

Data augmentation and related training strategies improve LLM-based text-to-SQL by increasing data quality, efficiency, and task coverage, while multi-stage designs address complex subtasks.

  • Data Augmentation: High-quality or augmented training data consistently outperforms low-quality or raw data in fine-tuning.The survey identifies training-label quality as a direct performance factor.
  • Data Augmentation: DAIL-SQL samples stronger few-shot instances and incorporates them into supervised fine-tuning to boost open-source LLM performance.
  • Data Augmentation: Recent methods augment or structure training data through bidirectional generation, structured-knowledge tasks, concise prompts, and failure-aware error analysis.CodeS, StructLM, Dubo-SQL, and Distillery represent distinct data-construction strategies.
  • Data Augmentation: Decomposing complex text-to-SQL into subtasks or multiple models can manage complexity, although proprietary ICL models have greater parameter scale than fine-tuned open-source models.Few-shot learning helps these models perform assigned subtasks effectively.

2) Schema Understanding and Representation:

LLM-based text-to-SQL methods improve schema linking and complex-query handling through natural-language schema prompts, structured attention, demonstrations, and multi-stage task designs, but face trade-offs in generalization and resources.

  • Schema Understanding and Representation: LLM-based schema linking uses in-context learning and structured attention to improve identification of relevant database elements.
  • Schema Understanding and Representation: DIN-SQL serializes schemas into natural-language prompts with foreign-key hints, while KaSLA prioritizes schema elements to reduce omissions and redundancies.
  • Schema Understanding and Representation: Few-shot demonstrations help LLMs generate rare SQL operations, including window functions, beyond manually defined grammar rules.DAIL-SQL uses examples of window functions to generalize to unseen formulations.
  • Schema Understanding and Representation: Decomposition manages complex queries through modularization, whereas prompt optimization improves semantic alignment and reasoning enhancement improves logical coherence.The survey notes cascading-error risks for multi-stage decomposition and reasoning limitations for prompt optimization.
  • Schema Understanding and Representation: Fine-tuning approaches trade specialization and generalization, while data augmentation risks synthetic-pattern overfitting and multi-task tuning complicates convergence.

VI. EXPECTATIONS

Future text-to-SQL research must improve real-world robustness, computational efficiency, privacy, and interpretability while extending execution-aware and database-structured methods to related tasks.

  • VI. EXPECTATIONS: Current text-to-SQL performance remains below practical application needs, especially on robustness-specific and real-world evaluations.
  • VI. EXPECTATIONS: 40% of ChatGPT-generated SQL queries contain incorrect execution, while current state-of-the-art models solve only 21.3% of real-world enterprise tasks.
  • VI. EXPECTATIONS: Ultra-complex schemas increase token lengths, API costs, and context-length pressure, motivating accurate question-related schema filtering.Using the full schema also introduces significant redundancy.
  • VI. EXPECTATIONS: Proprietary APIs can risk data leakage for confidential local databases, while local fine-tuning currently has imperfect performance and interpretability remains a broader deep-learning challenge.
  • VI. EXPECTATIONS: Execution-aware text-to-SQL approaches may extend to code generation, while database-structured factual knowledge may support more accurate knowledge-based question answering.
Loading 2406.08426v8…