MySQL Pipe

The MySQL pipe allows you to interact with your MySQL database from your Tadabase app. You can create records, update records, and read records from your database.

This pipe is partially-configurable and the Parameter, Response, and Headers tab can all be easily adjusted according to your specifications. For example, if it suits your app's requirements, you can replace dynamic parameters in the Request tab with hard-coded data. For more information on customizing pipes, see the article on Adding Custom Pipes.

Please Note: If you are hard-coding your parameters, remember to set the parameters' required option to 'off' in the Parameters tab (if you won't be using them). Otherwise, you will get an error, and the pipe will not be triggered.

The MySQL Pipe contains the following API calls:

• Get a Record
• Get all Records
• Delete a Record
• Create a Record
• Update a Record
• Get Filtered Records
• Get Records with Specific Columns
• Get Records with Connected Records
• Ping

Additionally, you can add any of the following operations to any of the API calls:

• Filters
• Multiple Filters
• Orders
• Pagination
• Batch Operations
• Ping

 

Get a Record

Use the Get a Record API call to to retrieve the values of a single database record based on the table name and record primary key. You can enter multiple primary keys separated by commas to retrieve an array of multiple records.

Note: To use this pipe call in your app or data tables, customize the responses according to the columns in your table.

 

Get all Records

Use the Get all Records API call to retrieve an array of all records in the specified table. Please note that pipe array responses can only be used in Custom Components. Alternatively, you can access a specific index number in an array. See Adding Pipes - Custom Pipes - Response for more details.

 

Delete a Record

Use the Delete a Record API call to delete a single record in a specified table. Enter the table name and the primary key value of the record you would like to delete. 

 

Create a Record

Use the Create a Record API call to create a single record or multiple records in a specified table. For the Record Data parameter, enter the fields and field values according to the JSON name-value syntax, as shown in the example below. The primary key field must be included to create the record successfully. 

To create multiple records, simply add another Record Data parameter in the Parameter tab with a new slug (i.e., "recordData2"), then add it to the Request tab as shown below. 

Please note: You can include dynamic parameters (i.e., from the Parameters tab) in the Request by enclosing the parameter slug in brackets. In the example below, there are double brackets because the parameter is wrapped in a JSON object.

 

Update a Record

Use the Update a Record API call to update a single record or multiple records in a specified table. For the Record Primary Key parameter, enter the primary key value of the record you would like to update. For the Record Data parameter, enter the updated fields and field values according to the JSON name-value syntax, as shown in the example below. 

To update multiple records, include multiple primary keys in 'Record Primary Key' parameter separated by commas. Then, simply add another Record Data parameter in the Parameter tab with a new slug (i.e., "recordData2") and add it to the Request tab as shown below. 

Please note: You can include dynamic parameters (i.e., from the Parameters tab) in the Request by enclosing the parameter slug in brackets. In the example below, there are double brackets because the parameter is wrapped in a JSON object.

 

Get Filtered Records

Use the Get Filtered Records API call to get all the records in a table that meet a specific search criteria. A full list of all filter operators and their use cases are listed at the end of this article.

In the Table Name parameter, enter the name of the table you wish to retrieve filtered records from. In the Filter parameter, enter the field you would like to filter by, a comma, the filter operator, a comma, and the filtered value (i.e., filterField,filterOperator,filteredValue).  In the example below, the API call will return all records in the customer table where the customerNumber field contains a value that is greater than (gt) 110. 

You can include Multiple Filters separated by '&filter='. In the example below, the API call will return all records in the customer table where the customerNumber field contains a value that is greater than (gt) 110 and the salesNumber contains a value that is less than (lt) 5000.

 

Get Records with Specific Columns

Use the Get Records with Specific Columns API call to get specific records in a table with only specific columns. 

In the Table Name parameter, enter the name of the table you wish to retrieve records from. In the Columns parameter, enter names of the columns you would like to be included in the response. In the example below, the API call will return the customerName field and creditLimit field for the records in the customers table which have a primary key of 112 or 114.

 

Get Records with Connected Records

Use the Get Records with Connected Records API call to get specific records in a table along with their connected records.

In the Table Name parameter, enter the name of the table you would like to get records from. In the Join parameter, enter the name of the join field you would like include connected records from. In the example below, the API call will return the records with a primary key value of 112 along with its connected record from the employees join field.

If you wish to retrieve multiple records, you can enter multiple values in the Primary Keys parameter separated by commas. You can also add multiple joins by separated the join columns with '&join='. In the example below, the API call will return the records with a primary key value of 112 and 114 along with their connected records from the employees join field and the salesRepEmployeeNumber join field.

 

Ping

Running the Ping API call connects to your monitoring and returns the time (in microseconds) it takes to connect and read data from the database and the cache.

 

Filters

Filters provide search functionality on list calls, using the "filter" parameter. You need to specify the column name, a comma, the match type, another comma and the value you want to filter on. These are supported match types:

• "cs": contain string (string contains value)
• "sw": start with (string starts with value)
• "ew": end with (string end with value)
• "eq": equal (string or number matches exactly)
• "lt": lower than (number is lower than value)
• "le": lower or equal (number is lower than or equal to value)
• "ge": greater or equal (number is higher than or equal to value)
• "gt": greater than (number is higher than value)
• "bt": between (number is between two comma separated values)
• "in": in (number or string is in comma separated list of values)
• "is": is null (field contains "NULL" value)

Negated filters:

• "ncs": does not contain string
• "nsw": does not start with
• "new": does not end with 
• "neq": does not equal
• "nlt": is not lower than
• "nle": is not lower or equal
• "nge": is not greater or equal
• "ngt": is not greater than
• "nbt": is not between (number is not between two comma separated values)
• "nin": is not in (number or string is in comma separated list of values)
• "nis": is not null

As an example, the request body below will get all records in the customers table where customerNumber is greater than 110:

{
  "action" : "/records/customers?filter=customerNumber,gt,110"
}

Multiple Filters

You can include multiple filters by adding another "filter" parameter in the request. For example, the request body below will get all records in the customers table where the customerNumber is greater than 110, and the salesRepEmployeeNumber is less than 5000.

{
  "action" : "/records/customers?filter=customerNumber,gt,110&filter=salesRepEmployeeNumber,lt,5000"
}

Orders

The "order" parameter allows you to sort the records by a specific column. (By default, the records are ordered by the table's primary key.) In the example below, the records in the categories table will be ordered by name in ascending order. 

{
  "action": "/records/categories?order=name"
}

By default the sort is in ascending order, but by specifying "desc" this can be reversed. In the example below, the records in the categories table will be ordered by name in descending order.

{
  "action": "/records/categories?order=name,desc"
}

Pagination

Pagination allows you to divide the data by pages, and get the data from a specific page, using the "page" parameter. Since records that are not ordered cannot be paginated, it is recommended to include the "order" parameter as well. By default, the records will be sorted by the table's primary key.

{
  "action": "/records/categories?order=id&page=1"
}

The default page size is 20 records, but it can be adjusted by adding a comma followed by the desired number of records per page. In the example below, the request will return the page 1 of the categories table, and each page holds 50 records. 

{
	"action": "/records/categories?page=1,50"
}

Note: The "results" parameter in the response returns the total number of records in the table which would be returned if no pagination would be used. 

Batch Operations

When you want to create, read, update or delete records, you may specify multiple primary key values in the request.

For example, entering the following code in the request body of Get a Single Record or Delete a Record will return or delete a record from the posts table with the primary key of 1 and a record from the posts table with the primary key of 2.

{
  "action": "/records/posts/1,2"
}

To perform a batch update in the Create a Record API call, you need to send a data array instead of a data object in the request body.

In the example request below, two records will be added to the customers table:

{
  "action" : "/records/customers",
   "data" : [
   {"customerNumber": 103,
    "customerName": "Larry Johnson"},
   {"customerNumber":104,
    "customerName": "Tina Write"}
   ]
}

To Update a Record using batch operations, enter multiple primary keys in the action as well as a data array. The number of primary keys must match the number of objects in the array.

In the example below, a record from the customers table with a primary key value of 100 will have the customerName column updated to a value of "Larry Johnson" and a record from the customers table with a primary key value of 101 will have the customerName  column updated to "Tina Write"

{
  "action" : "/records/customers/100,101",
  "data":[
  	{
	"customerName" : "Larry Johnson"
	},
 	{
	"customerName" : "Tina Write"
	}
	]
}

Ping

The ping action connects to your monitoring and returns the time (in microseconds) it takes to connect and read data from the database and the cache.

{
	"action": "/status/ping"
}