Bulk-insert using Database connector

In this article, I will explain how to implement the bulk insert operation in the Mule 4 Database Connector. For instance, we have a json Input payload that contains a list of people. Our interface will read that input file and insert the person data to a mySQL Database Table using Bulk Insert.

Anypoint Studio Flow with HTTP Listener, Transform Message, and Bulk Insert Connector — Figure 1: Complete Anypoint Studio flow layout with Bulk Insert connector

1HTTP Listener configuration:

Go to Global elements->create->select the HTTP LISTNER

Global Elements tab and Create HTTP Listener — Figure 2: Selecting HTTP Listener in Global Elements

Configuring HTTP Listener Host and Port — Figure 3: Configuring HTTP Listener connection parameters

Setting HTTP Listener path — Figure 4: Setting the HTTP Listener path

HTTP Listener configuration confirmation — Figure 5: HTTP Listener general configuration settings

2Transform Message(Dataweave 2.0)

Transform Message(Dataweave 2.0): This component will transform the CSV Data into a JSON format. The field name in the Dataweave will be the parameter name in our Database Connector Insert Query.

Transform Message DataWeave transformation mapping fields — Figure 6: DataWeave mapping CSV/Payload attributes to database query parameters

3Database Connector (Bulk Insert)

Database Connector (Bulk Insert): This Database Connector will execute the Bulk Insert Operation with the parameters from the payload(application/json).

Database Connector Bulk Insert configuration in Anypoint Studio — Figure 7: Database Connector Bulk Insert operation properties

4Setup-Database in clever-cloud:

Use this link to signup clever-cloud: https://www.clever-cloud.com/

Provide all the required details and login to the clever-cloud

Clever Cloud Login Console — Figure 9: Clever Cloud dashboard login

Create your organization:

Create Organization in Clever Cloud — Figure 10: Creating organization in Clever Cloud

Create an add-on:

Select Create Add-on in Clever Cloud — Figure 11: Choosing to create an add-on service

Add-on selection catalog — Figure 12: Catalog of available database add-ons

Select the Add-on according to your requirements ,here am adding mysql add-on

Selecting MySQL Database Add-on — Figure 13: Choosing MySQL as the target database engine

Provide the name of your mysql add-on

Naming the MySQL Add-on — Figure 14: Configuring the MySQL database instance name

Save the configuration details of Your ad-on

MySQL Add-on Connection Credentials — Figure 15: Database host, user, password, and port credentials

5Database Configuration in Anypoint Studio & phpMyAdmin

Database configuration:

By using your addon database configuration details configure the database in Any point studio

Configuring MySQL Connection in Database Config — Figure 16: Entering host, port, user, and password into Mule Database Config

Testing Database Connection in Anypoint Studio — Figure 17: Database Connection Test Successful

Click on phpmyadmin to create your database in clever-cloud

Accessing phpMyAdmin from Clever Cloud dashboard — Figure 18: Launching phpMyAdmin web console

Create table in clever-cloud Database :

Creating Table Structure in phpMyAdmin — Figure 19: Defining table columns in phpMyAdmin

Table created with zero initial records — Figure 20: Table details created with empty row count

6Sql–query for bulk insert:

Write the sql query to insert the data into the database

MySQL Bulk Insert Query

sql

INSERT INTO details (Firstname,Middlename,Lastname,Emailid,Country) VALUES (:firstname,:middlename,:lastname,:emailid,:country)

Configuring SQL Query and Parameters in Bulk Insert Connector — Figure 21: Bulk Insert connector configuration showing query text and input parameters

7Deployment and Test Execution

Save and deploy the application, after successful deployment Hit the url from Postman

Deploying Mule Application to Cloudhub/Runtime — Figure 22: Successful deployment log in Anypoint Studio console

Executing POST Request from Postman with JSON Array Payload — Figure 23: Sending JSON batch request via Postman

Test Result:

Postman HTTP 200 OK Response — Figure 24: Postman receiving successful response payload

Database Table is now having 163 records.

phpMyAdmin Table displaying 163 inserted records — Figure 25: phpMyAdmin table view confirming 163 rows inserted successfully

Conclusion

In this tutorial, we demonstrated the end-to-end implementation of the Mule 4 Database Connector Bulk Insert operation. By processing a structured JSON payload of people records, mapping attributes via DataWeave, configuring a cloud-hosted MySQL database on Clever Cloud, and executing the bulk query, we achieved efficient, high-throughput database batch insertion with full transaction verification.

“Thank you for taking out time to read the above post. Hope you found it useful. In case of any questions, feel free to comment below. Also, if you are keen on knowing about a specific topic, happy to explore your recommendations as well.”

About the Author

Vinay Kumar Theetla

Integration and API Security Specialist at DigitalBuzz Software, experienced in MuleSoft Anypoint Platform, Cloudhub architectures, database integrations, and high-throughput enterprise pipelines.

Share this article

All Articles

OAuth Mule