Mocking Database Endpoints in MUnit Tests

We will create a sample Mule application that accepts HTTP requests and queries the full results from a table in a database. I will use Derby in-memory DB for this demo. The next section will talk about how you could set up the database. Please click on this link to read more about Apache Derby.

Most of the online MuleSoft tutorial teaches you how to run Derby DB in embedded mode, here I will teach you to run it in server mode, which means you do not need to set up any spring bean and create any customized database initialization java codes, no none of these jiggery-pokery stuff (as any Kiwi would say, aye).

The other benefit of running a Derby in server mode is that you can keep reusing the database for any lab work you plan on doing, and I could keep reusing it to teach you new stuff.

The other reason I am writing this article is that I have posted a question to forums asking if we could mock database end points, the answers that I am getting ranges from nope, to may be, to I don’t know. The answer to the question is a resounding yes, you could absolutely mock a database endpoint response, and if you follow me through the article I will show you how this can be done.

Section 4.2 will show you how this can be done directly but I urge you to go through the whole material so that you could follow the story, as it builds up quite nicely bit by bit to section 4.2, so let the adventure begin.

Creating the Derby Demo DB

I am setting up the Derby in-memory database from a Windows environment. For more details, you can refer to this link.

1. Execute the following command in command prompt:

java -jar %DERBY_HOME%\lib\derbyrun.jar ij

2. Execute the following command

CONNECT 'jdbc:derby:DemoDb;create=true;user=me;password=mine';

Notice that after you have executed the command, you will see a DemoDb folder reflecting your execution.

3. Next, you need to execute the following SQL file, you can get the SQL file from GitHub.

You can just click on the link, copy the SQL scripts, and paste it into the command prompt and press enter.

4. In order to test if everything is successful execute select * from employees;.

If your tables are created successfully, you will see the following printed in your command prompt.

Starting the Derby Demo DB

5. Before you start the DB in the same command prompt you need to set the derby.system.home environment variable. Execute the following command.

set derby.system.home="C:\apache\db-derby-10.13.1.1-bin\DemoDb"

6. Run the following command:

setNetworkServerCP.bat

7. Starting up the DB server, open a new command prompt, execute the following command.

startNetworkServer -h 0.0.0.0 -p 1527

The following will show that server is started.

Creating the Mule Application

The Mule application that we will be creating is pretty straightforward it connects directly to the Derby database and it queries the employee table.

It is a simple application depicted by Figure 3.0a:

Figure 3.0a.

The solution files will contain both the implementation and the global.xml Mule configuration files as depicted in the following diagram.

Figure 3.0b.

I separated out the global configuration from the actual Mule flow implementation so that I could show you how you can conduct integration testing on a flow that has multiple dependencies across different Mule configuration files.

The global.xml Mule configuration files have no Mule flows in them. They're only populated with global configurations. The following illustration (Figure 3.0c) depicts only two global configuration items.

Figure 3.0d.

The generic database configuration item looks like the following, there is nothing being configured in any of the other tabs. I have only configured the generals tab.

Figure 3.0e.

At the bottom of the configuration screen there is a Test Connection button. Click on it to confirm if the database has been started. If the database has already been started, you will see the following dialog (Figure 3.0f).

Figure 3.0f.

Sending HTTP Request to the Mule Application

This is a simple rule application. Every time there is an HTTP request, it will log/insert a record to the CALL_LOG table, and it would then proceed to select all records from the employee table (from the derby database).

Let’s fire the Mule application up and I will show you what I mean.

Open up Postman and issue a GET request to the following URL http://localhost:8081/employees.

You do not need to key in any query parameter, your Postman screen will look like the following (Figure 3.1a).

Figure 3.1a.

Once you have clicked send, you will get back a JSON payload that is full of employee data.

Figure 3.1b.

On your Anypoint Studio Mule console, you will see the following being logged.

Figure 3.1c.

Notice the executionTimeStamp. Now, open another Windows command prompt and execute the following command.

java -jar %DERBY_HOME%\lib\derbyrun.jar ij

This will bring up the JI console for Derby database connection.

At the IJ console, type the following command and press enter:

connect 'jdbc:derby://localhost:1527/DemoDb';

You will now be connected to the derby database. Once in, I want you to execute Select * from call_log;.

You will see the following selected result set in Figure 3.1d.

Figure 3.1d.

Notice that the executionTimeStamp is the same as what is printed in the Anypoint console log.

Creating an MUnit Test

Now that we have a clear understanding of what the created Mule application is meant to be doing, we will then proceed to create an MUnit test for it. As depicted in Figure 4.0a, I need you to right-click on the flow and from the menu go to MUnit and select Create new munitdbmocking.xml suite.

Figure 4.0a.

Immediately following that mouse click, you will see an MUnit flow being created for you.

Figure 4.0b.

If you go to the global elements tab (in Figure 4.0c), you will see that MUnit has only imported one Mule configuration.

Figure 4.0c.

We must now create another import to also include the global.xml, in order to do this you need to click on the Create button, select Bean, and under it, select Import (Figure 4.0d).

Figure 4.0d.

Type “classpath:global.xml” in the text box (Figure 4.0e).

Figure 4.0e.

Now that we have all the dependencies sorted, we need to construct the HTTP initialization message (as if we are executing the Mule application from postman), shown in Figure 4.0f.

Figure 4.0f.

Next, I add in an Assert Payload. I have created a payload JSON file in test resources named expectedPayload.json, and referred to it in the “Assert Payload” MUnit message processor. The reason I am doing this is so that I do not obscure the MUnit configuration files with unnecessary hardcoded payload values.

Running the MUnit test

Next, we are going to run the MUnit test, but before we do that, I want you to delete all records from the CALL_LOG table.

Right-click on the blank area of the MUnit configuration window and select Run as shown in Figure 4.1a.

Figure 4.1a.

After a successful MUnit test run you will have a test window that shows green. This means all your test have executed successfully (Figure 4.1b).

Figure 4.1b.

Now if you go back to the command prompt console, try and select records from the call_log table.

You will notice that the MUnit test has executed an integration test for you and have written to the call_log  table. Now, this is inconsequential if you are doing a lab exercise; imagine if this were to happen in production, where you have accidentally executed MUnit tests against the production configuration. Imagine the horror of finding out all your production systems populated with MUnit test data.

Mocking Database Endpoints

This is why we must mock certain endpoints and this is why we are here not to mock the database: Endpoints. Let’s mock the endpoint where we are writing to the database. Drag a mock message processor to the MUnit test case (figure 4.2a) and select the highlighted message processor.

Figure 4.2a.

When we tested the Mule application earlier, we could see from the console log that an insert DB operation would create a payload of “1,” so let’s configure this into the mock, and click save. Upon completion of your mock configuration would look similar to the following depiction (figure 4.2b).

Figure 4.2b.

With the mock configure now let's rerun the MUnit test. The Anypoint console would display the same format of log messages as per our previous MUNIT test (Figure 4.2c).

Figure 4.2C.

Now if you go back to the IJ Derby console and run a select against the CALL_LOG table, you will see that there is no new records being created (Figure 4.2d). We have successfully mock database insert message processor sparing MUNIT from needing to execute the real insert DB operation in the original flow implementation.

Figure 4.2d.

Conclusion

The Mock message processor in MUnit can be used to mock any message processor; you just need to import all the dependent Mule configuration XML files. I was having issues doing this when I first started. I couldn’t find the message processor I wanted to mock, and I found out the root cause of it was because I had not imported the dependent Mule XML configuration. I am sure this mistake trips a lot of Mule developers up. If you have read this article in its entirety, you will be spared of the mistake.

The full source code, including the MUnit test case, can be obtained here.