db

Runs a SQL statement against a relational database and returns the rows.

Fields

Every db command names a database profile, which carries the connection string and the pool settings. The databases an agent can reach are therefore the ones the profiles name.

Field	Type	Default	Description
`onError`	command block		Commands to run if this command raises an uncaught error.
`profile`required	string		Database connection profile defined in the configuration.
`sql`	dynamic string		The SQL query to execute.
`params`	dynamic dict		Parameters that are set in the query.
`timeout`	dynamic string or integer	30	Query timeout in seconds.

Result

The result is a list of rows, each a dict keyed by column name. A statement that returns no rows, such as an INSERT, returns an empty list. A query that returns more rows than the max rows from a db call setting fails; see Runtime limits.

Example

"db": {
  "profile": "orders",
  "sql": "select id, status, total from orders where customer_email = :email order by created_at desc limit 20",
  "params": { "email": "{ sys.userEmail }" }
},
"var": { "orders": "{ result }" }

Rules

Named parameters (:email) with params are safer than SQL built from variables. Parameters go to the driver and are never interpolated into the statement.
The profile's connection string names an async driver, which must be installed in the agent server's Python environment. sqlite+aiosqlite works without extra installation. postgresql+asyncpg, mysql+aiomysql, oracle+oracledb and mssql+aioodbc are optional extras of the search2o package. aioodbc presents an async API but wraps the synchronous ODBC driver underneath, so work that loads the CPU there can slow the fully asynchronous agent server.
The default timeout is 30 seconds. A timeout or a connection failure raises an error that onError can catch.