RAG for Structured Data: Querying SQL Databases with LLMs

Q: How does LangChain build text-to-SQL systems?

SQLDatabase.from_uri connects the schema; create_sql_query_chain generates SQL from schema-in-context; QuerySQLDatabaseTool executes it. For iteration, SQLDatabaseToolkit plus a ReAct agent lets the LLM explore tables, run queries, read errors, and refine.

Contents

Plot twist time. We've spent eleven tutorials teaching LLMs to dig answers out of text — chunks, graphs, PDFs, the works. But here's the thing nobody warns you about: most of the world's knowledge isn't text at all. It lives in Postgres. In a warehouse. In neatly normalized tables with foreign keys and decades of carefully maintained structure. And the moment your RAG bot tries to answer "who were our top 5 customers last quarter?" by embedding rows of a database as if they were paragraphs, you've officially brought a poetry book to a math exam.

I did exactly that, obviously. :/ A client wanted a chat interface over their sales data, and I — fresh off this whole series — dutifully chunked their database exports into text, embedded everything, and built a beautiful vector pipeline. It worked... for exactly the questions where the answer happened to be sitting in one pre-exported row. The moment someone asked for a computation — totals, counts, comparisons, aggregations — my bot confidently recited whatever number happened to be nearest in embedding space. Today we fix this properly, because the right tool for structured data isn't retrieval at all — it's text-to-SQL.

Why Vector RAG Fails on Databases

Let's diagnose, as always. Why does your beloved chunk-based pipeline faceplant on structured data?

Answers require computation — total revenue by region doesn't exist in any row; it's an aggregation over thousands of rows. No chunk contains it.
Exact matching beats semantics — user asks for customer ID 48213. Fuzzy similarity politely returns customer 48210, and nobody notices until finance does.
You can't embed the future — the data changes constantly. Embed a sales table tonight and it's stale by tomorrow's first order.
Fresh exports are a maintenance nightmare — the pipeline I shipped needed a nightly job to re-dump, re-chunk, and re-embed the whole database. For a query that SQL answers in 40 milliseconds.

Here's the mental shift: text RAG retrieves existing answers; text-to-SQL computes new ones. When your data lives in tables, you don't want to retrieve the answer — you want to generate the query that produces it. Big difference, and the difference between a demo and a production system.

Enter Text-to-SQL

Text-to-SQL (sometimes called NL2SQL) does something beautifully simple:

Give the LLM your database schema — tables, columns, types, relationships
The user asks a question in plain English
The LLM writes a SQL query answering it
You execute that query and get real data back
The LLM reads the result and writes a natural-language answer

Sound familiar? It should. Remember the GraphRAG article, where the power came from walking actual relationships instead of fuzzy-matching blobs? Text-to-SQL is the same philosophical win, one level down: you stop approximating the data and start interrogating it directly, in its native language.

And unlike retrieval, the answer is always current — no re-indexing, no stale embeddings, no nightly export job. The query runs against the live database every time. That alone killed my nightly re-embedding cron job, and I mourned it for exactly zero seconds.

The Security Talk (Read This Part Twice)

Before we write a single line of connection code, we need to have the talk. You're about to let an LLM generate code that runs against your database. The same LLM that, earlier in this series, confidently hallucinated refund policies. A few non-negotiables:

Read-only credentials. Always. The account the LLM connects with gets SELECT and nothing else. No DROP, no UPDATE, not even by accident.
Connection limits and timeouts — cap query duration and row counts, or one pathological SELECT * FROM orders takes your app down with it.
Never interpolate user input into the query — the LLM parameterizes values, and you sanitize table/column identifiers against an allowlist.
Log every generated query — when something goes weird, you'll want the receipts.

IMO, a text-to-SQL system without read-only credentials isn't a prototype — it's an incident report waiting for a date. Let's build responsibly instead.

Building It with LangChain

LangChain ships strong SQL machinery. Install and connect:

pip install langchain langchain-openai langchain-community

from langchain_community.utilities import SQLDatabase
from langchain.chains import create_sql_query_chain
from langchain_community.tools import QuerySQLDatabaseTool
from langchain_openai import ChatOpenAI

db = SQLDatabase.from_uri(
    "postgresql+psycopg2://readonly_user:pass@localhost/sales_db"
)

llm = ChatOpenAI(model="gpt-4o", temperature=0)

write_query = create_sql_query_chain(llm, db)
execute_query = QuerySQLDatabaseTool(db=db)

sql = write_query.invoke({
    "question": "Who were the top 5 customers by revenue last quarter?"
})
result = execute_query.invoke({"query": sql})
print(sql)    # SELECT c.name, SUM(o.total) ... GROUP BY ... ORDER BY ... LIMIT 5
print(result) # [("Acme Corp", 482300.0), ...]

Notice what the chain actually did: create_sql_query_chain reads the schema — table names, columns, types, sample rows — and injects it into the prompt. The LLM isn't guessing that customers live in a customers table; it's been told, in the prompt, with the full map. Schema-in-context is the foundation of every text-to-SQL system, and its quality caps everything downstream. (You've heard that sentence pattern before in this series, haven't you? Extraction for PDFs, schema for SQL — the front door always matters most. :D)

The Full Agent Upgrade

A single chain writes one query and prays. But real questions need iteration — the LLM checks the result, notices something off, and tries again. Sound familiar? It's the agentic RAG grade-and-retry pattern from article eight, transplanted to SQL:

from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langgraph.prebuilt import create_react_agent

toolkit = SQLDatabaseToolkit(db=db, llm=llm)
tools = toolkit.get_tools()

agent = create_react_agent(llm, tools, prompt=SYSTEM_PROMPT)

result = agent.invoke({
    "messages": [("user",
        "Which region grew fastest last quarter, "
        "and by what percentage?")]
})

Now the LLM gets tools: list_tables, schema_sql_database, query_sql_database. It can explore the schema on its own, write a query, eyeball the results, and refine. For the fastest growing region question, that might mean: list tables → inspect the schema → query total revenue by region for both quarters → compute the growth — multiple hops, self-directed. This is exactly the multi-hop reasoning we needed agents for in the RAG world, except now the hops are SQL statements against live data.

Text-to-SQL NL2SQL LLM Query Structured Database RAG

Figure 1: Text-to-SQL — generate queries against live tables instead of embedding stale rows

Image Alt Text: "RAG for structured data querying SQL databases with LLMs via text-to-SQL"

The Techniques That Make It Actually Work

Text-to-SQL demos are easy; production is hard. Here's what separates them:

Few-Shot Examples (Schema Linking by Example)

LLMs write dramatically better SQL when shown your house style. LangChain supports few-shotting with FewShotPromptTemplate:

examples = [
    {
        "question": "How many orders did Acme place in March?",
        "query": "SELECT COUNT(*) FROM orders o "
                 "JOIN customers c ON o.customer_id = c.id "
                 "WHERE c.name = 'Acme Corp' "
                 "AND o.created_at >= '2024-03-01' "
                 "AND o.created_at < '2024-04-01'"
    },
]

A handful of gold examples teaches the model your conventions: how you handle dates, how joins work in your schema, how you prefer aggregations. Same lesson as fine-tuning the embedder — domain knowledge, delivered via examples, beats generic cleverness.

Validate Before You Execute

The single best trick in this entire article: never trust generated SQL on faith. The LLM will occasionally hallucinate a column — customer_name when your column is called cust_nm. Two cheap defenses:

db.run() errors are your friend. Catch the database's complaint, feed the error back to the LLM, and let it rewrite. That feedback loop fixes 90% of schema hallucinations automatically — the database is a better teacher than any prompt.
create_sql_query_chain with a query checker — a second pass that validates syntax and table references before execution costs one cheap call and saves endless confusion.

Curate the Schema, Don't Dump It

Big databases have hundreds of tables, and dumping every schema into the prompt wastes context and invites wrong-table queries. Instead:

Include only relevant tables per question — an LLM (or a retriever, ha!) picks the top tables for the query first
Add table and column descriptions — a comment like "fiscal quarter boundaries for finance reporting" on quarters saves the model from guesswork
Define business terms in the prompt — "revenue means orders.total excluding refunds" because your users say revenue and your database says something 40 characters longer

Technique	What it prevents	Cost
Few-shot examples	Wrong house style, bad date/join conventions	Prompt tokens
Error-feedback loop	Schema hallucinations (`customer_name` vs `cust_nm`)	Retry calls
Query checker	Syntax errors before execution	One cheap call
Curated schema	Wrong-table queries, bloated context	Curation effort
Read-only + timeouts	Disasters, runaway `SELECT *`	Setup once

Evaluating: Not RAGAS, But the Same Religion

You know I refuse to let you ship unmeasured systems, and text-to-SQL is no exception — but the scoreboard changes. RAGAS measures retrieval; here we measure execution accuracy: does the generated SQL return the same result as a hand-written gold query?

Build an eval set of (natural question, gold SQL) pairs, run your pipeline, and compare results — exact match on the returned data, not string-matching the SQL itself. There are two paths to the right answer; string matching punishes one of them, and your users won't care about SQL aesthetics, only correctness.

And keep the series habit of bucketing: single-table lookups, aggregations, multi-joins, time-window questions. I promise the accuracy curve won't be flat across buckets — my system scored near-perfect on lookups and fell apart on multi-join time comparisons, which told me exactly where the few-shot examples were needed. Numbers beat vibes, even when the vibes are wearing a database costume.

When Text-to-SQL vs. Text RAG?

My honest routing framework, because these are different tools and the worst architectures mash them together:

Text-to-SQL — counts, sums, trends, comparisons, who/what/when/how many over operational data
Vector RAG — policies, contracts, documentation, any prose where the answer is written down
Hybrid agents — the real-world winner. An agent with both tools: "What's our refund policy, and how many refunds did we issue last month?" routes to the vector store for the policy and to SQL for the count. This is the agentic routing pattern from the GraphRAG article, applied across data types instead of data shapes.

IMO, the hybrid agent is where every serious production system lands. Pure text-to-SQL bots flub policy questions; pure RAG bots flub math. Give the router both tools and let it choose — that's the whole point of everything we've built in this series.

Recommended Books

Designing Data-Intensive Applications by Martin Kleppmann — SQL, indexes, and query planning fundamentals that make schema curation decisions obvious.
AI-Powered Search by Trey Grainger et al. — retrieval and query understanding patterns that extend from text into structured interfaces.
Designing Machine Learning Systems by Chip Huyen — evaluation discipline and routing architectures for hybrid text + SQL agents.

Unlock AI That Actually Works

Get lifetime access to GPT-6 Astra, Claude Fable 5.1, Gemini 3.5, Grok 4.5, and more — all in one platform. Build websites, apps, videos, content, and digital products from a single command. No monthly fees. No tool-hopping.

Click here to get GPTAstra Max now — one-time payment, lifetime access.

Frequently Asked Questions

Why does vector RAG fail on SQL databases?

Answers like total revenue by region require computation over thousands of rows — no single embedded row contains them. Exact IDs need exact matching, embeddings go stale as data changes, and nightly re-export jobs reimplement what SQL already answers in milliseconds.

What is text-to-SQL (NL2SQL)?

Text-to-SQL gives the LLM your database schema, converts a natural-language question into a SQL query, executes it against live data, and turns the result into a natural-language answer. Unlike retrieval, the answer is always current because the query computes it fresh.

How do I secure LLM-generated SQL?

Use read-only credentials with SELECT only, cap query duration and row counts, parameterize values instead of interpolating user input, allowlist table/column identifiers, and log every generated query. Never connect the LLM with write permissions.

How does LangChain build text-to-SQL systems?

SQLDatabase.from_uri connects the schema; create_sql_query_chain generates SQL from schema-in-context; QuerySQLDatabaseTool executes it. For iteration, SQLDatabaseToolkit plus a ReAct agent lets the LLM explore tables, run queries, read errors, and refine.

How do I evaluate text-to-SQL systems?

Measure execution accuracy: compare results from generated SQL against hand-written gold queries on (question, gold SQL) pairs — match returned data, not SQL strings. Bucket by lookup, aggregation, multi-join, and time-window complexity to find weak spots.

When should I use text-to-SQL vs vector RAG?

Use text-to-SQL for counts, sums, trends, comparisons, and operational metrics. Use vector RAG for policies, contracts, and documentation. Hybrid agents route each question — policy to the vector store, last month's refund count to SQL.

Wrapping Up

Recap: structured data doesn't want retrieval — it wants queries. Text-to-SQL hands the LLM your schema, lets it write SQL, executes read-only, and feeds results back for a natural-language answer. We built the LangChain chain and the tool-using agent, upgraded with few-shot examples, error-feedback loops, and curated schema context, and evaluated with execution accuracy bucketed by query complexity.

My parting take? This article completes an arc the whole series has been building toward: match the retrieval mechanism to the shape of the data. Text wants embeddings; relationships want graphs; tables want SQL. The moment you stop force-feeding every data type into a vector store and start routing by shape, your systems stop hallucinating and start answering.

So — go check what your current RAG bot does when someone asks it to count something. I'll wait. ;) And when you wire up that read-only user, actually verify the credentials can't write — the version of me that shipped the first one didn't, and the less said about that afternoon, the better. The data was fine. My dignity, less so.