Building a Text-to-SQL Agent for Complex Database Queries

This chapter explores the design, implementation, and optimization of an agentic workflow capable of translating natural language queries into syntactically correct SQL, executing those queries against a relational database, and synthesizing the raw results back into human-readable natural language.

1. The Paradigm Shift: Natural Language as the Query Interface

In traditional software systems, interacting with relational databases requires technical expertise. Users must understand structured query languages (SQL), database schemas, table relationships, and join conditions. This creates a barrier for non-technical stakeholders who need direct access to data insights.

The emergence of Large Language Models (LLMs) has enabled a paradigm shift: Natural Language Interfaces to Databases (NLIDB). By constructing an agentic workflow, we can abstract the database layer entirely. The user communicates in plain English, and the agent assumes the responsibility of schema comprehension, query generation, execution, and response synthesis.

2. Core Mental Model & High-Level Architecture

The agentic workflow operates as a closed-loop system. It does not merely translate text to SQL; it acts as an intermediary that coordinates schema retrieval, query generation, execution, and final translation.

The Step-by-Step Execution Flow

3. Database Setup and Schema Inspection

To build a reliable Text-to-SQL agent, the system must have access to a database and a mechanism to inspect its schema. In this implementation, we use the Chinook Database, a standard sample database representing a digital media store (containing tables for artists, albums, media tracks, invoices, and customers).

We leverage LangChain's SQLDatabase utility to establish a connection and retrieve metadata.

from langchain_community.utilities import SQLDatabase

# Establish connection to the local SQLite database
db_uri = "sqlite:///Chinook.db"
db = SQLDatabase.from_uri(db_uri)

def get_schema(_=None):
    """
    Retrieves the complete schema information of the database.
    This includes table names, column names, data types, and foreign key relationships.
    """
    return db.get_table_info()

Understanding the Schema Relationships

The Chinook database contains complex relationships that require multi-level joins to resolve. For example:

• Artist Table: Contains artist metadata.
  • ArtistId (Primary Key)
  • Name
• Album Table: Linked to the Artist table.
  • AlbumId (Primary Key)
  • Title
  • ArtistId (Foreign Key referencing Artist.ArtistId)
• Track Table: Linked to the Album table.
  • TrackId (Primary Key)
  • Name
  • AlbumId (Foreign Key referencing Album.AlbumId)
  • GenreId (Foreign Key referencing Genre.GenreId)

4. Mitigating Hallucinations: The Debugging Wrapper Pattern

When LLMs generate SQL queries, they are prone to hallucinations—such as inventing non-existent columns, hallucinating table names, or producing syntax errors.

To debug and optimize the agent, we implement a custom wrapper around the database execution engine. This wrapper intercepts the generated SQL query, prints it to the console for developer inspection, and executes it.

def run_query(query: str) -> str:
    """
    Executes the generated SQL query against the database.
    Prints the query to the console to facilitate debugging and trace hallucinations.
    """
    # Clean the query string to ensure no trailing whitespaces or markdown formatting blocks
    cleaned_query = query.strip().replace("```sql", "").replace("```", "")
    
    print("\n--- [DEBUG] EXECUTING SQL QUERY ---")
    print(cleaned_query)
    print("-----------------------------------\n")
    
    try:
        return db.run(cleaned_query)
    except Exception as e:
        return f"Error executing query: {str(e)}"

Note: Printing the SQL query before execution is a critical design pattern in production AI engineering. It allows developers to identify exactly where a model's spatial reasoning or schema understanding fails, making it easier to refine prompt templates or select more capable models.

5. Model Flexibility: Switching Between Open-Source (Qwen) and Proprietary (GPT-4)

A robust agentic architecture should be model-agnostic. Depending on the environment (e.g., local prototyping vs. production enterprise environments), you may want to switch between open-source models hosted on Hugging Face and proprietary APIs like OpenAI's GPT-4.

• Open-Source (e.g., Qwen-2.5-7B-Instruct): Free to run, highly customizable, and excellent for local prototyping without incurring API costs. Hugging Face acts as the central repository (the "GitHub of Machine Learning") for hosting and serving these models.
• Proprietary (e.g., GPT-4): Paid API, highly optimized for complex reasoning, multi-table joins, and strict adherence to formatting instructions.

We implement a helper function to toggle between these models using a single boolean flag:

from langchain_openai import ChatOpenAI
from langchain_community.llms import HuggingFaceEndpoint

def get_llm(load_from_hugging_face: bool = False):
    """
    Returns an LLM instance based on the configuration flag.
    Demonstrates seamless model switching with a single line of code.
    """
    if load_from_hugging_face:
        # Load Qwen-2.5-7B-Instruct from Hugging Face (Free Open-Source Prototyping)
        print("[INFO] Loading Open-Source Qwen Model from Hugging Face...")
        return HuggingFaceEndpoint(
            repo_id="Qwen/Qwen2.5-7B-Instruct",
            temperature=0.1,
            max_new_tokens=512
        )
    else:
        # Load OpenAI GPT-4 (Paid Proprietary Production Model)
        print("[INFO] Loading OpenAI GPT-4 Model...")
        return ChatOpenAI(
            model="gpt-4",
            temperature=0
        )

6. Step 1 of the Agentic Workflow: Translating Natural Language to SQL

The first active node in our agentic workflow is the SQL Generator. This component takes the user's natural language question and the database schema, then outputs a raw SQL query.

Prompt Engineering for Strict SQL Generation

Because the output of this step is fed directly into a database execution engine, the model must return only the raw SQL string. Any conversational filler (e.g., "Here is your SQL query:"), markdown formatting (e.g., ```sql blocks), or explanatory text will cause a syntax error in the database engine.

from langchain_core.prompts import ChatPromptTemplate

# Define the prompt template for SQL generation
sql_generation_prompt = ChatPromptTemplate.from_messages([
    ("system", (
        "You are a precise SQL generator. Given an input question, convert it to a syntactically correct SQL query. "
        "Strictly follow these rules:\n"
        "1. Do NOT return any preamble or explanation.\n"
        "2. Do NOT wrap the query in markdown code blocks (e.g., do not use ```sql).\n"
        "3. Return ONLY the raw SQL query string.\n"
        "4. Use only the tables and columns present in the provided schema."
    )),
    ("human", (
        "Based on the database schema below, write a SQL query to answer the user's question.\n\n"
        "Schema:\n{schema}\n\n"
        "Question: {question}"
    ))
])

7. Step 2 of the Agentic Workflow: Synthesizing the Natural Language Response

Once the SQL query is executed, the raw database response (usually formatted as a list of tuples or a raw data matrix) must be translated back into a user-friendly, natural language format.

To do this, we construct a second prompt template that takes four inputs:

1. The database schema.
2. The user's original question.
3. The generated SQL query.
4. The raw SQL execution response.

# Define the prompt template for final response synthesis
response_synthesis_prompt = ChatPromptTemplate.from_messages([
    ("system", (
        "You are a helpful assistant. Given a user's question, the SQL query used to fetch the data, "
        "and the raw database response, synthesize a clear, natural language answer. "
        "Do not mention technical details like table names or SQL syntax in your final response unless asked."
    )),
    ("human", (
        "Schema:\n{schema}\n\n"
        "User Question: {question}\n\n"
        "Generated SQL Query: {sql_query}\n\n"
        "Raw Database Response: {sql_response}"
    ))
])

8. Constructing the End-to-End Chain

Using LangChain Expression Language (LCEL), we chain these components together. The workflow dynamically fetches the schema, generates the SQL query, executes it, and synthesizes the final response.

from langchain_core.runnables import RunnablePassthrough, RunnableLambda

def build_text_to_sql_agent(llm):
    """
    Constructs the complete Text-to-SQL agentic pipeline.
    """
    # 1. Define the SQL Generation Chain
    sql_chain = (
        RunnablePassthrough.assign(schema=get_schema)
        | sql_generation_prompt
        | llm
    )
    
    # 2. Define the Complete Workflow Chain
    full_chain = (
        RunnablePassthrough.assign(sql_query=sql_chain)
        | RunnablePassthrough.assign(
            schema=get_schema,
            sql_response=lambda x: run_query(x["sql_query"])
        )
        | response_synthesis_prompt
        | llm
    )
    
    return full_chain

9. End-to-End Execution & Complexity Evolution

To evaluate the agent's performance, we run it through a series of test cases, starting with simple queries and progressing to complex multi-table joins.

Test Case 1: Simple Single-Table Query

• User Query: "Give me the names of 10 artists"
• Target SQL: SELECT Name FROM Artist LIMIT 10;

# Initialize the agent with GPT-4
agent = build_text_to_sql_agent(get_llm(load_from_hugging_face=False))

# Run the query
response = agent.invoke({"question": "Give me the names of 10 artists"})
print(response.content)

Debug Output & Execution Trace

Test Case 2: Multi-Field Single-Table Query

• User Query: "Give me the name and artist ID of 10 artists"
• Target SQL: SELECT Name, ArtistId FROM Artist LIMIT 10;

response = agent.invoke({"question": "Give me the name and artist ID of 10 artists"})
print(response.content)

Debug Output & Execution Trace

Test Case 3: Single-Level Join (Two Tables)

• User Query: "Give me some albums for the artist named Audioslave"
• Target SQL:
  SQL

  Copy

  SELECT Album.Title 
  FROM Album 
  JOIN Artist ON Album.ArtistId = Artist.ArtistId 
  WHERE Artist.Name = 'Audioslave';
  

response = agent.invoke({"question": "Give me some albums for the artist named Audioslave"})
print(response.content)

Debug Output & Execution Trace

10. Handling Multi-Level Joins (3-Table Join Walkthrough)

The true test of a Text-to-SQL agent is its ability to handle multi-level joins. Suppose a user asks:

"Give me some tracks for the artist named Audioslave"

To resolve this, the agent must inspect the schema and determine that:

1. The Track table contains track names but has no direct connection to the Artist table.
2. The Track table connects to the Album table via AlbumId.
3. The Album table connects to the Artist table via ArtistId.
4. Therefore, it must perform a three-table join (Track $\to$ Album $\to$ Artist) to filter by the artist's name and retrieve the track names.

[Track] (TrackId, Name, AlbumId)
   |
   | JOIN on Track.AlbumId = Album.AlbumId
   v
[Album] (AlbumId, Title, ArtistId)
   |
   | JOIN on Album.ArtistId = Artist.ArtistId
   v
[Artist] (ArtistId, Name) WHERE Name = 'Audioslave'

Execution Trace

response = agent.invoke({"question": "Give me some tracks for the artist named Audioslave"})
print(response.content)

Debug Output & Execution Trace

The agent successfully resolved the schema relationships, generated a syntactically correct three-table join, executed it, and returned a clean, natural language list of tracks to the user.

11. Comparative Analysis: Qwen-7B vs. GPT-4

During prototyping, we evaluated both the open-source Qwen-2.5-7B-Instruct (via Hugging Face) and the proprietary GPT-4 (via OpenAI). Below is a comparative analysis of their performance in this agentic workflow:

12. Summary and Key Takeaways

• Agentic Closed-Loop Design: A successful Text-to-SQL agent does not just translate text; it coordinates schema retrieval, query generation, execution, and response synthesis in a closed loop.
• Dynamic Schema Injection: Injecting the database schema dynamically into the LLM's context window is essential for accurate query generation and multi-table joins.
• The Debugging Wrapper Pattern: Wrapping the database execution engine to print generated SQL queries is a critical development practice for catching hallucinations and syntax errors early.
• Strict Output Constraints: Prompt engineering must explicitly instruct the LLM to return only raw SQL strings during the translation step to prevent execution errors.
• Multi-Level Join Resolution: Modern LLMs (both open-source and proprietary) can successfully navigate complex relational schemas to perform multi-table joins (e.g., joining Track $\to$ Album $\to$ Artist) based on natural language prompts.
• Model Agnosticism: Designing the agentic pipeline to be model-agnostic allows developers to easily switch between free open-source models (like Qwen) for prototyping and proprietary models (like GPT-4) for production.