Testing database interactions is an essential aspect of MuleSoft application development. With MUnit, Mule developers can validate the functionality of our applications without requiring a live database connection. This is achieved using two main components: an H2 embedded database and the MUnit DB server. The H2 database serves as an in-memory database to replicate the schema and data of the MySQL server, while the MUnit DB server provides the tools to mock database interactions. One powerful feature of MUnit is its ability to use the DB server component to simulate a database for unit testing.In this tutorial, we’ll explore how to use the MUnit tools DB server to mock a MySQL connection. We’ll walk through creating a sample database schema and populating it with sample data to simulate a real-world scenario.
Prerequisites
Before proceeding, ensure you have the following installed:- Anypoint Studio with MUnit Plugin.
- An instance of MySQL Server. For testing purposes check out our previous post on How to Install MySQL on Docker
- A MySQL client for running SQL scripts. We can run them from Workbench or from a shell terminal, for example
Step 1: Create the Sample Database in MySQL
To replicate a real-world scenario, let’s create a database calledmule-db table Employees and populate it with 25 sample records. Follow these steps:- Open your MySQL client.
- Run the following SQL script to create the database and populate it with sample data:
-- Create the database
CREATE DATABASE mule_db;
-- Use the database
USE mule_db;
-- Create the Employees table
CREATE TABLE Employees (
employeeId INT AUTO_INCREMENT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
JobTitle VARCHAR(50),
JobID INT,
Salary DECIMAL(10, 2)
);
-- Insert sample data
INSERT INTO Employees (FirstName, LastName, Email, JobTitle, JobID, Salary) VALUES
('John', 'Doe', 'john.doe@example.com', 'Software Engineer', 101, 75000.00),
('Jane', 'Smith', 'jane.smith@example.com', 'Product Manager', 102, 90000.00),
('Alice', 'Brown', 'alice.brown@example.com', 'Data Scientist', 103, 88000.00),
('Bob', 'Davis', 'bob.davis@example.com', 'DevOps Engineer', 104, 82000.00),
('Charlie', 'White', 'charlie.white@example.com', 'QA Engineer', 105, 70000.00),
-- Add 20 more records for testing
('Employee1', 'Last1', 'employee1@example.com', 'Title1', 106, 65000.00),
('Employee2', 'Last2', 'employee2@example.com', 'Title2', 107, 67000.00),
('Employee3', 'Last3', 'employee3@example.com', 'Title3', 108, 68000.00),
('Employee4', 'Last4', 'employee4@example.com', 'Title4', 109, 69000.00),
('Employee5', 'Last5', 'employee5@example.com', 'Title5', 110, 66000.00),
('Employee6', 'Last6', 'employee6@example.com', 'Title6', 111, 72000.00),
('Employee7', 'Last7', 'employee7@example.com', 'Title7', 112, 71000.00),
('Employee8', 'Last8', 'employee8@example.com', 'Title8', 113, 70000.00),
('Employee9', 'Last9', 'employee9@example.com', 'Title9', 114, 73000.00),
('Employee10', 'Last10', 'employee10@example.com', 'Title10', 115, 76000.00),
('Employee11', 'Last11', 'employee11@example.com', 'Title11', 116, 78000.00),
('Employee12', 'Last12', 'employee12@example.com', 'Title12', 117, 79000.00),
('Employee13', 'Last13', 'employee13@example.com', 'Title13', 118, 75000.00),
('Employee14', 'Last14', 'employee14@example.com', 'Title14', 119, 74000.00),
('Employee15', 'Last15', 'employee15@example.com', 'Title15', 120, 76000.00),
('Employee16', 'Last16', 'employee16@example.com', 'Title16', 121, 77000.00),
('Employee17', 'Last17', 'employee17@example.com', 'Title17', 122, 80000.00),
('Employee18', 'Last18', 'employee18@example.com', 'Title18', 123, 81000.00),
('Employee19', 'Last19', 'employee19@example.com', 'Title19', 124, 82000.00),
('Employee20', 'Last20', 'employee20@example.com', 'Title20', 125, 83000.00);
Step 2: Create the Mule App
For testing purposes we will only create a flow for our app, no need to create all CRUD oparation. For that, we´ll create a flow that retrieves the JobTitle from the Employees table based on a given JobID. The JobID will be provided as an URI parameter.- Open your Mule project in Anypoint Studio.
- Drag and drop an HTTP Listener component to your canvas to expose an endpoint with the following details:
- Listens on port 8081 with HTTP protocol
- GET is the only allowed Method for this flow
- The path will be /employees/{jobID}, where {jobID} will be the URI parameter entered by the end user
- Add 2 Logger processors - one at the beginning and another one at the end. This is a best practice to track the start and end of our flow.
- Add a Set Variable processor. Define the variable jobID that will store the value of the {jobID} parameter
- Create a Properties file - It’s always a good practice to define a Properties file for our projects. In this case, we’ll use the properties file for the details of the connection to our database.
- From the left panel, right-click on the project name and select New > File. Select src/main/resources folder and name it properties.yaml
- Copy and paste the following properties to that file
db:
config: "Database_Config_MYSQL"
host: "localhost"
port: "3306"
user: [YOUR MULE USER FOR MYSQL]
password: [YOUR PASSWORD]
database: "mule-db"
- Go to the Global Elements tab of the config file and create a new element - Configuration Properties. Set the File to our Properties file
- Back to the Message Flow tab, add a Database Select operation to the canvas from the Database module to query the Employees table.
- From the Mule palette, click on Add Modules and add the Database connector.
- Next, drag and drop the Select operation to the canvas, right after the Set Variable processor.
- Then, Create a New Connector configuration:
- For the Name of the configuration, make sure you use the same name we put in the properties fle (Database_Config_MYSQL)
- Select MySQL Connection in the Connection dropdown
- We’ll need the MySQL JDBC Driver. Click on the Configure button and select Add recommended Libraries. That will add the following dependency to our POM file (adjust it with the latest version if possible)
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
- In the POM file, make sure that the Shared Library has been added to the Mule Maven Plugin:
<project>
...
<build>
...
<plugins>
<plugin>
<groupId>org.mule.tools.maven</groupId>
<artifactId>mule-maven-plugin</artifactId>
<version>${mule.maven.plugin.version}</version>
<extensions>true</extensions>
<configuration>
<sharedLibraries>
<sharedLibrary>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</sharedLibrary>
</sharedLibraries>
</configuration>
</plugin>
</plugins>
...
</build>
...
</project>
- Provide the details of the connection - Host, Port, User, Password and Database. We’ll reference the values we defined in our Properties file
- Test the connection to make sure it works
- Configure the database operation with the following query:
SELECT * FROM Employees WHERE JobID = :jobID;
{
jobId: payload.jobId
}
- Add a Transform Message component after the Database operation to format the response and select the JobTitle from the payload response:
%dw 2.0
output application/json
---
payload[0].JobTitle
- Save and run the project to verify the flow works as expected.
Step 3: Create the MUnit Test
We will create a simple Unit Test to verify the happy path of our flow. For that, we’ll create a test in which, providing a JobID value the response of the flow must match the JobTitle corresponding to that ID.From the canvas, right click on the flow and select MUnit > Create blank test for this flow. As a best practice, provide a naming convention for our tests and rename the test case to [flowName-TestType-scenario]. In our case, we’ll name it getEmployeeJobTitle-Positive-HappyPath. Notice that, when creating the test case from the flow, Anypoint Studio will create a blank test and add the Flow reference processor in the Exectuion of our test, pointing to the flow. Now, let’s add the rest of our components in our test case.
Mock Set Variable
Drag and drop a Mock When processor to the Behavior section. We’ll mock the Set Variable to provide a value for the variable that we can control. Click on Pick processor and select the Set Variable processor. Click OK
Scroll down to the Then return section and click on the Variables tab. Add a key/value for jobID that we know the response for. In our example, we’ll use the jobID == 10
Assert Response
Drag and drop an Assert that processor to the Validation section of our test case. We’ll use it to validate the value of jobID=10 is DevOps Engineer in our sample database. For that, set up the processor with the following configuration
Our Test case should look like this:
Then, copy and paste the following content (which are the same sample data of our database in csv format).
Make sure the versions of both dependencies are supported and compatible with each other. Check out the latest versions of both dependencies in the Maven repository:
We also need to add the h2 database dependency as a shared library in the mule maven plugin:
Step 4: Create a Properties file for the Unit tests
- We will now create another properties file. This time, this property file will be within the test resources folder and we’ll set it to only used when running tests. Same as we did before for the external database, in this property file we’ll include the details to connect to the h2 embedded database.
- From the left panel, right-click on the project name and select New > File. Select src/main/resources folder and name it properties.yaml.
- This time, select src/test/resources folder and name it properties.yaml
- Copy and paste the following content to the file:
db:
config: "TEST_Database_Config_MYSQL"
url: "jdbc:h2:tcp://localhost/mem:mule-db"
driver: "org.h2.Driver"
database: "mule-db"
- Go to the Global Elements tab of the MUnit file and create a new element - Configuration Properties. Set the File to our Properties file. Make sure you pick up the properties file in the test folder, not the main one.
Step 5: Create CSV file to be used for mocking the Database data.
From the left panel, right click on the src/test/resources folder and select New > File. In here, the name of the file must be exactly the same as the table name of the database we want to mock.Then, copy and paste the following content (which are the same sample data of our database in csv format).
FirstName,LastName,Email,JobTitle,JobID,Salary
John,Doe,john.doe@example.com,Software Engineer,101,75000
Jane,Smith,jane.smith@example.com,Data Scientist,102,82000
Mike,Brown,mike.brown@example.com,System Analyst,103,71000
Emily,Johnson,emily.johnson@example.com,UX Designer,104,68000
Robert,Wilson,robert.wilson@example.com,DevOps Engineer,105,90000
Sophia,Davis,sophia.davis@example.com,Product Manager,106,98000
David,Miller,david.miller@example.com,Software Architect,107,120000
Olivia,Moore,olivia.moore@example.com,QA Engineer,108,64000
James,Taylor,james.taylor@example.com,Business Analyst,109,70000
Isabella,Anderson,isabella.anderson@example.com,Scrum Master,110,87000
Liam,Thomas,liam.thomas@example.com,Frontend Developer,111,72000
Charlotte,Jackson,charlotte.jackson@example.com,Backend Developer,112,81000
Lucas,White,lucas.white@example.com,Cloud Engineer,113,93000
Mia,Harris,mia.harris@example.com,Technical Writer,114,60000
Ethan,Martin,ethan.martin@example.com,Support Engineer,115,58000
Amelia,Thompson,amelia.thompson@example.com,Data Engineer,116,89000
Alexander,Garcia,alexander.garcia@example.com,AI Engineer,117,110000
Ava,Martinez,ava.martinez@example.com,Network Engineer,118,76000
Henry,Robinson,henry.robinson@example.com,Cybersecurity Specialist,119,94000
Sophia,Clark,sophia.clark@example.com,Mobile Developer,120,77000
Benjamin,Lewis,benjamin.lewis@example.com,Game Developer,121,88000
Harper,Walker,harper.walker@example.com,IT Consultant,122,84000
Jackson,Hall,jackson.hall@example.com,Full Stack Developer,123,96000
Evelyn,Allen,evelyn.allen@example.com,Database Administrator,124,92000
Sebastian,Young,sebastian.young@example.com,Machine Learning Engineer,125,105000
Step 6: Add and Config the MUnit DB Server and the h2 embedded database
- Go to POM file of the project and add the following dependencies
<project>
...
<dependencies>
...
<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<!-- h2 embedded database dependency -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mulesoft.munit.utils/munit-dbserver-module -->
<!-- MUnit DB server Dependency -->
<dependency>
<groupId>com.mulesoft.munit.utils</groupId>
<artifactId>munit-dbserver-module</artifactId>
<version>3.0.0</version>
<classifier>mule-plugin</classifier>
<scope>test</scope>
</dependency>
...
</dependencies>
...
</project>
We also need to add the h2 database dependency as a shared library in the mule maven plugin:
<project>
...
<build>
...
<plugins>
...
<plugin>
<groupId>org.mule.tools.maven</groupId>
<artifactId>mule-maven-plugin</artifactId>
<version>${mule.maven.plugin.version}</version>
<extensions>true</extensions>
<configuration>
<sharedLibraries>
<sharedLibrary>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</sharedLibrary>
<sharedLibrary>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</sharedLibrary>
</sharedLibraries>
</configuration>
</plugin>
...
</plugins>
...
</build>
...
</project>
- Csv: The name of the csv file we created to be used as our mocking data.
- Database: The name of the database (our real database in MySQL). We’ll pick it up from the properties file.
- Connection String Parameters: MODE=MySQL
- Get back to the Global Elements tab in the MUnit file. Now, click on Create and create a new Database Config. We will provide the connection details that we defined in our test properties file.
- Name - The name we used in our properties file (for testing)
- Connection - Choose Generic Connection
- URL and Driver class name - from the test properties file
- Leave blank the user and password fields
Step 7: Modify the DB Connector Configuration
Get back to the XML config file of our main flow. From the flow, right click on the Select processor and Go to XML. That will take you to the XML view of the doc and to the corresponding line in the code of the DB connector. From there, we will modify theconfig-ref attribute and replace it with the placeholder ${db.config}
This is how we can switch to the h2 embedded database for testing:
- When running the app normally, the properties file used will be the one with the connector configuration pointing to the real instance in MySQL
- When running the MUnits, the properties file used will be a different one, with the connector configuration pointing to our h2 embedded DB.
Step 8: Run the MUnit test
Run the MUnit test and validate that the mocked database response matches the expected behavior of our Mule application.. To make sure our tests are executed with the h2 embedded database and not with the our external DB, try to shut down the MySQL server.
If everything went well, we should see in the console the result of our test case as success and the MUnit bar in green.