Over the past two years, tools that generate SQL from natural language have become very popular.
Type a question in plain English, and an LLM produces SQL for you. It's convenient.
But from my experience on real‑world projects, there is a clear problem when business requirements demand high accuracy.
This isn't because LLMs are weak. It's because of how they work – non‑deterministically.
Three practical issues with LLM‑generated SQL
1. Same question, different answers
The same natural language description can lead to different SQL statements. Edge cases may be handled inconsistently.
For financial reports, contract calculations, or any scenario where numbers must be exact, this is unacceptable.
2. Lack of explainability
If the SQL is wrong, it's very hard to know why the LLM chose a particular table, column, or join condition.
Debugging becomes guesswork.
3. Security and cost
Every query incurs API costs. And there have been real RCE vulnerabilities caused by LLM‑generated malicious code (for example, in older versions of Vanna).
Running such systems in production requires extra safeguards.
My approach: deterministic graph pathfinding
I'm not against LLMs.
In fact, I think they are great for intent understanding and structured configuration generation.
But I don't think they should generate SQL directly – especially for complex multi‑table queries.
So I tried a different approach: turning SQL generation from probabilistic reasoning into deterministic graph pathfinding.
How it works:
- Model your database tables and relationships as a weighted directed graph (tables as nodes, possible joins as edges). This graph is defined at deployment time – call it the “queryable graph”.
- Users only need to describe which fields and filters they want using a simple JSON DSL – no need to write any JOINs.
- The engine starts from the tables specified by the user, statically prunes the queryable graph to a relevant subgraph, then uses Dijkstra's algorithm to dynamically find the shortest join path, automatically filling in intermediate tables.
- The final SQL is 100% deterministic – same input, same SQL, every time.
I built this into an open source tool called Lexipathos.
Two‑level pruning: both flexible and controllable
The key is the two‑level graph design:
-
At deployment time: define
resourceDictionarybased on your business ER diagram (which tables, field labels, relationships). This defines the upper bound of what the engine can query. -
At runtime: the user specifies the tables they care about via
dataSources(e.g., only “contracts” and “buildings”). The engine routes only within that subgraph, automatically filling in intermediate tables (e.g., “contract_units”, “units”).
As a result, each query only touches a closed subgraph of 3–6 tables. The full database graph is never dragged into a single query, which avoids cycles and ambiguity.
A quick example
Imagine an office leasing system with tables: contracts, units, buildings, tenants.
You want to query: for all active contracts, show tenant name, unit area, and building address.
You only need to write this JSON:
`json
{
"dataSources": [
{"table": "contracts"},
{"table": "buildings"}
],
"rowDims": [
"tenants.name",
"units.lease_area",
"buildings.address"
],
"filters": [
{"field": "contracts.status", "op": "=", "value": "active"}
]
}
The engine automatically discovers the join path: contracts → contract_units → units → buildings, and contracts → tenants, then generates the complete SQL including all necessary JOINs.
You never write a JOIN again.
More than SQL generation: built‑in features
Besides automatic JOIN inference and deterministic generation, Lexipathos includes several practical features for business analytics:
-
Enum auto‑translation: internal English codes (e.g.,
"vacant") are automatically translated to human‑readable text in responses, while filters still use the codes. -
Two output formats: returns both raw
rowsand a flattenedflatstructure, plus a pivotmatrix. -
In‑memory computation: supports executing JavaScript on the
flatarray (e.g., grouping, ratios, ratings) without changing SQL or restarting the service. - Combined dimensions: merge multiple fields into one column, supporting string concatenation or arithmetic.
All these features are controlled via the JSON DSL – no code changes required.
Security note: in‑memory computation has risks
To be honest, the calcFn and calcFnForMatrix design is very aggressive – it uses new Function() to dynamically execute JavaScript strings coming from the request. This introduces risks of code injection, infinite loops, and memory leaks.
Therefore, this mechanism is currently only recommended for trusted internal network environments. If exposed to the public internet, either disable in‑memory computation or move it to the client side.
Pluggable business configuration
Although the project uses “office leasing” as a demonstration case, the engine itself is completely decoupled from the business domain. Switching to another industry only requires changing three configuration files:
-
resourceDictionary.js: defines tables, field labels, and relationships -
values_mapping.js: defines enum translation rules -
dataLoader.js: defines how to load data into DuckDB
No engine code needs to be changed.
Not anti‑LLM, but AI‑friendly in a different way
If you still want a natural language interface, you can let an LLM generate the JSON configuration, and then Lexipathos executes it deterministically.
This gives you the best of both worlds:
- Flexible frontend: natural language → JSON
- Reliable backend: JSON → deterministic SQL
No more “the AI changed my query and broke the report”.
Here is my work's link.
Lexipathos is open source, runs on DuckDB, and is written in JavaScript.
GitHub: cocosiu/lexipathos
The best natural language interaction is to let an AI agent learn how to use this engine – narrowing down the query semantics and avoiding hallucinations. Lexipathos has already been integrated with OpenClaw. If you have thoughts or want more information, feel free to discuss.I will show you some examples
Top comments (0)