The Database Query processor-type Function helps the user request data from a database and enrich the data in your stream. The data present in the database can come from one or more tables.

To configure the Database Query Function, specify the following information:

Database Info
Authentication
SQL Select Statement

The output from the DB Query is kept in the parameter queryResult (including any sub-parameter). The queryResult is added to the payload and is passed on to the next node in the stream. The output is made immutable, that is, it can not be edited or changed. If you still want to use and modify an object, you can use a script node to clone the complete content of queryResult, or any sub-parameter in it.

Note!

Cloning the complete queryResult will impact performance, resulting in high CPU load. The impact is related to the size of queryResult.

The following example shows how to clone queryResult.

Cloning queryResult

const lodash = require('lodash');
const result = lodash.cloneDeep(payload.queryResult);
result.foo = 'bar';

Database Info

The information required to access the external database.

Field	Description
Database Type	Select the type of database that you want to query the data from. The available options are: MySQL PostgreSQL Microsoft SQL
Database Name	Specify the name of the database you want to retrieve the data from.
Hostname or IP Address	Specify the hostname or the IP Address of the database
Port	Specify the port used by the database. When a Database Type is selected, the default port number is specified for each type of database. The default port numbers are: MySQL: 3306 PostgreSQL: 5432 Microsoft SQL: 1433

Authentication

The authentication information to log in to the external database.

Field	Description
Username	Specify the username for the authentication.
Password	Specify the password for the authentication.

SQL Select Statement

The lookup criteria are the space where you specify the statement that you can use to query data from the selected database. For example, SELECT, AS and OR, and so on.

Select the Enable cache checkbox to minimize the number of repeated database access requests using the same SELECT statement.

Enter a suitable number for Maximum items in cache (0 = no limit). There is no upper limit to this value.

Note!

A high value can result in an error due to memory outage. If so, the stream fails with the following error message in the log:
“Stream failed to complete due to reaching resource limits”

However, this error is received for memory issues in general and can have other causes than a high value for the maximum number of items in the cache.

Enter a suitable number of seconds for Items are removed from cache after. Minimum value is 1 and there is no upper limit to this value.

SQL Select Statement Examples

Example using SELECT

SELECT *
FROM movies;

Example using AS

SELECT name 
AS 'movie_title'
FROM movies;

Example using OR

SELECT name
FROM customers 
WHERE state = "ca" 
OR state = "ny";

Note!

The Database Query function supports Variable Insertion when writing SQL statements.

Example for Interpolation

SELECT dep FROM emp WHERE id = ${id} AND name = ${name};