# Step towards Self-Serve Analytics: Natural Language Queries on Databases

This blog post is connected to my earlier post here [https://waqassiddiqi.com/from-text-to-charts-generative-ui-using-assistant-ui](https://waqassiddiqi.com/from-text-to-charts-generative-ui-using-assistant-ui) in which I documented the UI part - This post here talks about what happens under-the-hood when user asks questions like “How many orders placed yesterday?”

## What is LlamaIndex?

LlamaIndex is an open-source framework that connects **large language models (LLMs)** with private data. It takes unstructured or structured data (databases, documents, APIs, etc.), builds indices, and allows natural-language queries on top of them.

At its core, LlamaIndex does three things:

1. **Data ingestion:** Pulls data from multiple sources: databases, data lakes, APIs, or documents.
    
2. **Indexing:** Creates efficient structures so that LLMs can understand and query the data.
    
3. **Querying:** Let uses plain English to ask questions, with the LLM turning them into the right underlying queries.
    

### Building Blocks of Our Implementation

* **SQLDatabase**: A thin wrapper over SQLAlchemy that exposes our database to LlamaIndex. It examines **tables, columns, table relations** and can sample rows
    
* **Nodes & Schemas**: LlamaIndex represents table schemas as structured **Nodes** (e.g. `SQLTableSchema`). These become part of the model’s context, so the model *knows* what exists.
    
* **ObjectIndex**: An index over schema objects (tables/views). Useful to send questions to the right subset of tables.
    
* **NLSQLTableQueryEngine**: It create prompt which includes (a) the user’s question (b) relevant schema (c) examples, and asks the LLM to **suggest SQL**.
    
* **Settings / Providers**: We configure the global LLM & embedding models. For our use case, we are using **Azure OpenAI** deployment.
    

### The Request Lifecycle (What happens on `/ask-sql`)

1. **User asks:** “Top 5 customers by total invoice amount for 2024.”
    
2. **Schema context:** `SQLDatabase` exposes table/column metadata. Pre-index these with `ObjectIndex` for cleaner prompts.
    

```python
# app/sql_engine.py (excerpt)
from sqlalchemy import create_engine
from llama_index.core import SQLDatabase
from llama_index.core.objects import SQLTableNodeMapping, ObjectIndex
from llama_index.core.indices.struct_store import SQLTableSchema
from llama_index.core import VectorStoreIndex

engine = create_engine(settings.sqlalchemy_url, pool_pre_ping=True)
sqldb = SQLDatabase(engine)

node_mapping = SQLTableNodeMapping(sqldb)
schema_nodes = [SQLTableSchema(table_name=t) for t in sqldb.get_usable_table_names()]
obj_index = ObjectIndex.from_objects(schema_nodes, node_mapping, VectorStoreIndex)
```

3. **Prompt build:** `NLSQLTableQueryEngine` assembles a system prompt with rules (only SELECT, use explicit JOINs, respect LIMIT), the **filtered schema**, and examples.
    

```python
# app/sql_engine.py (excerpt)
from llama_index.core.query_engine import NLSQLTableQueryEngine

table_retriever = obj_index.as_retriever(similarity_top_k=3)

query_engine = NLSQLTableQueryEngine(
    sql_database=sqldb,
    table_retriever=table_retriever,
    synthesize_response=True,
)

resp = query_engine.query("Top 5 customers by total invoice amount in 2024")
candidate_sql = getattr(resp, "metadata", {}).get("sql_query") or str(resp)
```

4. **LLM proposes SQL:** Azure OpenAI returns a candidate query (e.g., a SELECT with joins/filters).
    
5. **Guardrails:** We **validate** the SQL (deny DDL/DML, multiple statements, comments), **whitelist** tables, and **enforce LIMIT**.
    

```python
# app/guardrails.py usage (excerpt)
from sqlalchemy import text
from .guardrails import validate_sql, whitelist_tables, enforce_limit

validate_sql(candidate_sql)
whitelist_tables(candidate_sql)
safe_sql = enforce_limit(candidate_sql, settings.max_rows)
```

6. **Execution:** If validation passes, we run it via SQLAlchemy to get results.
    

```python
df = pd.read_sql(text(safe_sql), engine) 
```

### Prompting (Why it matters)

Good prompts lower hallucinations and push the model toward minimal, correct SQL.

We encode:

* **Rules**: *No* `SELECT *`*, explicit JOINs, always include LIMIT, no data modification.*
    
* **Scope**: Only the whitelisted tables/columns (keeps search space small and safer).
    
* **Few‑shot examples**: Show the model how we want dates, GROUP BYs, and aliases formatted.
    
* **Fallback behavior**: If the question is ambiguous, ask a **brief clarifying question** (we allow the engine to respond with a follow‑up, or you can implement a retry policy).
    

### Schema Exposure & Scoping

Large schemas overwhelm the prompt and increase error rates. To Keep the context **small and relevant**:

* Create **read‑only reporting views** that reflect analytics needs (clean joins, intuitive names).
    
* Maintain a **synonym dictionary** (e.g., “AU” → `country='Australia'`, “last quarter” → `[date range]`).
    
* For multi‑tenant apps, scope by **RLS** (Row-Level Security) or inject tenant predicates into generated SQL.
    

### Guardrails (What we block & why)

* **Forbidden tokens**: Reject anything that looks like DDL/DML or comment injection: `DROP`, `UPDATE`, `--`, `/* ... */`, etc.
    
* **Whitelist tables**: Only allow known‑safe tables/views.
    
* **Single‑statement only**: Block semicolons and multi‑statements.
    
* **LIMIT**: Always enforce an upper bound on rows.
    
* **AST validation (optional)**: For stricter control, parse the SQL (e.g., via `sqlglot`) and inspect the abstract syntax tree.
    

As I heard a guy speaking on this topic:

> Treat the model like an untrusted user—you must validate and constrain everything it produces.

### Common Pitfalls (and how we are going to mititage)

1. **Ambiguous business terms**  
    *“Sales”* could mean gross, net, or invoiced.
    
    **Mitigation**: a glossary/synonym map and/or clarify‑question policy.
    
2. **Join path confusion**  
    With multiple possible join routes, the model guesses.
    
    **Mitigation**: expose **reporting views** with the joins baked in; hide raw tables.
    
3. **Date math & time zones**  
    “Last month” depends on locale/time zone.
    
    **Mitigation**: convert relative periods to exact ranges server‑side (e.g., Australia/Perth), and teach the model the pattern.
    
4. **NULL semantics & aggregates**  
    SUM/COUNT with NULLs can be tricky.
    
    **Mitigation**: steer model via few‑shots to use `COALESCE` and correct grouping.
    
5. **Performance outliers**  
    A generated query may be logically correct but slow.
    
    **Mitigation**: LIMITs, timeouts, and a vault of **approved views** with proper indexes.
    
6. **Prompt bloat: cost/latency**  
    Too much schema in context increases tokens.
    
    **Mitigation**: scope tables, cache schema summaries, and keep few‑shots tight.
    
7. **Security drift**  
    New tables accidentally become queryable.
    
    **Mitigation**: explicit **allowlist**.
    

### Known Shortcomings / What LlamaIndex won’t solve for you

* **Ground truth vs. business logic**: LlamaIndex can *suggest SQL*, but it doesn’t know the business semantics; we must add them (views/glossary).
    
* **Optimiser hints / fine‑grained planning**: The LLM cannot guarantee optimal queries; we still need indexes and materialized views.
    
* **100% correctness**: No framework can fully prevent occasional hallucinations or mis‑joins.
    
* **Complex multi‑step analytics**: Long, multi‑CTE analytics may exceed token budgets. Consider prebuilt views or “tooling” that lets the app expose safe stored procedures.
    
* **Stateful chats**: Carrying lots of conversation context increases tokens and error surface. Be deliberate with memory.
    

### Performance & Cost Tuning Recipe

* **Keep schema small** (views, not raw tables).
    
* **Short prompts** with a handful of quality few‑shots.
    
* **Cache** schema summaries and reuse the engine.
    
* **LIMIT early**, and use **Postgres statement timeouts**.
    
* Prefer a **smaller model** for straightforward queries; upgrade only when needed.
    
* Introduce **result caching** or materialised views for popular questions.
    

### Future Improvements

* **AST validator**: Integrate `sqlglot` to parse and verify only safe SELECT shapes (no cross joins, bounded GROUP BY, approved functions).
    
* **Query templates**: Allow approved parameterized templates for high‑risk analytics while still supporting free‑text for low‑risk ones.
    
* **Observability to Azure**: Push traces/metrics to **Azure Monitor / App Insights**; add OpenTelemetry spans for each stage.
    
* **Synonym & unit dictionary**: Map business terms (e.g., **“AU”**, **“LTM”**) and units (e.g., *k*, *m*) to canonical forms pre‑prompt.
    
* **Partial structured outputs**: Ask the model for YAML‑typed intents before emitting SQL, it can be easier to validate.
    

*Link to example github repository:* [https://github.com/waqassiddiqi/llamaindex-postgres-nlp-starter](https://github.com/waqassiddiqi/llamaindex-postgres-nlp-starter)