H2 Database engine: a developer presepective

I'm now coding a prototype of my product (more on that in the near future) and one of the first things I was looking for was a repository to keep data. The repository needs to be embedded in my software with no external server running. After considering several alternatives, including exotic ones like RDF repositories, I decided to go for a relational database. Naturally, I was thinking of Oracle's Berkeley DB. It is a highly regarded embedded database, probably the best on the market.The main obstacle was the license: it is free if your software is free. So, I started looking for alternatives.

I had two main alternatives now. First one, Java DB. This is a new feature in JDK 6, which is actually a re-branding of Apache Derby, another popular embedded database. The next option was HSQLDB, the database engine used in Open Office. For those not familiar with Open Office, let's just say that if Open Office is the alternative to Microsoft Office then HSQLDB is the alternative to the Jet database engine (or the later "desktop" versions of SQL Server) used by Microsoft Access.  Open Office has an Access like application called Base, which uses it. Needless to say, the Microsoft alternatives were out of the question since I'm working on a Mac.

Then I came across an interesting product: H2. The H2 database engine is a free, open source, fully functional relational database management system written in Java. It can run in embedded mode or as a server. What caught my eye was a claim by the developers that H2 is faster than both HSQLDB and Apache Derby. Being an innovator myself, I like disruptive new products, hence, I decided to give it a shot.

Starting off
Installation was a breeze. I was up and running in literally minutes. What you get from their site is a zip file (installer if you're on Windows) and a PDF manual. That's it. You unzip the file and you're good to go. The manual is very useful, and got me started in no time. Once you start the server, by using a Java archive, it starts a web server which provides a web client for working with your database. So, again, unzip, run a Java command and that's it. You got a fully featured database server AND a database management application working.  BTW, starting the server takes about a second on my MacBook Pro, even when I've got over 10 other applications running in the background.

Working with H2
H2 supports SQL and JDBC, so it's much like any other database. The data is kept in a few compact files which you specify in your connection URL. If the files does not exist, they are created on the fly and you can start using them immediately. Backing up just means backing up your files. For those familiar with Jet, it is pretty much the same only much smaller and faster. The web client is great for viewing your database structure, viewing/editing table data and executing SQL commands. If you're looking for a tool that will help you design your database (i.e. you're too lazy to write the SQLs for creating the tables by hand), you can also use the Open Office Base wizards and table views or use other tools which support JDBC, like SQuirreL SQL (free, open source and cross platform).

The Developer Perspective
Since I'm working on a prototype, agility is my main concern. I'm using the Spring Framework JDBC Templates and it is working perfectly together. H2 also supports Hibernate, but I don't really need it at the moment. I can create new queries in minutes. I'm working in embedded mode and starting the server is very fast, usually less than a second, and it has a minimal memory footprint. This is important since I often want to execute simple tests and get instant results. I didn't get a chance to measure its' SQL execution performance, but that will happen in the near future.

I had one issue: once my process starts it locks the file, so I cannot see what's going on in the database using another client. I posted a question in the H2 Google group and quickly got a response, by the lead developer, that I can continue to work in embedded mode and still open up a port for remote connections. It took me about a minute to set this up and it just works. This is what I like about H2, it is very simple and easy to use.

Bottom Line
I'm happy with H2. It is just what I needed. It is a breeze to install, it is compatible with my ecosystem and it is quietly delivering results without disturbing my work and becoming a burden. I haven't used it for production systems or for heavy data crunching, but for my needs it works great. If you're writing a Java software and you need a simple, instant, persistent storage, this is a viable alternative to consider. H2 is currently in beta and it is still under development. This is something to keep an eye on.

Comments

[Boris Freidberg]

Hi,
1. What about MySql ? Did you try it?
2. By using Hibernate or JPA you can get DB independence project.

[Zviki]

1. I looked at MySQL. AFAICT, it does not support an embedded mode. This is a critical factor. Moreover, MySQL suffers from the same licensing issues as Berkeley DB - if your software is not open source under the GPL license, and you wish to distribute it, you need to buy the proprietary license. Just to be clear, I'm not developing an open source software. At least not at the moment.

2. I agree. However, in my case, using either JPA or Hibernate is an overkill for me. I use Spring JDBC templates - it took me a few minutes to master and it's very flexible and easy to use. Maybe I'll blog about it separately. I do have a decoupled DAL (data access layer) which can easily be replaced. The SQLs are very simple and generic, so I really don't think I'll have any problem migrating to another database, should the need arise.

[Stefan Edlich]

Good Blog.
And if you don't need SQL and you do want to work more close to your objects then give db4o a try. Embedded and for Java + .NET. Your H2 review would read the same for db4o but you get 3 other cool query languages (some very close to what hibernate can do) instead of SQL.
Give it a try ;-)

[Zviki]

Thanks, Stefan, for the kind words.

I did stumble across db4o during my search. One simple distinctive difference stands between db4o and H2. Like Berkeley DB, db4o is not freely redistributable if you use it for commercial software. There's a hefty license cost.

I wrote a post in the past about "when saving a buck actually costs you more" where I discussed the issue of using free software which may cost more than the commercial one. However, here, it's not an issue of paying a couple of grands. The issue of license cost can seriously affect my entire "early days" business plan. It is something that a MicroISV can't stand.

Having said that, I find such products just too interesting to overlook and I hope I'll have a chance to test-drive it myself in the future. Maybe we will migrate in version 2.0 :-)

[evanx]

thanks for the review. I remember the H2 project being announced, and the developer had previously developed HSQLDB and Pointbase, so i knew this had the potentially to be a great new embedded database, and clearly he has been very impressively busy during the past coupla years :) This H2 looks set to be a major player in the database world. PS. i noticed a typo - "sever" for "server"

[Zviki]

Thanks, Evan.

I find it really great that even today, in a world of database giants, a small group of people can find the time and reason sit and start writing a database engine from scratch.

[UlfertVos]

Hi Zviki,

Interesting article and H2 does seem promising. Am looking at options for a small, embedded, Java-based RDBMS myself and agree with your findings. Will try H2 myself but I was just wondering if your initial enthusiasm for H2 is still there???

[Zviki Cohen]

Thanks. My needs did not change much after the initial implementation. I'm now focusing on other aspects of my software so H2 is just sitting there, doing what it's supposed to do and that's it. I will write some more when I have new insights.

In the mean while, I can recommend going over the posts in the H2 Google Group. It's a vibrant community.

[Joel]

Just wanted to chime in that we have been using H2 intensely for months now, and it has performed like a champ.

Really good SQL support.

Way faster than anything else we have used or tested.

_Great_ feature set. You really won't believe all of the things it can do.

Just my 2 cents.

[Frans Lundberg]

We (SkyCash) are using this database for a new release of a high-performance payment clearing system. It works great and is faster than anything else we have tried including MySQL and Postgres.