In this tutorial, we will see MuleSoft Integration with Database with the help of best examples. Mule Connectors can connect any system without using any programming code in just few clicks. We can connect any target from any source in this way. Here we will not just see the Integration of Mulesoft with MySQL Database but we will also see the working of different database operations using different mule database connectors. So without further delay, Let's begin !!

Prerequisites

This tutorial assumes that you have below prerequisites already in place:-

• You should have a valid Anypoint platform account
• You should have a valid Salesforce Developers Account
• You should have Anypoint Studio installed in your System
• You should have a destination database available(In our case, it is MySQL DB)
• You should also have Postman installed in your System
• You must have basic knowledge in Anypoint Studio Project Implementations
• You must have basic knowledge of database query
• Core Java Knowledge is an added advantage

MuleSoft Integration with Database [Explained with examples]

Also Read: Introduction to Mulesoft Connectors [Explained with examples]

Step 1: Create a Project in Anypoint studio

Go to File -> New-> Mule Project to create a new mule project. Give a meaningful name to the Project.

Step 2: Add Mule Database Connectors to Anypoint Studio from Exchange

• Search in exchange button available in mule palette
• Sign in with Anypoint Platform username & password
• Search “database” keyword in search box
• Select Database Connector with Mulesoft Publisher
• Choose Add button as highlighted below
• Finish the selection automatically closed this window

Now database connector is available in mule palette. Now you can use this database connector in your project.

Step 3: Add Components from Mule Palette

You can drag and drop Mule Components from Mule palette as shown below.

Each of the connector listed under database section are designed for a specific database operation. Here we are going to insert customer data into database through mule database insert connector.

Step 4: Insert into Database

To create a database insert, we are going to use below group of mule connectors in the flow.

• Listener: It is configured for listening http requests.
• Logger: It is configured for monitoring and debugging mule application by logging important information such as error messages, status notifications, payloads, and so on.
• Transform Message: This component converts input data to a new output structure or format.
• Insert: This component is used for insert database operation.
• Error Handling: This component is used to route error to the first matching handler.

Then the xml code for insert database operation would look like below:-

<flow name="withsalesforceFlow" doc:id="669ff7e1-3024-48f0-b2d8-e1fc9ba90895" >
<http:listener doc:name="Listener" doc:id="d26c9bfd-54a2-460f-8677-30eaf5a243a1" config-ref="HTTP_Listener_config" path="${http.path}"/>
<logger level="INFO" doc:name="Start Logger" doc:id="506b10f9-38dc-47d6-b8f8-55c56237ef53" message='#["Processing event received."]' />
<ee:transform doc:name="Transform to Json" doc:id="bf692e4e-8116-40fd-8a46-8e15d2701659">
   <ee:message>
      <ee:set-payload><![CDATA[
                         %dw 2.0
                         output application/json
                          ---
                         payload

      ]]></ee:set-payload>
   </ee:message>
</ee:transform>

<db:insert doc:name="Insert into Database" doc:id="19d20864-541f-452e-8dd2-2a560f86ebb0" config-ref="Database_Config">
<db:sql><![CDATA[
                 INSERT INTO customerdata (first_name, last_name, company_name, address, city, county, state, zip, phone, email)
                 VALUES ( :value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10 )
]]></db:sql>

<db:input-parameters><![CDATA[#[{
                        value1: payload.first_name,
                        value2: payload.last_name,
                        value3: payload.company_name,
                        value4: payload.address,
                        value5: payload.city,
                        value6: payload.county,
                        value7: payload.state,
                        value8: payload.zip,
                        value9: payload.phone,
                        value10: payload.email
}]]]></db:input-parameters>
</db:insert>

<logger level="INFO" doc:name="End Logger" doc:id="323b8ce5-3e57-4931-a209-54d23a019149" message= “Message processed successfully"/>
</flow>

In transform message, data in any input format would be transformed into json data format.

%dw 2.0

output application/json
---
Payload

Database insert mule connector performs data insert operation. Insert connector has to configure with respected values. To do that, you need to first select the database connection type from the selection panel as shown below. Here we are choosing MySQL Connection as you can see below.

Then you need to add the required MySQL JDBC driver from Configure->Add recommended libraries option as shown below. Once it is configured properly, you will automatically see red icon displayed to green tick mark.

To configure database connection, you need to provide below details:-

• Host
• Port
• User
• Password
• Database

After configuring the database, you need to test the connection by clicking on Test Connection. Once the connection is successful then only you can proceed with the insert operation. Otherwise, you need to re-check your configuration until test connection is successful. Then you need to use below XML code for Database insert connector:-

<db:insert doc:name="Insert" doc:id="af556c13-8941-4583-a6a4-b59d36bfe675" config-ref="Database_Config">
<db:sql>
<![CDATA[
        INSERT INTO customerdata (first_name, last_name, company_name, address, city, county, state, zip, phone, email)
        VALUES ( :value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10 )
]]>
</db:sql>

<db:input-parameters>
<![CDATA[#[{
            value1: payload.first_name,
            value2: payload.last_name,
            value3: payload.company_name,
            value4: payload.address,
            value5: payload.city,
            value6: payload.county,
            value7: payload.state,
            value8: payload.zip,
            value9: payload.phone,
            value10: payload.email
}]]]>
</db:input-parameters>

</db:insert>

As you can see below, SQL query used for customerdata table would look like below where the values are provided with dynamic values which is further configured with input parameters. The input parameters can be chosen based on input data you would like to insert.

After finishing all the project operations like naming configurations, property file creations and connectors configuration, you can run the query on customerdata table. Then verify the table by running select * from `customerdata` query as shown below. You will see currently there are no data inserted into the table. We will insert the values through Postman request.

Step 5: Setup Postman

It is now time to setup and configure Postman for database requests. Go to Rest client Postman request screen and perform below steps:-

• Enter your API URL with resource path
• Change method to POST
• Change request type as body
• Choose the type of data RAW
• Data format as Json for sample
• Enter input data valid Json format without errors
• Send the http request to our API

Step 6: Validate Response

After sending request through Postman, you should see a response like below:-

You can verify the data inserted into customerdata table by using select * from `customerdata` sql query. It should show like below.

Above output confirm that our goal of mule integration with database has been achieved successfully. This was just for single data insert but if you are looking to insert bulk amount of data then you can go for database bulk insert connector.

Step 7: Update Data

Like insert operation, you can also perform update operation in database using update connector. For that, the mule update database flow would look like below. Here the only change is that instead of insert we are using update connector.

Using above flow, we are going to update first_name, last_name and phone based on reference email. We are using below SQL Query to update the data in customerdata table.

UPDATE customerdata
SET first_name= :value1,
last_name= :value2,
phone= :value10
WHERE email= :value11

And, the input parameters should look like below:-

Input parameters: {
value1: payload.first_name,
value2: payload.last_name,
value10: payload.phone,
value11: payload.email
}

Step 8: Send POST Request

Before sending postman request, it is always advised to verify the current data in the table.

Then send the request and notice the changes done.

You can see the http response code is 200 with the data insertion successful message. This confirms successful data update.

You can see that database will be updated based reference email as shown below.

Step 9: Delete Data

Likewise, you can also perform Delete operations based on reference email id. Now you need to add Delete connector instead of update in below Mule Flow.

To delete data from the customerdata table, you can use below SQL Query.

delete from customerdata
WHERE email= :value11

And, the input parameters section should look like below:-

{
 value11: payload.email
}

You can see that the above postman delete request resulted in http status code 200 in response tab. This confirms that data is successfully deleted.

Now if you go back and check the database table using the same select * from `customerdata` query, you will notice that the data is deleted successfully as you can see below.