How Chat2DB Processes Natural Language Queries: NL-to-SQL Pipeline Explained

Chat2DB processes natural language queries by streaming user questions through a Spring AI pipeline that uses strict system prompts and a dedicated text2sql tool callback to generate executable SQL.

Chat2DB is an open-source database client that lets developers query databases using plain English instead of handwritten SQL. Learning exactly how Chat2DB processes natural language queries reveals a layered architecture that combines REST controllers, streaming adapters, and LLM tool callbacks. This guide walks through the production code in OtterMind/Chat2DB to show how a free-text question becomes a runnable SELECT or INSERT statement.

REST Entry Point for Natural Language Queries

The pipeline starts when the front-end submits a JSON payload to the POST /api/ai/chat endpoint. In chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/AiChatController.java, the AiChatController accepts a ChatRequest body and immediately delegates to the IAiChatStreamService interface.

@PostMapping("/chat")
public DataResult<AiChatMessageResponse> chat(@RequestBody @Valid ChatRequest request) {
    return aiChatStreamService.stream(request);
}

This thin controller layer keeps HTTP concerns separated from AI orchestration. The request optionally carries datasource, database, and schema fields that downstream components use to ground the generated SQL in the correct schema context.

Streaming Adapter: Where Natural Language Queries Become SQL

The real orchestration happens in chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/adapter/ai/AiChatStreamAdapter.java. This class implements IAiChatStreamService and transforms the generic chat request into an NL-to-SQL generation task.

When stream(ChatRequest request) is invoked, the adapter performs three critical configuration steps:

1. Marks the intent — request.setQuestionType(QuestionTypeEnum.NL_2_SQL.getCode()) tells the backend this is a natural-language-to-SQL job.
2. Enables tool usage — request.setEnableTools(Boolean.TRUE) permits the LLM to invoke registered tool callbacks during generation.
3. Loads strict prompts — The adapter builds a Spring AI ChatClient using NL_2_SQL_SYSTEM_PROMPT, a constant that instructs the model to emit only valid SQL without markdown fences or explanatory text. Additional compliance prompts (SCOPE_AND_COMPLIANCE_PROMPT and NL_2_SQL_COMPLIANCE_PROMPT) enforce content-policy boundaries.

public R stream(ChatRequest request) {
    request.setQuestionType(QuestionTypeEnum.NL_2_SQL.getCode());
    request.setEnableTools(Boolean.TRUE);
    ChatClient client = modelFactory.buildChatClient(NL_2_SQL_SYSTEM_PROMPT);
    // … invoke client and return SseEmitter …
}

Behind the scenes, the Chat2DBContext SPI provides live connection metadata and schema details from chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/Chat2DBContext.java. This context is available to the LLM when it needs table names, column types, or relationship hints during SQL construction.

Tool Callbacks That Finalize Natural Language Query Processing

Chat2DB registers its NL-to-SQL capability as an MCP tool so the LLM can call it deterministically. The AiToolMcpAdapter class in chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/mcp/adapter/AiToolMcpAdapter.java is exposed as a ToolCallbackProvider (wired in Chat2dbMcpConfiguration) and defines a method annotated with @Tool(name = "text2sql").

When the LLM decides that schema-grounded generation is required, it invokes this callback with the original question and scope identifiers:

@Tool(name = "text2sql", description = "Convert a natural language question into SQL …")
public String text2sql(String question,
                       Long dataSourceId,
                       String databaseName,
                       String schemaName) {
    ChatRequest chatRequest = new ChatRequest();
    chatRequest.setInput(question);
    chatRequest.setDataSourceId(dataSourceId);
    chatRequest.setDatabaseName(databaseName);
    chatRequest.setSchemaName(schemaName);
    chatRequest.setQuestionType(QuestionTypeEnum.NL_2_SQL.getCode());
    chatRequest.setEnableTools(Boolean.TRUE);
    return aiChatStreamAdapter.chatSync(chatRequest);
}

Notice the recursive design: the tool callback reconstructs a ChatRequest, pins QuestionTypeEnum.NL_2_SQL, and calls aiChatStreamAdapter.chatSync(chatRequest). This synchronous rerun forces the streaming adapter back into NL-to-SQL mode, where the strict system prompt compels the LLM to return a single raw SQL string rather than conversational text.

Delivering the Generated SQL to the Frontend

Once the LLM emits the SQL statement, it travels back through the tool-callback chain to the original stream() invocation inside AiChatStreamAdapter. The generated SQL is ultimately delivered to the front-end as a plain string, or wrapped in a DataResult, ready for immediate injection into the query editor or direct execution against the configured data source.

Summary

Chat2DB converts plain English into database queries through a tightly controlled pipeline:

• AiChatController accepts natural language via POST /api/ai/chat and hands off to the stream service.
• AiChatStreamAdapter sets QuestionTypeEnum.NL_2_SQL, enables tool callbacks, and primes the LLM with NL_2_SQL_SYSTEM_PROMPT plus compliance prompts.
• AiToolMcpAdapter exposes the text2sql MCP tool, which re-enters the adapter synchronously to force raw SQL output.
• Chat2DBContext supplies live schema metadata so the generated SQL is contextually accurate.
• The final SQL string returns through the callback chain and is rendered in the UI without markdown or narration.

Frequently Asked Questions

What REST endpoint does Chat2DB use for natural language queries?

Chat2DB exposes POST /api/ai/chat in AiChatController.java. The front-end sends a JSON ChatRequest containing the user’s question and optional database context to this endpoint, and the controller delegates immediately to IAiChatStreamService.

Why does Chat2DB use a separate text2sql tool instead of generating SQL directly?

The text2sql tool callback in AiToolMcpAdapter.java gives the LLM a deterministic, schema-aware execution path. By invoking aiChatStreamAdapter.chatSync() with QuestionTypeEnum.NL_2_SQL, Chat2DB guarantees that the model re-enters the strict prompt context and returns only executable SQL rather than conversational explanations.

How does Chat2DB prevent the LLM from adding markdown or explanations to the SQL?

The AiChatStreamAdapter injects NL_2_SQL_SYSTEM_PROMPT when building the Spring AI ChatClient. This system prompt explicitly instructs the model to output only valid SQL. Compliance prompts (SCOPE_AND_COMPLIANCE_PROMPT and NL_2_SQL_COMPLIANCE_PROMPT) add additional policy guardrails.

Where does Chat2DB get database schema information for the LLM?

Runtime schema metadata is provided through the Chat2DBContext SPI located in chat2db-community-server/chat2db-community-spi/src/main/java/ai/chat2db/spi/sql/Chat2DBContext.java. This context holder supplies datasource connections, table definitions, and SQL builder utilities whenever the AI pipeline needs to ground a natural language question in the actual database structure.

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