Jenkins H2 Database Integration Testing

Introduction

An Agile software development principle is continuous delivery and integration of working software. Jenkins software automates this process. As part of the Jenkins automation, unit testing of software makes sure coding changes introduced into working software do not break existing logic at the unit level. Unit testing of software generally "stubs out" external functionality. Its only concern is whether the software works at the module level, but what is missing in this approach is automated integration testing.

The project I collaborated on had many web services integration tests developed before the project adopted Agile principles. The main concern migrating to Agile was that the integration tests could not be leveraged in Agile. This code would have been thrown away even though it had proven valuable in previous software releases for catching bugs in web services from the business logic to the database backend.

You may also enjoy:  Unit Testing vs. Integration Testing: What's the Difference? 

Agile and Jenkins software testing in our enterprise environment is set up for unit testing. For integration testing, the team needed access to external database resources.  One major obstacle is database access in Jenkins.  Although a separate test database became available for use, it could not be utilized properly, and an alternate solution had to be discovered. Fortunately, the H2 Database Engine allows an in-memory database with DB schema support—a solution to leverage H2 in the Jenkins pipeline.

Integration Testing

Integration testing of software encompasses all aspects of a software system’s performance, functionality, and reliability. Functional testing of software modules is presumed accurate but prior to integration testing, each module is tested independently and not tested as part of a whole subsystem with many modules communicating to test a service or application-specific functionality. For example, retrieving a customer account and returning a balance would be considered part of an integration test.

Jenkins

Jenkins is an open-source automation server written in Java. It automates the software development process with continuous integration and delivery. Jenkins runs in servlet containers such as Apache Tomcat, utilizing a process called the Jenkins Pipeline for continuous delivery of software from version control to users and customers. A JUnit plugin is provided to publish test reports generated during the builds and provide graphical visualization of historical test results via a Web UI.

Jenkins Pipeline

A major component of Jenkins is their pipeline. It includes JUnit testing as a goal. During this processing, 100% of JUnit tests must pass to continue to the next step in the pipeline SonarQube. The eventual goal is the successful deployment and building of artifacts. Many JUnit tests developed for Jenkins are functional testing only of a specific Java class. All external references, external APIs, and variables are "mocked’ and often stubbed out to not be included as part of the test. A functional test suite is comprised of every class tested separately in a system. Integration testing of multiple classes is usually not a consideration. The Jenkins Unit testing has a set code coverage for passing Jenkins pipeline. Depending on the mission-criticality of the application, recommended code coverage is set anywhere from 65% to 100%. Achieving higher code coverage comes at considerable costs and effort as one approaches 100% of functional testing.

H2 Database

H2 is an open-source relational database management system written in Java and is a high-performance in-memory database. This database is used in embedded mode or in server mode. In embedded mode, the data is not persisted and H2 requires a small footprint (2 Mb). The main programming APIs are SQL and JDBC. Both APIs provide support for multiple databases including Db2. It is possible to create both in-memory tables that are temporary. All data manipulation operations are transactional. H2 is secured by running in single JVM, userid/password, port. Once the Java program execution is completed, the H2 database including the data are destroyed in memory.

SQL Support in H2

• Support for multiple schemas, information schema
• Referential integrity/foreign key constraints with cascade, check constraints
• Inner and outer joins, subqueries, read-only views and inline views
• Triggers and Java functions/stored procedures
• Many built-in functions, including XML and lossless data compression
• Wide range of data types including large objects (BLOB/CLOB) and arrays
• Sequence and auto-increment columns, computed columns (can be used for function-based indexes)
• ORDER BY, GROUP BY, HAVING, UNION, OFFSET/FETCH (including PERCENT and WITH TIES), LIMIT, TOP, DISTINCT/DISTINCT ON (...)
• Support for users and roles
• Compatibility modes for Relational databases (Db2, Postgres, MS-SQL, Oracle, MySQL, etc.)
• Supports the EXPLAIN PLAN statement

H2 Embedded Mode

H2 database supports an in-memory mode where the data is not persisted. For testing in the Jenkins pipeline, the H2 database must be accessed in embedded mode. There, an application opens a database from within the same JVM using JDBC. There is no limit on the number of databases open concurrently, or on the number of open connections. A disadvantage is that a database may only be open in one virtual machine and class loader at any time. The integration tests and H2 database run in the same JVM. Memory must be available to allow the tests to complete successfully. Fortunately, we did not run into an issue with memory in the Jenkins pipeline.

Web Service

A web service is a method of communicating between electronic systems over a network. Different software systems often need to exchange data with each other, and a Web service is a method of communication that allows two software systems to exchange data. The data exchanged is commonly in JSON format, although other formats such as XML are supported.

JUnit Web Service Testing

JUnit integration testing involves all the components needed for supporting web services:

The web service uses existing production code using an embeddable web server implementation for JUnit testing. A web service is a software system designed to support interoperable machine-to-machine interaction over a network.

The business logic uses existing production code. The business logic is the part of the application that encodes the real-world business rules determining how data can be created, stored, and changed. Business logic is the portion of an enterprise system which determines how data is transformed or calculated and how it is routed to people or software (workflow).

The JDBC driver to connect to the H2 embedded database will require a properties file containing H2 property settings. Java Database Connectivity (JDBC) is an application programming interface (API) for the programming language Java, which defines how a client may access a database.

The Database will be loaded into memory using H2. All schemas, reference tables, and data required for testing will be included.

Jenkins Integration Test

The integration tests developed required minimal configuration changes to run in the Jenkins pipeline. None of the JUnit test code in our environment was modified after migrating to an Agile environment. The team leveraged good design principles and separation of concerns. The only change required was modifying the JDBC connection to point to the H2 database.

Code will need to be developed to use H2. The H2 database will need to load the database schema, build the object-relational model (ORM) for the database tables, keys, and table relationships. Reference and test data will need to be loaded via SQL scripts. The H2 in-memory database will need to be initialized during the Jenkins test phase. It is recommended that each JUnit integration test do a clean up after each test completion as to not interfere with subsequent tests.

Advantages to Jenkins H2 testing

Security

No connections are required to an external database in the Jenkins pipeline, which means no external user id and password are required. This prevents a security risk because no external configuration files are used which may contain plain text and external databases cannot be accessed.

Speed

H2 in-memory database runs extremely fast versus disk access. Be aware this does not prevent bad programming practices. Inefficient queries using table scans will run in milliseconds. Millions of rows of data that may be on external tables will not be in the test database. Table scans on disk can run from minutes to hours. Another caveat is that the software developers may get complacent and not optimize their SQL queries because of the speed of H2. The development team should continue to work closely with their respective DBAs to maintain optimal query performance. The Jenkins pipeline should not be considered as a replacement for database performance tuning!

Consistency

Using database schemas, reference tables, and test data, the H2 database is consistent between Jenkins builds. The H2 database is built during each Jenkins Unit goal and destroyed after it completes.  Consistency is important for integration tests between Jenkins builds. The in-memory internal database eliminates the possibility of data being "injected" resulting in skewed testing and causing false positives which may inadvertently occur on an external persistent database with many users.

Conclusion

Web services integration testing can be achieved in an Agile environment using the Jenkins pipeline.  This provides another level of software testing in Agile: automated integration testing of subsystems. JUnit tests are most commonly written to test software modules with external dependencies "mocked."  

However, JUnit integration tests can be written to test software functionality from web services to the database backend without "mocking."  H2 in-memory database can be leveraged to supporting a test database in the Jenkins pipeline that offers security, speed and consistency between Jenkins builds.

Further Reading

Integrate an H2 Database in Your Spring Boot App

Jenkins in a Nutshell