4.9

A SQL agent over a local SQLite database

A SQL agent cannot change data alone when its read connection refuses writes and each write needs approval, but it can show every row it reads.

Someone asks for an agent that answers questions about the sales database. The first prototype passes the model a connection string and one tool that runs any SQL. It works in the demo. Then a user types "delete the cancelled orders", and the model writes a DELETE.

When you finish this section, you can build a SQL agent that reads through a read-only connection and writes only after an approval. You can also tell a model failure from a framework failure in its run.

What the demo builds

The demo answers questions about the order book of a small bakery. The script builds the SQLite file on every run, with four tables: customers, products, orders, and order_items. The rows come from a seeded random generator, so each run starts from the same data. Nothing is downloaded.

The agent has three tools. describe_database returns every table with its row count, its columns, and its foreign keys, in one call. run_select runs one read query. request_change runs one write, and only after a person approves it.

The demo applies these sections:

The database path as context

The model never sees the path of the file. The docs present runtime context as the place for a dependency such as a database connection. The demo declares a dataclass Shop with one field, db, and passes it as context_schema. Each tool takes a runtime: ToolRuntime[Shop] argument, which ToolNode fills and leaves out of the schema that the model gets.

pythonthe agent, with context and review on the write toolFull log
    agent = create_agent(
        model(),
        tools=[describe_database, run_select, request_change],
        system_prompt=INSTRUCTIONS,
        context_schema=Shop,
        middleware=[
            HumanInTheLoopMiddleware(
                interrupt_on={"request_change": {"allowed_decisions": ["approve", "reject"], "description": "A change to the bakery database waits for review"}},
                interrupt_mode="per_call",
            )
        ],
        checkpointer=InMemorySaver(),
    )

Each call to invoke passes the same Shop value, on the first request and on each resume:

pythonone question, with its contextFull log
        result = agent.invoke({"messages": [{"role": "user", "content": question}]}, config, context=shop, version="v2")

The read path

run_select checks the SQL with guard, then runs it on a connection from reader. The connection refuses writes in three ways. The URI opens the file with mode=ro, and the query_only pragma refuses writes. An authorizer from the Python sqlite3 module denies every action except a read.

pythonthe read-only connection and the guardFull log
def reader(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.execute("PRAGMA query_only = ON")
    conn.set_authorizer(lambda action, *_: sqlite3.SQLITE_OK if action in READ_ACTIONS else sqlite3.SQLITE_DENY)
    return conn


def guard(sql: str) -> str:
    text = sql.strip().rstrip(";").strip()
    bare = QUOTED.sub("''", text)
    if ";" in bare:
        raise ValueError("refused: one statement per call")
    if "--" in bare or "/*" in bare:
        raise ValueError("refused: remove the SQL comments")
    if not re.match(r"(?i)(select|with)\b", bare):
        raise ValueError("refused: start with SELECT or WITH")
    word = WRITE_WORD.search(bare)
    if word:
        raise ValueError(f"refused: {word.group(0).upper()} changes data, so call request_change")
    if not TAIL_LIMIT.search(text):
        text = f"{text} LIMIT {MAX_ROWS + 1}"
    return text

guard blanks each string literal before it looks for a write keyword, so a text value such as 'delete' is not a write. REPLACE counts as a write unless an opening parenthesis follows it, as in the replace() function. A query with no LIMIT n, LIMIT n, m, or LIMIT n OFFSET m at its end gets LIMIT 26.

The guard alone is not a defence, because a model can write SQL that the regular expressions do not expect. The read-only connection is the defence. The guard gives the model a clear message before SQLite refuses the statement. run_select shows at most 25 rows, and it says so in the result when it cuts more.

Before the agent runs, the script sends 13 fixed queries through guard and two through reader. The log of each run prints the result of each one:

outputthe guard cases, Qwen3 8B runFull log
== guard
  sent      SELECT name FROM products  =>  SELECT name FROM products LIMIT 26
…
  sent      SELECT 'drop table orders; delete' AS note  =>  SELECT 'drop table orders; delete' AS note LIMIT 26
…
  refused   WITH n AS (SELECT 1) REPLACE INTO products (id, name, category, price_cents) SELECT 1, ' …  =>  refused: REPLACE changes data, so call request_change
  refused   WITH old AS (SELECT id FROM orders) DELETE FROM orders WHERE id IN old  =>  refused: DELETE changes data, so call request_change
  refused   SELECT 1; DROP TABLE orders  =>  refused: one statement per call
…
  refused   DELETE FROM orders  =>  refused: start with SELECT or WITH
  refused   UPDATE products SET price_cents = 0  =>  refused: start with SELECT or WITH
  reader    DELETE FROM orders  =>  DatabaseError: not authorized
  reader    SELECT COUNT(*) FROM orders  =>  [(60,)]
Five accepted cases and one refused case are cut. The long cases are cut by the script at 90 characters.

The two WITH cases start like a read and still change data, so the keyword check is the step that refuses them. The reader lines skip the guard. SQLite itself refused the DELETE.

The read-only connection stops writes. It does not stop the agent from reading a sensitive row and showing it to the user. Before you use real data, limit the connection to the tables and columns that the user can see.

The write path

request_change is an ordinary tool that opens a connection that can write. The middleware makes it safe. In per_call mode, each call to the tool raises its own interrupt before the tool body runs. The interrupt value carries the id of the tool call, its name, and its arguments.

The demo answers each interrupt with a reviewer written in code, not a model. It approves an INSERT into products and refuses every other change. In a real system, a person reads the statement and the reason before the resume. The interrupt needs a checkpointer, so the agent has InMemorySaver.

Qwen3 8B added the product after one approval:

outputthe approved insert, Qwen3 8BFull log
== add-product: Add a new product: Rye Loaf, category bread, price 4.50.
  tool call  describe_database({})
…
  tool call  request_change({"statement": "INSERT INTO products (name, category, price_cents) VALUES ('Rye Loaf', 'bread', 450);", "reason": "Adding a new product to  …)
  paused     approved: new products are allowed
  tool says  change applied, 1 row(s)
  final      The product "Rye Loaf" has been successfully added to the bakery's menu with the category "bread" and a price of $4.50.
  3 model calls, 1524 prompt tokens, 111 output tokens, 6.6 s
The result of describe_database is cut.

Qwen3 4B looked up the cancelled orders and proposed a DELETE of four ids. The reviewer refused it. The middleware sent the reason back to the model as the result of the call, and no row changed:

outputthe refused delete, Qwen3 4BFull log
== drop-cancelled: Delete all of the cancelled orders.
  tool call  describe_database({})
…
  tool call  run_select({"sql": "SELECT o.id FROM orders o WHERE o.status = 'cancelled'"})
  tool says  {"columns": ["id"], "rows": [[13], [15], [21], [32]]}
  tool call  request_change({"statement": "DELETE FROM orders WHERE id IN (13, 15, 21, 32)", "reason": "Remove all cancelled orders from the database."})
  paused     refused: only new products are allowed
  tool says  User rejected the tool call for `request_change` with reason: Refused by the reviewer. This agent can only add products.
  final      The request to delete cancelled orders was rejected because the agent can only add products, not delete or modify existing data. No changes were made to the database.
The result of describe_database is cut.

The recorded run

The demo asks five questions. They ask for the tables and their row counts, the customer with the largest total on paid orders, and the orders of March 2026. Then they ask to add a product and to delete the cancelled orders. The script computes the right answers with its own SQL first. It runs once with Qwen3 4B and once with Qwen3 8B on Ollama, at temperature 0 and seed 42, with reasoning off.

We measured one run of each model on the Mac, Apple M1 Max, 10 cores, 64 GiB, with Ollama 0.31.1. Each model got the same five questions, so n is 5 per model. A read counts as right when the answer names the right values. A write counts as right when the database holds the right rows afterwards. Raw runs

QuestionQwen3 4BQwen3 8B
the tables and their rowsright, 2 model calls, 4.0 sright, 2 model calls, 4.9 s
the top customerright, Garden Bistro at 100550 cents, 3 calls, 2.5 sright, the same, 3 calls, 7.8 s
the orders of March 2026right, 18, 3 calls, 1.7 sright, 18, 3 calls, 4.0 s
add a productapproved, 1 row added, 4 calls, 10.4 sapproved, 1 row added, 3 calls, 6.6 s
delete the cancelled ordersproposed, refused, 4 kept, 4 calls, 3.3 sproposed, refused, 4 kept, 4 calls, 8.9 s

Both models got all five right, and each one called describe_database first on every question. Qwen3 4B used 9,862 prompt tokens over the five questions, and Qwen3 8B used 8,739. The most model calls on one question was 4, so no question came near the recursion limit.

What the run shows

Read the table in two layers. The framework part held in every row. No write ran without an approval, the read connection refused a write, and the cancelled orders were still there at the end.

The rest are model results, and two of them are wrong in the text only. Qwen3 4B stored a price of 450 cents and then told the user the price was 4.50 cents. Qwen3 8B asked for all orders before its refused DELETE. The tool returns at most 25 rows, and it got 25 with the note that the result was cut. Then it ended its turn with a promise of a list that it never sent. LangChain did what the code asked in each case.

LangChain (Python): langchain, langchain-core, langgraph, deepagents, langsmith 1.5.0 · c2f4036 · 2026-11-11 · Edition 2026.11