Contact Free trial Login

Database Utils

Overview

To test a piece of code that connects with a database server, you must install a database server in your local environment to run the tests. You could also have a dedicated external database server for testing, but your Maven project would not be portable — a third party would need to install the testing database server to compile your project.

To make it easier for you to test database connections, MUnit allows you to implement a database server in your local environment.

Example

  1. Assume that you want to test the following Mule application:

    <!-- 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>
  2. The file db.properties in src/main/resources has the following content:

    db.config=Database_Config
  3. You 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.

Installing MUnit DB Server Module

  1. From Anypoint Studio, go to the Mule Palette and Search in Exchange…​.

  2. 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 must have the test scope:

    <!-- dbserver Dependency -->
    <dependency>
        <groupId>com.mulesoft.munit.utils</groupId>
        <artifactId>munit-dbserver-module</artifactId>
        <version>2.0.2</version>
        <classifier>mule-plugin</classifier>
        <scope>test</scope>
    </dependency>
  • The h2 dependency used for the database is a shared library.
    You must 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 takes 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. See the Defining the DB Structure from an SQL File section for more information.

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. See Defining the DB Structure from a CSV File section for more information.

connectionStringParameters

Defines the connection string for your database. You can concatenate the parameters with a semicolon.

For this example, define the DB structure and content using a CSV file.

  1. Go to the src/test/resources directory in your project.

  2. Create a file named jobtitlelookup.csv containing the following values:

    JOBTITLE,EECSALARYORHOURLY,JOBTITLEID
    Developer,10,DEV
  3. Navigate to your Global Elements tab, and select your MUnit DB Server Config element.

  4. 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>
  5. Define the DB configuration to 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:tcp://localhost/mem:DATABASE_NAME" driverClassName="org.h2.Driver" />
    </db:config>
  6. 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

After configuring the DB server you can run the 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>

This test validates that the query ran in your production code is correct, and that the payload returned by the DB server 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, you must 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

To define your DB structure based on a CSV file:

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