Chat With Your Database! Build a Local SQL AI Agent to Query Databases (LangChain & Ollama)

TL;DR

The agent uses a tool-based loop (list tables, sample rows, describe schema, execute SQL) to plan a correct SQL query instead of generating SQL in one shot.

Briefing Cornell Notes

Briefing

A fully local “chat with your database” agent can translate natural-language questions into SQL, run the queries against a local SQLite database, and return business-friendly answers—complete with step-by-step tool usage and reasoning. The core value is practical: instead of building a rigid set of canned reports, the system plans an execution path (which tables to inspect, how to sample data, how to join schemas) and iterates until it can produce a correct result.

The setup uses a simple agent architecture: a chat history plus a toolset. The agent receives a user question, decides which tools to call, executes them against the database, and uses the tool outputs as feedback for the next action. The database in the demo is an e-commerce schema with tables for products, customers, orders, and a many-to-many relationship between orders and products. The dataset is intentionally self-contained and generated offline (the products/customers/orders content was produced using “quote 3.7”), so it isn’t pulled from an external source.

On the model side, the project is configured to run locally via Ollama, with optional integration for Groq. A model configuration object stores the model name, temperature (set to 0 for reproducibility), and provider. The default choice is “coin 2.5” (7B) on Ollama, with a noted trade-off: some local models may not natively support tool calling, so the implementation uses a “template hack” to enable tool use. The configuration also sets a context window (248 tokens) to balance response speed and conversation length.

The agent’s behavior is driven by a custom system prompt that instructs it to plan tasks, use the available tools, and format results as Markdown (so Streamlit can render tables and lists cleanly). The prompt also includes time-aware logic: if a user asks about “how many days since” a past event, the agent can compute it using the current date/time injected into the prompt. Importantly, the prompt doesn’t hardcode the step-by-step workflow; it pushes the model to generate its own plan and tool sequence.

Execution is handled by an “ask” loop with a maximum-iteration cap to prevent infinite tool-calling. Each loop iteration invokes the model with the growing message history. If the model emits a tool call, the system runs the corresponding database tool, appends the tool result back into history, and continues. If no tool call is produced, the model’s final response is returned.

The toolset is deliberately small and SQL-focused: list tables, sample table rows, describe table schema, and execute arbitrary SQL. Each tool includes structured argument/return descriptions (via docstrings) so the model can call them correctly. Database access is managed through a SQLite cursor helper that supports read-only transactions and clean resource handling.

Finally, the agent is wrapped in a Streamlit UI that shows the database metadata in a sidebar and provides a chat interface. The demo queries prove the approach works: for example, it identifies the customer with the most placed orders (using a join across customer and order data) and then uses conversation history to answer follow-ups like listing that customer’s orders. The result is a reusable pattern: generic tools plus a planning prompt can turn local LLMs into interactive SQL analysts.

Cornell Notes

The system builds a local AI agent that turns natural-language questions into SQL, runs them on a local SQLite database, and returns Markdown-formatted answers. It relies on a simple agent loop: the model chooses from a small toolset (list tables, sample rows, describe schema, execute SQL), then uses tool outputs as feedback to plan the next step. A custom system prompt encourages planning rather than hardcoding a fixed workflow, and it includes time-aware instructions for “days since” style questions. An iteration cap prevents endless tool calling. Wrapped in Streamlit, the agent supports chat history for follow-up queries like “show me the orders this customer made,” demonstrating multi-step reasoning over the database schema.

What makes the agent “plan-and-execute” rather than just “text-to-SQL”?

The agent doesn’t jump straight to SQL. It first inspects the database using tools: it may call list tables to find relevant entities, sample table rows to understand data patterns, and describe table schema to learn column names and types. Only after that does it generate an SQL query (often including joins). Tool outputs are appended back into the conversation history, letting the model decide the next action based on what it learned.

How does the system prevent infinite loops during tool calling?

The ask function runs a while-loop with a maximum-iterations parameter. Each time the model requests a tool call, the loop executes the tool, appends the tool result to message history, and increments the iteration counter. If the loop can’t reach a final response before the cap, it raises a runtime error—so the agent “gives up” rather than continuing indefinitely.

Why include tools like “describe table” and “sample table” instead of only “execute SQL”?

Schema and data awareness reduce SQL mistakes. “describe table” returns column names and data types so the model can write correct queries and joins. “sample table” provides example rows, which helps the model infer value formats (IDs, dates, statuses) and confirm assumptions before running a full query. This is especially important when the database has multiple tables and relationships.

What role does the system prompt play in query quality and formatting?

The system prompt instructs the model to plan tasks, use the provided tools, and format the final response as Markdown. It also nudges the output toward tables and lists and targets business analysts and data scientists who may not know SQL well. It includes time-aware guidance by injecting the current date/time, enabling calculations like “how many days have passed since the last order.”

How does chat history improve follow-up questions?

After answering an initial question, the agent’s conversation history includes both the user request and the tool results/assistant response. In the follow-up test (“show me the orders that this customer made”), the agent can reuse the identified customer (e.g., Nicole Bond and its customer ID) rather than re-deriving it from scratch, then generate SQL to fetch the related orders.

What are the practical model trade-offs between Ollama and Groq in this setup?

The configuration supports both providers. For Ollama, the default model is “coin 2.5” with temperature 0, but tool calling may require a “template hack” because some local models don’t natively support tool calling. Groq models like “llama 3.3” are described as supporting tool calling more directly, which can improve tool-use reliability, though the demo still emphasizes coin 2.5 as a strong choice.

Review Questions

How does the agent decide which tool to call next, and what information does it use from prior tool outputs?
Why does the system cap the number of iterations in the ask loop, and what failure mode does that cap prevent?
What specific database tools are used to handle schema uncertainty, and how do they differ from executing SQL directly?

Key Points

1
The agent uses a tool-based loop (list tables, sample rows, describe schema, execute SQL) to plan a correct SQL query instead of generating SQL in one shot.
2
A custom system prompt drives planning, time-aware calculations, and Markdown output formatting suitable for Streamlit tables and lists.
3
SQLite access is wrapped in helper utilities that manage cursors and read-only transactions to keep database interactions safe and consistent.
4
A maximum-iteration cap in the ask loop prevents infinite tool-calling when the model gets stuck.
5
Tool argument/return descriptions (docstrings) help the model call functions with the right parameters and make debugging easier.
6
Streamlit provides a simple UI that exposes database metadata and supports chat-based follow-ups using in-session history.
7
The demo schema includes products, customers, orders, and a many-to-many relationship, requiring joins and multi-step inspection to answer realistic questions.

Highlights

The system turns natural-language questions into SQL by letting the model choose among a small set of database tools and iterating until it can answer.

A planning-first prompt avoids hardcoding the workflow; the model decides the tool sequence based on schema and sample data.

The “ask” loop appends tool results back into chat history, enabling follow-up questions like listing a previously identified customer’s orders.

The Streamlit UI renders results as Markdown, including tables and lists, making SQL outputs readable for non-SQL users.

Topics

Local SQL Agent
LangChain Tools
Ollama Tool Calling
SQLite Schema Introspection
Streamlit Chat UI

Mentioned

Venelin Valkov
SQL
AI
UI
LLM