SQL Connector
The SQL Connector is an outbound Connector that allows you to connect your BPMN service with SQL databases (MariaDB, Microsoft SQL Server, PostgreSQL, MySQL).
Prerequisites
To use the SQL Connector, ensure you have an SQL database instance running.
To avoid exposing your sensitive data as plain text, use Camunda secrets. Follow our documentation on managing secrets to learn more.
Create an SQL Connector task
You can apply a Connector to a task or event via the append menu. For example:
- From the canvas: Select an element and click the Change element icon to change an existing element, or use the append feature to add a new element to the diagram.
- From the properties panel: Navigate to the Template section and click Select.
- From the side palette: Click the Create element icon.
After you have applied a Connector to your element, follow the configuration steps or see using Connectors to learn more.
Make your SQL Connector executable
To make your SQL Connector executable, fill out the mandatory fields highlighted in red in the properties panel.
Database
Select the database type you want to connect to. The SQL Connector supports the following databases:
- MariaDB
- Microsoft SQL Server
- MySQL
- PostgreSQL
Connection
The SQL Connector supports two types of connections:
- URI: Use this option to connect to your database using a URI (similar to a connection string).
- Detailed: Use this option to connect to your database by providing detailed connection information (host, port, database name, username, password).
URI connection
If you choose the URI connection type, you need to provide:
Property | Type | Required | Description | Example |
---|---|---|---|---|
URI | String | Yes | The URI connection string for your database. The format of the URI depends on the database type you selected. You can find more details about the URI format in the relevant official documentation for MariaDB, PostgreSQL, MySQL, and Microsoft SQL Server. | jdbc:mysql://mysqlHost:3306/mydatabase?someOption=someValue |
Properties | Object | No | Optional properties that can be used to configure the connection. These properties are appended to the URI. You can find more details about the properties in the relevant official documentation for MariaDB, PostgreSQL, MySQL, and Microsoft SQL Server. | ={useSSL:false, requireSSL:false, user: "john", password:"securePwd"} |
Detailed connection
If you choose the detailed connection type, provide the following:
Property | Type | Required | Description | Example |
---|---|---|---|---|
Host | String | Yes | The host of your database. | localhost |
Port | Number | Yes | The port of your database. | 3306 |
Username | String | No | The username to connect to your database. | myuser |
Password | String | No | The password to connect to your database. | mypassword , {{secrets.MY_PASSWORD}} |
Database | String | No | The name of your database. | mydatabase |
Properties | Object | No | Optional properties that can be used to configure the connection. You can find more details about the properties in the relevant official documentation for MariaDB, PostgreSQL, MySQL, and Microsoft SQL Server. | ={useSSL:false, requireSSL:false} |
Query
You should pay extra attention to the query you are executing. Make sure it is safe and does not expose your database to SQL injection attacks. Use variables as much as possible to prevent SQL injection attacks.
Property | Type | Required | Description | Example |
---|---|---|---|---|
Return results | Boolean | Yes (default is false ) | If the query should return results (when using SELECT or RETURNING ), set this field to true . Otherwise (insert, update, delete, create table or database), leave the checkbox unchecked.This property will change the response type. See the details here. | true |
Query | String | Yes | The SQL query you want to execute. See the details here. | SELECT * FROM mytable WHERE field = :field |
Variables | List or object | No | Variables that can be used in the query. See the details here. | ={field: "theFieldValue"} , =[24] |
Return results
When
false
, the response (see the output section) will consist of an object containing an integer (modifiedRows
) representing the number of modified rows. This is applicable for:INSERT
UPDATE
DELETE
This will return 0
for:
CREATE TABLE
CREATE DATABASE
, except for MySQL, where it will return1
When
true
, the response will be a list of objects. This list will contain the results of theSELECT
query.
For instance,SELECT * FROM mytable
, wheremytable
is a table with columns'name
andage
, will return:{
"resultSet": [
{
"name": "John Doe",
"age": 29
},
{
"name": "Jane Doe",
"age": 27
}
]
}
Query
The query you want to execute. We currently support the following SQL queries:
SELECT
INSERT
UPDATE
DELETE
CREATE TABLE
CREATE DATABASE
The query might contain variables that can be used in the query, and we recommend using them as a best practice to prevent SQL injection attacks. See the variables section for more details.
Variables
Variables need to be provided as a list or an object. We provide three ways to use variables in your query:
Type | Query example | Variables example |
---|---|---|
Named parameters | SELECT * FROM mytable WHERE field = :field INSERT INTO Employee (id, name, age, department) VALUES (:id, :name, :age, :department) | ={field: "theFieldValue"} ={id: 1, name: "John", age: 34, department: "Dept"} |
Positional parameters | SELECT * FROM mytable WHERE field = ? INSERT INTO Employee (id, name, age, department) VALUES (?, ?, ?, ?) | =["theFieldValue"] =[1, "John", 34, "Dept"] |
List parameters | SELECT * FROM mytable WHERE field IN (<listField>) | ={listField: ["val1", "val2"]} |
Appendix & FAQ
How do I store secrets for my Connector?
Use Camunda secrets to avoid exposing your credentials. Follow our documentation on managing secrets to learn more.
What is the output format of the SQL Connector?
Depending on the type of query you execute, the response will contain either the number of modified rows (an object with a modifiedRows
attribute) or the result set (an object with a resultSet
attribute).
If the query doesn't return results (insert, update, delete, create table or database), the response will consist of an integer representing the number of modified rows. See the return results section for more details.
In this case, the response will look like this:
{
"modifiedRows": 1
}If the query is a
SELECT
query (or uses theRETURNING
keyword), the response will be an object with aresultSet
property (as a list). This list will contain the results of theSELECT
query as explained in the return results section. For instance,SELECT * FROM mytable
, wheremytable
is a table with columns'name
andage
, will return:{
"resultSet": [
{
"name": "John Doe",
"age": 29
},
{
"name": "Jane Doe",
"age": 27
}
]
}