MySQL

Read and write a MySQL (or MariaDB) database from a workflow: your own server, or a hosted one such as Azure Database for MySQL. Look rows up, insert, update and delete them without writing SQL, or run any single statement with the workflow's variables bound as parameters.

The Module adds five Tasks: Find rows, Insert row, Update rows, Delete rows and Run SQL. The connection is set once per Environment.

What you can build

• Look a customer up when a chat or form starts: Find rows by email, then greet by {customer.name}.
• Record what happened: Insert row into requests with the form's fields and created_at = NOW(), and tell the visitor the new {inserted.insertId}.
• Keep a status in sync: Update rows status = shipped where id equals {order.id} after the carrier's webhook.
• Nightly clean-up: a Scheduler trigger runs Delete rows where expires_at is less than {execution.now}.
• A daily report: Run SQL with totals grouped by day, then send the rows with an Output or MailKit.

Requirements

• A MySQL 5.7+ or MariaDB 10.3+ server the Engine can reach (a public host name with TLS, a private endpoint, or a firewall rule for the Engine's outbound address).
• A database user. Give it only the grants the flows need (SELECT, INSERT, UPDATE, DELETE on their tables); the Module's access level is a second guard, not a replacement for grants.

Configuration

Setting	Required	Notes
Host	yes	Host name or IP. Azure Database for MySQL: <server>.mysql.database.azure.com.
Port	no	Empty: 3306.
Database	yes	The schema the Tasks work on. Table names without a prefix are looked up here.
Username / Password	yes	The password is stored encrypted.
TLS	no	Required (encrypted, certificate not checked; what Azure needs) by default. Verify server certificate or Verify CA check the certificate. None only for a private network.
Access	no	Read and write (default): SELECT, SHOW, INSERT, UPDATE, DELETE, REPLACE, CALL. Read only: queries only, run inside a read-only transaction so even a stored function that writes is refused by the server. Full: also CREATE, ALTER, DROP, TRUNCATE, RENAME.
Statement timeout	no	1-300 seconds, empty: 30. A longer statement fails with timeout.
Connection timeout	no	1-60 seconds, empty: 15.
Max rows	no	1-10,000, empty: 1,000. The most rows one step reads into variables.
Connection string	no	Expert: a full MySqlConnector connection string that replaces the fields above. Stored encrypted.

Whatever is set, the Module forces AllowLoadLocalInfile=false (no LOAD DATA LOCAL) and its own timeouts.

How values move

• Variables are always parameters. WHERE email = {email} sends the value as a bound parameter; whatever a visitor typed can only be compared, never executed. Write a variable bare, never in quotes ('{email}' would be the literal text).
• A lone {variable} keeps its type. {order.id} stays a number, {flag} a boolean, a missing variable is NULL. Text around it (ORD-{id}) is a string. A list or object becomes JSON text (for a JSON column).
• Rows come back typed. Integers are numbers, DECIMAL keeps its scale, DATETIME is a date, TINYINT(1) is true/false, a JSON column is a nested object ({row.tags.vip}), TIME is HH:mm:ss, binary data is base64 text, NULL is null. A join's repeated column names get _2, _3.
• Names are checked and quoted. A table or column is orders, shop.orders, o.email or, for other characters, `my column`. Table names may hold {variables}; the result is still checked.
• One statement per step. Each Task runs one statement on a fresh pooled connection and commits. There are no transactions across steps.

Tasks

Find rows

Reads the rows of one table that match conditions.

Property	Description
Table	The table: orders, shop.orders or `my table`. {variables} allowed.
Columns	Comma separated names. Empty or *: every column. For expressions (COUNT, SUM, CONCAT) use Run SQL.
Keep rows that match / Where	All conditions or Any condition. Each condition is a column, an operator and a value (text, a number, a date, {variables}).
Order by	Sort keys, first one first.
Limit / Offset	At most this many rows (0: the Environment's Max rows, which is also the ceiling) and how many to skip.
Rows variable gets	All rows: a list ({rows.0.name}, {rows.count}). First row: one record ({row.name}), or nothing when no row matches.
Rows variable / Count variable	Where the rows and their number go.

Operators: equals, does not equal, contains, does not contain, starts with, ends with, is empty (NULL or ''), is not empty, is NULL, is not NULL, greater than, greater or equal, less than, less or equal, is one of, is not one of.

• equals with a missing variable is IS NULL.
• contains, starts with, ends with and is one of with an empty value match no row: a blank form field can never turn a filter into "every row". % and _ in the value are matched literally.
• greater than and the other comparisons turn numeric text (99.5, 99,5) into a number, so {form.fields.min} compares as a number.
• is one of takes a list variable ({ids}) or ; separated text (open; pending).

Insert row

Inserts one row from column values.

Property	Description
Table	The table to insert into.
If the key exists	Fail (error duplicate_key), Update the existing row (ON DUPLICATE KEY UPDATE with the same values) or Keep the existing row (INSERT IGNORE).
Values	One line per column: a Value (text or {variables}, passed as a parameter), a SQL expression (NOW(), UPPER({name}), JSON_OBJECT('vip', true); its {variables} are parameters too) or NULL. Columns left out take their default.
Store result in	{inserted}: insertId (the new AUTO_INCREMENT id, 0 when the table has none), affected and outcome: inserted, updated, unchanged (the row already held the same values) or ignored.

Update rows

Changes the rows that match conditions.

Property	Description
Table	The table to change.
Set	The columns and their new values: a Value, a SQL expression (qty - {n}) or NULL.
Keep rows that match / Where	As in Find rows. A step with no condition fails with invalid_input unless Change every row is on.
Affected variable	{affected}: the number of rows changed. A row set to the values it already had does not count.

Delete rows

Deletes the rows that match conditions.

Property	Description
Table	The table to delete from.
Keep rows that match / Where	As in Find rows. A step with no condition fails with invalid_input unless Delete every row is on.
Affected variable	{affected}: the number of rows deleted. Rows other tables still reference fail with foreign_key.

Run SQL

Runs one statement you write.

Property	Description
SQL	One statement. {variables} are bound as parameters: write them bare (WHERE id = {order.id}).
Rows variable gets	All rows (a list), First row (one record, or nothing) or Single value (the first column of the first row: SELECT COUNT(*) ...).
Rows variable	Where the result goes. Empty: the rows are not kept.
Store result in	{sqlResult}: rowCount, affected (rows written), insertId (the new AUTO_INCREMENT id) and columns.

What may run depends on the Environment's Access:

Statements	Read only	Read and write	Full
SELECT, WITH ... SELECT, SHOW, DESCRIBE, EXPLAIN, TABLE, VALUES	yes	yes	yes
INSERT, UPDATE, DELETE, REPLACE, CALL, DO, WITH ... UPDATE/DELETE	not_allowed	yes	yes
CREATE, ALTER, DROP, TRUNCATE, RENAME	not_allowed	not_allowed	yes
Several statements, LOAD DATA, INTO OUTFILE, LOAD_FILE(), SET, USE, GRANT, LOCK, transactions, PREPARE, administration	refused	refused	refused

A read that returns more than Max rows fails with too_many_rows: add a LIMIT, or page with LIMIT ... OFFSET .... A CALL may return several result sets; the first one is kept.

SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE c.email = {email} AND o.created_at >= {since}
ORDER BY o.created_at DESC
LIMIT 20

Errors

Every Task writes {mysqlError} when it takes its error branch: code to branch on with a Switch, message a short sentence safe to show an end user, detail what went wrong exactly (the server's number and text) for you. The step's log carries the detail too.

Code	When
invalid_input	A field is missing or malformed: a bad table name, no condition on a Delete, an output that is not a {variable}.
not_configured	The Environment's MySQL configuration is incomplete.
connection	The server could not be reached (host, port, firewall, TLS handshake).
access_denied	Wrong credentials, or the user may not touch that database, table or column.
not_allowed	The Access level forbids the statement (a write with Read only, a schema change without Full).
refused	A statement the Module never runs (see the table above).
invalid_sql	The server could not parse the statement.
unknown_database, unknown_table, unknown_column	A name does not exist.
duplicate_key	A primary or unique key already holds the values.
foreign_key	A missing parent row, or children still referencing the row.
invalid_value	A value does not fit its column: too long, wrong type, NULL into NOT NULL.
too_many_rows	The result has more rows than Max rows.
timeout, lock_timeout, deadlock	The statement took too long, waited on a lock, or lost a deadlock. Retrying after a short Delay usually works.
sql_error	Any other server error; see detail.

Good to know

• Test runs use the same database as live runs: point a Testing Environment at a test database.
• Connections are pooled per Environment configuration; a step borrows one and gives it back.
• Dates: a DATETIME has no time zone; store and compare UTC ({execution.now}) unless the database is known to use another zone.
• Large results belong in a file, not in variables: Run SQL with a LIMIT, or export with your own tooling and read the file with File Query.