Custom SQL tools
================

A SQL tool is a stored, parameterized query that an agent can invoke by
name. Use it to give an agent access to your own tables and business
logic, without writing any Rust or exposing a native AIDB function:

.. code:: sql

   SELECT aidb.create_sql_tool(
       name             => orders_by_customer,
       description      => Look up recent orders for a customer by id.,
       sql_statement    => SELECT id, status, total FROM orders WHERE customer_id = ${customer_id} ORDER BY created_at DESC LIMIT ${limit},
       params           => aidb.tool_params(
           aidb.tool_param(customer_id, INT, The customers id.),
           aidb.tool_param(limit, INT, Maximum number of orders to return.)
       ),
       read_only        => true,  -- optional; enforced, not just descriptive (see below). Default: true
       return_type_hint => NULL   -- optional; documents the returned row shape, same aidb.tool_params() shape as `params`. Default: NULL
   );

.. csv-table::
  :header: Parameter,Type,Default,Description
  :widths: 15,12,12,20
  :align: left
  :class: longtable

  `name`,TEXT,Required,Unique name for the tool.
  `description`,TEXT,Required,Shown to the model as part of the tool's definition.
  `sql_statement`,TEXT,Required,"The query to run, with `${name}` placeholders for each declared parameter."
  `params`,JSONB,Required,"The tool's parameters, built with `aidb.tool_params()`."
  `read_only`,BOOLEAN,`true`,"If `true`, reject the statement at registration time unless it's provably read-only."
  `return_type_hint`,JSONB,`NULL`,"Documents the query's return shape, in the same `aidb.tool_params()` shape. Not currently surfaced to the model."

``aidb.tool_param(name, type, description)`` builds one parameter;
``aidb.tool_params(...)`` combines several; ``aidb.tool_params()`` with
no arguments declares a tool that takes no parameters.

Register the tool on an agent the same way as any other:

.. code:: sql

   SELECT aidb.create_agent(sales_helper, Answer questions about customer orders., my_gpt,
       tools => ARRAY[orders_by_customer]);

Naming
------

A tool’s name must be unique across every tool type — it can’t collide
with a built-in :ref:`Native tools <Native tools>`  or a tool already imported from an
:ref:`MCP tools <MCP tools>`  . ``aidb.create_sql_tool()`` rejects the call up front if
it does.

Parameter placeholders
----------------------

Reference each declared parameter in ``sql_statement`` as ``${name}`` —
AIDB substitutes each with the value the model supplied for that call,
bound as a real query parameter (not string-interpolated). A parameter
used more than once in the statement only needs to be declared once.
``params`` is required; pass ``aidb.tool_params()`` with no arguments
for a tool that takes no parameters.

Read-only tools
---------------

``read_only`` defaults to ``true`` . Pass ``read_only => false`` for a
tool that needs to write — and that’s enforced, not just descriptive, in
both directions: left at the default ``true`` , registration fails
immediately if ``sql_statement`` matches a recognized write pattern
(``INSERT`` , ``UPDATE`` , ``DELETE`` , and so on), rather than letting
a mislabeled tool through. A tool left at the default
``read_only => true`` is also usable by an agent running in
:ref:`Read-only mode <Read-only mode>`  ; a tool explicitly marked ``read_only => false`` is
excluded from a read-only run, the same as an unclassified native or MCP
tool.

``sql_statement`` must be a single statement, and can’t be a utility
statement like ``EXPLAIN`` or ``COPY`` . Trailing whitespace and a
trailing ``;`` are stripped automatically when you register it.

!!!note ``create_sql_tool`` also accepts a ``return_type_hint``
argument, in the same ``aidb.tool_params()`` shape as ``params`` ,
describing the shape of the rows the query returns. It’s stored
alongside the tool but isn’t currently surfaced to the model or to
``aidb.tools`` . For now, its only use is as documentation for anyone
inspecting aidb.sql_tool_registry directly.

Removing a tool
---------------

.. code:: sql

   SELECT aidb.delete_tool(orders_by_customer);

``aidb.delete_tool()`` also removes an :ref:`MCP tools <MCP tools>`  if the name you
pass belongs to one instead of a SQL tool, but built-in native tools
can’t be deleted.