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

> Discover how the OpenDerisk SQL agent transforms natural language into executable SQL queries. Learn about its schema-aware LLM prompting, JSON parsing, and asynchronous database execution.

- Repository: [derisk-ai/openderisk](https://github.com/derisk-ai/openderisk)
- Tags: how-to-guide
- Published: 2026-02-28

---

**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`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/agent/expand/actions/chart_action.py)); for multiple statements requiring dashboard composition, it selects **DashboardAction** ([`agent/expand/actions/dashboard_action.py`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/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:

```python
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:

```python
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:

```python
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`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/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`](https://github.com/derisk-ai/openderisk/blob/main/agent/resource/database.py). The resource manager instantiates concrete connectors—such as `SQLiteConnector` from [`derisk_ext/datasource/rdbms/conn_sqlite.py`](https://github.com/derisk-ai/openderisk/blob/main/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.