Insert Query

Add MySQL Execute Function

Start by adding the MySQL Execute function. Give it a Result Name and set the Type to Insert.

Write the SQL Query

In the function configuration, write the SQL query you want to use for inserting data.

Assume we have a users table and want to insert a new record. The users table schema looks like this:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int unsigned NOT NULL,
  `activated` tinyint(1) NOT NULL DEFAULT '1',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now write the following query:

INSERT INTO users(name, email, age, activated) VALUES(?, ?, ?, ?)

Note that we use placeholders (?) instead of hardcoding values—this helps safely pass parameters.

Fill the Arguments

You can provide arguments in three ways:

1. Pass a List: Provide a list of values matching the placeholders in the SQL query.
2. Use a Literal List: Construct a new list using literals or variables.
3. Pipe Values: Use pipes to transform current input values using a custom function.

In this example, we'll use a literal list for simplicity. Make sure the Arguments As Literal option is enabled to allow building a literal list.

Tap the editor to open a dialog and define your list:

Here's an example of the list literal:

["$name", "$email", "$age", 1]

We use the $ sign to reference variables and pass 1 directly for the activated field.

Return Insert ID & Affected Rows

To return the last inserted ID, enable the Last Insert Id option.
You can also enable Affected Rows to get the number of rows impacted by the query.

Setup Dependency with MySQL Connect

Instead of passing a new *MySQLDb instance each time, you can inject it using the Registry.

Use the MySQL Connect function inside your own setup function like SetupDependency. You can configure to runs when the server starts, connects to MySQL, and registers the database connection as MySqlDb.

You can register it with any name, but make sure the Dependency Name input in MySQL Execute matches exactly. (By default, it's empty, which falls back to MySqlDb.)

Choose a caching strategy:

• Cache Once or Cache Lazy are usually sufficient inside API handlers.

Enable Force Unwrap to retrieve the result only if there’s no error. It's recommended to handle errors gracefully.

Use the Returned Result

The result returned is either Res<SqlRes> or SqlRes, depending on whether you unwrapped it or not.

Add MySQL Execute Function

Start by adding the MySQL Execute function. Give it a Result Name and set the Type to Insert Bulk.

Write the SQL Query

Just like in the previous example, we're inserting data into the users table. However, this time we’re inserting multiple records at once, so we use the following query:

INSERT INTO users(name, email, age, activated) VALUES #PLACE_HOLDERS

Here we use #PLACE_HOLDERS instead of individual placeholders (?) because we’re batching multiple rows in a single query. The platform will automatically inject the correct number of placeholders based on your configuration.

You’ll also need to define:

• Batch Size: how many records to insert per query (e.g., 3)
• Place Holder Count: the number of placeholders each row needs (in this case, 4 for name, email, age, activated)

Fill the Arguments

In this example, we use pipes to generate mock data for insertion.

When using bulk insert, instead of a flat list of values, you need to pass a list of lists, where each inner list contains the values for one row.

To do this, click Add Pipe below the Arguments input and define the pipe logic.

Inside the pipe:

1. Use the Make List function to create three individual lists of type List[Any], each containing mock user data.
2. Append each list to a variable called pArguments, which is of type List[List[Any]].
3. Return pArguments at the end.
4. Click the close icon to return to the main stack.
5. You’ll now see a label like +7 Pipe below the Arguments input, indicating the pipe was successfully added.

Testing

Once everything is set up, run the function to test the insert.