MySQL

MySQL connects your flow to a MySQL database for two tasks: saving into a table the records that come from other components and reading data with an SQL query. It can also hand the connection details to an agent that queries the database by itself.

More complete version

There is another MySQL component that also reads, updates and deletes rows without writing SQL. For new flows, use it instead of this one.

When to use it

• Saving into a table the contacts or orders the flow has collected.
• Reading with an SQL query the data a report needs.
• Giving a SQL Agent access to your database so it answers questions about it.

Configuration

You need a MySQL credential with the server (Host), port, database, user and password. See Credentials.

The Operation field decides what the component does and which fields appear:

• Add (default): saves into a table the records you connect to Ingest Data. If the table does not exist, it creates it with a text column for each field of the records.
• SQL: runs the query you write in Search Query and returns the rows obtained.
• DatabaseInfo: prepares the connection details and the table to hand them to another component.

Field	What it does
Credential (required)	The MySQL credential with the connection details.
Operation	What the component does. Default: Add.
Table Name (required)	With Add and DatabaseInfo. The table where the data is saved or the one handed to another component.
Ingest Data	With Add. The records to save, one per row. Connect the data output of another component here.
Search Query	With SQL. The query to run, for example SELECT name, email FROM customers WHERE city = 'Bilbao'.

Advanced options

Field	What it does
Drop Table	With Add. Deletes the table, with all its content, and creates it again before saving the new records. Default: off.

Output

• Data: with Add, the records that were saved; with SQL, the rows the query returns, each with its columns. It does not appear with DatabaseInfo.
• Database Info: with DatabaseInfo, the reference to the database and the table. Connect it, for example, to a SQL Agent.

Example

Academia Faro saves in MySQL the enrolments that come from its website:

1. Add MySQL with the academy’s MySQL credential and leave Operation set to Add.
2. Type enrolments in Table Name.
3. Connect the enrolment data (name, email and course) to Ingest Data, for example from JSON to Data.

Each run adds a row to the enrolments table. The first time, if the table does not exist, it is created with the columns name, email and course.

Good practices

• Turn on Drop Table only if you want to replace the whole content of the table: it deletes the previous data without asking for confirmation.
• The component can create, delete and write tables. If you will only look things up, use a credential with a read-only user; if it will save data, give it permissions only on the tables it needs.
• Try SQL queries with few results before running them on large tables.

Related components

• MySQL: the more complete version of this component.
• SQL Agent: answers questions about your database.
• PostgresSQL: the same for PostgreSQL.