MySQL Component

Query and manage data in a MySQL Database

Component key: mysql

Description

MySQL is a popular relational database system. This component allows you to query a MySQL database.

MySQL parameterized queries

Instead of generating dynamic queries and sanitizing SQL yourself, you can use generic queries with parameter placeholders using the ? placeholder. You can use the placeholder several times in your query, and provide a list of optional parameters to the action. For example, you can enter a query that reads INSERT INTO users (name, email) VALUES (?, ?). Then, configure the optional parameters input to look like the following, ["myUsername", "myEmail"] or whatever values you like. Values provided for name and email are sanitized automatically.

If you are dynamically generating SQL queries and do not know how many parameters you will have ahead of time, create a dynamic array of parameters in another step and reference them through the Reference Parameters input.

Bulk inserting data

If you are inserting an unknown number of rows of data into a table, you must use the Reference Parameters input, and your input must be a doubly-wrapped array.

For example, if your query reads INSERT INTO myTable (id, mycolumn1, mycolumn2) VALUES ?, your reference parameter should read something like

[
  [
    ["3", "foo3", "bar3"],
    ["4", "foo4", "bar4"],
    ["5", "foo5", "bar5"]
  ]
]

Connections

MySQL Connection

Create a new MySQL connection and enter the host, port, and database for your MySQL server. The username and password are optional inputs that can be put directly into a MySQL connection.

On-prem enabled: this connection can be configured to connect to an on-prem resource on a private network. Learn more.

Input	Default	Notes
Database string / Required database		The name of your database.
Host string / Required host	my-server.example.com	The publicly-accessible address of your MySQL server.
Password password password
Port string / Required port	3306	The port your database server is exposing.
Username string username

Actions

Query

Returns the results of a MySQL database query | key: query

Input	Default	Notes	Example
Connection connection / Required mySQLConnection
Parameters string Value List params		Optional parameters to insert into a query. Use this when you know how many parameters your query uses ahead of time. Use either parameters or reference parameters, not both.
Query code / Required queryField		A query to be executed by the MySQL server. Substitute parameters with '?' marks.
Reference Parameters code referenceParams		Optional parameters to insert into a query. Use this when you don't know how many parameters your query uses ahead of time. Reference parameters from a previous step that returns an array. Use either parameters or reference parameters, not both.

{
  "data": [
    {
      "id": 1,
      "mycolumn1": "foo",
      "mycolumn2": "bar"
    },
    {
      "id": 2,
      "mycolumn1": "foo2",
      "mycolumn2": "bar2"
    }
  ]
}

---