Skip to content

Queries

The Queries page allows you to configure reusable SQL statements that your AI agents can execute against various datasources. Queries are essential for fetching business data, logging transactions, or updating records in external databases. If external, try to utilize APIs to communicate to databases as a secure abstraction. However, if API options are not available or you are building an internal agent, consider using queries to communicate to database(s) directly.

Prerequisites

Before you can manage Queries, ensure the following:

  1. You are logged into the application.
  2. Your user role has the necessary permissions (owner, admin, or queries).
  3. You have an Account and an Agent selected from the sidebar.
  4. You have configured a Connector if you intend to run queries against an external database.

Page Overview

The page provides functionalities to:

  • Add new query configurations.
  • Use an AI assistant to suggest SQL statements and variable mappings.
  • List existing queries associated with the selected agent.
  • Edit, duplicate, or delete existing queries.
  • Filter queries by statement type (e.g., SELECT, INSERT).

Creating a New Query

To create a new query, click on the "New Query" expander.

Helper Buttons

  • AI Suggest Configuration: Opens a dialog where you can describe your data goal (e.g., "Find customer by email"), and the AI will architect the SQL statement, map variables, and suggest result formats.
  • Add Variable: Opens a dialog to create a new variable directly on this page.
  • Lookup Names: Opens a lookup dialog to find exact names of existing Connectors, Variables, etc.

Configuration Fields

  • Name: A unique, descriptive name for the query.
  • Status: A toggle to enable or disable the query.
  • Agent(s): Select which agents are permitted to execute this query.
  • Connector: The datasource where the query will execute.
  • Description: Optional notes about the query's purpose.
  • Statement Type: Categorizes the operation (select, insert, update, delete, search, vector_search, upsert). This helps the system optimize execution.
  • Statement: The actual SQL code. Use %s for positional parameters or %(varname)s for named parameters to make the query dynamic.
  • Param Values: A comma-separated list of PinionAI variables that will be mapped to the placeholders in the statement.
    • Example Format: {var['email']}, {var['user_id']}
  • Returning Fields: (Optional) Comma-separated list of variable names to return if using a RETURNING clause.
  • Result Format:
    • list_of_tuples: Returns all rows as a list of arrays.
    • single_tuple: Returns only the first row as an array.
    • dict: Returns rows as key-value pairs (using column names as keys).
    • none: Used for operations like INSERT or DELETE that don't return data.
  • Result Variable: The variable name where the output will be stored in the agent's context.

Managing Existing Queries

Existing queries are listed in expanders. - Edit (:material/edit:): Modify the query configuration or SQL statement. - Delete (:material/delete:): Permanently remove the query. - Duplicate (via Edit or List): Create a copy of an existing query to use as a starting point for a new one.

Configuration Examples

Example 1: Fetching User Data (SELECT)

Retrieves specific columns based on a variable input.

  • Statement: SELECT full_name, phone FROM users WHERE email = %s
  • Param Values: {var['user_email']}
  • Result Format: dict
  • Result Variable: user_record
  • Usage: Access values via {var['user_record']['full_name']}.

Example 2: Logging an Activity (INSERT)

Logs data into a table with multiple parameters.

  • Statement: INSERT INTO activity_log (user_id, action) VALUES (%(uid)s, %(act)s)
  • Param Values: {var['current_user_id']}, {var['last_action']}
  • Statement Type: insert
  • Result Format: none

Example 3: Update with Returning Fields

Updates a record and captures the timestamp of the change.

  • Statement: UPDATE tickets SET status = 'closed' WHERE user_id = %s RETURNING closed_at
  • Param Values: {var['current_user_id']}
  • Returning Fields: closure_timestamp
  • Result Variable: update_status