How to use the MUnit DB Server - Part I

byGonzalo Marcos -

Testing database interactions is an essential aspect of MuleSoft application development. With MUnit, MuleSoft's testing framework, developers can validate the functionality of their 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 you 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 called mule-db table Employees and populate it with 25 sample records. Follow these steps:

  1. Open your MySQL client.
  2. 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 oparations. 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"


Where user and password are the credentials that our Mule App will use to connect to our database. (Don’t use the root user for this). As a best practice, we should use secure properties for the password but we’ll keep them in plain for now, so that we don’t make this post too long. The config value will be the name of the connection in the connector. That will be important later.

  • 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;


Where jobID will pick up the value from the jobID variable we defined in the previous step. Use a DataWeave expression to pass the JobID as a parameter to the query.


{
    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.

You may like these posts

Previous Post Next Post