A text-to-SQL agent lets you ask a company database a question in plain English and get an answer back. An LLM (large language model, the kind of AI behind ChatGPT) writes the SQL, the language databases understand. The agent checks that query, runs it safely and explains the result.
A sales manager wants to know which region fell short last month. The numbers already sit in a database, but getting them means asking an analyst who has a queue of other requests. A text-to-SQL agent shortens that wait for routine questions. It only works well when it is built with care, and this guide explains both halves: how it works, and where it fails.
What is an LLM, in plain words?
A large language model (LLM) is an AI system trained on a very large amount of text so that it can predict what text should come next. When you type a question into ChatGPT, Claude or Gemini, an LLM reads your words and writes a reply one small piece at a time.
The training text included books, websites and a great deal of code. So the model has seen many examples of people turning a request into a program. That is why an LLM can write SQL, even though nobody built it specifically for databases.
An LLM also has three gaps, and they explain how every text-to-SQL system is designed:
- It does not know your database. It has never seen your tables unless you show them to it.
- It does not know today's date unless you tell it. This matters because "last month" means a different month each time.
- It can be confidently wrong. It writes text that looks right. When that text is invented, it is called a hallucination.
The rest of this guide is mostly about closing those three gaps.
What are a database and SQL, and why do they matter here?
A database is an organised store of information, and most business databases keep it in tables. A table works like one sheet in Excel: each column is a type of information (region, date, amount) and each row is one record (one sale).
Here is a small sample table called sales. It is made-up data for this guide, not Ivy data.
| order_id | order_date | region | product | amount_inr |
|---|---|---|---|---|
| 1 | 2026-09-12 | North | Laptop | 55000 |
| 2 | 2026-09-20 | South | Phone | 22000 |
| 3 | 2026-10-02 | North | Phone | 18000 |
| 4 | 2026-10-05 | South | Laptop | 60000 |
| 5 | 2026-10-09 | East | Tablet | 25000 |
| 6 | 2026-10-14 | West | Laptop | 58000 |
| 7 | 2026-10-18 | North | Tablet | 24000 |
| 8 | 2026-10-21 | South | Phone | 20000 |
| 9 | 2026-10-25 | East | Laptop | 62000 |
| 10 | 2026-10-29 | West | Phone | 19000 |
| 11 | 2026-11-01 | North | Laptop | 57000 |
SQL (Structured Query Language) is the standard language for asking a database for data. One request written in SQL is called a query. Most queries use a handful of words:
| SQL word | What it means | Plain-English version |
|---|---|---|
SELECT |
Which columns do you want? | "Show me..." |
FROM |
Which table? | "...from the sales table" |
WHERE |
Which rows only? | "...only for October" |
GROUP BY |
Combine rows that share a value | "...one line per region" |
SUM(...) |
Add up the numbers | "...total of the amounts" |
ORDER BY |
Sort the result | "...biggest first" |
SQL is not hard to read, but it takes practice to write correctly, and that skill is what most business users do not have.
Why is company data hard for most people to reach?
Company data is hard to reach because answering a business question needs two skills: knowing the question and knowing how to write the SQL for it. Most managers have the first. Few have the second.
The questions themselves are usually simple: "Which regions fell short last month?" or "How has churn changed over six months?" The difficulty is the translation into a query. Dashboards help, but a dashboard only answers the questions someone planned in advance. When a new question comes up, the request goes back to an analyst and the cycle restarts.
The SQL does not disappear in the second workflow. It still runs on the database. The difference is that the user no longer has to write it.
What is text-to-SQL?
Text-to-SQL is the process of turning a question written in everyday language into a valid SQL query. The user asks conversationally, and the system produces the query that fetches the data.
Take the question: "Show me revenue by region for last month." Using the sales table above, and knowing that today is 2 November 2026, a text-to-SQL system produces this:
SELECT region, SUM(amount_inr) AS total_revenue
FROM sales
WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01'
GROUP BY region
ORDER BY total_revenue DESC;
Read it line by line:
SELECT region, SUM(amount_inr) AS total_revenueasks for the region and the total of all amounts, and names that totaltotal_revenue.FROM salessays the data comes from thesalestable.WHERE order_date >= '2026-10-01' AND order_date < '2026-11-01'keeps only October 2026. This is how "last month" becomes real dates. The model needs to be told today's date to work it out.GROUP BY regioncombines all rows of the same region into one line.ORDER BY total_revenue DESCputs the highest total first.
We ran this query on the sample data. The result:
| region | total_revenue (₹) |
|---|---|
| East | 87,000 |
| South | 80,000 |
| West | 77,000 |
| North | 42,000 |
You can check it by hand. October has 8 orders (orders 3 to 10), and 87,000 + 80,000 + 77,000 + 42,000 = ₹2,86,000. The sale on 1 November and the two September sales are correctly left out.
One more term: databases speak slightly different versions of SQL, called dialects. SQL Server, MySQL, PostgreSQL and SQLite handle dates and other functions differently. A good system tells the model which dialect to write.
On its own, text-to-SQL is a one-shot translation. The model writes a query and nobody checks it. That is where agents come in.
What is a text-to-SQL agent, and how is it different?
A text-to-SQL agent is an LLM that has been given tools and allowed to use them in a loop: act, look at the result, decide the next step. A plain text-to-SQL system translates. An agent translates, checks, runs and corrects.
Think of a careful junior analyst. They write a query, look at it before running it, run it, and if the database says something is wrong, they read the message and fix the query. An agent does the same. LangChain, a widely used framework for building agents, describes the same idea in its SQL agent tutorial: when a query fails, the error message goes back to the model, which writes a new query, until it works or a limit is reached.
Two more terms help here:
- A tool is an action the agent is allowed to take, such as "run this SQL query" or "list the tables".
- A schema is the description of your database: table names, column names, data types and how tables connect.
An agent is built in layers. The user only sees the first one.
| Layer | What it does | In our example |
|---|---|---|
| 1. User interface | Takes the question in a chat box | You type "Show me revenue by region for last month" |
| 2. LLM engine | Reads the question and the schema, decides which tables and columns are needed | Picks the sales table and the region and amount_inr columns |
| 3. Agent logic | Checks the query for safety and correctness, and retries if it fails | Confirms it is a single SELECT, fixes a wrong column name |
| 4. SQL database | Runs the approved query, with read-only permission | Returns four rows |
| 5. Response formatter | Turns raw rows into a readable answer | A short table and a one-line summary |
The agent logic layer is the one that separates a demo from something you can trust. It is where checks and retries live.
How does a text-to-SQL agent work, step by step?
A text-to-SQL agent works in six steps: take the question, understand it, write the SQL, check it, run it, and explain the result. If any step fails, the agent loops back and tries again a limited number of times.
Here is the same journey for our sample question:
| Step | What happens | In our example |
|---|---|---|
| 1. Natural language input | The user types a business question | "Show me revenue by region for last month" |
| 2. Intent interpretation | The model works out which tables, columns, filters and totals are needed | Needs sales, group by region, total of amount_inr, only October |
| 3. SQL generation | The model writes the query | The query shown earlier |
| 4. Validation | Code checks the query before it touches the database | Is it a single SELECT with no write commands? |
| 5. Database execution | The approved query runs and returns rows | Four rows, East to North |
| 6. Response delivery | The rows are turned into a readable answer | "East was highest in October at ₹87,000." |
What the model actually receives. The agent does not just forward your question. It builds a prompt like this, which closes two of the three gaps from the start of this guide:
Database schema:
CREATE TABLE sales (order_id INTEGER, order_date TEXT, region TEXT,
product TEXT, amount_inr INTEGER)
Today's date is 2026-11-02. Dates are stored as YYYY-MM-DD text.
Write one SQLite SELECT query that answers:
Show me revenue by region for last month
Return only the SQL.
What a retry looks like. Suppose the model's first attempt uses a column called revenue, which does not exist. The database replies with a real error message: no such column: revenue. The agent sends that message back to the model with the instruction to fix the query. The second attempt uses amount_inr and succeeds. We tested this exact sequence with the code later in this guide. This feedback loop is the core idea of an agent.
Question: “Show me revenue by region for last month.” The first generated query used a non-existent revenue column, so SQLite returned no such column: revenue.
Agent correction: The retry used the documented amount_inr column, grouped the October rows by region and returned East as the highest at ₹87,000. A manual total over the sample rows matched the result. This is a test against the guide’s small SQLite database—not a client result—and the complete validation and retry code appears below.
Where do businesses use text-to-SQL agents?
Businesses use text-to-SQL agents wherever managers ask routine questions of data that already sits in a database. The table shows typical questions by function.
| Business function | Sample question | What it saves |
|---|---|---|
| Finance | What are this quarter's profit margins compared with last year? | A wait for an analyst on a routine comparison |
| HR | What is the attrition rate by department over the last 6 months? | Quicker view of workforce trends |
| Sales | Which regions are behind target this month? | Pipeline visibility for sales managers |
| Operations | What was the average machine downtime last week? | Faster spotting of production bottlenecks |
| Marketing | Which campaigns had the highest conversion in Q3? | Campaign decisions backed by data |
| Customer success | How has customer churn changed over the last quarter? | Earlier action on retention |
Each of these still depends on the data being in the database and being reasonably clean. The agent can only answer questions the data can answer.
What are the benefits of text-to-SQL agents?
The main benefit is that non-technical people can get answers to routine data questions without waiting in a queue. The other benefits follow from that:
- Faster answers to routine questions. Simple "how many" and "which region" questions no longer need an analyst.
- Self-service for non-technical teams. Managers can explore a question and then ask a follow-up straight away.
- Better use of analyst time. Analysts spend less time on repetitive queries and more on analysis that needs judgement.
- Shorter request queues. Fewer routine tickets reach the data team.
- A habit of asking data questions. When asking is easy, more people do it.
We have not quoted a figure for time saved, because it varies by company and by how well the agent is set up. If you need a number for a business case, measure it in your own pilot.
What can go wrong, and how do you reduce the risk?
A text-to-SQL agent can give a wrong answer that looks right, so every deployment needs checks. The table lists the main risks and the usual mitigation for each.
| Risk | What it means | How to reduce it |
|---|---|---|
| Complex or undocumented schemas | Confusing table and column names lead the model to pick the wrong ones | Give the model clear descriptions of every table and column, with examples |
| Unclear business definitions | "Revenue" may mean before or after returns, and the model cannot know your rule | Write your definitions into the schema notes, such as "revenue = amount_inr, excluding returns" |
| Query accuracy | The query runs but answers a slightly different question than the one asked | Add automated checks and test on questions where you already know the answer |
| Hallucination | The model invents a table, column or filter | Check the query against the real schema, and let the database error trigger a retry |
| Data security | An unrestricted agent could expose sensitive data or change data | Use read-only database roles, narrow permissions, query logging and strict access control |
| Compliance | Regulated sectors need to show who accessed what | Log every question, query, user and result so there is an audit trail |
Security comes first. The model writes the SQL, so treat that SQL as untrusted until it has been checked. Give the agent a database login that can only read, and only the tables it needs. LangChain's own documentation warns that running model-generated SQL carries inherent risk, and says narrowly scoped permissions reduce the risk but do not remove it. Keep personal identifiers such as phone numbers, Aadhaar and PAN out of any table the agent can see.
How do you build a text-to-SQL agent?
You build a text-to-SQL agent either in the cloud, on your own servers (offline), or as a mix of both. The right choice depends on compliance needs, your IT team's skills and how much control over data you need.
| Cloud (online) | Offline (on-premise) | Hybrid | |
|---|---|---|---|
| How it works | The LLM runs on a cloud platform such as Azure AI Foundry, AWS Bedrock or Google Vertex AI | An open-source LLM runs on your own server, for example through Ollama or Hugging Face | The LLM runs in a secure cloud while the database stays on your premises, linked by secure APIs |
| Best when | You want fast deployment and built-in monitoring and compliance tools | Your rules demand that data never leaves your premises | You need cloud-scale AI but must keep data on-site |
| Trade-off | Your questions and schema go to a cloud provider | You manage hardware, model updates and performance | More moving parts to secure and maintain |
An API (application programming interface) is simply the way two pieces of software talk to each other. Ollama is a free tool that lets you run open-source language models on your own machine. Model names change every few months, so pick a current SQL-capable model from the platform's own catalogue.
The cloud approach, step by step
- Deploy an LLM on the platform. Choose a current model and deploy it with the platform's security, compliance and monitoring settings switched on.
- Define SQL execution as a tool. Set up "run this query" as a function the agent can call. This keeps thinking (the model) separate from doing (the database), which makes audits easier.
- Provide schema context. Give the model table names, column descriptions, relationships and a few sample rows.
- Generate SQL through structured tool calling. The agent passes its query to your function in a fixed format, so you control exactly what reaches the database.
- Validate and execute with read-only permission. Check syntax and logic, then run the query on the database with a read-only login.
- Return formatted results. Turn the raw rows into a table or a short summary.
For the code, follow LangChain's current SQL agent tutorial. It builds the agent with its create_agent function, so check the latest version before copying older examples that use create_sql_agent. Keep passwords and API keys in environment variables, never inside the code.
The offline approach, step by step
- Run a model locally. Use Ollama to run an open-source model that is good at SQL on your own server or private cloud.
- Connect to the internal database with a read-only service account.
- Build middleware that supplies the schema. Middleware is a small program that sits between the user and the model. Here it reads the table definitions and sends them to the model with every question.
- Validate before executing. Block anything that is not a single read-only SELECT.
- Return a structured answer as a table, text or an exported report.
Below is a complete working example of the offline approach using SQLite, a small database stored in a single file. We tested the logic by replacing the model with a fixed stand-in that first returns a wrong query and then a correct one. Before you use it with a real model, connect your own model in ask_llm and test it on a copy of your data.
import re
import sqlite3
from datetime import date
import ollama # pip install ollama ; pull a model first with "ollama pull <model-name>"
MODEL = "your-model-name" # any SQL-capable model you have pulled in Ollama
DB_FILE = "company_data.db"
# 1. Open the database in READ-ONLY mode. Even a wrong query cannot change data.
conn = sqlite3.connect(f"file:{DB_FILE}?mode=ro", uri=True)
def get_schema():
"""Read table and column names so the LLM knows what exists."""
rows = conn.execute("SELECT sql FROM sqlite_master WHERE type='table'").fetchall()
return "\n".join(r[0] for r in rows)
def ask_llm(prompt):
reply = ollama.chat(model=MODEL, messages=[{"role": "user", "content": prompt}])
return reply["message"]["content"]
def clean(sql):
"""Remove markdown fences the model may add around the query."""
return re.sub(r"```(?:sql)?", "", sql).strip()
BLOCKED = ("insert", "update", "delete", "drop", "alter", "create", "attach", "pragma", "replace")
def is_safe(sql):
"""2. Validation: one SELECT statement only, no write commands."""
text = sql.strip().rstrip(";")
if ";" in text or not text.lower().startswith("select"):
return False
return not any(re.search(rf"\b{word}\b", text.lower()) for word in BLOCKED)
def answer(question, max_tries=3):
schema = get_schema()
today = date.today().isoformat()
feedback = ""
for attempt in range(1, max_tries + 1):
prompt = (
f"Database schema:\n{schema}\n\n"
f"Today's date is {today}. Dates are stored as YYYY-MM-DD text.\n"
f"Write one SQLite SELECT query that answers: {question}\n"
f"Return only the SQL.{feedback}"
)
sql = clean(ask_llm(prompt))
if not is_safe(sql):
feedback = "\nYour last query was not a single safe SELECT. Try again."
continue
try:
cursor = conn.execute(sql) # 3. Execute
columns = [d[0] for d in cursor.description]
return sql, [dict(zip(columns, row)) for row in cursor.fetchall()]
except sqlite3.Error as error: # 4. Retry with the error message
feedback = f"\nYour last query failed with: {error}. Fix it."
raise RuntimeError("No valid query after several tries. Ask a person to check.")
if __name__ == "__main__":
sql, rows = answer("Show me revenue by region for last month")
print("SQL used:\n", sql)
print("Result:", rows)
What each part does:
- Read-only connection.
mode=romeans the database refuses any write. When we tried aDELETEon this connection, SQLite answered "attempt to write a readonly database". - Schema.
get_schemashows the model your real tables, closing the first gap from the start of this guide. - Today's date. The prompt includes it, closing the second gap.
- Validation.
is_safeaccepts only one SELECT and blocks write commands. It is a minimal guard, so in production add stronger checks and keep the read-only login as your main protection. - Retry loop. If the query fails, the error message goes back to the model, up to
max_triestimes. After that, a person takes over.
The hybrid approach. Many enterprises run the LLM in a secure cloud environment while the database stays on their premises, connected through secure APIs. This balances AI capability with data control, and it needs more care to set up and secure.
When should you not use a text-to-SQL agent?
You should not use a text-to-SQL agent when a simpler tool answers the need, or when a wrong answer would be costly and nobody will check it.
- When people ask the same few questions every week. A dashboard or a saved query is cheaper, faster and predictable. An agent earns its place when the questions keep changing.
- When the number must be exact for a legal or audit purpose. For statutory filings and audited figures, use certified reports. Use the agent to explore, not to sign off.
- When the database is messy and undocumented. If even your analysts struggle to say what a column means, the model will guess. Document the schema first.
- When your data fits in one spreadsheet. A filter or a pivot table in Excel or Google Sheets is simpler than building an agent.
- When nobody will review the answers. If there is no pilot and no one to check results against hand-written SQL, a confident wrong answer will go straight into a decision.
What should you do next?
- List 10 questions managers really ask, and pick one small group of tables that can answer them.
- Write a one-line description for every table and column, including your business definitions, such as what "revenue" includes. This is the single most useful input you can give the agent.
- Run a pilot on a read-only copy of the data. Have an analyst compare every answer with hand-written SQL, and widen access only after the answers hold up.
If you want to learn the skills behind this, see Ivy's Generative AI course and the Data Analytics and Generative AI course. For teams, see Ivy's enterprise AI programmes. More guides are in the GenAI / LLM section of the AI Help Center.
Frequently asked questions
What is text-to-SQL?
Text-to-SQL is the process of converting a question in everyday language into a SQL query that a database can run. An LLM usually does the conversion, using a description of the database to choose the right tables and columns.
Do I need to know SQL to use a text-to-SQL agent?
You do not need SQL to ask questions, but someone on your team must set the agent up and check its answers. Learning basic SQL helps you verify results, which is the safest way to use these tools.
Is a text-to-SQL agent the same as ChatGPT?
No. ChatGPT is a chat product built on an LLM. A text-to-SQL agent uses an LLM as one part, and adds a database connection, safety checks, a retry loop and a way to present results.
Can a text-to-SQL agent change or delete my data?
It should not, if you connect it with a read-only database login. This is why read-only access is the first safeguard. A validation check helps, but the permission on the database login is what actually prevents changes.
How accurate is a text-to-SQL agent?
Accuracy depends on how well your tables are documented, how clear the question is and which model you use, so no single figure applies to every company. Measure it on your own questions during a pilot, by comparing the agent's answers with queries an analyst writes by hand.
Can it run without sending data outside the company?
Yes. An offline setup runs an open-source model on your own servers, so questions and schema stay on your premises. The trade-off is that you manage the hardware, the model updates and the performance yourself.
Glossary: key terms in one line each
| Term | Meaning |
|---|---|
| LLM | Large language model: an AI trained on large amounts of text to predict and write text |
| Hallucination | A confident answer from an AI that is wrong or invented |
| Database | An organised store of information, usually made of tables |
| Table, row, column | A table is like a spreadsheet sheet; a row is one record; a column is one type of information |
| SQL | Structured Query Language, the standard way to ask a database for data |
| Query | One request written in SQL |
| Dialect | A database-specific version of SQL, such as SQL Server or PostgreSQL |
| Schema | The description of a database: tables, columns, data types and links between tables |
| Text-to-SQL | Turning a plain-language question into a SQL query |
| Agent | An LLM that can use tools in a loop: act, check the result, decide the next step |
| Tool | An action an agent may take, such as running a query |
| Validation | Automatic checks on a query before it runs |
| Read-only role | A database login that can look at data but cannot change it |
| API | The way two pieces of software talk to each other |
| Ollama | A free tool for running open-source language models on your own machine |
| On-premise | Running software on your own servers instead of in the cloud |
Sources and verification
- LangChain. Build a SQL agent.
- The sample table, query results and SQLite error shown in this guide were produced by running the included code against a small sample database.

By