Contact Free trial Login

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.

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:

<!-- Local Database Configuration -->
<db:config name="Database_Config">
    <db:my-sql-connection host="localhost" port="1234" />
</db:config>
<!-- Properties according to the environment -->
<configuration-properties file="db.properties" />

<flow name="selectFlow" >
    <!-- Perform a query based on a value passed by a variable -->
    <db:select config-ref="${db.config}">
        <db:sql >SELECT * FROM jobtitlelookup WHERE jobtitleid = :id</db:sql>
        <db:input-parameters ><![CDATA[#[id : vars.jobid]]]></db:input-parameters>
    </db:select>
    <!-- Set two conditions depending on the query result -->
    <choice>
        <!-- If there is one or more values resulting from the query set those values as the payload -->
        <when expression="#[sizeOf(payload) > 0]">
            <set-payload value="#[payload[0].jobtitle]"/>
        </when>
        <!-- If the query throws no results, inform it in the payload  -->
        <otherwise>
            <set-payload value="#['No job title for $(vars.jobid) was found']" />
        </otherwise>
    </choice>

    <!-- Finally, pass the payload content as a Flow variable  -->
    <set-variable value="#[payload]" variableName="job" />
</flow>

where the file db.properties in src/main/resources has the following content:

db.config=Database_Config

We need to create a test that:

  1. Contains a Database Server with a jobtitlelookup table.

  2. Provides a valid Database Structure for the Database connector to perform its' query.

  3. Passes a value for the jobid variable that the Database connector uses

Install MUnit DB Server Module

For this example, you need to install the DB server Module provided by MUnit.
From Anypoint Studio, go to the Mule Palette and Search in Exchange…​.
In the search bar look for MUnit Utils Database Server, and add the module to your project.

A few considerations after adding the module to your project:

The MUnit DB server artifact in your pom file should have the test scope:

<!-- dbserver Dependency -->
<dependency>
    <groupId>com.mulesoft.munit.utils</groupId>
    <artifactId>munit-dbserver-module</artifactId>
    <version>2.0.1</version>
    <classifier>mule-plugin</classifier>
    <scope>test</scope>
</dependency>

Also the h2 dependency used for the database is a shared library. You should add the dependency to your pom.xml file and list it as a sharedLibrary in the mule-maven-plugin.

<!--Third party libs-->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.3.166</version>
</dependency>
<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>com.h2database</groupId>
                <artifactId>h2</artifactId>
            </sharedLibrary>
        </sharedLibraries>
    </configuration>
</plugin>

Defining The MUnit DB Server

Define the Database server using the MUnit DB Server Config from the Global Elements in your canvas.
The DB Server can take the following parameters:

Attribute Name Description

name

Defines the configuration name of this DB server. Must be unique.

database

Defines the name of the in-memory DB.

sqlFile

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.

csv

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.

connectionStringParameters

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:

Navigate to your Global Elements tab, and select your MUnit DB Server Config element. Click Edit and complete the fields:

Name: MUnit_DB_Server_Config
Csv.: jobtitlelookup.csv
Database: DATABASE_NAME
Connection string parameters:MODE=MySQL

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection csv="jobtitlelookup.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

Define the DB configuration that will connect to your DB Server:

Name: Test_Database_Config
Connection: Generic connection
URL: jdbc:h2:mem:DATABASE_NAME
Driver class name:org.h2.Driver

<db:config name="Test_Database_Config">
    <db:generic-connection url="jdbc:h2:mem:DATABASE_NAME" driverClassName="org.h2.Driver" />
</db:config>

Define the db.properties file in your src/test/resources folder that will pick up your Test Database Configuration

db.config=Test_Database_Config

Running The Test

Once our DB server is configured we can run our test.

<munit:test name="selectFlowTest" description="Test selectFlow"  >
    <munit:behavior>
        <!-- Passes a variable to value to run in the main flow. -->
        <set-variable variableName="jobid" value="DEV" />
    </munit:behavior>
    <munit:execution>
        <!-- Run the production code. -->
        <flow-ref name="db-server-docsFlow"/>
    </munit:execution>
    <munit:validation>
        <munit-tools:assert-equals actual="#[vars.job]" expected="Developer" />
    </munit:validation>
</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.

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.

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection sqlFile="FILE_NAME.sql" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>
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.

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection csv="FILE_NAME.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

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.

<dbserver:config name="MUnit_DB_Server_Config" >
    <dbserver:connection csv="FILE_NAME.csv;FILE_NAME_1.csv" database="DATABASE_NAME" connectionStringParameters="MODE=MySQL" />
</dbserver:config>

This creates two tables FILE_NAME and FILE_NAME_1

MUnit DB server 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.

<dbserver:validate-that config-ref="DB_Server"
query="SELECT * FROM jobtitlelookup WHERE JOBTITLE='Developer';" returns="&quot;JOBTITLE&quot;,&quot;EECSALARYORHOURLY&quot;,&quot;JOBTITLEID&quot;\n&quot;Developer&quot;,&quot;10&quot;,&quot;DEV&quot;" />

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.

<!-- Execute a SQL instruction using the execute operation -->
<dbserver:execute config-ref="MUnit_DB_Server_Config" sql="INSERT INTO jobtitlelookup VALUES ('Culinary Team Member','10','HIR');"/>

<!-- Check the update using execute-query operation -->
<dbserver:execute-query config-ref="MUnit_DB_Server_Config" sql="SELECT * FROM jobtitlelookup"/>

<!-- log the resulting payload -->
<logger level="INFO" message="#[payload]"/>

The logger in your console should read:

org.mule.runtime.core.internal.processor.LoggerMessageProcessor: [{JOBTITLEID=DEV, EECSALARYORHOURLY=10, JOBTITLE=Developer}, {JOBTITLEID=HIR, EECSALARYORHOURLY=10, JOBTITLE=Culinary Team Member}]

We use cookies to make interactions with our websites and services easy and meaningful, to better understand how they are used and to tailor advertising. You can read more and make your cookie choices here. By continuing to use this site you are giving us your consent to do this.