In some scenarios, relying on LLMs to generate SQL is neither rigorous nor reliable.Right way to teach LLMs to generate SQL is here

Coco — Tue, 19 May 2026 07:24:05 +0000

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 resourceDictionary based 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 rows and a flattened flat structure, plus a pivot matrix.
In‑memory computation: supports executing JavaScript on the flat array (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

I got tired of calculating commercial lease billing by hand, so I built a tool

Coco — Wed, 13 May 2026 10:50:09 +0000

I worked in commercial real estate. Not as a developer — as an operator.
Every month, someone on the team had to sit down and manually calculate billing schedules. Every contract had a free rent period, a rent escalation clause, a non-standard start date. Usually all three. You’d open Excel, start calculating day counts, apply the escalation rate, handle the stub period at the beginning, handle the one at the end.
It took hours. It was error-prone. And when you got it wrong, the tenant pushed back.
I got tired of it. I knew how to code. So I built something.
What the problem actually looks like
A real contract:
Lease starts March 15
Free rent for the first 2 months
From month 3: base rate kicks in
From month 13: base rate × 1.05 (anniversary escalation)
Billing anchor is the 15th of each month
Now generate a clean monthly billing schedule for 3 years. Every period needs the right dates, the right applied rate, the right amount. The stub periods at the start and end need to be handled correctly. The free rent and escalation can overlap.
This is not a hard algorithm problem. It’s a tedious, high-stakes edge case problem. Get it wrong and you have a legal dispute. Get it right and you’ve spent two hours on something that should be automated.
@cosiu/periodix — an npm package that takes a lease contract and returns a fully split billing schedule.
const ContractPeriodMonthSplitter = require('@cosiu/periodix');

const result = ContractPeriodMonthSplitter.splitContractPeriods({ startDate: '2024-03-15', endDate: '2027-03-14', pivotDate: '2024-03-15', area: 500, baseTotalRentRate: 10000, serviceRate: 3, freePeriods: [ { startDate: '2024-03-15', endDate: '2024-05-14' } ], increaseRules: [ { type: 'ANNIVERSARY', anchorDate: '2024-03-14', rate: 0.05 } ] });

Each period in the output has exact dates, the applied escalation rate, rent and service fee amounts, and a flag for whether it’s a stub period or a full month.

try it:npm install @cosiu/periodix
GitHub: https://github.com/cocosiu/periodix
If you’re building property management software and you’ve been solving this problem yourself, I’d be curious whether your approach is different. And if you find an edge case this doesn’t handle, open an issue.

DEV Community: Coco