Week 9 - DBSetup

In the following blog post I want to introduce you to DBSetup. It is an alternative to DBUnit that is OpenMRS’ standard database tool for unit tests.


For those of you who don’t know DBUnit I will give you a short example: DBUnit uses a flat xml file, where all data is defined

<?xml version='1.0' encoding='UTF-8'?>
    <location_tag location_tag_id="1" name="Operation Theater" description="" creator="1" date_created="2005-01-01 00:00:00.0" retired="false" uuid="af3e9ed5-2de2-4a10-9956-9cb2ad5f84f2"/>

    <location location_id="1" name="OT 1" creator="1" date_created="2008-08-15 13:46:50.0" retired="false" uuid="9356400c-a5a2-4532-8f2b-2361b3446eb1"/>
    <location location_id="2" name="OT 2" creator="1" date_created="2008-08-15 13:46:50.0" retired="false" uuid="9356400c-a5a2-4532-8f2b-2361b3446eb2"/>

    <location_tag_map location_tag_id="1" location_id="1"/>
    <location_tag_map location_tag_id="1" location_id="2"/>

This file creates two new locations and assigns the location tag “Operation Theater” to them. Perhaps you have mentioned fields like “creator”, “date_created”, “uuid” and “retired” that have to be defined. We come back to that later.

These are the disadvantages of DBUnit


Due to this shortcomings I was curious if there is an alternative approach out there. I launched my search engine and stackoverflow and found other people with the same desire. Shortly afterwards, I came across DBSetup from ninja-squad. It looked very promising and I decided to give it a shot.

The main difference to DBUnit is that there is no xml file. Data that should be inserted is defined with Java code

Here is a short example from their website:

      .withDefaultValue("VERSION", 0)
      .columns("ID", "FIRST_NAME", "LAST_NAME", "BIRTH_DATE")
      .values(1, "John", "Doe", "1975-05-06")
      .values(2, "Mark", "Smith", "1980-07-03")
      .values(3, "Claire", "Connell", "1981-09-17")

You see that you don’t have to repeat the column name for each row that you want to insert - that’s nice, but not the killer feature right? But if we look more closely at this example we notice line two:

withDefaultValue("VERSION", 0)

This means we can omit the version field from our inserts. Its older brother

withGeneratedValue(fieldName, valueGenerator)

is even more powerful. With his help we can define sequences of integers, strings and dates out of the box. This is the feature that I love most in DBSetup! In the example above we could even remove the “ID” field by declaring

withGeneratedValue("ID", ValueGenerators.sequence())

This would lead to the same result as the default sequence starts at 1 and is incremented by 1.

"creator", "date_created", ...

Now it’s time to come back to the fields (“creator”, “date_created”, “uuid” and “retired”). During testing, usually I just don’t care about this values. With the help of value generators and a utility function, inserting two locations has become a lot easier:

DbUtil.insertInto(Config.LOCATION).columns("name").values("OT 1").values("OT 2").build()

All other values including the primary key and uuid (“Location1”, “Location2”) are inserted automatically.

The magic happens inside the utility class DbUtil and its inner class Config. You can find the internals in this commit

OpenMRS integration

Finally I want to give you an example of how it looks like inside an actual OpenMRS test class

public class Test extends BaseModuleContextSensitiveTest {

	private static DbSetupTracker dbSetupTracker = new DbSetupTracker();

	public void setUp() throws Exception {
		Operation operation = sequenceOf(
						.columns("name", "intervention_duration", "ot_preparation_duration", "inpatient_stay")
						.values("Appendectomy", 35, 25, 4)
		DbSetup dbSetup = DbUtil.buildDBSetup(operation, getConnection(), useInMemoryDatabase());


In this blog post I have presented an alternative libary to DbUnit and how it can be integrated into OpenMRS. Value generators are a great way to automatically populate row columns. Data is defined with java code rather than as xml file which is more powerful. If you add a new field to a table you don’t have to rewrite all your tests. The only thing you have to do is to add another default value generator to the config inside DbUtil class.

Your feedback is highly appreciated!