How to Use MuleSoft to Store and Retrieve Files of Any Format in a Database

MuleSoft is a powerful platform that allows you to create, integrate, and manage APIs for various applications and services. One of the common challenges that developers face is how to store and retrieve any type of file, such as images, videos, documents, etc., without compromising the quality or integrity of the file.

In this blog post, we will demonstrate how to develop a MuleSoft API that stores and retrieves any type of files using MySQL server DB, using Postman for testing purposes.

Prerequisites

To follow along with, you will need:

• Anypoint Studio 7.9 or later
• MySQL Server account( Register (freesqldatabase.com) )
• Postman

Step 1: Create a MySQL account

1. Click on MySQL Account Registration to create an account.
2. Login to the account.
3. Select the Server Location.
4. Go to Database Details and click on Start, it will generate the credentials.
5. Wait for the DB Status to become Live.
6. Create fileInfo table using the script in Appendix section.

Step 2: Create a Mule Project

First, we need to create a new Mule project in Anypoint Studio. To do this, go to File > New > Mule Project and give it a name, such as “file-storage-api”. Select the Mule runtime version as 4.3.0 or later and click Finish.

Step 3: Add Dependencies

Next, we need to add some dependencies to our project. These are the modules and connectors that we will use to implement our API logic. To do this, go to the pom.xml file in your project and add the following dependencies:

<dependencies>
  <dependency>
   <groupId>org.mule.connectors</groupId>
   <artifactId>mule-http-connector</artifactId>
   <version>1.6.0</version>
   <classifier>mule-plugin</classifier>
  </dependency>
  <dependency>
   <groupId>org.mule.connectors</groupId>
   <artifactId>mule-sockets-connector</artifactId>
   <version>1.2.2</version>
   <classifier>mule-plugin</classifier>
  </dependency>
  <dependency>
   <groupId>org.mule.connectors</groupId>
   <artifactId>mule-file-connector</artifactId>
   <version>1.3.4</version>
   <classifier>mule-plugin</classifier>
  </dependency>
  <dependency>
   <groupId>org.mule.connectors</groupId>
   <artifactId>mule-db-connector</artifactId>
   <version>1.11.0</version>
   <classifier>mule-plugin</classifier>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>5.1.48</version>
  </dependency>
 </dependencies>

Save the file and wait for the dependencies to be downloaded.

Step 4: Configure the MySQL DatabaseConnector

Before we can implement the API logic, we need to configure the database connector that allows interaction with MySQL. To do this, open the fs-db-filestore-retrive.xml file and from the Global Elements tab, click Create and select Database_Config: MySQL Connection.

In the configuration window, enter the following details:

1. Host: sql12.freesqldatabase.com
2. Port: 3306
3. User: YourUserName
4. Password: YourPassword
5. Database: YourDatabaseName

Click OK to save the configuration.

Step 5: Implement the API Logic

Now, we are ready to implement the API logic. To do this, go to the Message Flow tab and follow these steps:

POST /postData

This endpoint will upload a file to MySQL and return its metadata as JSON.

1. Drag an HTTP Listener component from the Mule Palette to the canvas and drop it. This will create an HTTP Listener configuration. Enter the following details:

• Name: httpListenerConfig
• Host: 0.0.0.0
• Port: 8081

Click OK to save the configuration.

2. Double-click on the HTTP Listener component and set the Path to /postData

3. Drag a database component from the Mule Palette to the canvas and drop it in the POST:/postData flow, after the Transform Message component. Double-click on it and set the following properties:

• Set connector configuration
• SQL query test
• Input params

GET /getData

This endpoint will upload a file to MySQL and return its metadata as JSON.

1. Drag an HTTP Listener component from the Mule Palette to the canvas and drop it. This will create an HTTP Listener configuration. Enter the following details:

• Name: httpListenerConfig
• Host: 0.0.0.0
• Port: 8081

Click OK to save the configuration.

2. Drag a database component from the Mule Palette to the canvas and drop it in the POST:/postData flow, after the Transform Message component. Double-click on it and set the following properties:

• Set connector configuration
• SQL query test
• Input params

3. Drag the file connector to write the file in a specific location. Double-click on it and set the following properties:

• Set connector configuration
• Path
• Content

Step 6: Test the API

Now, we can test our API using Postman or any other API testing tool. To do this, follow these steps:

1. Open Postman and create a new request.
2. Set the method to POST and enter http://localhost:8081/postData as the URL.
3. In the params tab, add a key-value pair with filePath as the key and a file of your choice as the value.
4. Click on Send and check that you get a 201 response with a JSON object.
5. Create another request with GET method and enter http://localhost:8081/getData
6. Click on Send and check that you get a 200 response with a JSON array containing all your file’s metadata.
7. Once you retrieve the data you can verify that your file is stored in the specified path and that the file is not corrupted.

Appendix

SQL Query to create the table:

create table sql12629982.fileInfo(filename varchar(255), filetype varchar(20),content LONGBLOB,
lastupdate TIMESTAMP default now());

Project GIT URL : Store-and-Retrieve-Any-Type-of-File-with-MuleSoft-API

Conclusion

Hopefully this post has being enjoyed and taught something new. By following these steps, one can successfully create a MuleSoft API that can store and retrieve any type of files using databases. For any questions or feedback, please leave a comment below. Thank you for reading!