Execute Query (Relational DB) v1.0.6 Help ​

Executes SQL queries against a relational database table using PostgreSQL.

How can I use the Step? ​

The Step lets you manage relational databases in the Relational DB service. You can use the Step to retrieve data, modify records, perform complex data transformations, and more.

How does the Step work? ​

You select the database and provide the SQL query you want to execute, along with any necessary query parameters. The Step takes these inputs to run the SQL statement against the selected table in the database.

Prerequisites ​

Before you get started, make sure to meet the following conditions:

  • The Step requires a database table in the Relational DB service.
  • If you want to use an external database, connect it to the Relational DB service first.

How to connect an external database? ​

To connect an external database to our Relational DB service, follow these steps:

  1. Go to Data > Relational DB and click +Create database.

  2. In the modal window, select the Connect external database checkbox, and enter the following details:

    • Name: Database name. Ensure it matches the name of the external database you want to connect.
    • Host: Machine or server of the external database location.
    • Port: Network port number to establish the connection.
    • Username: User name to authenticate the user.
    • Password: Password to authenticate the user and grant them access to the database.

  3. Click Create. If the connection is successful, the external database appears in the list of all databases in your account.

Database settings ​

To set up the section, do the following:

  • For Database, select a database name from the list provided by the Relational DB service or input one using the Merge field. If you use the Merge field, the options to choose an internal or an external database appear - make sure to select an appropriate one.
  • Optional: For Saved query, select one of the previously saved queries in the Relational DB service. This query automatically populates the Query field in Query settings.

Query settings ​

The Step supports the PostgreSQL query language, providing advanced SQL features such as foreign keys, subqueries, and triggers. To learn more, see the PostgreSQL documentation.

To set up the section, follow these steps:

  1. For Query, enter the SQL query you want to execute.
  2. Optional: For Query parameters, specify an array of parameters (empty by default).
  3. Optional: Enable Log query to add query information to the session log.

Note: Request has a time limit of 2 minutes. If the time limit is exceeded, the Step throws a socket hang-up error, and the Flow goes to the error exit. However, the query continues to run in the background without reporting success or failure. Therefore, building SQL queries that can be completed within 2 minutes is important.

Query parameters ​

Specify query parameters in the form of an array.

Query parameters allow you to write parameterized queries to prevent SQL injection attacks. The database uses provided query parameters regardless of what user input supplies.

For example, if you run the following parametrized query:

js
Query: 
      INSERT INTO users(name, email) 
      VALUES($1, $2)
      RETURNING *

Query parameters:
      ['brianc', 'brian.m.carlson@gmail.com']
Query: 
      INSERT INTO users(name, email) 
      VALUES($1, $2)
      RETURNING *

Query parameters:
      ['brianc', 'brian.m.carlson@gmail.com']

Instead of values $1 and $2, the database uses provided query parameters brianc and brian.m.carlson@gmail.com.

Merge field settings ​

The Step returns the result as a JSON object and stores it in the Merge field variable. So you can use the retrieved data in subsequent Steps or other Flows.

Output example ​

The output depends on the development and the settings you provide. It contains information about the SQL query that has been executed.

For example, if you run the following SQL query against the mock_data table:

sql
INSERT INTO mock_data(id, first_name, last_name, email) 
VALUES (1010, 'John', 'Doe', 'john.doe@test.com')
INSERT INTO mock_data(id, first_name, last_name, email) 
VALUES (1010, 'John', 'Doe', 'john.doe@test.com')

The resulting JSON object looks like this:

json
{
  "command": "INSERT",
  "rowCount": 1,
  "rows": [],
  "fields": [],
}
{
  "command": "INSERT",
  "rowCount": 1,
  "rows": [],
  "fields": [],
}

Error handling ​

By default, the Handle error toggle is on, and the Step handles errors with a separate exit. If any error occurs during the Step execution, the Flow proceeds down the error exit.

If the Handle error toggle is disabled, the Step does not handle errors. In this case, if any error occurs during the Step execution, the Flow fails immediately after exceeding the Flow timeout. To prevent the Flow from being suspended and continue handling errors, you can place the Flow Error Handling Step before the main logic or your Flow.

Reporting ​

After the Step completes, it generates a report that includes its execution status and other details. You can customize the report by adjusting the Step's log level and adding tags.

Log level ​

By default, the Step's log level matches that of the Flow. You can change the Step's log level by selecting an appropriate option from the Log level dropdown.

Tags ​

Tags provide a way to classify and search for sessions based on their attributes. To create a new tag, specify its category, label, and value. You can then use tags to filter and group the sessions in the report.

Service dependencies ​

  • flow builder v2.34.0
  • postgresql v2.4.0

Release notes ​

v1.0.6

  • Add an external database selection for Database

v1.0.0

  • Initial release