Swimlane Relational Databases

The Swimlane Relational Databases connector allows users to perform direct SQL operations and procedure calls on various RDBMS from within the Swimlane platform.

The Swimlane Relational Databases connector enables seamless interaction with various relational databases, such as MS SQL Server, PostgreSQL, MySQL, and Oracle. It allows users to execute SQL queries, including data modification and retrieval, directly within Swimlane Turbine's low-code automation platform. By integrating this connector, end-users can automate database operations, enhance security workflows with real-time data, and streamline incident response by executing stored procedures or transforming query results into CSV format for further analysis.

Prerequisites

To utilize the Swimlane Relational Databases connector, ensure you have the following prerequisites:

Custom authentication for databases with the following parameters:

Database Type: Specify the type of the relational database (e.g., MySQL, PostgreSQL)

Username: The username credential for database access

Password: The password credential for database access

Host: The hostname or IP address of the database server

Database name: The name of the specific database to connect to

Capabilities

This connector is compatible with the following databases:

Microsoft SQL Server

PostgreSQL

MySQL

Oracle

Although the connector was tested against the mentioned databases, it is possible to use it with other databases as long as the engines are compatible.

To modify data in a database (i.e., INSERT, etc.), use the task Run Modification Query.

To retrieve data from a database (i.e., SELECT), use the task Run Query.

To execute stored procedures, use the task Run Stored Procedure.

Asset information

The asset requires a username, password, host, port, and database name.

Database Type & Database Engine

Additionally, the asset requires an engine dialect to supply the database type.

If supplying a database type, the connector will use the default dialects for the corresponding database. Ex: PostgreSQL will default to postgresql+pg8000

Available types are: MS SQL Server, PostgreSQL, MySql, Oracle.

It is possible to provide the specific dialect to use in case its needed.

Available dialects are:

Database	Available	Recommended
Postgres	postgresql, postgresql+pg8000	postgresql+pg8000
MySql	mysqlconnector	mysqlconnector
Oracle	oracle, oracle+cx_oracle	oracle
Microsoft SQL Server	mssql+pyodbc, mssql+pymssql	mssql+pymssql

Note: In order to use mssql+pyodbc, you must install the package "pyodbc" in Turbine.

Tasks Setup and Filtering

The task "Direct Query" allows inputs to be passed as wild cards. Any "?" present will be replaced, in order, with supplied "Parameters."

Example:

Query = SELECT id FROM Person where id = %s or id < %s Parameters = ["10","20"]

The following query will be executed:

SELECT id FROM Person where id = 10 or id < 20

Configurations

Asset

Authentication for Databases.

Configuration Parameters

Parameter	Description	Type	Required
database_type	Database type. Available - MS SQL Server, PostgreSQL, MySql, Oracle.	string	Required
username	Username to connect to the Database.	string	Required
password	Password.	string	Required
host	Server Host.	string	Required
database_name	Name of the Database.	string	Required
port	Host port to use.	number	Optional
database_dialect	Database Dialect. This will override the database type supplied. See Readme for available. Example - postgresql+pg8000.	string	Optional
ssl	enable/disable SSL connection with database when needed.	boolean	Optional

Actions

Run Modification Query

Executes a non-returning SQL query such as INSERT, UPDATE, or DELETE on a database using the specified query string.

Input

Argument Name	Type	Required	Description
query	string	Required	Query - Using parameters is acceptable ('select * from foo where id = %s or id = %s'). Parameters need to be supplied through the parameter input in order.
parameters	array	Optional	A list of parameters. Should be in order.

Input Example

{"parameters":["idparam1","idparam2"],"query":"INSERT INTO example1 VALUES (2, 'Bob');"}

Output

Parameter	Type	Description
result	string	Result of the operation
query_executed	string	Output field: query_executed
row_count	number	Count value

Output Example

{"result":"Success","query_executed":"INSERT INTO example1 VALUES (2, 'Bob');","row_count":1}

Run Query

Executes a specified SQL query on the database and returns the results. An input of 'query' is required.

Input

Argument Name	Type	Required	Description
query	string	Required	Query - Using parameters is acceptable ('select * from foo where id = %s or id = %s'). Parameters need to be supplied through the parameter input in order.
parameters	array	Optional	A list of parameters. Should be in order.
to_csv	boolean	Optional	When True, returns a CSV as an attachment.

Input Example

{"parameters":["idparam1","idparam2"],"query":"SELECT TABLE_NAME FROM DBA_TABLE","to_csv":false}

Output

Parameter	Type	Description
result	array	Result of the operation
query_executed	string	Output field: query_executed
row_count	number	Count value
file	object	File
file.file_name	string	Name of the resource
file.file	string	Output field: file.file

Output Example

{"result":[{"id":1,"name":"Adam"}],"query_executed":"SELECT * FROM table1","row_count":1}

Run Stored Procedure

Executes a specified stored procedure in the relational database and retrieves the resulting data.

Input

Argument Name	Type	Required	Description
stored_procedure_name	string	Required	Name of the Stored Procedure to Run.
stored_procedure_params	array	Optional	Parameters in order that need to be executed with the Stored Procedure.
to_csv	boolean	Optional	When True, returns a CSV as an attachment.

Input Example

{"stored_procedure_name":"S_getPerson","stored_procedure_params":["1","2"],"to_csv":false}

Output

Parameter	Type	Description
result	array	Result of the operation
query_executed	string	Output field: query_executed
row_count	number	Count value
file	object	File
file.file_name	string	Name of the resource
file.file	string	Output field: file.file

Output Example

{"result":[{"id":1,"name":"Adam"}],"query_executed":"SELECT * FROM table1","row_count":1}

Response Headers

Header	Description	Example
Content-Type	The media type of the resource	application/json
Date	The date and time at which the message was originated	Thu, 01 Jan 2024 00:00:00 GMT