How the OpenDerisk SQL Agent Generates and Executes Database Queries from Natural Language

The OpenDerisk SQL agent converts natural language into executable SQL through a pipeline of schema-aware LLM prompting, structured JSON parsing, and asynchronous database execution via the DBResource abstraction.

The derisk-ai/openderisk repository implements an intelligent SQL agent that bridges natural language and relational databases. This system enables users to query structured data using plain English requests while the underlying engine generates syntactically correct SQL and handles execution asynchronously. Understanding how this SQL agent generates and executes database queries from natural language reveals a modular architecture built on prompt engineering, strict output validation, and resource abstraction.

Step-by-Step Pipeline: From Natural Language to SQL Execution

Schema-Aware LLM Generation

In agent/expand/code_agent/agent.py, the CodeAssistantAgent constructs prompts using templates like _DEFAULT_PROMPT_TEMPLATE and _DEFAULT_PROMPT_TEMPLATE_ZH. These templates inject the target database schema directly into the context, ensuring the LLM understands table definitions before generating SQL. The agent sends the user's natural language request—such as "How many customers churned last month?"—to the LLM with this structured context.

Structured Output Parsing

The raw LLM response flows through SQLOutputParser or SQLListOutputParser, defined in core/interface/output_parser.py. These parsers enforce strict JSON validation, extracting fields like sql, display_type, and thought from the model's output. This transformation turns free-form text into deterministic Python dictionaries that the system can route programmatically.

Action Selection and Routing

Based on the parsed payload, the agent's action registry selects the appropriate handler. For single SQL statements, the system invokes ChartAction (agent/expand/actions/chart_action.py); for multiple statements requiring dashboard composition, it selects DashboardAction (agent/expand/actions/dashboard_action.py). This mapping ensures the correct execution strategy for the user's intent.

Database Resource Resolution

The action requests a DBResource from the resource manager. The concrete implementation—whether SQLiteDBResource or RDBMSConnectorResource—is instantiated from the global CFG.local_db_manager configuration. This abstraction layer, centered in agent/resource/database.py, decouples the agent logic from specific database engines.

Asynchronous SQL Execution

Execution occurs through db.query_to_df(sql) or db.query(sql), which delegate to DBResource.query. The system wraps actual database calls using blocking_func_to_async with a ThreadPoolExecutor, preventing the agent from blocking during long-running queries. The underlying RDBMSConnector—such as SQLiteConnector in derisk_ext/datasource/rdbms/conn_sqlite.py—handles the final execution via SQLAlchemy or native drivers.

Result Visualization and Response

Returned rows are wrapped in pandas DataFrame objects for chart actions or preserved as raw data for dashboards. The action passes this data to the render protocol (VisChart or VisDashboard), generating visualizations attached to the ActionOutput. The final response travels back through the agent's messaging pipeline to the user interface.

Key Architectural Components

Prompt-Driven SQL Synthesis

The agent's ability to generate accurate SQL depends on dynamic prompt construction. By formatting schema metadata into templates via self._prompt_template.format(...), the system guarantees the LLM references actual table columns and relationships, reducing hallucination errors.

Typed Parser Safety

SQLOutputParser provides type safety by mandating JSON-compliant responses. This parser eliminates ambiguity in LLM outputs, ensuring the sql field contains executable statements and the display_type specifies the visualization format.

Modular Database Connectors

The DBResource abstraction supports multiple RDBMS backends through a unified interface. New datasources require only implementing the connector's run(sql) method in the derisk_ext.datasource.rdbms package, making the system extensible to PostgreSQL, MySQL, or proprietary databases.

Non-Blocking Execution Strategy

All database interactions utilize blocking_func_to_async to maintain agent responsiveness. This pattern allows the SQL agent to handle multiple concurrent natural language queries without freezing the main event loop.

Code Implementation Examples

Generate SQL from natural language using the CodeAssistantAgent:

from derisk.agent.expand.code_agent.agent import CodeAssistantAgent

agent = CodeAssistantAgent()
response = await agent.chat(
    "Show the total sales per region for the last quarter."
)

# The LLM returns a JSON payload like:

# {

#   "display_type": "response_chart",

#   "sql": "SELECT region, SUM(sales) FROM orders WHERE order_date >= '2023-07-01' GROUP BY region;",

#   "thought": "Summarise sales by region."

# }

Execute the generated SQL via the DBResource abstraction:

from derisk.agent.resource.database import SQLiteDBResource
from derisk.datasource.rdbms.conn_sqlite import SQLiteConnector

# Create a connector for an in‑memory SQLite DB (for demo)

connector = SQLiteConnector.from_file_path(":memory:")
db_res = SQLiteDBResource(name="demo_sqlite", connector=connector)

# Execute the query produced above

df = await db_res.query_to_df(
    sql="SELECT region, SUM(sales) FROM orders GROUP BY region;"
)
print(df.head())

Use ChartAction directly to process LLM output and generate visualizations:

from derisk.agent.expand.actions.chart_action import ChartAction

action = ChartAction()
ai_message = """{
    "display_type": "response_chart",
    "sql": "SELECT date, revenue FROM revenue_daily WHERE date BETWEEN '2023-01-01' AND '2023-01-31';",
    "thought": "Show daily revenue for January."
}"""

output = await action.run(ai_message, resource=db_res)

# output.view now contains a VisChart ready to be displayed in the UI

Summary

• The SQL agent uses schema-injected prompts in CodeAssistantAgent to generate contextually accurate SQL from natural language inputs.
• SQLOutputParser validates and structures LLM responses into executable command dictionaries.
• The action registry routes commands to ChartAction or DashboardAction based on payload complexity.
• DBResource and RDBMSConnectorResource abstract database connectivity, supporting SQLite, MySQL, PostgreSQL, and other engines.
• Asynchronous execution via blocking_func_to_async ensures the agent remains responsive during query processing.
• The render protocol decouples data retrieval from visualization, enabling flexible output formatting.

Frequently Asked Questions

How does the SQL agent ensure generated queries match the database schema?

The agent injects live schema metadata into LLM prompts through templates defined in agent/resource/database.py, specifically _DEFAULT_PROMPT_TEMPLATE and _DEFAULT_PROMPT_TEMPLATE_ZH. By formatting table definitions and column types directly into the context window, the system grounds the LLM in actual database structure before SQL generation begins. This schema injection prevents hallucinated table names and ensures syntactic compatibility with the target datasource.

What prevents the SQL agent from executing malicious or incorrect statements?

The SQLOutputParser in core/interface/output_parser.py validates that LLM outputs conform to a strict JSON schema containing only expected fields like sql and display_type. While the parser handles structural validation, the DBResource layer can enforce connection-level security policies, including read-only modes and restricted user permissions at the connector level. Additional safeguards can be implemented within the RDBMSConnector classes to block destructive operations based on SQL parsing.

Can the SQL agent handle multiple database types simultaneously?

Yes, the architecture supports heterogeneous datasources through the DBResource abstraction in agent/resource/database.py. The resource manager instantiates concrete connectors—such as SQLiteConnector from derisk_ext/datasource/rdbms/conn_sqlite.py or RDBMSConnector for PostgreSQL/MySQL—based on runtime configuration, allowing a single agent instance to query SQLite, MySQL, and other engines within the same session. Each connector implements the standard run(sql) interface, providing uniform execution semantics across different database backends.

How does the agent maintain performance during slow database queries?

The DBResource.query method wraps all database calls using blocking_func_to_async executed within a ThreadPoolExecutor, preventing long-running SQL operations from blocking the agent's main event loop. This asynchronous pattern allows the SQL agent to process multiple natural language requests concurrently while waiting for I/O-bound query results. The thread-pool abstraction ensures that slow queries against large datasets do not degrade the responsiveness of the conversational interface.

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