db

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

Fields

Either profile or connectionString must be given. A database profile carries the connection string and the pool settings. When the profile sets a connection string, an agent cannot override the connection string.

Field	Type	Default	Description
`onError`	command block		Commands to run if this command raises an uncaught error.
`profile`	profile name		Database connection profile defined in the configuration. Either this or 'connectionString' must be specified.
`connectionString`	dynamic string		The database connection string. Either this or 'profile' must be specified. It must specify an async driver installed in your Python environment: sqlite+aiosqlite, mysql+aiomysql, postgresql+asyncpg or oracle+oracledb.
`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 row is 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 are passed to the driver and are never interpolated into the statement.
The connection string names an async driver, which must be installed in the server's Python environment. sqlite+aiosqlite works out of the box. 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 synchronous operations that load the CPU can cause performance issues in the fully asynchronous server.
The default timeout is 30 seconds. A timeout or a connection failure raises an error that onError can catch.