MySQL

3.0

The MySQL connector allows you to query your MySQL database directly within tray

Overview

Copy

The MySQL connector allows you to query your MySQL database directly within tray without worrying about data pipelines or servers. The variety of use-cases here is wide, but common use-cases of the MySQL connector are:

• Querying for additional data to enrich accounts in SaaS tools you're using - usually for enrichment purposes

• Saving data from other software services into MySQL

Connecting to your MySQL instance

Copy

To allow Tray to connect to your MySQL database, you'll need to either make your database publicly accessible or you can white list ALL the Tray public IP addresses based on your region.

Refer to the Tray public IPs document for complete list of the Tray public IP addresses.

Note: it is extremely important that your MySQL database is only accessible with a strong username and password combination when whitelisting these IP addresses, as they will be used by anyone using the connector in a Tray workflow.

Authentication

Copy

Within the workflow builder, highlight the MySQL connector.

In the MySQL connector properties panel to the right of the builder, click on the Authenticate tab and the 'New authentication' button.

This will result in a Tray authentication pop-up modal. The first page will ask you to name your authentication and select the type of authentication you wish to create ('Personal' or 'Organisational').

The next page asks you for your 'Host', 'Port', 'Database', 'User', and 'Password' mandatory credentials. You can also optionally provide details for 'SSL certificate', 'SSL key', and 'SSL certificate authority' based on your requirements.

The above mentioned creds are required to connect to your database server. You can get these credentials from the platform where you have hosted your database server.

• Host: The host name or IP address of the MySQL server.

• Port: The TCP/IP port on which the MySQL server is listening (the default is 3306).

• Database: The name of your MySQL database. You can always use the show databases; query to get the list of all the available databases;

• User: The specific username tied to your MySQL database.

• Password: The specific password tied to your MySQL database.

• SSL certificate: An SSL certificate enables SSL encryption. SSL certificate is a bit of code on your web server that provides security for online communications. It is an optional parameter.

• SSL key: Path to the Key file for SSL.

• SSL certificate authority: It is an entity trusted to sign, issue, distribute and revoke digital certificates.

Once you have added these fields to your Tray authentication pop-up window, click the 'Create authentication' button.

Go back to your settings authentication field (within the workflow builder properties panel), and select the recently added authentication from the drop-down options now available.

Your connector authentication setup should now be complete.

Notes on using MySQL

Copy

Run SQL query

Copy

The Run SQL query operation helps communicate with the MySQL database by executing raw specified SQL commands. The SQL commands can be used to perform actions like create and manipulate database objects, run queries, load tables, and modify the data in tables.

A sample query to create a table in the MySQL database would be something similar to this: Create table purchase_details (order_id int, email varchar(20), total_price float, currency varchar(20));

For the ease of working with the MySQL queries, refer to the MySQL Statements document.

To learn in detail about MySQL refer to the MySQL Reference Manual.

Available Operations

Copy

The examples below show one or two of the available connector operations in use.

Please see the Full Operations Reference at the end of this page for details on all available operations for this connector.

Example usage

Copy

There are various ways in which you can upload (insert/update) data into the MySQL tables based on your requirements and scenarios. Below are the few example workflows that will help you gain a better understanding of these ways:

• Insert records

• Adding a record to a particular table

• Add or update record

In these examples, we have shown that you have received some customer data via the Webhook trigger, and now need to process it into MySQL. However users may have received this data in any number of other ways such as from the Salesforce or SurveyMonkey connector, through the CSV Editor, and so on.

Insert records

Copy

Below is an example of how you could potentially use the MySQL connector to insert new records into the MySQL database and read all the records from said database.

Upon receiving the records, the workflow checks whether the table we want to add these records to already exists in MySQL. If it does not, then the workflow creates a new table and then adds the received records to that table. Once the records are added, the workflow continues to read the records from the selected MySQL table.

This example demonstrates the following operations of the MySQL connector:

• Run SQL query: Executes a raw specified SQL query on the chosen database.

• Insert new rows: Inserts one or more rows into your chosen MySQL database.

• Find rows: Finds and reads the rows from your chosen database based on the specified criteria.

The steps will be as follows:

1. Pull the client records from the source (a webhook trigger in this example) and create the table (if it doesn't exist) in the MySQL database.

2. Push the records one by one into the MySQL database using the Loop connector.

3. Read the records from the MySQL database.

The final outcome should look like this:

1 - Pull records & create table

Copy

When using a Webhook trigger 'When webhook is received, auto respond with HTTP 200' is the most common operation, unless you want to specify a custom response. Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

In this example, the records received by the Webhook are in JSON format:

[{ "data": { "id": "6012fe453f38ecab86530e97", "first_name": "Brooks", "last_name": "Hendrix", "phone": "+1 (990) 546-2598" }, "meta": { "event": "create", "type": "prospect" } }, { "data": { "id": "6012fe454256655c4da94179", "first_name": "Crane", "last_name": "Lindsey", "phone": "+1 (932) 505-2355" }, "meta": { "event": "create", "type": "account" } }, { "data": { "id": "6012fe45c0d81c6f14a78c4d", "first_name": "Blake", "last_name": "Mosley", "phone": "+1 (956) 577-2515" }, "meta": { "event": "update", "type": "prospect" } }, { "data": { "id": "6012fe45fd903b3e38f8f5ef", "first_name": "Francis", "last_name": "Mcdowell", "phone": "+1 (940) 447-2645" }, "meta": { "event": "update", "type": "account" } }, { "data": { "id": "6012fe4509b4461450de082c", "first_name": "Sue", "last_name": "Wilkerson", "phone": "+1 (905) 508-3504" }, "meta": { "event": "update", "type": "prospect" } }, { "data": { "id": "6012fe45150b03d02a887d2b", "first_name": "Berry", "last_name": "Riggs", "phone": "+1 (872) 460-3574" }, "meta": { "event": "update", "type": "prospect" } }, { "data": { "id": "6012fe45c86ec53152c0adcd", "first_name": "Ray", "last_name": "Nichols", "phone": "+1 (859) 492-3067" }, "meta": { "event": "update", "type": "prospect" } }, { "data": { "id": "6012fe45dbfaabd167439bde", "first_name": "Reeves", "last_name": "Carrillo", "phone": "+1 (834) 541-2561" }, "meta": { "event": "update", "type": "account" } } ]

Remember that the format in which you receive data will vary depending on where it is coming from - pulling records from Salesforce, Webform, etc., will have different results / views.

Next, add a MySQL connector and set the operation to 'Run SQL query'. Add the following query in the 'SQL query' field:

create table if not exists client_info (client_id varchar(20), first_name varchar(20), last_name varchar(20), phone varchar(20));

The above query will verify if the specified table (in this case, client_info) exists in the MySQL database. If it exists, then the workflow proceeds. If it doesn't, then it creates the table based on the table information provided in the above SQL query.

2 - Add records

Copy

Add a Loop connector with the 'List' field set to $.steps.trigger.body. You can use the connector-snake to generate this automatically.

Next, add a MySQL connector inside the Loop connector and set the operation to 'Insert new rows'.

In the 'Rows to insert' field, add all the property names using the 'Add property to Row' button for the selected table.

Using the connector-snake once more, find the jsonpath for each field from the previous step.

3 - Read records

Copy

Add another MySQL connector and set the operation as 'Find rows'.

Under the 'Fields' section, select the field names (from the drop-down options) for the set table for which you would like to retrieve the information from.

Adding a record to a particular table

Copy

This workflow demonstrates how to insert records into multiple MySQL tables based on the received data.

The MySQL database considered in this example contains three tables, namely 'account', 'prospect', and 'sequenceState'. So depending on the tableName specified in the received data, the workflow will add the data to the specified table.

The steps will be as follows:

1. Pull the records from the source (a webhook trigger in this example) and extract the table name from the value of the tableName attribute.

2. Navigate the course of the workflow to the appropriate branch using the Branch connector based on the value (table name) received from the previous step.

3. Insert the new records into the appropriate MySQL table.

The final outcome should look like this:

1 - Pull records and extract the table name

Copy

When using a Webhook trigger 'When webhook is received, auto respond with HTTP 200' is the most common operation, unless you want to specify a custom response. Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

The records received by the Webhook are in JSON format:

{ "data": { "id": "20", "first_name": "Blake", "last_name": "Mosley", "phone": "+1 (956) 577-2515" }, "meta": { "tableName": "create.prospect" } }

Remember that the format in which you receive data will vary depending on where it is coming from - pulling records from Salesforce, Outreach, etc., will have different results/views.

Next, add the Text Helper connector and set the operation to 'Get text after'.

The Text Helper connector using the 'Get text after' operation returns a substring based on the inputs provided.

As you can see in the image below, given a jsonpath to the 'String' field using the connector-snake ($.steps.trigger.body.meta.tableName) and a value to the 'Pattern' field as create.. It returns the substring between where the pattern was found depending on the 'Match number' 2 and the beginning of the string.

To understand this operation, consider the example JSON provided above. Based on the provided jsonpath and values for each field, the retrieved values are:

• String: create.prospect

• Pattern: create.

• Match number: 2

So when the values of the String and Pattern fields are compared, the match found is create.. So the operation provides the substring that comes after(second) the match. In this case, as you can see in the image below, the substring returned by the operation is prospect.

Based on this step's output, the workflow will navigate to the branch with the Label as 'Prospect'.

For more clarification on the pathways, you have available, open the Debug panel to view your step's Input and Output.

2 - Navigate and add records

Copy

Add a Branch connector with the 'Value To Test' field set to $.steps.text-helpers-1.result. You can use the connector-snake to generate this automatically.

Set the 'Value' and 'Label' pair for each branch with the MySQL table names, as shown in the image below.

The Branch connector will navigate the workflow execution based on the input received from the previous step. The possible values you could receive from the previous step are 'prospect', 'account', and 'sequenceState', thus the three branches.

The third branch is an auto-generated 'Default' branch, which will terminate the workflow if the branch does not receive any of the three values mentioned above as an input.

For the workflow to terminate, add the Terminate connector under this fourth branch, i.e., the 'DEFAULT' branch.

Next, add the MySQL connector under each branch except the 'DEFAULT' branch, and set the operation to 'Insert new rows'.

As you can see, the 'Table' and the 'Rows to insert' fields are mandatory.

Select the appropriate table name from the drop-down options based on the branch your MySQL connector is on. As you can see we have selected the table name for the first branch as prospect.

Using a connector-snake find the jsonpath for the 'Row' field from the Trigger step. It should appear similar to this: $.steps.trigger.body.data.

Similarly, add values for the 'Table' and the 'Rows to insert' fields for the MySQL connectors under the 'ACCOUNT' and 'SEQUENCE STATE' branches.

The Insert new rows operation will insert the records received through the Webhook trigger in the selected MySQL table.

Add or update record

Copy

The below example demonstrates how you could potentially use the MySQL connector to insert and update the records into the MySQL database.

In this workflow, the record to be uploaded to the MySQL database is received through the Webhook trigger. The received record includes the event attribute which specifies if the record is to be created or updated.

The steps will be as follows:

1. Pull the records from the source (a webhook trigger in this example) and navigate the course of the workflow to the appropriate branch using the Branch connector based on the value (event) received from the previous step.

2. Insert or update the record to the MySQL database.

The final outcome should look like this:

1 - Pull records and navigate

Copy

When using a Webhook trigger 'When webhook is received, auto respond with HTTP 200' is the most common operation, unless you want to specify a custom response. Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

The records received by the Webhook are in JSON format:

{ "data": { "id": "20", "first_name": "Blake", "last_name": "Mosley", "phone": "+1 (956) 577-2515" }, "meta": { "event": "create" } }

Remember that the format in which you receive data will vary depending on where it is coming from - pulling records from Salesforce, Outreach, etc., will have different results/views.

Next, add the Branch connector with the 'Value To Test' field set to $.steps.trigger.body.meta.event. You can use the connector-snake to generate this automatically.

Set the 'Value' and 'Label' pair for the first two branches as 'create' and 'update' as shown in the image below.

The Branch connector will navigate the workflow execution based on the value of the event attribute received in the JSON data from the trigger. The possible values you could receive are 'create' and 'update', thus the first two branches.

The third branch is an auto-generated 'Default' branch, which will terminate the workflow if the branch does not receive any of the two values mentioned above as an input.

For the workflow to terminate, add the Terminate connector under this fourth branch, i.e., the 'DEFAULT' branch.

2 - Insert or update the record

Copy

Now, add the MySQL connector under each branch except the 'DEFAULT' branch.

As you can see in the image below, the first MySQL connector under the 'CREATE' branch will insert a new record into the MySQL database. The second MySQL connector under the 'UPDATE' branch will update the received record into the MySQL database.

The two MySQL connectors for insert and update operation can be configured as follows:

MySQL connector to insert a record:

To insert the record, set the operation to 'Insert new rows'.

As you can see, the 'Table' and the 'Rows to insert' fields are mandatory.

Select the table name of your choice from the available drop-down options. As you can see, in this example, we have selected the table name as customer_details.

Using a connector-snake find the jsonpath for the 'Row' field from the Trigger step. It should appear similar to this: $.steps.trigger.body.data.

Now, based on the selected table and the jsonpath for the received data, the workflow will insert the received record into the selected MySQL table.

MySQL connector to update a record:

To update the record, set the operation to 'Update rows'.

Set the values for the 'Table' and the 'Rows to insert' fields similarly as we did above.

Before updating the record, the Update rows operation matches the existing 'id' from the selected MySQL table with the ID of the data received from the trigger: $.steps.trigger.body.data.id.

Thus, as you can see in the image below, the condition is set to specify the id of the existing record that needs to be updated.

Now, based on the specified table, jsonpath, and the condition, the workflow will locate and update the record if it already exists.

Managing Data

Copy

Batch insertion of data

Copy

For efficiency purposes, and to reduce the amount of calls you make to MySQL, you want to avoid a situation where you are looping through data and inserting it row by row, such as the following:

The following workflow shows a simple example of how you can loop through batches of e.g. 200 records so that you can make e.g. 10 calls of 200 instead of 2000 individual calls.

1. Loop batches is the main loop which is set to 'loop forever' and keeps running until Salesforce doesn't return an offset token - indicating there are no more batches to loop through.

1. get page offset gets the offset token returned by Salesforce in the previous loop, so that Salesforce knows where to start retrieving this batch of records.

1. list accounts specifies what record type and fields to retrieve from Salesforce.

It also passes the page offset token, and sets the batch size.

1. Insert rows then inserts the data from the current list into the selected MySQL table in a single call.

The payload sent to MySQL is in the form of an array which matches the format accepted by MySQL.

1. page offset NULL? uses $.steps.salesforce-1.next_page_offset to check if Salesforce has returned an offset token to indicate more batches are to be processed. If not, then the main loopbatches loop is broken.

2. set page offset then stores the offset token to be retrieved at the start of the next batch.

Processing dynamic data

Copy

Depending on the data source, sometimes your workflow may receive dynamic data payloads - i.e., the attributes within may vary for each execution of your workflow.

Let's assume an example where you wish to insert some data into a MySQL database.

The data that you have received for the first execution of your workflow is in this format:

1
{
2
	"data": {
3
		"id": "129273",
4
		"attributes": {
5
			"department": "IT",
6
			"branch": "London"
7
		}
8
	},
9
	"meta": {
10
		"deliveredAt": "2021-01-29T07:52:28.094+00:00"
11
	}
12
}

For the second execution, your workflow may receive the data with more attributes like this:

1
{
2
	"data": {
3
		"id": "129273",
4
		"attributes": {
5
			"department": "IT",
6
			"branch": "London",
7
			"country": "uk",
8
			"zip": "JU59KL"
9
		}
10
	},
11
	"meta": {
12
		"deliveredAt": "2021-01-29T07:52:28.094+00:00"
13
	}
14
}

So in such a situation, where it is difficult to gauge the attributes you may receive in your data for each execution. We cannot map each attribute with their respective column names in order to upload the data in the MySQL table, i.e., something like this, as shown in the image below, is not possible:

To understand the mapping of each attribute with the column name, please refer to the Insert records workflow explained above.

So as a solution, you have to upload the data in bulk, i.e., in one go. This is possible only if the received JSON data has a flat structure.

The best way to transform a nested JSON structure into a flat structure is by using a Data Mapper connector.

So the following workflow demonstrates how you could deal with dynamic data using a Data Mapper connector that flattens the nested JSON and uploads this data into your MySQL database table.

In this example, we show data being received via the Webhook trigger - i.e., a scenario whereby you have configured an external service to automatically send new record data to your Tray workflow.

In practice, you can receive data in any structure, and in other ways, such as from any pre-built Tray service trigger or from a previous workflow step which has pulled data from another source (e.g., Salesforce 'Find Records', CSV Reader, etc.)

1. The Webhook trigger with operation as 'Auto respond with HTTP 200' listens for the incoming data.

2. The Process dynamic data step is a Data Mapper connector step which flattens the received JSON data in order to handle the incoming dynamic data.

The 'Mapping' field transforms the nested structure i.e attributes.[column_name] to a flat structure, i.e. just the [column_name]. The 'Mapping' field contains a list of all the possible attributes that you wish to get a flat structure for.

Make sure to uncheck the 'Included unmapped' field. If you do not uncheck this field, it will include the fields from the received JSON data that you haven't listed in the Mappings field, i.e., something like this:

If the 'Included unmapped' field is unchecked the expected flat JSON output should look like this:

1. The Insert new rows step inserts new rows based on the flatten JSON data received from the previous step $.steps.data-mapper-1 in the selected MySQL table.