UPDATE: All features described in this article are currently included in the official release of the H2 database.
Slow and non-deterministic tests can become a real issue, when dealing with large, monolithic applications and pre-existing code, that depends on a centralized data model (i.e. a shared database with some “test” data). One common improvement is to use an in-memory database instead, and run each test suite in it’s own, isolated environment with no external dependencies.
There are several advantages of taking this approach:
- Tests become more expressive and are easier to debug, since all initial state needs an explicit declaration in the test code.
- Test suite isolation makes parallel execution easier and more predictable. Similarly, explicit transaction management may not be mandatory in the test context anymore (e.g. some people like to
@Rollbacktheir integration tests, when using Spring).
- Performance and reliability are improved, since no predefined database connection is required.
- Testing environment is much easier to setup, since it can be spawned directly by Maven, Gradle or some other build tool.
- It may improve portability of your development environment.
The main drawback is the compatibility factor of course, since some issues may be visible in the target environment only. Whether this an acceptable risk or not, depends entirely on your application, your deployment process and your general approach to integration testing of course.
Having this in mind, most in-memory databases work pretty well with various JPA implementations, such as Hibernate. It only becomes a challenge when you start working with native queries and/or legacy code, that implement some “custom” persistence solutions.
Usage should be rather straightforward - just include a
MODE=MSSQLServer in the JDBC connection string, when instantiating a
Using IDENTITY as an alias for AUTO_INCREMENT
A column definition in SQL Server might include an
IDENTITY keyword for handling automatic id generation by the database. H2 supports this syntax as well, but - unfortunately - it can’t be combined with a
PRIMARY KEY prefix in a column definition. For this reason, I’ve added an
AUTO_INCREMENT alias for
IDENTITY, which enables us to run the following type of statements in our code:
… or with a
NOT NULL modifier:
Discarding table hints
Table hints are special SQL expressions, which are meant to override the default behavior of the query optimizer. We don’t need to support those in our tests, therefore we’re going to ignore them entirely.
The following types of hints are currently supported by the implementation (ignored by the parser):