SQL

The SQL component is a data component that allows users to access the SQL database of your choice. It can carry out the following tasks:

Insert
Insert Many
Update
Select
Delete
Create Table
Drop Table

Release Stage

Alpha

Configuration

The component definition and tasks are defined in the definition.yaml and tasks.yaml files respectively.

Setup

In order to communicate with the external application, the following connection details need to be provided. You may specify them directly in a pipeline recipe as key-value pairs within the component's setup block, or you can create a Connection from the Integration Settings page and reference the whole setup as setup: ${connection.<my-connection-id>}.

Field	Field ID	Type	Note
Engine (required)	`engine`	string	Choose the engine of your database. Enum values `MySQL` `PostgreSQL` `SQL Server` `Oracle` `MariaDB` `Firebird`
Username (required)	`username`	string	Fill in your account username.
Password (required)	`password`	string	Fill in your account password.
Database Name (required)	`database-name`	string	Fill in the name of your database.
Host (required)	`host`	string	Fill in the host of your database.
Port (required)	`port`	number	Fill in the port of your database.
SSL / TLS (required)	`ssl-tls`	object	Enable SSL / TLS.

The ssl-tls Object

SSL TLS

ssl-tls must fulfill one of the following schemas:

`No SSL / TLS`

Field	Field ID	Type	Note
SSL / TLS Type	`ssl-tls-type`	string	Must be `"NO TLS"`

`TLS`

Field	Field ID	Type	Note
CA Certificate	`ssl-tls-ca`	string	Base64 encoded CA certificate file.
SSL / TLS Type	`ssl-tls-type`	string	Must be `"TLS"`

`mTLS`

Field	Field ID	Type	Note
CA Certificate	`ssl-tls-ca`	string	Base64 encoded CA certificate file.
Client Certificate	`ssl-tls-cert`	string	Base64 encoded client certificate file.
Client Key	`ssl-tls-key`	string	Base64 encoded client key file.
SSL / TLS Type	`ssl-tls-type`	string	Must be `"mTLS"`

Supported Tasks

Insert

Perform insert operation

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_INSERT`
Table Name (required)	`table-name`	string	The table name in the database to insert data into.
Data (required)	`data`	object	The data to be inserted.

Output	Field ID	Type	Description
Status	`status`	string	Insert status.

Insert Many

Perform insert operation with multiple rows

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_INSERT_MANY`
Table Name (required)	`table-name`	string	The table name in the database to insert data into.
Data (required)	`array-data`	array[object]	The array data to be inserted.

Output	Field ID	Type	Description
Status	`status`	string	Insert many status.

Update

Perform update operation

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_UPDATE`
Table Name (required)	`table-name`	string	The table name in the database to update data into.
Filter (required)	`filter`	string	The filter to be applied to the data with SQL syntax, which starts with WHERE clause.
Update (required)	`update-data`	object	The new data to be updated to.

Output	Field ID	Type	Description
Status	`status`	string	Update status.

Select

Perform select operation

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_SELECT`
Table Name (required)	`table-name`	string	The table name in the database to be selected.
Filter	`filter`	string	The filter to be applied to the data with SQL syntax, which starts with WHERE clause, empty for all rows.
Limit	`limit`	integer	The limit of rows to be selected, empty for all rows.
Columns	`columns`	array[string]	The columns to return in the rows. If empty then all columns will be returned.

Output	Field ID	Type	Description
Rows	`rows`	array[json]	The rows returned from the select operation.
Status	`status`	string	Select status.

Delete

Perform delete operation

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DELETE`
Table Name (required)	`table-name`	string	The table name in the database to be deleted.
Filter (required)	`filter`	string	The filter to be applied to the data with SQL syntax, which starts with WHERE clause.

Output	Field ID	Type	Description
Status	`status`	string	Delete status.

Create Table

Create a table in the database

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_CREATE_TABLE`
Table Name (required)	`table-name`	string	The table name in the database to be created.
Columns (required)	`columns-structure`	object	The columns structure to be created in the table, json with value string, e.g `{"name": "VARCHAR(255)", "age": "INT not null"}`.

Output	Field ID	Type	Description
Status	`status`	string	Create table status.

Drop Table

Drop a table in the database

Input	Field ID	Type	Description
Task ID (required)	`task`	string	`TASK_DROP_TABLE`
Table Name (required)	`table-name`	string	The table name in the database to be dropped.

Output	Field ID	Type	Description
Status	`status`	string	Drop table status.