Tool-Calling: the `run_sql` Contract and Its JSON Schema

Description

In the previous lesson, the model "requested" running run_sql. But how does the model know that tool exists, what it takes, and what it returns? This lesson answers that: tool-calling, and specifically the contract for the run_sql tool.

A tool contract has two sides. The first is the description you give the model — name, what it's for, and what parameters it accepts, in JSON Schema format — with that, the model knows it can request "run run_sql with query = 'SELECT ...'." The second is the implementation — the real function that runs the SQL and returns the result. This is where the module closes the loop with everything so far: M6's run_sql function reinvents nothing; it reuses Module 4's validator (does it parse? is it SELECT? do the columns exist?) and Module 5's guardrails (read-only connection, row cap). You'll see both halves of the contract, and the function actually running against Reservo with a good query, a hallucinated one, and a destructive one.

Connection to the module

Lesson 02 gave you the cycle (the while with a cap); this one gives you the engine of each loop: the tool the model invokes. Without a clear contract, the model wouldn't know what it can request; without a shielded implementation, the agent would have an open door to the database. Lesson 04 will show the exact back-and-forth shape with the Messages API; here we focus on the contract and on the implementation reusing M4+M5.

Analogy: the order form and the warehouse

Think of how you order something from a warehouse using a form. The form has a fixed format: it says what you can order ("a read-only query") and which box to fill in ("write your query here, as text"). You can't ask for just anything in just any way: the form defines the contract. The model, on seeing the form, knows exactly what to fill in.

On the other side of the counter is the warehouse clerk, who receives the form and does the work: checks that the order is valid (that you're not asking to "empty the inventory"), goes to the shelf, and brings you the goods — or hands you back a note saying "that doesn't exist." The form is the tool's description (the JSON Schema); the clerk is the implementation (the run_sql function). And since the clerk is the only person with the warehouse key, it's enough for them to check every order for the whole system to be under control. That's run_sql's role: one door, one place to validate and shield.

The first side of the contract: the description (JSON Schema)

For the model to know run_sql exists and how to use it, you describe it with a dictionary: name, description, and the parameters it accepts in JSON Schema format. This is the contract you'd send the model along with the question:

run_sql_tool = {
    "name": "run_sql",
    "description": (
        "Runs a READ-ONLY SQL query (SELECT) against the Reservo database "
        "(SQLite) and returns the resulting rows. The tables are: "
        "rooms, members, bookings, payments. Money is in cents (INTEGER). "
        "Returns {ok, columns, rows} on success, or {ok: false, error} on failure."
    ),
    "input_schema": {
        "type": "object",
        "properties": {
            "query": {
                "type": "string",
                "description": "The SELECT SQL query to run.",
            }
        },
        "required": ["query"],
    },
}

Let's break it down, because every field has a job:

• name ("run_sql"): the identifier the model uses to request the tool.
• description: the text the model reads to decide when and how to use it. Here we pack in three golden hints: that it's read-only (SELECT), the list of tables (a preview of Module 2, the schema as context, embedded in the description to reduce name hallucinations), and the shape of what it returns ({ok, columns, rows} or {ok: false, error}). A good description is prompting: the more precise, the better the model decides.
• input_schema: the JSON Schema for the parameters. type: "object" with a query property of type string, and required: ["query"] to say it's mandatory. The model, on seeing this, knows it must fill in exactly one query field with text.

That dictionary is all the model needs to request the tool correctly. It doesn't execute anything: it describes. Execution is the other half.

The second side of the contract: the real implementation (M4 + M5 inside)

Here's this module's contribution: the run_sql function that actually runs the SQL. And instead of reinventing validation and safety, it plugs in the pieces from modules 4 and 5. Let's start with what it reuses.

From Module 4, the shape-and-schema validator (does it parse? is it a single statement? is it SELECT? do the columns exist?):

import sqlite3, re, json

def strip_sql(sql):
    """Removes comments and leading/trailing whitespace (M4)."""
    sql = re.sub(r"--[^\n]*", "", sql)
    sql = re.sub(r"/\*.*?\*/", "", sql, flags=re.DOTALL)
    return sql.strip()

def validate(sql, con):
    """Validates shape + schema WITHOUT running the query (M4). Returns (ok, error)."""
    clean = strip_sql(sql)
    if not clean:
        return False, "empty query"
    body = clean.rstrip(";").strip()
    if ";" in body:                                   # a single statement?
        return False, "more than one statement"
    first = body.split(None, 1)[0].upper()            # is it a read?
    if first not in ("SELECT", "WITH"):
        return False, f"not a read (starts with {first})"
    try:                                              # does it parse and do the columns exist?
        con.execute("EXPLAIN " + body).fetchall()     # EXPLAIN compiles, doesn't run
    except sqlite3.Error as e:
        return False, f"{type(e).__name__}: {e}"
    return True, None

From Module 5, the strongest guardrail: a read-only connection. PRAGMA query_only = ON makes that connection unable to write even if someone tries to sneak in a DELETE:

def open_reservo(path="reservo.db"):
    """Opens Reservo in READ-ONLY mode (M5 guardrail)."""
    con = sqlite3.connect(path)
    con.execute("PRAGMA query_only = ON")   # the connection can't write
    return con

And now, the tool that brings both halves of the contract together with execution. Notice it validates (M4) before running, runs on the read-only connection (M5), applies a row cap (M5, so a query can't bring back millions of rows), and always returns the same uniform dictionary:

def run_sql(query, con, max_rows=100):
    """The agent's ONLY door to Reservo. Validates (M4), runs with guardrails (M5),
    and returns {ok, columns, rows, truncated} or {ok: false, error}."""
    ok, err = validate(query, con)          # M4: doesn't touch the DB if the SQL is invalid
    if not ok:
        return {"ok": False, "error": err}
    body = strip_sql(query).rstrip(";").strip()
    try:
        cur = con.execute(body)             # M5: con is read-only
        rows = cur.fetchmany(max_rows + 1)  # M5: row cap (fetches one extra to detect it)
        return {"ok": True,
                "columns": [d[0] for d in cur.description],
                "rows": [list(r) for r in rows[:max_rows]],
                "truncated": len(rows) > max_rows}
    except sqlite3.Error as e:
        return {"ok": False, "error": f"{type(e).__name__}: {e}"}

That uniform return shape — ok, and then either columns+rows or error — is what the description promised the model. The agent always knows how to read the response, on success or on failure. And the error, when there is one, carries the engine's exact message — the same no such column from M4 — which in lesson 07 the model will use to correct itself.

Worked example: run_sql executed

Let's see both halves of the contract working together. We run run_sql with four queries: a good one, one with a hallucinated column, a destructive one, and two statements stuck together.

con = open_reservo()

# 1) Good query
print(json.dumps(run_sql(
    "SELECT name, capacity FROM rooms WHERE capacity >= 4 ORDER BY capacity", con),
    ensure_ascii=False))

# 2) Hallucinated column (b.total doesn't exist)
print(json.dumps(run_sql("SELECT SUM(b.total) FROM bookings b", con), ensure_ascii=False))

# 3) Destructive (M4's allowlist rejects it for not being SELECT)
print(json.dumps(run_sql("DELETE FROM rooms", con), ensure_ascii=False))

# 4) Two statements stuck together
print(json.dumps(run_sql("SELECT COUNT(*) FROM rooms; DROP TABLE rooms", con), ensure_ascii=False))

con.close()

What to expect:

{"ok": true, "columns": ["name", "capacity"], "rows": [["Studio", 4], ["Lounge", 6], ["Boardroom", 10]], "truncated": false}
{"ok": false, "error": "OperationalError: no such column: b.total"}
{"ok": false, "error": "not a read (starts with DELETE)"}
{"ok": false, "error": "more than one statement"}

Four queries, four responses from the same contract:

1. The good one returns ok: true with columns and rows — Studio, Lounge, Boardroom. The model can format this as an answer.
2. The hallucinated column returns ok: false with the engine's error. validate's EXPLAIN caught it before touching the data. The model knows it failed and why — a fact it'll use in lesson 07 to request corrected SQL.
3. The destructive one doesn't even reach the engine: M4's allowlist (not a read) rejects it for not starting with SELECT/WITH.
4. The two stuck-together statements get rejected on shape: more than one statement. The smuggled DROP TABLE never runs.

The guardrail that doesn't depend on the allowlist: the read-only connection

M4's allowlist (not a read) is a text check: it looks at the first word. It's fast and sufficient, but it's worth having a second line of defense at the connection level, in case a query ever slipped past the text check someday. That's PRAGMA query_only = ON: even if a DELETE reached the engine, the engine itself blocks it.

con = open_reservo()   # PRAGMA query_only = ON
try:
    con.execute("DELETE FROM bookings")   # bypassing run_sql, straight to the engine
except sqlite3.Error as e:
    print(f"{type(e).__name__}: {e}")
print("bookings intact:", con.execute("SELECT COUNT(*) FROM bookings").fetchone()[0])
con.close()

What to expect:

OperationalError: attempt to write a readonly database
bookings intact: 23

Even if the DELETE reaches the engine directly — completely bypassing run_sql and its allowlist — the read-only connection flatly rejects it: attempt to write a readonly database. The 23 bookings are still intact. Two layers for the same danger (M4's text and M5's connection) is exactly the defense in depth that makes the agent's only door safe. The details of each layer are in M4 and M5; here we're just confirming the tool reuses them.

The row cap in action

The other M5 guardrail run_sql reuses is the row cap: an agent shouldn't be able to bring a million rows into the model's context (expensive and dangerous). max_rows trims the result and flags truncated: true so the model knows there's more:

con = open_reservo()
print(json.dumps(run_sql("SELECT name FROM rooms ORDER BY id", con, max_rows=3), ensure_ascii=False))
con.close()

What to expect:

{"ok": true, "columns": ["name"], "rows": [["Focus"], ["Studio"], ["Boardroom"]], "truncated": true}

With max_rows=3, out of the 5 rooms only 3 come back, and truncated: true warns that the list got trimmed. In Reservo the tables are small and the default cap (100) never kicks in; the example lowers the cap to watch it work. The fetchmany(max_rows + 1) trick — fetching one extra row — is what lets you detect the trimming without counting the whole table.

Common mistakes

1. Believing the model executes the SQL. It doesn't. The model requests running run_sql (filling in query according to the input_schema); your program executes it. That separation is what lets you put validation and guardrails in place: the power to run SQL is in your function, not in the model.

2. A poor description. If the description doesn't say it's read-only, doesn't list the tables, and doesn't state the shape of the return, the model hallucinates more names and misreads the results. The tool description is prompting; write it with the same care as a system prompt (M3).

3. Reimplementing validation inside run_sql. The module's goal is to reuse M4 and M5, not rewrite them. The run_sql function calls validate (M4) and runs on the connection from open_reservo (M5). If you find yourself copying the EXPLAIN check or the PRAGMA, stop: it already exists.

4. Giving raw access to the database instead of a controlled tool. If run_sql were a plain con.execute(query) with no validate or query_only, it would be the key to destruction. The point of funneling everything through one function is that there's a single place to shield.

5. Returning different formats depending on the case. If sometimes you return a list of rows and sometimes an error string, the model (and your runner) have to guess the shape. The uniform {ok, ...} dictionary the description promises is what makes the back-and-forth predictable.

Exercises

Exercise 1: Reading the contract (Easy)

Given the lesson's run_sql_tool, answer without running anything: how many parameters does the tool accept, what's its name and type, and is it required? What three hints about the schema/behavior does the description include?

Exercise 2: Testing the success format (Medium)

Use run_sql to run SELECT tier, COUNT(*) AS n FROM members GROUP BY tier and show the dictionary it returns. Identify each key.

con = open_reservo()
print(json.dumps(run_sql("SELECT tier, COUNT(*) AS n FROM members GROUP BY tier", con),
                 ensure_ascii=False))
con.close()

{"ok": true, "columns": ["tier", "n"], "rows": [["basic", 4], ["pro", 4]], "truncated": false}

Exercise 3: A contract for a counting tool (Hard)

Design the contract (the dictionary with name, description, input_schema) for a hypothetical count_rows tool that takes a table name and returns how many rows it has. Don't implement it; just write the contract and explain how the model would know to use it.

count_rows_tool = {
    "name": "count_rows",
    "description": (
        "Counts the rows in a Reservo table and returns the total. "
        "Valid tables: rooms, members, bookings, payments. "
        "Returns {ok, count} on success, or {ok: false, error} if the table doesn't exist."
    ),
    "input_schema": {
        "type": "object",
        "properties": {
            "table": {
                "type": "string",
                "enum": ["rooms", "members", "bookings", "payments"],
                "description": "The name of the table to count.",
            }
        },
        "required": ["table"],
    },
}

Summary and next step

• Tool-calling has a two-sided contract. The description (name, description, input_schema in JSON Schema) tells the model what it can request and how; the implementation is the real function that runs.
• The description is prompting: including that it's read-only, the list of tables, and the shape of the return reduces hallucinations and helps the model read the results.
• M6's run_sql function reuses M4 and M5: it validates before running (validate), runs on a read-only connection (PRAGMA query_only = ON), and applies a row cap. It doesn't reinvent either layer.
• The return is a uniform dictionary — {ok, columns, rows, truncated} or {ok: false, error} — that the contract promises and the agent always knows how to read.
• Two layers against the destructive query (M4's text allowlist and M5's read-only connection) is defense in depth over the agent's only door.

Next lesson: The tool_use → tool_result shape of the Messages API — You'll see, as concept with claude-sonnet-5, the exact shape of the tool_use block the model uses to request the tool and the tool_result you use to hand back the result; and you'll confirm the function that would run is this lesson's real run_sql.

Additional resources

1. Claude — Tool use (function calling) — The input_schema, how the model reads the description to decide, and the shape of tool_use.
2. JSON Schema — The format used to describe a tool's input_schema (type, properties, required, enum).
3. SQLite — PRAGMA query_only — M5's guardrail that makes the read-only connection; the second layer against the destructive.
4. SQLite — EXPLAIN — M4's check that validate reuses to know whether the SQL parses and references real columns without running it.
5. Python — sqlite3.Cursor.fetchmany — Where the row cap comes from: fetching max_rows + 1 to detect the trimming.