Introduction

MuleSoft is a powerful integration platform that helps developers design, build, and manage APIs for various applications. A common challenge is handling different file types—like images, videos, and documents while preserving their quality and integrity. This guide will walk you through creating a MuleSoft API that stores and retrieves any file format from a MySQL database, with Postman for testing. Think of it as building a secure, digital filing cabinet where you can store and retrieve any item perfectly intact.

What You Will Need

Before starting, ensure you have the following tools and accounts ready:

Anypoint Studio 7.9 or later: The IDE for developing Mule applications.
A MySQL Server account: You can register for a free account at https://www.freesqldatabase.com/register/.
Postman: For testing the API endpoints.

Step 1: Set Up Your MySQL Database

First, you need a database. After registering and logging into your MySQL account (like the free one from freesqldatabase.com), select your server location and generate your database details. Once the database is active, create the necessary table using this SQL script:

sql

CREATE TABLE sql12629982.fileInfo (
    filename VARCHAR(255),
    filetype VARCHAR(20),
    content LONGBLOB,
    lastupdate TIMESTAMP DEFAULT NOW()
);

This table will store the file's name, type, binary content, and a timestamp.

Step 2: Create a New Mule Project

Open Anypoint Studio and create a new project. Go to File > New > Mule Project. Name it something descriptive, like "file-storage-api". Select Mule runtime version 4.3.0 or later and click Finish.

Step 3: Add the Required Dependencies

Your project needs specific connectors to function. Add these dependencies to your project's pom.xml file. These modules enable HTTP listening, database interactions, and file operations.

HTTP Connector: For handling API requests.
Sockets Connector: For network communication.
File Connector: For reading and writing files.
Database Connector: For interacting with MySQL.
MySQL Java Connector: The JDBC driver for MySQL.

Wait for Studio to download all the dependencies after saving the pom.xml file. (Source: https://docs.mulesoft.com/general/)

Step 4: Configure the Database Connection

Now, configure the link between your Mule app and the MySQL database. In your project's Global Elements, create a new Database (DB) Config and select MySQL Connection. Enter your database credentials:

Host: sql12.freesqldatabase.com
Port: 3306
User: YourUserName
Password: YourPassword
Database: YourDatabaseName

Click OK to save this configuration. This connector is the bridge your API will use to talk to the database.

Step 5: Implement the API Logic

This is the core of your application. You will create two main endpoints.

Creating the POST Endpoint to Store a File

The POST /postData endpoint receives a file and stores its metadata in the database.

Drag an HTTP Listener to the canvas. Set its host to 0.0.0.0 and port to 8081. Set the path to /postData.
The listener triggers a flow. Use a Transform Message component to prepare the data.
Add a Database (Insert) component. Configure it to use your DB connection and write an INSERT query to add the file's name, type, and binary content (as a BLOB) to the fileInfo table.

Creating the GET Endpoint to Retrieve a File

The GET /getData endpoint fetches the file's information from the database and reconstructs the file.

Drag another HTTP Listener to a new flow. Set the path to /getData.
Add a Database (Select) component to query the fileInfo table for the requested file.
Use a File Write component to take the binary data from the database response and write it to a specific location on your server, effectively recreating the original file.

Step 6: Test Your API with Postman

It's time to see your API in action.

Test the POST Endpoint: In Postman, create a new POST request to http://localhost:8081/postData. In the Params tab, add a key filePath with the path to a test file. A successful 201 response with a JSON object confirms the file was stored.
Test the GET Endpoint: Create a new GET request to http://localhost:8081/getData. A 200 response with a JSON array means the metadata was retrieved. Check the designated file path to confirm the file itself was recreated without corruption.

Conclusion and Next Steps

Congratulations! You have successfully built a MuleSoft API that can store and retrieve any file type from a MySQL database. This provides a robust foundation for managing digital assets in your applications.

You can find the complete project on GitHub: Store-and-Retrieve-Any-Type-of-File-with-MuleSoft-API. If you have any questions or suggestions, please feel free to share them in the comments below. Thank you for reading!

How to Store and Retrieve Files with MuleSoft and MySQL | MuleSoft Guide