How to Debug Text2SQL Generation in DB-GPT's Auto-Execute Mode: A Complete Guide

Enable verbose logging and trace the pipeline through schema retrieval, prompt construction, LLM generation, and output parsing to isolate Text2SQL failures in DB-GPT's auto_execute mode.

DB-GPT's auto-execute mode automates the full Text2SQL workflow, from natural language understanding to SQL execution against your datasource. When the generated SQL fails, returns empty results, or produces syntax errors, you need a systematic approach to debug Text2SQL generation in DB-GPT's auto_execute mode. This guide walks you through the exact source files and debugging techniques used by the core maintainers.

Understanding the Auto-Execute Pipeline

The auto-execute flow in ChatWithDbAutoExecute follows a strict six-stage pipeline. A failure at any stage cascades downstream, so identifying the exact breakpoint is critical.

Stage	Component	Key Source File
Request Handling	/v1/chat/completions instantiates ChatWithDbAutoExecute	packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py
Schema Retrieval	generate_input_values gathers table metadata via DBSummaryClient or Connector.table_simple_info	packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py
Prompt Construction	AppScenePromptTemplateAdapter builds the final prompt using RESPONSE_FORMAT_SIMPLE	packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/prompt.py
LLM Call	Request routed to the text2sql_proxyllm model worker	packages/dbgpt-app/src/dbgpt_app/openapi/api_v1/api_v1.py
Response Parsing	DbChatOutputParser extracts sql, thoughts, display_type from JSON or raw SQL	packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/out_parser.py
SQL Execution	ChatWithDbAutoExecute.do_action calls self.database.run_to_df	packages/dbgpt-app/src/dbgpt_app/scene/chat_db/auto_execute/chat.py

Step-by-Step Debugging Workflow

Enable Verbose Logging

Set the environment variables before starting the DB-GPT server to capture the full execution trace:

export DEBUG=True
export LOG_LEVEL=DEBUG

With DEBUG enabled, the logs in chat.py and out_parser.py emit critical diagnostics:

• Retrieved table info error: … – indicates schema fallback to table_simple_info.
• clean prompt response: … – shows the raw LLM output after stripping markdown fences.
• parse_view_response error! … – flags execution-time failures in the view layer.

Inspect Input Values and Schema Retrieval

The generate_input_values method in chat.py prepares the context dictionary sent to the LLM. Add explicit logging to verify schema integrity:

input_vals = await chat_instance.generate_input_values()
logger.debug("Text2SQL input values: %s", json.dumps(input_vals, ensure_ascii=False, indent=2))

Verify these critical fields:

• db_name – Must match a registered connector in ConnectorManager.
• table_info – JSON array of tables/columns; check for truncation if schema_max_tokens is exceeded.
• dialect – Database type (e.g., mysql, sqlite) must match the target engine.
• top_k – Row limit injected into the prompt to prevent unbounded results.

If table_info is empty, check DBSummaryClient in db_summary_client.py or the fallback Connector.table_simple_info in your datasource connector.

Verify Prompt Construction

The final prompt is rendered by AppScenePromptTemplateAdapter using the template defined in prompt.py. Dump the rendered prompt to verify placeholder substitution:

from dbgpt_app.scene.chat_db.auto_execute.prompt import prompt_adapter
logger.debug("Full prompt sent to LLM:\n%s", prompt_adapter.prompt.render(input_vals))

Check for:

• Correct injection of {db_name}, {table_info}, and {user_input}.
• Presence of RESPONSE_FORMAT_SIMPLE JSON schema, which dictates the expected LLM output structure.

Confirm LLM Model Registration

The auto-execute mode requires the text2sql_proxyllm model worker. Verify registration via the API:

curl -X GET http://localhost:7860/api/v1/model/types

The response must include "text2sql_proxyllm". If absent, start the corresponding LLM worker (OpenAI, Llama, or local proxy) and ensure it registers with the WorkerManager under the correct model name.

Capture Raw LLM Output

Before DbChatOutputParser processes the response, capture the raw string to diagnose formatting issues:

raw_out = await llm_client.call(prompt)  # Low-level LLM call

logger.debug("Raw LLM output:\n%s", raw_out)

Common anomalies:

• Pure SQL without JSON – Parser treats this as SqlAction(sql, "", "", ""), which may work for simple queries but loses metadata.
• Markdown fences – The parser strips ```sql blocks, but explanatory text outside fences can break JSON parsing.
• Truncated JSON – Indicates context length exceeded; reduce table_info size or increase model token limit.

Validate Parser Extraction

Test the parser independently on saved raw output:

from dbgpt_app.scene.chat_db.auto_execute.out_parser import DbChatOutputParser
parser = DbChatOutputParser()
action = parser.parse_prompt_response(raw_out)
logger.debug("Parsed action: %s", action.to_dict())

Validation checklist:

• action.sql contains a single, syntactically valid statement (verify with sqlparse).
• action.thoughts explains the query logic (useful for debugging LLM reasoning).
• action.display specifies the visualization type (response_table, response_line_chart, etc.).

If action.sql is empty, the parser likely failed to detect JSON or SQL patterns; review the parse_prompt_response implementation in out_parser.py.

Test SQL Execution Manually

Isolate execution issues from generation issues by running the parsed SQL directly:

# For SQLite

sqlite3 /path/to/db.sqlite "$(echo "$SQL")"

# For MySQL

mysql -u user -p -e "$SQL" database_name

If manual execution fails, check:

• Datasource configuration in ConnectorManager.get_connector.
• Driver compatibility (e.g., pymysql, psycopg2 installed).
• Network access and credentials for remote databases.

Check View Rendering

If SQL executes successfully but the UI displays errors, inspect parse_view_response in out_parser.py. Common issues:

• Missing display_type defaults to response_table, which may not suit the data shape.
• parse_vector_data_with_pca requires scikit-learn; absence raises ImportError.

Install optional dependencies:

pip install "scikit-learn>=1.0"

Or disable vector chart visualization by removing "response_vector_chart" from the display_type list in the prompt configuration.

Key Source Files for Debugging

File	Purpose	GitHub Link
chat.py	Core auto-execute logic: ChatWithDbAutoExecute, generate_input_values, do_action	View Source
prompt.py	Prompt template construction and RESPONSE_FORMAT_SIMPLE JSON schema	View Source
out_parser.py	DbChatOutputParser, parse_prompt_response, parse_view_response	View Source
api_v1.py	Model type filtering for text2sql_proxyllm	View Source
connector_manager.py	Datasource connector retrieval	View Source
db_summary_client.py	Table metadata summarization	View Source
config.py	Configuration constants for schema retrieval	View Source

Quick Debugging Code Snippets

Run an isolated auto-execute chat to inspect the pipeline:

import json
from dbgpt_app.scene.chat_db.auto_execute.chat import ChatWithDbAutoExecute
from dbgpt_app.scene.base_chat import ChatParam
from dbgpt import SystemApp
from dbgpt_app.scene.chat_db.auto_execute.out_parser import DbChatOutputParser

sys_app = SystemApp()
chat_param = ChatParam(
    select_param="sqlite_demo",
    current_user_input="How many students scored above 90?",
    model_name="text2sql_proxyllm",
    user_name="developer",
    sys_code="dbgpt",
)
chat = ChatWithDbAutoExecute(chat_param, sys_app)

# Inspect input values

input_vals = await chat.generate_input_values()
print("=== INPUT VALUES ===")
print(json.dumps(input_vals, indent=2, ensure_ascii=False))

# Inspect raw LLM response

response_text = await chat.llm_client.call(chat.prompt.build(input_vals))
print("\n=== RAW LLM RESPONSE ===")
print(response_text)

# Inspect parsed action

parser = DbChatOutputParser()
action = parser.parse_prompt_response(response_text)
print("\n=== PARSED ACTION ===")
print(action.to_dict())

Verify the Text2SQL model registration:

curl -X GET http://localhost:7860/api/v1/model/types | grep text2sql_proxyllm

Manually test generated SQL:

# SQLite example

sqlite3 /path/to/database.sqlite "SELECT * FROM students WHERE score > 90 LIMIT 10"

Summary

• Enable debug logging with DEBUG=True and LOG_LEVEL=DEBUG to expose schema retrieval, prompt rendering, and parser errors in chat.py and out_parser.py.
• Trace the input values produced by generate_input_values to verify that table_info, dialect, and db_name are correctly populated before reaching the LLM.
• Dump the rendered prompt from AppScenePromptTemplateAdapter to ensure the JSON response schema and user question are properly formatted.
• Confirm model availability by checking that text2sql_proxyllm appears in the /api/v1/model/types endpoint and is registered with the WorkerManager.
• Capture raw LLM output before DbChatOutputParser processes it to detect markdown fences, truncated JSON, or pure SQL responses.
• Validate parser output by running parse_prompt_response manually to ensure action.sql contains valid, executable SQL.
• Execute SQL manually against the target database to isolate datasource configuration issues from generation errors.

Frequently Asked Questions

How do I know if the schema retrieval is failing in auto_execute mode?

Check the debug logs for messages containing Retrieved table info error. If DBSummaryClient fails to generate summaries, the system falls back to Connector.table_simple_info in connector_manager.py. Verify that your datasource connector is properly registered and that the database credentials allow metadata queries.

Why does the LLM return malformed JSON instead of the expected SQL response?

The DbChatOutputParser in out_parser.py expects either pure SQL or a JSON object matching RESPONSE_FORMAT_SIMPLE. If the model returns explanatory text wrapped around JSON or uses markdown code fences inconsistently, parsing fails. Increase prompt specificity in prompt.py or adjust PROMPT_TEMPERATURE to 0.1 for more deterministic output.

How can I test the generated SQL without running the full DB-GPT pipeline?

Extract the action.sql string from the DbChatOutputParser output and execute it directly using your database CLI or a Python script with the same connection string used by ConnectorManager.get_connector. This isolates whether the issue is SQL generation or datasource connectivity/configuration.

What should I do if the UI shows an error but the SQL executes correctly in my manual test?

If manual execution succeeds but the UI fails, inspect parse_view_response in out_parser.py. The error likely stems from missing display_type metadata or a missing scikit-learn dependency required for parse_vector_data_with_pca. Install optional dependencies with pip install "scikit-learn>=1.0" or disable vector chart visualization in the prompt configuration.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}