Natural-Language SQLite
The previous edition of this chapter used SQLDatabaseChain from langchain_experimental.sql. That class has been deprecated for over a year, and the replacement is a proper LangGraph ReAct agent equipped with SQL tools. The new version is more code but strictly better along every axis: the agent inspects the schema before writing queries, checks its own SQL for mistakes, retries on errors, and stays entirely within the primitives you have already learned in Chapters “LangGraph 1.0 Fundamentals” through “Multi-Agent Supervisor Pattern”.
We will build a natural-language query interface over a small self-contained SQLite database of employees, departments, customers, and invoices. Everything runs locally: Ollama for the LLM, SQLite for the database, and raw SQLAlchemy (not langchain-community; more on why below) for the tools that give the agent its SQL access.
The sample database
The _make_db.py script builds company.db with four tables:
- departments (id, name)
- employees (id, first_name, last_name, department_id, hire_date, salary)
- customers (id, first_name, last_name, city, country)
- invoices (id, customer_id, employee_id, invoice_date, total)
Five employees across three departments, six customers across five countries, nine invoices. Small enough that you can eyeball whether the agent’s answers are correct, big enough that the queries are non-trivial (aggregations, joins, per-group sums).
Setup:
1 $ cd source-code/sql_agent
2 $ uv sync
3 $ ollama pull qwen3.5:4b
The first agent invocation triggers _make_db.py automatically if company.db is missing.
The four SQL tools
Older LangChain editions gave you this agent’s tools for free: langchain_community.agent_toolkits.SQLDatabaseToolkit wrapped a SQLDatabase object and handed back four ready-made Tool instances. That toolkit has no standalone LangChain 1.0 replacement, and pulling in the whole langchain-community package for four tools it does not otherwise need is not worth it for this book’s stack; langchain-community is not a dependency anywhere else in this book. So _agent.py reimplements the same four tools directly on top of SQLAlchemy, which is already a dependency for the database work:
| Tool | Purpose |
|---|---|
sql_db_list_tables |
List every table in the database. |
sql_db_schema |
Return the CREATE statement plus three sample rows for one or more tables. |
sql_db_query |
Execute a SQL query and return the resulting rows (or an error string). |
sql_db_query_checker |
An LLM-driven validator that reviews a query for obvious mistakes before it gets executed. |
Same tool names, same behavior as far as the agent is concerned, same system-prompt-driven procedure; only the implementation moved. sql_db_list_tables and sql_db_schema read from a sqlalchemy.MetaData object reflected once at startup; sql_db_query runs raw SQL through the SQLAlchemy engine and returns the rows as a string; sql_db_query_checker sends the query to the model with the same review prompt LangChain’s own checker tool used, so the model gets equally specific guidance about NOT IN with NULL, UNION versus UNION ALL, and the rest of the usual SQL mistakes.
That set is deliberately narrow. The agent has enough tooling to read the database and answer questions, but no way to mutate it. Combined with a system prompt that forbids INSERT/UPDATE/DELETE, the risk surface for pointing this at a production database is small, small enough that in practice I do point it at production replicas, though never at primary write databases.
Building the agent
We start by writing the agent library used by the later examples in this chapter source-code/sql_agent/_agent.py:
1 import sqlalchemy as sa
2 from langchain_core.messages import SystemMessage
3 from langchain_core.tools import tool
4 from langchain_ollama import ChatOllama
5 from langgraph.prebuilt import create_react_agent
6 from sqlalchemy.exc import SQLAlchemyError
7
8 from _make_db import DB_PATH, make_db
9
10 QUERY_CHECKER_PROMPT = """
11 {query}
12 Double check the {dialect} query above for common mistakes, including:
13 - Using NOT IN with NULL values
14 - Using UNION when UNION ALL should have been used
15 - Using BETWEEN for exclusive ranges
16 - Data type mismatch in predicates
17 - Properly quoting identifiers
18 - Using the correct number of arguments for functions
19 - Casting to the correct data type
20 - Using the proper columns for joins
21
22 If there are any of the above mistakes, rewrite the query. If there are no mistakes, just reproduce the original query.
23
24 Output the final SQL query only.
25
26 SQL Query: """
27
28 SYSTEM_PROMPT = SystemMessage(
29 content=(
30 "You are an assistant that answers questions about a SQLite database. "
31 "Use the available tools to explore and query the database. "
32 "Always follow this procedure:\n"
33 "1. Call sql_db_list_tables to see what tables exist.\n"
34 "2. Call sql_db_schema on any tables that look relevant to the question.\n"
35 "3. Write a SQL query that answers the question.\n"
36 "4. Call sql_db_query_checker on your query.\n"
37 "5. Call sql_db_query to execute the (possibly corrected) query.\n"
38 "6. Return a concise, plain-English answer to the user's question.\n\n"
39 "Never write INSERT, UPDATE, DELETE, DROP, CREATE, or ALTER statements. "
40 "Read-only queries only."
41 )
42 )
43
44
45 def _sample_rows(engine: sa.Engine, table: sa.Table, n: int = 3) -> str:
46 columns = "\t".join(col.name for col in table.columns)
47 with engine.connect() as conn:
48 rows = conn.execute(sa.select(table).limit(n)).fetchall()
49 rows_str = "\n".join("\t".join(str(v)[:100] for v in row) for row in rows)
50 return f"{n} rows from {table.name} table:\n{columns}\n{rows_str}"
51
52
53 def make_sql_tools(engine: sa.Engine, model: ChatOllama) -> list:
54 metadata = sa.MetaData()
55 metadata.reflect(bind=engine)
56
57 @tool
58 def sql_db_list_tables() -> str:
59 """Input is an empty string, output is a comma-separated list of tables in the database."""
60 return ", ".join(sorted(metadata.tables))
61
62 @tool
63 def sql_db_schema(table_names: str) -> str:
64 """Get the CREATE statement and sample rows for a comma-separated list of tables."""
65 chunks = []
66 for name in (t.strip() for t in table_names.split(",")):
67 table = metadata.tables.get(name)
68 if table is None:
69 return f"Error: table '{name}' not found"
70 create_stmt = str(sa.schema.CreateTable(table).compile(engine)).strip()
71 chunks.append(f"{create_stmt}\n\n/*\n{_sample_rows(engine, table)}\n*/")
72 return "\n\n".join(chunks)
73
74 @tool
75 def sql_db_query(query: str) -> str:
76 """Execute a SQL query against the database and get back the result.
77 If the query is not correct, an error message will be returned.
78 If an error is returned, rewrite the query, check the query, and try again."""
79 try:
80 with engine.begin() as conn:
81 result = conn.execute(sa.text(query))
82 if not result.returns_rows:
83 return ""
84 rows = [tuple(row) for row in result.fetchall()]
85 return str(rows) if rows else ""
86 except SQLAlchemyError as e:
87 return f"Error: {e}"
88
89 @tool
90 def sql_db_query_checker(query: str) -> str:
91 """Use this tool to double check if your query is correct before executing it.
92 Always use this tool before executing a query with sql_db_query!"""
93 prompt = QUERY_CHECKER_PROMPT.format(query=query, dialect=engine.dialect.name)
94 return model.invoke(prompt).content
95
96 return [sql_db_query, sql_db_schema, sql_db_list_tables, sql_db_query_checker]
97
98
99 def build_sql_agent():
100 if not DB_PATH.exists():
101 make_db()
102
103 engine = sa.create_engine(f"sqlite:///{DB_PATH}")
104 model = ChatOllama(
105 model="qwen3.5:4b",
106 temperature=0,
107 num_ctx=16336,
108 reasoning=False,
109 )
110 tools = make_sql_tools(engine, model)
111
112 return create_react_agent(model, tools, prompt=SYSTEM_PROMPT)
Four configuration decisions worth spelling out.
The system prompt. The six-step procedure is not optional. Without it, small local models tend to skip the schema inspection and write queries that reference columns that do not exist. The procedure is essentially “look before you leap, and double-check your work”: cheap on tokens, expensive to skip.
The prohibition on Data Manipulation Language (DML). LangChain does not enforce this; the agent could still write an INSERT if it wanted to. What actually prevents damage is (a) that we asked politely in the prompt and (b) that the sql_db_query tool ultimately runs against a SQLite file we own, so worst case is a lost company.db we regenerate. In production, the actual guarantee has to come from database permissions: the connection string points at a role that only has SELECT privileges. The prompt is a belt-and-suspenders layer, not the primary defense.
Passing the model into make_sql_tools. The sql_db_query_checker tool needs a model to review the query before it runs, so model is threaded into make_sql_tools(engine, model) and closed over by that one tool. It does not have to be the same model object as the outer agent (in some setups it makes sense to use a small, fast model here and a stronger one for the outer agent), but we use the same one for simplicity.
num_ctx=16336 and reasoning=False. These two settings are not optional. The ReAct loop produces a long transcript (the schema tool returns full CREATE statements plus sample rows, the query checker echoes the query back, and each tool call adds two messages), so the default 2k-token context window fills up within the first couple of steps and the model loses track of what it has already done; num_ctx=16336 leaves headroom for a full run. qwen3.5:4b is also a “thinking” model that by default wraps its output in <think>...</think> tags; with thinking on, the agent mis-serializes tool calls and can loop or return an empty answer because the real response never left the stripped think block. reasoning=False turns Ollama’s native think flag off so the model emits plain tool calls and a real final answer.
Running it
Here is a listing of the first example using the agent library 01_sql_agent.py:
1 from langchain_core.messages import HumanMessage
2 from _agent import build_sql_agent
3
4 agent = build_sql_agent()
5
6 QUESTIONS = [
7 # "How many employees are there?",
8 "Which employee has the highest salary?",
9 "Which customer has generated the most total revenue?",
10 "What is the total revenue per department?",
11 ]
12
13 for q in QUESTIONS:
14 print(f"USER: {q}")
15 result = agent.invoke(
16 {"messages": [HumanMessage(content=q)]},
17 config={"recursion_limit": 30},
18 )
19 final = result["messages"][-1]
20 print(f"AGENT: {final.content.strip()}\n")
The first question from earlier drafts of this script (“How many employees are there?”) is commented out above. It’s the simplest of the four (a bare COUNT(*), no ordering or join required), so the three that remain are the ones that actually put the agent’s schema-reading and query-checking loop to work. Uncomment it if you want a warm-up question.
Representative output, captured by actually running the script against the shipped company.db:
1 $ uv run 01_sql_agent.py
2 USER: Which employee has the highest salary?
3 AGENT: The employee with the highest salary is **Bob Brown**, who earns $110,000 per year.
4
5 USER: Which customer has generated the most total revenue?
6 AGENT: **Marie Dupont** has generated the most total revenue, with **$3,700**. This comes from two invoices: $2,100 on February 10 and another invoice (not shown in the sample data) that contributed to her total.
7
8 USER: What is the total revenue per department?
9 AGENT: The total revenue per department is:
10
11 | Department | Total Revenue |
12 |------------|---------------|
13 | Sales | $12,875.00 |
14
15 Based on the data in your database, only one invoice was generated by an employee from the Sales department (employee Carol Chen), which totaled $1,287.50 across two invoices ($1,250 + $850). There were no invoices associated with employees from Engineering or Support departments in this dataset.
Two things worth noticing in that transcript. First, the numbers that matter are real: Bob Brown genuinely has the highest salary ($110,000), Marie Dupont genuinely generated the most revenue ($3,700, across exactly two invoices), and $12,875.00 attributed entirely to Sales is exactly what running the aggregate query by hand returns; every invoice in this sample database happens to have been written by one of two Sales employees, so Engineering and Support really do show zero. Second, the prose wrapped around those numbers is not entirely trustworthy: the department answer says “only one invoice,” names a single employee, and offers a dollar breakdown ($1,250 + $850) that does not even sum to the total it claims. The tabulated answer is correct; the model’s own explanation of how it got there is partly confabulated. That gap (a right answer with an unreliable explanation bolted onto it) is exactly why you check an agent’s work by watching its tool calls, not by reading its summary. That is what streaming is for.
Watching the SQL get written
For debugging any agent that writes SQL, .stream() is essential as seen in the second example 02_stream_sql_agent.py:
1 for step in agent.stream(
2 {"messages": [HumanMessage(content=question)]},
3 config={"recursion_limit": 30},
4 ):
5 for node_name, node_output in step.items():
6 print(f"=== node: {node_name} ===")
7 for m in node_output.get("messages", []):
8 if getattr(m, "tool_calls", None):
9 for call in m.tool_calls:
10 args = str(call["args"])
11 if len(args) > 200:
12 args = args[:200] + "..."
13 print(f" tool_call: {call['name']}({args})")
14 if m.content:
15 snippet = m.content if len(m.content) < 300 else m.content[:300] + "..."
16 print(f" {type(m).__name__}: {snippet}")
17 print()
The real trace for “What is the total revenue per department?”, the same question behind the confabulated summary above:
1 $ uv run 02_stream_sql_agent.py
2 USER: What is the total revenue per department?
3
4 === node: agent ===
5 tool_call: sql_db_list_tables({})
6
7 === node: tools ===
8 ToolMessage: customers, departments, employees, invoices
9
10 === node: agent ===
11 tool_call: sql_db_schema({'table_names': 'departments,employees,invoices,customers'})
12
13 === node: tools ===
14 ToolMessage: CREATE TABLE departments (
15 id INTEGER,
16 name TEXT NOT NULL,
17 PRIMARY KEY (id)
18 )
19
20 /*
21 3 rows from departments table:
22 id name
23 1 Engineering
24 2 Sales
25 3 Support
26 */
27
28 CREATE TABLE employees (
29 id INTEGER,
30 first_name TEXT NOT NULL,
31 last_name TEXT NOT NULL,
32 department_id INTEGER NOT NULL,
33 hire_date...
34
35 === node: agent ===
36 tool_call: sql_db_query_checker({'query': 'SELECT d.name AS department, SUM(i.total) AS total_revenue FROM invoices i JOIN employees e ON i.employee_id = e.id JOIN departments d ON e.department_id = d.id GROUP BY d.id ORDER BY d.id'...)
37
38 === node: tools ===
39 ToolMessage: SELECT d.name AS department, SUM(i.total) AS total_revenue FROM invoices i JOIN employees e ON i.employee_id = e.id JOIN departments d ON e.department_id = d.id GROUP BY d.id ORDER BY d.id;
40
41 === node: agent ===
42 tool_call: sql_db_query({'query': 'SELECT d.name AS department, SUM(i.total) AS total_revenue FROM invoices i JOIN employees e ON i.employee_id = e.id JOIN departments d ON e.department_id = d.id GROUP BY d.id ORDER BY d.id'...)
43
44 === node: tools ===
45 ToolMessage: [('Sales', 12875.0)]
46
47 === node: agent ===
48 AIMessage: The total revenue per department is:
49
50 | Department | Total Revenue |
51 |------------|---------------|
52 | Sales | $12,875.00 |
53
54 Based on the data in your database, only one invoice was generated by an employee from the Sales department (employee Carol Chen), which totaled $1,287.50 across two in...
Four tool calls (list tables, get schema, check the query, run the query), then the final answer. Five steps, each one visible, and the trace explains the confabulation above. The sql_db_query result is a single bare row, [('Sales', 12875.0)]: no employee name attached anywhere. The only employee names that appear anywhere in the whole transcript come from the sql_db_schema step’s three-row sample of the employees table, which, because LIMIT 3 returns rows in primary-key order, shows Alice Anderson, Bob Brown, and Carol Chen, but not Dan Davis. Carol and Dan are both in Sales and both wrote invoices; the model only ever saw Carol by name. When it had to narrate an answer it did not have the receipts for, it reached for the one Sales employee it had actually observed. The lesson generalizes: an agent’s summary can smuggle in details from anywhere in its context window, not just from the tool call that produced the number you asked about, which is one more reason to check the trace instead of trusting the prose.
Where to take this next
Adapting this to a real project is a small number of changes:
- Point at a different database. Change the URI passed to
sa.create_engine(...). SQLAlchemy speaks PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, and everything else the reader is likely to have; nothing else inmake_sql_toolsorbuild_sql_agentneeds to change. - Restrict which tables the agent can see.
metadata.reflect(bind=engine, only=[...])limits reflection to a named subset of tables, sosql_db_list_tablesandsql_db_schemanever mention the rest. Useful when your database has hundreds of tables but you only want the agent looking at a curated subset. - Add an approval interrupt on
sql_db_query. Wrap the tool node with the pattern from Chapter “Human-in-the-Loop Patterns” to require human approval on any query that touches specific tables, or on any query whose EXPLAIN plan is expensive. - Add a checkpointer. The “Durable, Restart-Safe Agents” chapter’s
SqliteSavergives the agent per-thread conversation memory, useful when users ask a series of related questions (“…and what about last quarter?”).
What we covered
SQLDatabaseChainfrom the previous edition is deprecated, and its would-be LangChain 1.0 successorSQLDatabaseToolkithas no standalone replacement either; the modern approach is four small tools hand-rolled directly on SQLAlchemy.- Those four tools (list tables, get schema, run a query, check a query) give a ReAct agent everything it needs to answer read-only questions, and nothing it needs to change data.
- A system prompt with a fixed six-step procedure keeps small local models on track and enforces read-only behavior at the prompt layer.
create_react_agent(model, tools, prompt=SYSTEM_PROMPT)is the whole agent; every other primitive (checkpointer, HITL interrupts, supervisor) from earlier chapters composes with it directly..stream()is the primary debugging tool. A tabulated answer can be correct even when the agent’s prose explanation of it is not; the trace is how you tell the difference, and how you catch an agent quietly borrowing details from the wrong part of its own context window.
The next chapter “DBpedia and Wikidata as Agent Tools” leaves relational data behind for the semantic web, with SPARQL as the query language.