DB Query

The Database Query component allows you to make SQL-Queries. The component makes a connection to the specified SQL-like server using its configuration.

Supported databases are:

• MySQL
• MSSQL
• Postgres
• OracleDB (has been tested with OracleDB 23, support for older version might be limited)
• IBM Db2

note

If your username or password contains special characters, you need to URL encode them. For example, if your password is "p@ssw0rd", you need to use "p%40ssw0rd" in the connection string. You can use Javascript's encodeURIComponent function, or other tools to do this. Be aware to not put your credentials in tools you don't trust.

Configuration

Connection info

Connection String

The connection string to the database server.

Example: mysql://username:password@localhost:3306/xill4

Disable Connection String Encoding

By default, the connection string is encoded in order to parse it. If your username or password contains special characters, this might cause problems. With this option turned on you'll need to encode your username and password yourself. For example, if your password is "p@ssw0rd", you need to use "p%40ssw0rd" in the connection string. You can use Javascript's encodeURIComponent function, or other tools to do this. Be aware to not put your credentials in tools you don't trust.

Advanced Configuration

Any additional configuration that should be used to connect to the database. For more information on what options can be used, refer to this documentation

note

The Connection Options input field uses HandleBars. For more information, please read HandleBars

Example:

{
  "keepAlive": true,
  "application_name": "Xill4"
}

This is an example for connecting to Postgres.

{
  "options": {
    "trustServerCertificate": true,
    "tdsVersion": "7_1",
    "encrypt": false,
    "packetSize": 16368
  }
}

This setting could be used for connecting to MS SQL Server 2000 SP4. Note that "options" is only mandatory for MSSQL, not for others.

Use TLS

Enables TLS for the database connection.

Allow Invalid Certificates

If TLS is enabled, checking this will disable certificate validation and remove the need for a Certificate Authority File when using a Self-Signed Certificate.

warning

Specifying this option in a production environment makes your application insecure and potentially vulnerable to expired certificates and to foreign processes posing as valid client instances.

Client Certificate File

If TLS is enabled, a Client Certificate File is required. This field should contain the path to the certificate.

Example: .\client-cert.pem

Client Key File

If TLS is enabled, a Client Key File is required. This field should contain the path to the key.

Example: .\client-key.pem

Certificate Authority File

If TLS is enabled, a Certificate Authority File can be supplied. It is only required in case Self-Signed Certificates are used. This field should contain the path to the certificate.

Example: .\ca.pem

Query info

Query

The SQL Query to execute.

note

The Query input field uses HandleBars. For more information, please read HandleBars

Examples:

SELECT 1

SELECT * FROM users

INSERT INTO users (full_name, email) VALUES ("alice", "alice@gmail.com")

Rate Limit

It is possible that the query that will be executed returns too many records at once. This could cause all kinds of issues, like a decrease in performance but also unwanted side effects. To remedy this, you can enable rate limiting. It will perform the query in batches of a given limit, and interval.

When this toggle is enabled, it is required to update the query to include two magic variables; $skip and $limit or $end. These variables will be used to iterate over the rows and make subsequent calls to the database. In most scenarios, you will want to use $skip and $limit. $end is useful when working with a database that does not support the LIMIT (or similar) clause.

An example for using it with MySQL would be:

SELECT * FROM users LIMIT $limit OFFSET $skip

An example for using it with DB2 would be:

SELECT * FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rn, * FROM users) tmp WHERE rn <= $end) tmp2 WHERE rn > $skip

$end is used to limit the total amount of rows that will be returned. This is used to determine when to stop querying. But since it is based on the row number it needs to be increased by itself each iteration. This automatically done by the component and this is the difference between $end and $limit. Limit is a fixed number that is used to determine how many rows should be returned each iteration and is increased by itself each iteration.

Depending on the database dialect the exact usage of the $skip and $limit variables might change. Consult the documentation of your database's query language to see how to limit the number of rows returned.

When implementing a limit with interval, **always** include an explicit sort clause in your query. Without a defined sort order, the database is free to return results in an arbitrary order, which can lead to inconsistent results across pages.

Limit

The maximum number of rows that should be collected in a single query execution.

Example: 1000

Interval

The amount of time in milliseconds to wait between query executions.

Example: 5000

Usage with OracleDB

To use the DB Query component with OracleDB, you will need to install some additional Oracle drivers. You can find the drivers on the Oracle website. You will need to download the Basic and SDK packages for your specific OS.

After downloading, you need to follow the following steps to install the drivers:

1. Extract the downloaded files to a location on your system. For Windows, we recommend extracting the files to C:\oracle\instantclient. For Linux, we recommend extracting the files to /opt/oracle/instantclient.
2. Update your ORACLE_HOME environment variable to include the location of the extracted files.
3. (Only on linux) Update your LD_LIBRARY_PATH environment variable to include the location of the extracted files.

After installing the drivers, you can use the DB Query component with OracleDB. You will need to provide the connection string in the following format: oracle://username:password@host:port/service_name.

Usage with IBM Db2

To use the DB Query component with IBM Db2, you will need to install some additional drivers. You can find the drivers on the IBM website. You will need to download the IBM Data Server Driver for ODBC and CLI (64-bit) package for your specific OS.

After downloading, you need to follow the following steps to install the drivers:

1. Extract the downloaded files to a location on your system. For Windows, we recommend extracting the files to C:\ibm_db\clidriver. For Linux, we recommend extracting the files to /opt/ibm_db/clidriver.
2. Update your IBM_DB_HOME environment variable to include the location of the extracted files.
3. (Only on linux) Update your LD_LIBRARY_PATH environment variable to include the locations of the lib and lib/icc folders in the extracted files.

After installing the drivers, you can use the DB Query component with IBM Db2. You will need to provide the connection string in the following format: db2://username:password@host:port/database_name.

Inputs | Outputs

DB Query

Input

Output

Finished Output

Error

00 0 (ilwuv41)

The incoming data isn't used unless the query uses a variable from this object. The incoming data can be empty if you don't need any variables.

Example:

// Query in config:

SELECT * FROM documents WHERE kind="{{kind}}"

// Incoming data:
{
  kind: "CONTAINER"
}