MySQL integration

Overview

Connects to a MySQL server and performs structured CRUD operations — select, insert, update, upsert and delete — as well as raw SQL execution. Queries are parameterized to prevent SQL injection, and every identifier you supply is backtick-escaped. Data can be auto-mapped from the incoming item’s fields to table columns or mapped column by column. WHERE clauses, sorting, limits, INSERT IGNORE, ON DUPLICATE KEY UPDATE and three query-batching modes (single, independent, transaction) are all supported.

Node Type

Action — processes input items and produces output

Input / Output

Direction	Port(s)
Input	Input
Output	Output, Error

Credentials

This tool requires MySQL credentials. See the Credentials Guide for setup instructions.

Operations

Operation	Value	Description
Delete	deleteTable	Delete an entire table or rows in a table
Execute SQL	executeQuery	Execute an SQL query
Insert	insert	Insert rows in a table
Insert or Update	upsert	Insert or update rows in a table (ON DUPLICATE KEY UPDATE)
Select	select	Select rows from a table
Update	update	Update rows in a table

Parameters

Delete (deleteTable)

Parameter	Type	Required	Default	Description
Table	string	Yes	—	Name of the MySQL table to operate on.
Command	options	No	truncate	The delete command to execute.
				Options: truncate (remove all rows but preserve table structure), delete (delete rows matching conditions; all rows if no conditions), drop (delete the table and its structure permanently)
Select Rows	fixedCollection	No	{}	WHERE conditions to filter rows. If not set, all rows are selected.
— Column	string	No	—	Column name to filter on.
— Operator	options	No	equal	Comparison operator. LIKE uses % for wildcards.
				Options: equal, !=, LIKE, >, <, >=, <=, IS NULL, IS NOT NULL
— Value	string	No	—	Value to compare against. Not needed for IS NULL / IS NOT NULL. (hidden when Operator is IS NULL, IS NOT NULL)
Combine Conditions	options	No	AND	How to combine multiple WHERE conditions.
				Options: AND (all conditions must be true), OR (at least one condition must be true)

Execute SQL (executeQuery)

Parameter	Type	Required	Default	Description
Query	string	Yes	—	The SQL query to execute. Use $1, $2, etc. for parameterized values and $1:name for identifier parameters. Set replacement values in Options > Query Parameters.

Insert (insert)

Parameter	Type	Required	Default	Description
Table	string	Yes	—	Name of the MySQL table to operate on.
Data Mode	options	No	autoMapInputData	Whether to map input data to columns automatically or define columns manually.
				Options: autoMapInputData (use when input field names match table column names exactly), defineBelow (set the value for each column manually)
Values to Send	fixedCollection	No	{}	Column-value pairs to insert or update. Used in manual data mode. (shown when Data Mode is defineBelow)
— Column	string	No	—	Column name.
— Value	string	No	—	Value to set.

Insert or Update (upsert)

Parameter	Type	Required	Default	Description
Table	string	Yes	—	Name of the MySQL table to operate on.
Data Mode	options	No	autoMapInputData	Whether to map input data to columns automatically or define columns manually.
				Options: autoMapInputData (use when input field names match table column names exactly), defineBelow (set the value for each column manually)
Column to Match On	string	Yes	—	Column used in the WHERE clause to find the row to update. Must be a unique key for upsert. Enter the column name directly.
Value of Column to Match On	string	No	—	The value to match in the specified column. Used only in manual data mode. (shown when Data Mode is defineBelow)
Values to Send	fixedCollection	No	{}	Column-value pairs to insert or update. Used in manual data mode. (shown when Data Mode is defineBelow)
— Column	string	No	—	Column name.
— Value	string	No	—	Value to set.

Select (select)

Parameter	Type	Required	Default	Description
Table	string	Yes	—	Name of the MySQL table to operate on.
Return All	boolean	No	false	Whether to return all results or only up to a given limit.
Limit	number	No	50	Max number of results to return. (shown when Return All is false)
Select Rows	fixedCollection	No	{}	WHERE conditions to filter rows. If not set, all rows are selected.
— Column	string	No	—	Column name to filter on.
— Operator	options	No	equal	Comparison operator. LIKE uses % for wildcards.
				Options: equal, !=, LIKE, >, <, >=, <=, IS NULL, IS NOT NULL
— Value	string	No	—	Value to compare against. Not needed for IS NULL / IS NOT NULL. (hidden when Operator is IS NULL, IS NOT NULL)
Combine Conditions	options	No	AND	How to combine multiple WHERE conditions.
				Options: AND (all conditions must be true), OR (at least one condition must be true)
Sort	fixedCollection	No	{}	ORDER BY rules for select queries.
— Column	string	No	—	Column to sort by.
— Direction	options	No	ASC	Sort direction.
				Options: ASC, DESC

Update (update)

Parameter	Type	Required	Default	Description
Table	string	Yes	—	Name of the MySQL table to operate on.
Data Mode	options	No	autoMapInputData	Whether to map input data to columns automatically or define columns manually.
				Options: autoMapInputData (use when input field names match table column names exactly), defineBelow (set the value for each column manually)
Column to Match On	string	Yes	—	Column used in the WHERE clause to find the row to update. Must be a unique key for upsert. Enter the column name directly.
Value of Column to Match On	string	No	—	The value to match in the specified column. Used only in manual data mode. (shown when Data Mode is defineBelow)
Values to Send	fixedCollection	No	{}	Column-value pairs to insert or update. Used in manual data mode. (shown when Data Mode is defineBelow)
— Column	string	No	—	Column name.
— Value	string	No	—	Value to set.

All Operations

Parameter	Type	Required	Default	Description
Options	collection	No	{}	Advanced connection and query settings.
— Connection Timeout (ms)	number	No	30000	Milliseconds reserved for connecting to the database.
— Connection Limit	number	No	10	Maximum number of connections. High values can cause performance issues.
— Query Batching	options	No	single	How queries should be batched and sent to the database.
				Options: single (combine all items into a single query), independently (execute one query per input item), transaction (execute all queries in a transaction; rollback on failure)
— Query Parameters	string	No	—	Comma-separated values for $1, $2, $3 placeholders in your query. (shown when Operation is executeQuery)
— Output Columns	string	No	*	Comma-separated column names to return, or * for all columns. (shown when Operation is select)
— Output Large Numbers As	options	No	text	How to output NUMERIC and BIGINT columns. (shown when Operation is select or executeQuery)
				Options: numbers, text (use for numbers longer than 16 digits to avoid precision loss)
— Output Decimals as Numbers	boolean	No	false	Whether to output DECIMAL types as numbers instead of strings. (shown when Operation is select or executeQuery)
— Priority	options	No	LOW_PRIORITY	Insert priority level. (shown when Operation is insert)
				Options: LOW_PRIORITY (delays execution until no other clients are reading from the table), HIGH_PRIORITY (overrides the —low-priority-updates option)
— Replace Empty Strings with NULL	boolean	No	false	Whether to replace empty strings with NULL in input data. (shown when Operation is insert, update, upsert or executeQuery)
— Select Distinct	boolean	No	false	Whether to remove duplicate rows from results. (shown when Operation is select)
— Output Query Execution Details	boolean	No	false	Whether to include the executed SQL in the output.
— Skip on Conflict	boolean	No	false	Whether to use INSERT IGNORE to skip rows that violate unique constraints. (shown when Operation is insert)
Max Concurrency	number	No	1	Maximum number of items to process concurrently. Keep low for database connections.

Output Data

The result replaces the item’s JSON — the incoming fields do not pass through — so downstream nodes see the database’s answer and nothing else.

Operation	Output
select	One item per returned row, each carrying that row’s columns. A query that matches nothing produces a single item carrying success: true.
executeQuery	One item per row when the statement returns rows. A statement that changes data returns a single item with affectedRows and insertId. A statement with no rows and no counts returns success: true.
insert	One item carrying affectedRows and insertId.
update, upsert	One item carrying affectedRows.
deleteTable with Command delete	One item carrying affectedRows.
deleteTable with Command truncate or drop	One item carrying success: true and command.

A selected row arrives as the row itself:

{
  "id": 42,
  "name": "Ada Lovelace",
  "status": "active",
  "created_at": "2024-01-15T09:30:00.000Z"
}

With Output Query Execution Details on, an Execute SQL run returns a single item shaped { "sql": "…", "data": [ … ] } instead, so you can inspect the statement that ran.

Example Configuration

Select filtered, sorted rows:

{
  "type": "mysql",
  "parameters": {
    "operation": "select",
    "table": "users",
    "returnAll": true,
    "where": {
      "values": [
        { "column": "status", "condition": "equal", "value": "active" }
      ]
    },
    "sort": {
      "values": [
        { "column": "created_at", "direction": "DESC" }
      ]
    }
  }
}

Select a page of distinct rows with a narrowed column list:

{
  "type": "mysql",
  "parameters": {
    "operation": "select",
    "table": "products",
    "returnAll": false,
    "limit": 50,
    "where": {
      "values": [
        { "column": "category", "condition": "equal", "value": "electronics" }
      ]
    },
    "sort": {
      "values": [
        { "column": "price", "direction": "ASC" }
      ]
    },
    "options": {
      "selectDistinct": true,
      "outputColumns": "id,name,price,description"
    }
  }
}

Insert each incoming item, mapping its fields onto columns of the same name:

{
  "type": "mysql",
  "parameters": {
    "operation": "insert",
    "table": "analytics_events",
    "dataMode": "autoMapInputData",
    "options": {
      "queryBatching": "transaction",
      "skipOnConflict": true
    }
  }
}

Insert with explicit column values:

{
  "type": "mysql",
  "parameters": {
    "operation": "insert",
    "table": "products",
    "dataMode": "defineBelow",
    "valuesToSend": {
      "values": [
        { "column": "name", "value": "Product Name" },
        { "column": "price", "value": "29.99" },
        { "column": "category_id", "value": "5" }
      ]
    }
  }
}

Update a row matched on a key column:

{
  "type": "mysql",
  "parameters": {
    "operation": "update",
    "table": "orders",
    "dataMode": "defineBelow",
    "columnToMatchOn": "order_id",
    "valueToMatchOn": "12345",
    "valuesToSend": {
      "values": [
        { "column": "status", "value": "shipped" },
        { "column": "updated_at", "value": "2023-12-01 10:30:00" }
      ]
    }
  }
}

Upsert with auto-mapped input:

{
  "type": "mysql",
  "parameters": {
    "operation": "upsert",
    "table": "user_preferences",
    "dataMode": "autoMapInputData",
    "columnToMatchOn": "user_id",
    "options": {
      "replaceEmptyStrings": true
    }
  }
}

Run a parameterized query:

{
  "type": "mysql",
  "parameters": {
    "operation": "executeQuery",
    "query": "SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at >= $1 GROUP BY u.id HAVING order_count > $2",
    "options": {
      "queryReplacement": "2023-01-01,5",
      "detailedOutput": true
    }
  }
}

Delete rows matching two conditions:

{
  "type": "mysql",
  "parameters": {
    "operation": "deleteTable",
    "table": "logs",
    "deleteCommand": "delete",
    "where": {
      "values": [
        { "column": "created_at", "condition": "<", "value": "2023-01-01" },
        { "column": "level", "condition": "equal", "value": "debug" }
      ]
    },
    "combineConditions": "AND"
  }
}

Error Handling

Mode	Behavior
stop	Halts workflow on first error
continue	Skips failed items, passes successful ones through
errorPort	Routes failed items to Error output port

Tips

This tool executes SQL queries and performs CRUD operations on MySQL database tables. It is ideal for selecting, inserting, updating, upserting, or deleting rows when precise control over database interactions is needed. It produces the results of SQL queries or confirmation of successful data manipulation.

• Values are always parameterized. Column names, table names and other identifiers are backtick-escaped, and values are bound rather than interpolated — so a value containing a quote or a semicolon is data, never SQL.
• Placeholders in Execute SQL are positional. Write $1, $2, $3 for values and $1:name for an identifier, then supply the replacements in Options → Query Parameters as one comma-separated list, in order.
• Command on Delete is the difference between a filter and a wipe. delete honours the Select Rows conditions — and removes every row when there are none. truncate always empties the table, and drop removes the table itself.
• Update needs something to change. Every column other than the match column is written; if the only column supplied is the match column, the item fails rather than issuing a no-op statement.
• Upsert’s match column must be a unique key, otherwise ON DUPLICATE KEY UPDATE never triggers and you get duplicate rows.
• Auto-map sends the whole item. In autoMapInputData mode every property of the incoming item becomes a column, so trim the item upstream if it carries workflow metadata your table does not have.
• Large numbers default to text. BIGINT and NUMERIC columns are returned as strings to avoid precision loss; switch Output Large Numbers As to numbers only when the values are small enough to be safe.
• Keep Max Concurrency low. Each concurrent item holds a database connection; the default of 1 is deliberate.
• Select and Execute SQL fan out — each row becomes its own item, so downstream nodes process rows individually.