Back to Blog Archive

Mule, Derby and Flyway: Embedded Database for PoC and Tests

Posted on: November 19, 2018
Author:
Davide Martorana

This blog post focuses on how to integrate derby and flyway with Mule to create self-contained PoCs and more effective tests for a CICD pipeline with embedded Database.

Index

  1. Introduction
  2. A Word About Apache Derby for Beginners
  3. Derby & Mule
    1. Dependencies
    2. Properties
    3. Context Configuration
  4. Creating the Database
    1. Dependencies
    2. Properties
    3. Context Configuration
    4. SQL Scripts
  5. Using the Database Connector in the Flows
  6. Testing with Embedded DataBase
  7. Conclusion

Introduction

Let’s say you want to create a PoC project to be shared with third parties. You would want this to be a self-contained application, like a bundle (for instance a .zip or .jar file), which is easy to share (e.g. as email attachment) and easy to run on any machine with no extra effort or requirements.

Such a project might require the use of a database to store and retrieve data. This requirement presents a number of challenges that need to be addressed, and preparatory tasks need to be carried out before running the PoC, for instance:

  1. Installing the RDBMS
  2. Creating the Database
  3. Populating the DB with Data
  4. Providing the application with the database credentials so that it can get connected when it is deployed.

Having to perform all of these tasks runs counter to the desired self-contained application.

Luckily, the Mule Database Connector provides out of the box support for Apache Derby, which helps us to create a self-contained application by embedding the Database in the application.

Before proceeding with this blog, please open the referred project students-exams-flyway-derby-mule for an easier reading.

1. A Word About Apache Derby for Beginners

Apache Derby (or Derby for short) is a RDBMS written in Java that can be used by any application able to use the JDBC API (which is the Java interface that makes it possible to access relational databases from Java programs).

Derby users need to write SQL (Structured Query Language) commands in order to interact with the Database.
According to the dev guide:

Derby supports entry-level SQL as well as some higher-level features. Entry-level SQL is a subset of the full SQL specified by ANSI and ISO that is supported by nearly all major DBMSs today.
[Developer’s Guide].

The majority of the supported features are SQL-99 and SQL-2003 compliant, please refer here for further details.

Apart from being used by a Java application externally, Derby can also be embedded into any Java application. This means that when the application starts, Derby is booted on the same JVM, and follows the same life-cycle as the application. Note that a new database instance is deployed each time you deploy the application. Therefore, concurrent deployment of the same application results in multiple databases, one for each deployment (see the Tests paragraph).

Another useful feature of Derby that we can use is the In-memory database facility, this allows an in-memory database to reside entirely in the main memory and not in the file system. When the application ends, irrespective of whether it crashes or terminates gracefully, the in-memory database is automatically deleted.

2. Derby & Mule

As explained above, the Mule Database Connector supports Derby out of the box. Thus the embedded database can be treated like any other normal Database. However, prior to use, 3 steps need to be carried out:

Note: You can see these steps applied on this project on GitHub.

2.1 Dependencies

Add two new dependencies for Derby and Spring JDBC to be part of the final zip file:

2.2 Properties

It is good practice to have properties rather than values hard-coded:

Please note that the url set the property create=true. This is important because the database is in memory, so each time the application is shutdown the database is wiped out. Thus a new database needs to be created each time the app is deployed.

2.3 Context Configuration

Now from the global-config.xml file, you can refer to the properties file named application.properties

3. Creating the Database: Flyway

The RDMBS is in place, but no database has been created and/or filled with data yet. There are several ways to achieve this, many of which include some java code that creates the database first and inserts the data later. I do not find this be a good solution because you end up mixing business and configuration code.

In my opinion a better solution is to use one of those tools known as database migration tools. In the Java world the most popular are Flyway and LiquiBase. The latter has more functionality and is engine-independent, meaning that the same scripts can be used over several RDBMS, Derby included. On the down side, you need to learn a new language to describe the database structure.
Therefore, in order to keep this example simple, I am using Flyway.

3.1 Dependencies

Let’s start by adding dependencies to the Pom.xml file:

3.2 Context Configuration

The final step needed to get flyway running at start-up is to add the following configuration into the global-config.xml configuration file:

Please note that the init method must be included and set to migrate, and the dataSource property is set to the bean defined earlier (see Derby section).

Thanks to the dependency and to this bean in the configuration, Flyway is executed when the application starts. Without further configuration, it scans the resources inside the zip file, under the sub-folder; db/migration looking for SQL scripts, and executes them in order.

3.3 SQL Scripts

Please refer to the files of the project in GitHub students-exams-flyway-derby-mule, under the directory src/main/resources/db/migration to see the SQL scripts created for the example project.

Note also the file names. All of them start with V followed by the version number, then __ (double underscore) and finally a description. They follow the default naming convention, see here.
When the application starts, and the connection is ready to use, Flyway executes the scripts following the version number contained in the file names.

4. Using the Database Connector in the Flows

After adding the above configurations and dependencies, you can use the databaseGlobalConfiguration database configuration in the normal way like any other database configuration by referring to it from the Database Connector, as in the following snippet (config-ref):

The provided example project has several flows. For example,  professors.xml contains many parameterised queries to the database using normal SQL commands which work on any other RDBMS. The following is a more complex snippet extracted from the get-professor-exams-flow flow:

Please note that the Mule application has access to all the databases that the embedded RDBMS Derby loads into memory when the application starts.

5. Testing with Embedded DataBase

In a similar way, you can use an in-memory Database feature to run integration tests. This is because the CICD approach requires automated tests to be run before and after code merge happens (using  Jenkins or similar products). Jobs running the building process need to be independent from each other, and as environment independent as possible. This means that the connection to a database cannot be guaranteed, and if a central database is provided, the database could change in an unexpected way over time, making tests unreliable. Therefore, with anin-memory database we achieve independence from the environment.

Please refer to the same project above, in the branch develop-test, from this link.

In this branch the dependencies for Derby and Flyway have the scope set to test, as you can see in the pom.xml file. Also the migration files have been moved under src/test/resources, so that they can be used during the test phase, but not distributed.
Note also that the test suite defined in the file students-test-suite.xml is importing the configuration file test-global-config.xml under src/test/munit/configs.

These changes guarantee a separation between the code that is distributed (under src/main/) and the code that is executed in the test environment.

Also remember that Derby uses the same JVM of the running application. This guarantees complete isolation between Jenkins Jobs, which is a key-requirement in our scenario. Therefore, several jobs can run in parallel without conflicting with each other and failing because of another job using the same database.

6. Conclusion

In this blog post we have made two new friends; Derby and Flyway, and if we are in need they are friends indeed! They are useful tools that any developer should keep in his toolkit. They can be very useful in both PoC and Integration Test scenarios, as described in this blog.

There are many other scenarios where these tools may be useful. In particular, scenarios where a project depends on Databases, but the version or the RDBMS is not crucial, and the database can be or must be embedded in the application.

Author:
Davide Martorana

Comments

Contact Us

Ricston Ltd.
Triq G.F. Agius De Soldanis,
Birkirkara, BKR 4850,
Malta
MT: +356 2133 4457
UK: +44 (0)2071935107

Send our experts a message

Need Help?
Ask our Experts!