Dosu LogoDosu Logo
Ask
Join our Discord
ragflowPublic
InfiniFlow
Documentsragflow
Text2SQL
Text2SQL
Type
Topic
Status
Published
Created
Jul 31, 2026
Updated
Jul 31, 2026

Text2SQL in RAGFlow#

RAGFlow provides two distinct Text2SQL modes: a chat-assistant SQL path for structured knowledge bases (spreadsheets/Excel) queried via Elasticsearch, Infinity, or OceanBase, and a standalone agent template (ExeSQL) that connects to external relational databases. Both translate natural-language questions into SQL, but they differ in who owns the schema and where the query runs.

Entry Points#

ModeEntry pointWhen it runs
Chat assistant (KB)use_sql() in api/db/services/dialog_service.pyAutomatically when the KB has a field_map (structured data)
Agent (ExeSQL)agent/tools/exesql.py, agent/templates/text2sql_data_expert.jsonExplicitly wired in an agent flow

The chat path is triggered inside async_chat(): KnowledgebaseService.get_field_map() checks the KB's parser_config for a field_map dict; if one exists, use_sql() is called first. A successful result (rows returned or an aggregate answer) short-circuits the normal vector-search path . On failure or empty results, it falls back to vector retrieval .

Field Map: Schema Source of Truth#

When an Excel or CSV file is parsed with the Table parser, rag/app/table.py builds a field_map dict and saves it into the KB's parser_config .

Key structure by engine:

EngineKey formatExample
ES / OpenSearch<pinyin>_<type_suffix>product_tks, price_flt, stock_long
Infinity / OceanBase<pinyin_lowercase>product, price

Type suffixes (_tks, _flt, _long, _dt, _kwd) map to the ES dynamic field template conventions. Values in the dict are the original spreadsheet column headers (human-readable display names).

KnowledgebaseService.get_field_map(ids) merges the field_map from all KBs in a dialog into a single dict — the merged dict is what use_sql() receives as field_map.

Column names with spaces: Spreadsheet headers are stored as-is in the field_map values (e.g., "Product Name") but the keys are Pinyin-derived alphanumeric identifiers with type suffixes. This means the LLM never needs to quote identifiers with spaces in the SQL it generates for the KB path — it uses the suffix-encoded key, not the original header.

Engine-Specific Prompt Construction#

use_sql() detects the active document engine via settings.DOC_ENGINE_INFINITY / settings.DOC_ENGINE_OCEANBASE and builds a different system prompt + user prompt for each .

Elasticsearch / OpenSearch :

  • Fields are accessed directly by their ES field names (e.g., product_tks, price_flt).
  • The user prompt lists available fields with types: - product_tks (text).
  • The LLM is instructed to quote fields starting with a digit (e.g., "123_field").
  • Non-aggregate SELECTs must include doc_id and docnm_kwd for citation tracking.

Infinity :

  • Data is stored in a JSON chunk_data column; field access uses json_extract_string(chunk_data, '$.FieldName').
  • The table name encodes the KB: ragflow_{tenant_id}_{kb_id} (one table per KB).
  • The LLM receives exact (case-sensitive) field names and detailed rules for null checks, numeric casting, and string comparisons (the '"value"' double-quote-in-single-quote pattern).

OceanBase : Nearly identical to Infinity, but non-aggregate SELECTs include docnm_kwd instead of docnm.

All three branches call the LLM at temperature=0.06 for deterministic output and run normalize_sql() to strip <think> blocks, markdown fences, and trailing semicolons .

SQL Execution and ES-Specific Query Rewriting#

Generated SQL goes through settings.retriever.sql_retrieval(sql, format="json"), which calls ESConnectionBase.sql() for the ES/OS backend .

Before execution, sql() applies two preprocessing steps:

  1. Backtick and whitespace normalization — re.sub(r"[ \]+", " ", sql)` strips backtick-quoted identifiers and collapses spaces . This means ES SQL does not support backtick-quoted identifiers with spaces.
  2. MATCH rewriting — predicates on *_tks / *_ltks fields (field_tks LIKE 'value' or field_tks = 'value') are rewritten to Elasticsearch full-text MATCH(field, 'tokenized_value', 'operator=OR;minimum_should_match=30%') expressions . This is critical: the LLM produces standard SQL equality/LIKE syntax, and the driver converts it to the ES-native MATCH form transparently.

After execution, use_sql() injects a validated kb_id WHERE filter for ES/OS (Infinity uses the table name instead) and builds a Markdown table result, mapping column names back to display names via map_column_name() .

Identifier Quoting and Columns with Spaces#

ES SQL does not natively allow unquoted identifiers with spaces. Backticks are stripped pre-flight , so identifiers with spaces in the original column name would fail. RAGFlow avoids this problem by design: the LLM is always prompted with the Pinyin+suffix keys (e.g., product_name_tks), not the raw display names. The field_map values (human-readable names) are used only for output rendering, not in the SQL itself.

The ES prompt also explicitly instructs the LLM to quote digit-leading field names with double quotes — the one case where ES SQL requires quoting.

Error Handling and Retry Loop#

use_sql() has a two-attempt retry loop :

  1. First attempt — Execute the LLM-generated SQL.
  2. On failure — Rebuild the user prompt with the error message appended, re-prompt the LLM, and try once more.
  3. Double failure — Log an error and return None; async_chat() falls back to vector search.

At the ES layer, ESConnectionBase.sql() catches BadRequestError (HTTP 400 from ES) and logs it at WARNING level rather than ERROR, because this is the expected hot-path for LLM-generated SQL referencing non-existent columns . The exception is re-raised so the retry loop in use_sql() can act on it. Real connectivity errors (ConnectionTimeout, generic exceptions) continue to surface at ERROR level . This logging behavior was introduced in PR #15709 .

Missing source-column repair: If the returned table lacks doc_id or docnm_kwd (needed for citations), use_sql() makes one additional re-prompt asking the LLM to add those columns while preserving query intent .

Aggregate queries (COUNT, SUM, etc.) are detected via regex . For these, the result table is returned as the answer, and a separate SELECT doc_id, docnm_kwd FROM ... WHERE <same_where_clause> is issued to provide source citations .

Security Hardening and Agent Mode#

Chat-Path SQL Guards#

  • Read-only allowlist — PR #15124 enforces that only SELECT statements can reach sql_retrieval, blocking LLM-generated DROP/UPDATE/etc.
  • UUID injection guard — kb_id values are validated as canonical UUIDs before interpolation into the WHERE kb_id = '...' filter .
  • Match-predicate quote-escape hardening — PR #14852 fixed a CWE-89 vulnerability where the regex [^']+ failed to consume SQL-escaped quotes ('') in WHERE clause values, allowing attacker-controlled fragments to bypass the KB isolation filter. The fix updated the pattern to (?:''|[^']+)+ and re-escapes tokenizer output before injecting it into MATCH literals.

Agent Mode: ExeSQL#

The standalone ExeSQL agent tool (agent/tools/exesql.py) connects to external RDBMSs (MySQL, PostgreSQL, MariaDB, MSSQL, IBM DB2, Trino, OceanBase) . It uses an SSRF guard to validate the database host before connection and is paired with the multi-stage retrieval agent template in agent/templates/text2sql_data_expert.json , which fetches schema DDL, Q&A examples, and field descriptions from three separate KB retrievals before prompting for SQL. The read-only allowlist enforcement for ExeSQL was addressed separately in PR #14877 .

Documents
Agent Import and DSL Compatibility
Agent Retrieval
API Authorization
API Error Codes
Ascend Inference Pipeline
Authentication
Canvas Architecture
Chat Assistant Configuration
Chat Completion API
Chrome for Testing ARM64 Support
Chunk Metadata Extraction
Chunker Pipeline
Compilation Template Management
Component Variable Propagation
Connection and Resource Management
Connector Architecture
Connector Document Sync
Conversation Session Management
What is the complete API flow for building a frontend UI with RAGFlow, covering dialogs, conversations, message history, streaming responses, and deletion?
Database Migrations
Dataflow Pipeline Execution
Dataset Access Control
Dataset Configuration UI
Dataset Parsing Mode
DeepDoc Model Distribution
DeepDoc Module
Dify External Knowledge Integration
Docker Build Configuration
Document Parsing Pipeline
Elasticsearch Index Management
Embedding Pipeline
Embedding Vector Validation
Encrypted Storage
GPU and Accelerator Support
Hybrid Search and Retrieval
Infinity Database Stability
Internal Compilation Artifact Indexing
Keyword Extraction
Knowledge Compilation Pipeline
Knowledge Graph
Knowledge Graph Retrieval
Knowledge Graph Visualization
Layout Element Overlap Detection
LLM Driver Integration
LLM Provider Integration
MCP Server Integration
Metadata Filtering
MinerU Configuration and Provider Resolution
Model Provider Architecture
Model Selection UI
Model Thinking and Reasoning
Multi-Architecture Docker Support
Multi-Backend Object Storage
OCR Backend and Model Loading
Parser Configuration
Parser Output Lifecycle
Parser-Chunk Contract
Picture Chunker Media Processing
Pipeline Canvas Architecture
Python Dependency Management
RAGFlow Python SDK
Redis Cache Architecture
Retrieval API
Retrieval Pipeline
SSRF Protection
Table Column Field Normalization
Table Structure Parsing
Task Cancellation
Tenant Model Resolution
Text2SQL
TSR Coordinate System Alignment