# LlamaIndex SQL: Safe Natural Language Queries

> Build secure LlamaIndex text-to-SQL tools, optimize schema context, inspect generated SQL, and choose between query engines and agents.

## Introduction

**LlamaIndex SQL** can turn a plain-language question into a database query, run it, and explain the result. The challenge is giving the model enough schema context to choose the right tables and columns without filling the prompt with the whole database. TL;DR: keep the schema narrow, inspect generated SQL, and add schema retrieval only as the database grows. For focused, one-question-at-a-time tools, **NLSQLTableQueryEngine** turns questions into answers directly.

This guide covers building that path, improving schema selection, and choosing between text-to-SQL LlamaIndex and a LangChain SQL agent. You'll learn to:

- connect a LlamaIndex database safely;
- restrict and describe the relevant schema;
- inspect generated SQL before trusting an answer;
- move to schema retrieval only when the database grows.

![LlamaIndex text-to-SQL query engine and retriever guide](/assets/llamaindex-text-to-sql-guide.webp)

*LlamaIndex’s official guide covers query-engine, schema-retrieval, row-retrieval, and column-retrieval patterns, while warning that arbitrary SQL execution requires restricted roles and other safeguards.*

## How NLSQLTableQueryEngine Handles Natural Language SQL

**NLSQLTableQueryEngine** answers natural-language questions over relational data. For each request, it gives the model the user's words, SQL dialect, and schema, runs the generated SQL through the configured connection, and can turn the rows into a readable answer. The official [LlamaIndex text-to-SQL guide](https://docs.llamaindex.ai/en/stable/examples/index_structs/struct_indices/SQLIndexDemo.html) describes this as retrieval plus response synthesis.

| Stage | Input | Output |
|---|---|---|
| Question | Which campaign had the best return last month? | Natural-language request |
| Schema context | Campaign, spend, conversion, and revenue fields | A map for the model |
| SQL generation | Question plus schema | A SQL statement |
| Execution | Generated SQL | Database rows |
| Synthesis | Rows plus the original question | A readable answer |

In a single-query scenario, one focused database operation answers each request. The SQL may still include a join, aggregation, subquery, or common table expression, provided the application needs no open-ended tool-call loop.

A LlamaIndex database wrapper does not teach the model undocumented business rules, and text-to-SQL code does not replace access control. It only provides a controlled route from language to SQL; accuracy still depends on schema context, model quality, and testing.

## LlamaIndex SQL or LangChain SQL Agent?

For a known reporting area, I'd start with **LlamaIndex SQL** and a narrow table list; fewer moving parts make generated SQL easier to inspect. Choose an agent to explore unfamiliar databases, retry errors, call multiple tools, or combine database work with other actions.

| Decision point | NLSQLTableQueryEngine | LangChain SQL agent |
|---|---|---|
| Typical job | One question, one focused answer | Multi-step investigation |
| Table handling | Fixed tables or a supplied table retriever | Agent lists tables and requests schemas |
| Error recovery | Add application logic if retries are needed | Agent can revise SQL after an error |
| Query checking | Your validation layer | Toolkit includes a query-checking tool |
| Predictability | Higher when scope is narrow | More flexible, with more steps and model calls |
| Best fit | Embedded reports, internal lookup, simple analytics | Exploratory analysis and tool-rich workflows |

LangChain's current [SQL agent tutorial](https://docs.langchain.com/oss/python/langchain/sql-agent) shows an agent listing tables, fetching schemas, generating and checking SQL, executing it, and correcting errors. That helps when the route is unknown but adds needless machinery when every question concerns two marketing tables.

This is a default, not a platform limit. LlamaIndex supports agents and workflows; LangChain supports simple chains. For optimized single queries, NLSQLTableQueryEngine makes requests easier to measure, secure, and explain.

## Build Your First LlamaIndex SQL Database Query

Start with a small SQLite copy or read-only analytics database. Suppose campaigns has campaign_id, name, channel, and spend; conversions has campaign_id, converted_at, and revenue. Initially, expose only those tables through the **LlamaIndex database** integration.

1. Install LlamaIndex, its model integration, SQLAlchemy, and the driver for your database.
2. Create the SQLAlchemy engine.
3. Wrap it with SQLDatabase and limit visible tables.
4. Build NLSQLTableQueryEngine with the same table list.
5. Run a known question and inspect both the answer and generated SQL.

~~~python
from sqlalchemy import create_engine
from llama_index.core import SQLDatabase
from llama_index.core.query_engine import NLSQLTableQueryEngine

db_engine = create_engine('sqlite:///marketing.db')

sql_database = SQLDatabase(
db_engine,
include_tables=['campaigns', 'conversions'],
)

query_engine = NLSQLTableQueryEngine(
sql_database=sql_database,
tables=['campaigns', 'conversions'],
context_str_prefix=(
'Revenue is stored in USD. Return on ad spend is '
'SUM(revenue) / SUM(spend). Use converted_at for date filters.'
),
synthesize_response=True,
verbose=True,
)

response = query_engine.query(
'Which campaign had the highest return on ad spend last month?'
)

print(response)
print(response.metadata.get('sql_query'))
~~~

Before constructing the engine, configure the language model through LlamaIndex's current model integration or Settings. Package imports change over time, so pin tested versions and compare the example with the [current LlamaIndex source signature](https://github.com/run-llama/llama_index/blob/main/llama-index-core/llama_index/core/indices/struct_store/sql_query.py).

Do not trust a plausible sentence: verify the date field, join, numerator, denominator, and null handling. Fluent answers built on wrong SQL remain wrong.

## Improve Schema Context for Text-to-SQL LlamaIndex

Schema context maps natural language to SQL; column names tell only part of the story. The model also needs table grain, join paths, business definitions, time zones, units, and coded-value meanings. Documenting one spend row per campaign and many conversion rows prevents spend multiplication after a join.

| Database size | Schema strategy | Why |
|---|---|---|
| 1 to 5 relevant tables | Pass an explicit tables list | Smallest prompt and simplest behavior |
| Several domains with known routes | Create one engine per domain | Keeps marketing and IT meanings separate |
| Many tables with unknown relevance | Retrieve table descriptions at query time | Avoids sending the entire schema |
| Ambiguous values or codes | Retrieve example rows or column values | Helps map user words to stored values |

The LlamaIndex guide warns that pulling every schema can overflow the model's context. Its [advanced text-to-SQL example](https://docs.llamaindex.ai/en/stable/examples/pipeline/query_pipeline_sql/) therefore retrieves relevant table schemas and optional sample rows. Start with fixed tables; retrieval adds an embedding index, tuning, and another failure point.

When needed, describe SQLTableSchema objects in business language and create an ObjectIndex. Set similarity_top_k as low as possible while retaining required joins; start by testing two or three tables, not treating that range as universal.

- State the table's grain: one row per order, user, or daily campaign.
- Name primary and foreign-key relationships in plain language.
- Define metrics such as active customer, net revenue, and resolved incident.
- Record units, time zones, soft-delete rules, and valid status values.
- Keep secrets and personal data out of schema descriptions and sample-row indexes.

Schema retrieval often beats a longer generic prompt by supplying the question's specific facts.

## SQL Schema Retrieval Without Losing Single-Query Simplicity

A large **LlamaIndex database** does not require a full agent; you can keep one query call while selecting schema per request. Index well-described table objects, then pass the retriever to SQLTableRetrieverQueryEngine or the current NLSQLTableQueryEngine table_retriever parameter.

~~~python
from llama_index.core import VectorStoreIndex
from llama_index.core.objects import (
ObjectIndex,
SQLTableNodeMapping,
SQLTableSchema,
)
from llama_index.core.query_engine import SQLTableRetrieverQueryEngine

table_schemas = [
SQLTableSchema(
table_name='campaigns',
context_str='One row per campaign; spend is total campaign spend in USD.',
),
SQLTableSchema(
table_name='conversions',
context_str='One row per conversion; join on campaign_id; revenue is USD.',
),
SQLTableSchema(
table_name='support_tickets',
context_str='One row per IT ticket; resolved_at is null while open.',
),
]

mapping = SQLTableNodeMapping(sql_database)
table_index = ObjectIndex.from_objects(
table_schemas,
mapping,
VectorStoreIndex,
)

retrieval_engine = SQLTableRetrieverQueryEngine(
sql_database,
table_index.as_retriever(similarity_top_k=2),
)
~~~

This retrieves SQL schemas and generates a database query within one query-engine request. Test the retriever separately. For each evaluation question, record whether top results included the required tables. Even a perfect SQL generator fails if the join table never reaches its prompt.

The [Spider 2.0 benchmark](https://spider2-sql.github.io/) contains **632** enterprise tasks, with databases often exceeding **1,000 columns** and workflows that may exceed 100 SQL lines. Its launch results reported 17.1% success for o1-preview and 10.1% for GPT-4o, versus 86.6% for GPT-4o on Spider 1.0. The gap warns against treating complex enterprise workflows as bigger single queries.

## Integration Patterns for Optimized LlamaIndex SQL Queries

In production, separate natural language SQL generation, validation, execution, and presentation behind one user request. NLSQLTableQueryEngine can generate SQL without running it via sql_only; disable synthesize_response when the application needs raw results.

| Pattern | Configuration | Good use |
|---|---|---|
| Direct answer | Execute and synthesize | Low-risk internal lookup |
| Review before run | sql_only enabled | Analyst approval or sensitive data |
| Structured API | Synthesis disabled | Charting, exports, and downstream code |
| Domain endpoint | One engine and table set per domain | Marketing, finance, or IT portals |

Use this request path:

1. Classify the request into an approved data domain.
2. Select a fixed NLSQLTableQueryEngine or a tested schema retriever.
3. Generate SQL and reject statements outside the allowed policy.
4. Apply row limits, query timeouts, and cost controls.
5. Execute with read-only credentials.
6. Return rows or synthesize a short answer.
7. Log the question, selected schema, SQL, timing, and outcome without storing sensitive values.

Caching repeated questions can cut costs, but keys must include the normalized question, schema version, tenant, and data-freshness rule to avoid serving yesterday's answer as current. For dashboards, I prefer structured rows with application code handling labels and formatting. Synthesize a response only when a short explanation helps.

## Accuracy, Safety, and Common LlamaIndex SQL Pitfalls

Generated SQL is executable input, so database safety comes first. LlamaIndex's documentation and source recommend restricted roles, read-only databases, and sandboxing, which matter more than prompts asking the model to be careful.

| Item | What to check | Why it matters |
|---|---|---|
| Permissions | SELECT-only user and approved views | Blocks writes if generation fails |
| Scope | Explicit tables, columns, and tenant filters | Reduces data exposure |
| Query cost | LIMIT, timeout, and scanned-data ceiling | Prevents runaway warehouse bills |
| Validation | Parse SQL and allow approved statement types | Catches multiple statements and unsafe commands |
| Evaluation | Known questions with expected rows | Measures correctness, not confidence |
| Monitoring | SQL, latency, errors, and empty-result rate | Finds drift after schema changes |

Build a prelaunch test set. In a narrow use case, fifty representative questions can expose obvious problems. If 45 return expected rows, execution accuracy is 90%, an internal measurement, not proof every future question will work. Include awkward dates, nulls, duplicate joins, zero denominators, misspelled business terms, and requests that should be refused.

Common failures and fixes:

- **Wrong metric**: document the business formula in schema context.
- **Double counting**: state each table's grain and test joins with known totals.
- **Wrong date**: identify the business date and time zone.
- **Missing table**: raise retrieval recall or improve table descriptions.
- **Valid but expensive SQL**: impose timeouts and inspect query plans.
- **Convincing wrong answer**: display or retain the generated SQL for review.

## Four Practical Text-to-SQL LlamaIndex Examples

Start with bounded questions, small named schemas, clear checks, and answers a subject specialist can verify.

| Team | Natural-language question | Needed context | Verification |
|---|---|---|---|
| Marketing | Which paid campaign had the highest ROAS last month? | ROAS formula, currency, conversion date | Compare with a trusted monthly report |
| IT | Which service had the most P1 incidents this quarter? | Severity values, service join, business time zone | Match the incident dashboard total |
| Finance | Which customers have invoices more than 30 days overdue? | Due date, paid status, credit-note treatment | Reconcile a sample with accounts receivable |
| Customer success | What was churn by plan in June? | Churn definition, plan history, effective dates | Check several customer timelines manually |

Marketing may need only a fixed NLSQLTableQueryEngine over campaigns and conversions; IT may need incidents and services. Finance should usually query a selected view because raw payment tables can obscure accounting rules. Churn is most deceptive: if the organization cannot agree on a plain-language definition, the model cannot invent one.

Start with ten questions from one team, recording the correct SQL or expected result for each. Add question types only after the set is stable. The slower pace is less thrilling but produces a LlamaIndex SQL tool people can trust.

## Conclusion

**NLSQLTableQueryEngine** suits cases where one question maps to one focused database answer. Keep the LlamaIndex database scope small, document business meaning alongside column names, inspect generated SQL, and compare execution results with a test set. Add SQL schema retrieval only when fixed table lists fail; use a LangChain SQL agent or other multi-step workflow when tasks must inspect the database, retry failed queries, and coordinate tools.

Begin with one domain and ten verified questions. Give the connection SELECT-only access, log the selected schema and SQL, and expand only after results survive real schema changes. That turns text-to-SQL LlamaIndex from demo into useful database interface.

## Frequently asked questions

### Does the user need to know SQL?

No. Users can ask in ordinary language, but a database expert must define context and review tests.

### Can one request join several tables?

Yes. Single-query describes the interaction, not the table count. Supply join relationships and every required table.

### Should every table go into the prompt?

Usually not. Use a small explicit list when possible, and schema retrieval when relevant tables are unpredictable.

### Is text-to-SQL LlamaIndex accurate enough for automatic decisions?

Accuracy is use-case specific. Validate against expected results and require human review for financial, legal, personnel, or other high-impact decisions.

### What happens when the schema changes?

Rebuild schema descriptions or indexes, rerun the evaluation set, and version the deployed configuration.

### Can NLSQLTableQueryEngine write data?

The model may generate unsafe SQL if the connection allows it. Use read-only credentials and validation; never rely on the model to enforce policy.

The engine removes SQL syntax from the user's job, not database governance from the operator's.

### When should I use a fixed table list instead of schema retrieval?

Use a fixed table list when questions stay within a small, known reporting area. Add schema retrieval only when relevant tables vary enough that maintaining narrow table sets becomes impractical.

### How can I verify that a generated answer is correct?

Inspect the generated SQL and compare its results with trusted reports or known test cases. Pay particular attention to joins, date filters, metric formulas, null handling, and duplicate rows.

### How should I prevent LlamaIndex SQL from changing database data?

Connect with SELECT-only credentials and expose only approved tables or views. Validate generated statements before execution, because prompt instructions alone cannot reliably block unsafe SQL.

### What business context should I provide beyond column names?

Document each table’s row-level grain, join relationships, metric definitions, units, time zones, status values, and deletion rules. This context helps prevent common errors such as double counting revenue or filtering on the wrong date.

### When is a LangChain SQL agent a better fit?

Choose an agent when the task requires exploring an unfamiliar schema, checking and retrying failed queries, or coordinating database work with other tools. For predictable questions in one domain, a focused LlamaIndex query engine is usually simpler to test and govern.

### What production controls should surround generated SQL?

Apply row limits, timeouts, approved-statement validation, tenant restrictions, and query-cost controls before execution. Log the question, selected schema, SQL, latency, and outcome while excluding sensitive data.

### What should I do after the database schema changes?

Update table descriptions and rebuild any schema-retrieval index that depends on them. Then rerun the verified question set and version the configuration before deploying the change.

---

[View the canonical page](https://dbsilk.com/blog/llamaindex-nlsqltablequeryengine-guide/) · [Browse llms.txt](https://dbsilk.com/llms.txt)