H2 Database

H2 (project page, or on Wikipedia) is a SQL database implemented in Java with couple of features that are very handy especially for testing:

• Firstly, H2 is all packed in a single JAR which weights way under 2 MB and contains features you would not expect in such a cute packaging.
• It can store its tables both in-memory and on the disk. Using in-memory storage is great for tests, because it’s fast (for reasonable database size) and also naturally disappears after the test is run.
• It supports both embedded mode – running in-process with the application itself – and client-server mode where it can run standalone and you can connect to it from different process. For automated tests I prefer embedded mode, but when I experiment I use client-server mode, so I can check the state of my database independently from my demo application.
• It contains various tools – most prominent of which is web-based SQL console. This is what you can use to check the database in server mode while it’s being modified by an application.
• Naturally, it contains JDBC driver.
• SQL support is rich enough to mimic much more evolved/expensive databases, I used it to test JPA applications that were originally developed for Oracle or Microsoft SQL Server – no changes besides the JPA configuration were required.
• While it does not support any scripting (like Oracle’s PL/SQL or PostgreSQL PL/pgSQL) you can plug triggers and procedures developed in Java. Maybe I’d not use it if the database contained a lot of code, but when you need to handle couple of simple procedures and some triggers, it can be done.

Starting it

You can download it as a Windows Installer or multi-platform ZIP, but all you really need is the JAR file. If you manage your application’s dependencies you probably have it somewhere on the disk already. So just locate it and run it. If everything is set up properly, just pressing Enter on the JAR in some file manager will do. If not, try the following command:

$ java -jar $HOME/.m2/repository/com/h2database/h2/1.4.190/h2-1.4.190.jar

Of course, adjust the JAR location and name, and if you don’t have java on your PATH (yet!) do something about it. When everything clicks your default browser will open and you can log into your default database (user sa, empty password). You can learn more in this Quickstart. I probably forgot to mention that it is well documented too!

Using it as a devel/test DB

I have a long relationship with H2 and I can only recommend it. In 100% of projects we used it as a developer database – if for nothing else than for running fast commit tests at least – everything was better and smoother. There is some overhead involved when we had to manage alternative schema and test data for H2 as well, but a lot of it can be automated (Groovy is my language of choice for this). But this overhead was always offset by the benefits for testing and the fact that we looked at our schema through the perspective of two different RDBMS (production one and H2) actually often helped too.