Jenkins Pipeline H2 Database Integration Testing
By David P. Morris, Jr., Leidos Health Software Development Analyst
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 does not break existing logic at the unit level. Unit testing of software generally ‘stubs out’ external functionality. Its only concern is if the software works at the module level, but what is missing in this approach is automated integration testing.
The Db2 project I collaborated on had many web services integration tests developed before the project adopted Agile principles. A main concern migrating to Agile was that the Db2 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 Db2 database backend.
Agile and Jenkins software testing in our enterprise environment is setup for Unit testing. For integration testing, the team needed access to external Db2 database resources. One major obstacle is database access in Jenkins. Although a separate Db2 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 Db2 DB schema support—a solution to leverage H2 in the Jenkins pipeline.
Integration testing of software encompasses all aspects of a software system’s performance, functionality, and reliability. Functional testing of software modules are 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 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.
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 critically 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 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 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 Db2 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.
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 embedded-able webserver 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 Db2 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 Db2 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 Db2 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 Db2 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
No connections are required to an external Db2 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.
H2 in memory database runs extremely fast versus disk access. Be aware this does not prevent bad programming practices. Inefficient Db2 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!
Using Db2 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.
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 with Db2 schemas, tables and test data can be leveraged to supporting a Db2 test database in the Jenkins pipeline that offers security, speed and consistency between Jenkins builds.