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
Note Oracle and Postgres support are activated but not further implemented or validated. Feel free to try it out. Also, note that it might require third-party libraries.
Handlebars
This component lets you use Handlebars templates. More information about Handlebars can be found in this section
Configuration
Connection info
Connection String
The Connection string to the SQL server.
Example: mysql://username:password@localhost:3306/xill4
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
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.
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.
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.
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
Inputs
Input
The incoming message isn't used unless the query uses a variable from this object. The incoming message can be empty if you don't need any variables.
Example:
//query in config:
SELECT * FROM documents WHERE kind="{{kind}}"
//incoming message:
{
kind: "CONTAINER"
}
Outputs
Output
Each retrieved row, combined with the incoming message.
Example:
{
value: {
// the incoming message, if there isn't any this is an empty object
},
result : {
// the resulting record
}
}
Finished Output
Once all queries are executed, a message will be sent to this output. This indicates that the component is finished.
Example:
{
value: {
// the incoming message, if there isn't any this is an empty object
},
resultCount: 10 // The amount of documents that were send to `output`
}