DEV Community

Solon Framework
Solon Framework

Posted on

Text2SqlTalent in Solon AI: Safe Natural-Language Database Agents

#agents #ai #llm #sql

Most “chat with your database” demos stop at prompt engineering: dump the schema, ask the model for SQL, hope nothing breaks.

That fails in production for boring reasons:

  • the schema is too large for one context window
  • the model invents joins that do not exist
  • aliases and dialect syntax drift
  • a “helpful” write statement sneaks in
  • one wide result set blows up the agent context

Solon AI (v4.0.3) packages this as Text2SqlTalent: a Talent that turns natural language into guarded SQL execution, not a raw prompt template.

Why a Talent, not a bare SQL tool

In Solon AI, a Tool is an atomic function. A Talent packages tools with instruction, activation, and SOP-style constraints.

Text-to-SQL needs that packaging:

  • schema strategy depends on table count
  • dialect rules must live next to the tools
  • safety is not optional decoration

So you hang it with defaultTalentAdd(...), not by exposing a naked execute_sql method and hoping the system prompt is enough.

Maven module:

<dependency>
    <groupId>org.noear</groupId>
    <artifactId>solon-ai-talent-text2sql</artifactId>
</dependency>
Enter fullscreen mode Exit fullscreen mode

What Text2SqlTalent actually owns

From the official design, the closed loop is:

  1. detect dialect from the live connection
  2. load table remarks and foreign-key edges for the allow-listed tables
  3. inject either full schema or a catalog map into the agent instruction
  4. expose execute_sql (and get_table_schema in DYNAMIC mode)
  5. repair, guard, page, and truncate before results re-enter the model

That is the product boundary: intent → SQL → safe observation.

Schema modes: FULL vs DYNAMIC

Mode Default trigger What the model sees Extra tool
SchemaMode.FULL table count <= 20 full column metadata in instruction none
SchemaMode.DYNAMIC table count > 20, or forced table list + FK map only get_table_schema

FULL is for small, high-precision analytics surfaces. DYNAMIC is for larger catalogs where dumping every column on every turn is wasteful.

You can also force the mode:

Text2SqlTalent sqlTalent = new Text2SqlTalent(dataSource, "users", "orders", "order_refunds")
        .schemaMode(SchemaMode.DYNAMIC)
        .maxRows(50)
        .readOnly(true)
        .maxContextLength(8000);
Enter fullscreen mode Exit fullscreen mode

Dialects that travel with the Talent

Built-in adapters cover the common production set:

Dialect Matched products Identifier quoting Pagination style
MySQL MySQL, MariaDB, TiDB backticks LIMIT n
PostgreSQL PostgreSQL, Kingbase, Highgo double quotes LIMIT n
Oracle Oracle, Dameng double quotes ROWNUM wrap
SQLite SQLite double quotes LIMIT n
H2 H2 double quotes LIMIT n (can switch to MySQL dialect in MySQL mode)
Generic fallback double quotes FETCH FIRST n ROWS ONLY

Auto-detection uses DatabaseMetaData.getDatabaseProductName(). You can override:

Text2SqlTalent sqlTalent = new Text2SqlTalent(dataSource, "users", "orders")
        .dialect(new PostgreDialect())
        .readOnly(true);
Enter fullscreen mode Exit fullscreen mode

Custom dialects register through SqlDialectManager.register(...).

Four safety rails on every execute

These are not “best practices in a blog comment”. They are runtime behavior:

  1. Read-only gate — with readOnly(true) (default), non-SELECT statements are blocked
  2. Alias repair — missing dialect quotes on AS alias are fixed before execution
  3. Pagination inject — if the SQL has no LIMIT / ROWNUM / FETCH FIRST / TOP, the Talent appends dialect paging using maxRows (default 50)
  4. Result truncate — JSON larger than maxContextLength (default 8000) is cut and marked truncated

That is why Text2SqlTalent is useful even when the model is already “pretty good at SQL”.

Minimal agent wiring

Constructor forms:

// DataSource + explicit table allow-list
new Text2SqlTalent(dataSource, "users", "orders", "order_refunds")

// or an existing SqlUtils instance
new Text2SqlTalent(sqlUtils, "users", "orders")
Enter fullscreen mode Exit fullscreen mode

ReAct agent example from the official pattern:

Text2SqlTalent sqlTalent = new Text2SqlTalent(dataSource, "users", "orders", "order_refunds")
        .maxRows(50)
        .readOnly(true)
        .maxContextLength(8000);

ReActAgent agent = ReActAgent.of(chatModel)
        .role("Finance analyst")
        .instruction("Analyze order and refund data. Amounts are in CNY. Prefer table comments when field names are ambiguous.")
        .defaultTalentAdd(sqlTalent)
        .build();

String answer = agent.prompt("How many refund orders did Zhang San have in 2024, and what is the total amount?")
        .call()
        .getContent();
Enter fullscreen mode Exit fullscreen mode

ChatModel can also take the same Talent through defaultTalentAdd(...) when you do not need the full ReAct loop.

Built-in tools

Tool When exposed Role
execute_sql always run SELECT with repair + guards + paging + truncate
get_table_schema DYNAMIC only fetch one table’s column metadata on demand

In FULL mode the second tool stays hidden on purpose. The schema is already in the instruction; another discovery tool would only add noise.

How a turn actually runs

  1. Attach / init — dialect select, remarks, imported keys
  2. Instruction — environment context + schema or catalog map + dialect-specific rules
  3. Optional probe — in DYNAMIC mode, model calls get_table_schema for the tables it needs
  4. Execute — model calls execute_sql; Talent repairs and guards
  5. Observe — truncated JSON comes back; dialect error hints help the next retry

That loop is why this belongs next to ReActAgent, not next to a one-shot chat completion.

Production checklist

  1. Allow-list tables in the constructor — do not expose the whole database if the task only needs three tables
  2. Prefer DB-level read-only accounts even though Talent already defaults to readOnly(true)
  3. Write real COMMENT / remarks on tables and key columns — the Talent surfaces them as semantic hints
  4. Force DYNAMIC when column volume is high, even if table count is under 20
  5. Keep maxRows and maxContextLength intentional — they are context-budget controls, not cosmetics
  6. Override dialect when you run compatibility modes that auto-detect poorly

When not to use it

  • you only need one fixed report query with known parameters → a normal Tool is enough
  • the user is not allowed to explore data at all → ship parameterized APIs, not NL2SQL
  • the schema is hostile (no keys, no comments, overloaded column names) → fix metadata first

Bottom line

Text2SqlTalent is not “LLM writes SQL”. It is a domain Talent with:

  • adaptive schema injection
  • dialect-aware execution
  • multi-layer safety rails
  • progressive table discovery when the catalog grows

If your agent needs to answer business questions against real tables, start here instead of pasting DDL into the system prompt.

References

Solon version used for this write-up: v4.0.3.

Top comments (0)