Setting your integration test data with Maven SQL plugin

There are mainly two requirements when writing integration tests which interact with the database:

1) Reference data, i.e. data which are mostly static should be available before the test run. This is required to resolve usually the numerous Foreign Keys (FK) of a relational database. Examples of reference data include countries, currencies, client names, etc.

2) Test data. This data should not be committed to the database to achieve what Cedric Beust and Hani Suleiman define in their Testing and TestNG book as idempotent tests, e.g. tests which don't affect the state of the underlying infrastructure after each run. This requirement is important in order to guarantee that each test finds the database clean and in the same state. For tests running in parallel (yes, TestNG can do that) also each test sees the database as clean as if it was the only client of that database.

There are various ways to fulfill the requirements above. As for point 1, I personally use Spring transactional test support for TestNG. Provided that one configures a Transaction Manager with Spring, the test support classes start a new transaction before each test rolling it back when the test completes.

This article concentrates mainly on point two. For cases when I need to setup my database with reference data, I choose to use Maven SQL Plugin. I use this plugin as follows:

1) I bind a plugin execution which creates reference data to the process-test-resources phase. This execution executes one or more SQL scripts.

2) I bind another plugin execution to the post-integration-test phase to remove data once the integration tests have completed.

Following the two steps above, I guarantee that each "integration test session" will run on a fresh and clean database, filled with just reference data. This is my configuration:

<build>
        <plugins>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>sql-maven-plugin</artifactId>

                <dependencies>
                    <!-- specify the dependent jdbc driver here -->
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>${mysql.connector.java.version}</version>
                    </dependency>
                </dependencies>

                <configuration>
                    <driver>org.gjt.mm.mysql.Driver</driver>
                    <url>jdbc:mysql://${db.mysql.server.name}:${db.mysql.server.port}/imatdev</url>
                    <!-- The value of settingsKey should be a server id in ~/.m2/settings.xml -->
                    <settingsKey>mysql-db-dev</settingsKey>
                    <!--all executions are ignored if -Dmaven.test.skip=true-->
                    <skip>${maven.test.skip}</skip>
                </configuration>

                <executions>

                    <!--  It creates integration test data before running the tests -->
                    <execution>
                        <id>create-integration-test-data</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <orderFile>ascending</orderFile>
                            <fileset>
                                <basedir>${basedir}/src/test/resources/sql</basedir>
                                <includes>
                                    <include>create-int-test-data.sql</include>
                                </includes>
                            </fileset>
                        </configuration>
                    </execution>

                    <!--  It drops data after executing tests -->
                    <execution>
                        <id>drop-db-after-test</id>
                        <phase>post-integration-test</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <orderFile>ascending</orderFile>
                            <fileset>
                                <basedir>${basedir}/src/test/resources/sql</basedir>
                                <includes>
                                    <include>drop-int-test-data.sql</include>
                                </includes>
                            </fileset>
                        </configuration>
                    </execution>
                   
                </executions>
               
            </plugin>

        </plugins>
    </build>

There are few things to note about the above configuration:

1) I use a parent project (not shown here) to define all versions when possible through the <dependencyManagement> element.

2) The JDBC driver version in the plugin dependency MUST be defined, even when you've used the <dependencyManagement> element in your parent POM.

3) I protected sensitive information (such as usernames and passwords) by externalising these information in the ~/.m2/settings.xml file.

4) I defined a profile named "integration-tests" to run my integration tests.

By running mvn clean install -P integration-tests from the command line snippets of the output are shown:

[INFO] [sql:execute {execution: create-integration-test-data}]
[INFO] Executing file: /home/mtedone/development/java/jemos-persistence-common/src/test/resources/sql/create-int-test-data.sql
[INFO] 11 of 11 SQL statements executed successfully
[INFO] [compiler:testCompile {execution: default-testCompile}]
[WARNING] File encoding has not been set, using platform encoding UTF-8, i.e. build is platform dependent!
[INFO] Compiling 3 source files to /home/mtedone/development/java/jemos-persistence-common/target/test-classes
[INFO] [surefire:test {execution: default-test}]
[INFO] Surefire report directory: /home/mtedone/development/java/jemos-persistence-common/target/surefire-reports

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
[ ... Tests output omitted ]

[INFO] [jar:jar {execution: default-jar}]
[INFO] Building jar: /home/mtedone/development/java/jemos-persistence-common/target/jemos-persistence-common-1.0.0-SNAPSHOT.jar
[INFO] [sql:execute {execution: drop-db-after-test}]
[INFO] Executing file: /home/mtedone/development/java/jemos-persistence-common/src/test/resources/sql/drop-int-test-data.sql
[INFO] 2 of 2 SQL statements executed successfully
[INFO] [install:install {execution: default-install}]
[INFO] Installing /home/mtedone/development/java/jemos-persistence-common/target/jemos-persistence-common-1.0.0-SNAPSHOT.jar to /home/mtedone/data/mvn-repo/uk/co/jemos/persistence/jemos-persistence-common/1.0.0-SNAPSHOT/jemos-persistence-common-1.0.0-SNAPSHOT.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] ------------------------------------------------------------------------

In looking at the above snippets, please note "[INFO] [surefire:test {execution: default-test}]" and "[INFO] [sql:execute {execution: drop-db-after-test}]" before and after tests are executed.

Hope this helps.

Happy technology to everyone.

M.

Comments

[Jm2dev]

Very nice article.

[Roger]

Thanks for this article. I'm learning Maven (with the intent of introducing it in courses I teach), and this is very helpful for my examples that use databases.

[Marco Tedone]

Happy to help Roger, hope your classes will go well.

[Tiago Wanke Marques]

Nice article.
Im triyng to use sql maven plugin to create my database and tables in postgres.

The create database works fine, the problem is with create tables. I have set in my global configurations properties, but the create tables script its executed at postgres database.

Did u have any ideia why?
Tnks.

[Praveen Ranjan]

Hi Tiago,

I am facing same issue. Did you get any solution for this issue?