SQL | Documentation

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.json and tasks.json 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	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	ID	Type	Description
Status	`status`	string	Insert status.

#Insert Many

Perform insert operation with multiple rows

Input	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	ID	Type	Description
Status	`status`	string	Insert many status.

#Update

Perform update operation

Input	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	ID	Type	Description
Status	`status`	string	Update status.

#Select

Perform select operation

Input	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	ID	Type	Description
Rows	`rows`	array[object]	The rows returned from the select operation.
Status	`status`	string	Select status.

#Delete

Perform delete operation

Input	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	ID	Type	Description
Status	`status`	string	Delete status.

#Create Table

Create a table in the database

Input	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	ID	Type	Description
Status	`status`	string	Create table status.

#Drop Table

Drop a table in the database

Input	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	ID	Type	Description
Status	`status`	string	Drop table status.