Cyberithub

MuleSoft Integration with Database [Explained with examples]

Advertisements

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:-

Advertisements
  • 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]

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.

Advertisements

MuleSoft Integration with Database [Explained with examples] 2

 

 

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.

MuleSoft Integration with Database [Explained with examples] 3

 

Step 3: Add Components from Mule Palette

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

Advertisements

MuleSoft Integration with Database [Explained with examples] 4

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.

Advertisements
  • 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.

MuleSoft Integration with Database [Explained with examples] 5

NOTE:

Just for demonstration purpose, we are using an existing database table called customertable in below examples. To know more about MySQL Database table creation, check How to Create Table in MySQL 5.5 with Easy Steps article.

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.

MuleSoft Integration with Database [Explained with examples] 6

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.

MuleSoft Integration with Database [Explained with examples] 7

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.

MuleSoft Integration with Database [Explained with examples] 8

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.

MuleSoft Integration with Database [Explained with examples] 9

 

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

NOTE:

Please note that in our case API url is http://localhost:8081/create as shown below. It could be different for you so you need to use the right URL here, otherwise request would fail.

MuleSoft Integration with Database [Explained with examples] 10

 

Step 6: Validate Response

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

MuleSoft Integration with Database [Explained with examples] 11

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

MuleSoft Integration with Database [Explained with examples] 12Above 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.

MuleSoft Integration with Database [Explained with examples] 13

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
}

NOTE:

Please note that value1, value2, value10 and value11 are internal dynamic properties.

 

Step 8: Send POST Request

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

MuleSoft Integration with Database [Explained with examples] 14Then send the request and notice the changes done.

MuleSoft Integration with Database [Explained with examples] 15

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

MuleSoft Integration with Database [Explained with examples] 16

 

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

MuleSoft Integration with Database [Explained with examples] 17

 

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.

MuleSoft Integration with Database [Explained with examples] 18

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.

MuleSoft Integration with Database [Explained with examples] 19

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.

MuleSoft Integration with Database [Explained with examples] 20

1 thought on “MuleSoft Integration with Database [Explained with examples]”

Leave a Comment