<!-- dbserver Dependency -->
<dependency>
<groupId>com.mulesoft.munit.utils</groupId>
<artifactId>munit-dbserver-module</artifactId>
<version>1.1.0</version>
</dependency>
MUnit Database Server
Overview
One of the main problems of testing production code are external system connections. If we create a test of a piece of code that connects with a database server, we need to install a DB server in our local environment in order to run the tests. Another option is to have an external DB server for testing only, but the major problem with this approach is that our Maven project would not be portable — we could not send it to a third party because they would not be able to compile it without installing the DB server first.
To enable you to avoid this issue, MUnit allows you to implement a database server in your local environment.
Install MUnit DB Server Module
For this example, you need to install the DB server Module provided by MUnit.
From Anypoint Studio, go to Help and Install New Software….
In the Work with: panel look for MUnit Update Site, and from the MUnit Tools for Mule section, select DB Server Module (Mule 3.4.0+)
A few considerations if you are using Maven: You need to add the MUnit DB server artifact to your Also this code example uses Spring for the Database configuration. When running from Maven, you should add Spring’s dependencies to your
|
Creating a Test Using MUnit DB Server
For the purpose of this documentation we are going to assume we are testing the following Mule code:
Studio Visual Editor
XML or Standalone Editor
<!-- Set Up a default Java Data Base Connection -->
<spring:beans>
<spring:bean id="jdbcDataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
<spring:property name="driverClass" value="${db.driver}"/>
<spring:property name="url" value="${db.url}"/>
<spring:property name="username" value="${db.user}"/>
<spring:property name="password" value="${db.password}"/>
</spring:bean>
</spring:beans>
<!-- Configure your DB connector referencing your JDBC specs -->
<db:generic-config name="Generic_Database_Configuration" doc:name="Generic Database Configuration" dataSource-ref="jdbcDataSource"/>
<flow name="testDbFlow">
<!-- Perform a query based on a value passed by a flow variable -->
<db:select config-ref="Generic_Database_Configuration" doc:name="Database" >
<db:parameterized-query><![CDATA[SELECT * FROM jobtitlelookup WHERE jobtitleid = #[variable:jobid]]]></db:parameterized-query>
</db:select>
<!-- Set two conditions depending on the query result -->
<choice doc:name="Choice">
<!-- If there is one or more values resulting from the query set those values as the payload -->
<when expression="#[payload.size()>0]">
<set-payload value="#[payload[0].get('jobtitle')]" doc:name="Set Payload if True"></set-payload>
</when>
<!-- If the query throws no results, inform it in the payload -->
<otherwise>
<set-payload value="No job title for #[flowVars.jobid] was found" doc:name="Set Payload if False"></set-payload>
</otherwise>
</choice>
<!-- Finally, pass the payload content as a Flow variable -->
<set-variable value="#[payload]" variableName="job" doc:name="Variable"></set-variable>
</flow>
We need to create a test that:
-
Contains a Database Server with a
jobtitlelookup
table. -
Provides a valid Database Structure for the Database connector to perform its' query.
-
Passes a value for the
jobid
variable that the Database connector uses
Defining The MUnit DB Server
Define the DataBase server using the DB Server
connector from the MUnit Integration Test Tools
in your Mule Pallet.
The DB Server can take the following parameters:
Attribute Name | Description |
---|---|
|
Defines the configuration name of this DB server. Must be unique. |
|
Defines the name of the in-memory DB. |
|
Specifies the path of the SQL file that defines the DB structure/contents. This parameter is used to create the DB at startup. Find out more about this in the Defining the DB Structure from an SQL File section. |
|
Specifies the path of the-comma separated file used to define the DB structure/contents. This parameter is used to create the DB at startup. Find out more about this in the Defining the DB Structure from a CSV File section. |
|
Defines the connection string for your database. You can concatenate the parameters with a semicolon. |
For this example we define the DB structure and content using a .csv file.
Create a file named jobtitlelookup.csv
containing the following values:
JOBTITLE,EECSALARYORHOURLY,JOBTITLEID
Developer,10,DEV
In order for this csv file to be visible within your project, make sure you locate it in the src/test/resources
directory from your application.
Proceed to define your DB Server configuration in a new MUnit Suite.
First, you need to set both mock connectors
and mock inbounds
options to false:
Studio Visual Editor
Navigate to your Global Elements tab, and select your MUnit Configuration element. Click Edit and uncheck both Mock Mule transport connectors and Mock Inbound endpoints.
XML or Standalone Editor
<munit:config mock-inbounds="false" mock-connectors="false"/>
Defines MUnit configuration. Notice that mock-connectors
and mock-inbounds
properties are set to false
.
Next, define your DB configuration:
Studio Visual Editor
Navigate to your Global Elements tab, and select your DB Server element. Click Edit and complete the fields:
Name: DB_Server
csv.: jobtitlelookup.csv
Database: DATABASE_NAME
Connection String Parameters:MODE=MySQL
XML or Standalone Editor
<dbserver:config name="DB_Server" database="DATABASE_NAME" doc:name="DBServerConfig" connectionStringParameters="MODE=MySQL" csv="jobtitlelookup.csv" />
Defines the DB server configuration.
DB Server Connection Parameters
The MUnit DB server has the following default set of connection parameters:
|
The values of the db.user
and db.password
parameters are intentionally blank.
Defining the DB Structure
There are two different ways to define the structure and content of your database:
-
SQL
-
CSV
The MUnit DB server is based on H2. Complex structures, index and join commands may not work properly. |
Defining the DB Structure from an SQL File
To define you DB structure and content from an SQL file, provide a valid set of ANSI SQL DDL (Data Definition Language) instructions.
Studio Visual Editor
XML or Standalone Editor
<dbserver:config name="DB_Server" database="DATABASE_NAME" sqlFile="FILE_NAME.sql" connectionStringParameters="MODE=MySQL" />
Defining the DB Structure from a CSV File
You can create your DB from CSV files.
-
The name of the table is the name of the file (in the example below,
customers
). -
The name of the columns are the headers of your CSV file.
Studio Visual Editor
XML or Standalone Editor
<dbserver:config name="DB_Server" database="DATABASE_NAME" csv="FILE_NAME.csv connectionStringParameters="MODE=MySQL""/>
You can also split your DB structure among several CSV files. In this case, include the file names as a list separated by a semicolon, as shown below.
Studio Visual Editor
XML or Standalone Editor
<dbserver:config name="DB_Server" csv="FILE_NAME.csv;FILE_NAME_1.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" doc:name="DB Server"/>
This creates two tables FILE_NAME
and FILE_NAME_1
Starting The MUnit DB Server
In order for it to run, the database server must be started in a before-suite. You start the server using the start-db-server
message processor.
Studio Visual Editor
XML or Standalone Editor
<munit:before-suite name="beforesuite" description="Starting DB server">
<dbserver:start-db-server config-ref="DB_Server" doc:name="startServer"/>
</munit:before-suite>
If you are running from Maven and are getting a
If you are getting this exception and you are not using Maven, you need to add the
This adds h2 libraries to your build path allowing your project to use the org.h2.Driver class |
Running The Test
Once our DB server is up and running we can run our test.
Studio Visual Editor
XML or Standalone Editor
<munit:test name="test-suite-testDbFlowTest" description="Asserts the payload after running the project">
<!-- Passes a variable to value to run in the main flow. -->
<set-variable variableName="jobid" value="DEV" doc:name="Variable"/>
<!-- Run the production code. -->
<flow-ref name="testDbFlow" doc:name="testDbFlow"/>
<!-- Asserts the payload returned by the production code. This value comes from our in-memory database. -->
<munit:assert-on-equals expectedValue="Developer" actualValue="#[flowVars['job']]" doc:name="Assert Equals"/>
</munit:test>
We are not using any new message processor, since the database has already been initialized and loaded with the proper data. We are just validating that the query ran in our production code is correct, and that the payload returned is the expected one.
Stopping The MUnit DB Server
To stop the DB server gracefully, it needs to be stopped in an After Suite, using a db-server
message processor with the Stop db server
operation.
Studio Visual Editor
XML or Standalone Editor
<!-- Stop the server -->
<munit:after-suite name="munit3Flow-test-suiteAfter_Suite" description="After suite actions">
<dbserver:stop-db-server config-ref="DB_Server" doc:name="stopServer"/>
</munit:after-suite>
Other MUnit DB server Message Processors
The MUnit DB server also offers a few other features, outlined in this section.
Validating SQL Query Results
The MUnit DB Server allows you to validate that the results of a query are as expected.
To do this, you use the validate-that
operation. Set the results
property to CSV with rows separated by a newline character (\n
), as shown below.
Studio Visual Editor
XML or Standalone Editor
<munit:test name="test-suite-testDbFlowAssertQuery" description="Check if a specific query returns the expected value">
<dbserver:validate-that config-ref="DB_Server"
query="SELECT * FROM jobtitlelookup WHERE JOBTITLE='Developer';" returns=""JOBTITLE","EECSALARYORHOURLY","JOBTITLEID"\n"Developer","10","DEV"" doc:name="validateQuery"/>
</munit:test>
The result should be a CSV text. In order to be able to see it as the payload, MUnit DBserver uses OpenCSV libraries
If you are running from Maven and are getting a
If you are getting this exception and you are not using Maven, you need to add the
This adds open csv libraries to your build path allowing your project to properly render the csv into your payload. |
Executing SQL instructions and SQL Queries
The MUnit DB Server allows you to execute instructions on the in-memory databases, so you can add or remove registries before a test, and also check if your data was stored correctly.
Use a DB Server connector with the execute
operation to insert the values new values to the table (Culinary Team Member,10,HIR
), then use another DB Server connector with an execute query
operation to retrieve all values from the table (including the newly added ones) and check the payload using a logger component to store the payload.
Studio Visual Editor
XML or Standalone Editor
<munit:test name="test-suite-testDbFlowExecuteQuery" description="Execute a query from the DB connector">
<!-- Execute a SQL instruction using the execute operation -->
<dbserver:execute config-ref="DB_Server" doc:name="Execute" sql="INSERT INTO jobtitlelookup VALUES ('Culinary Team Member','10','HIR');"/>
<!-- Check the update using execute-query operation -->
<dbserver:execute-query config-ref="DB_Server" sql="SELECT * FROM jobtitlelookup" doc:name="Check by Executing a Query"/>
<!-- log the resulting payload -->
<logger message="#[payload]" level="INFO" doc:name="Logger"/>
</munit:test>
The logger in your console should read:
[main] org.mule.api.processor.LoggerMessageProcessor: [{JOBTITLEID=DEV, EECSALARYORHOURLY=10, JOBTITLE=Developer}, {JOBTITLEID=HIR, EECSALARYORHOURLY=10, JOBTITLE=Culinary Team Member}]
Execution Environments
You may have noticed that our production code example makes extensive use of placeholders for certain parameters, such as driverName
, url
etc. in the example below:
<spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="${db.driver}" />
<spring:property name="url" value="${db.url}" />
<spring:property name="user" value="${db.user}" />
<spring:property name="password" value="${db.password}" />
</spring:bean>
The reason for this is that properties allow us to create code that is more configurable. Compare the example above with:
<spring:bean id="jdbcDataSource" class="org.enhydra.jdbc.standard.StandardDataSource" destroy-method="shutdown">
<spring:property name="driverName" value="org.mule.fake.Driver" />
<spring:property name="url" value="192.168.0.3" />
<spring:property name="user" value="myUser" />
<spring:property name="password" value="123456" />
</spring:bean>
The second example code is untestable, even without MUnit. If we need to test this code before going to production, we always hit the production DB server with our real credentials, which entails risk.
On the other hand, the first example code allows us to define two different property files:
-
One for the testing environment
-
One for the production environment
This is use in combination with the Mule property placeholder, shown below with $site
:
<global-property value="mule.${env}.property"/>
In the example above, the use of $site
allows us to leverage execution environments. So for example we can define two separate properties files, mule.test.properties
and mule.prod.properties
, containing the same properties with values according to the environment we wish to use.
To run your test from Maven and issue the env parameter from the command line, you can run: mvn -DargLine="-Dmule.env=test" clean test .
|