CODEWITHSUNDEEP
postgresqlhsqldbderbyh2embedded-database
adi
adi

Reputation: 1741

Testing: Best embedded database to use

I've been using Derby (embedded) for testing my applications. It was working fine, but recently it started giving me problems like this one Derby Sequence Loop in junit test.

The same test runs fine with other databases (e.g. H2).

My tech stack is Java 1.6, Spring, Hibernate.

I've seen comparisons of similar databases (H2, HSQLDB, Derby, PostgreSQL and MySQL), but the comparisons don't mentions the problems (like the one I faced).

So, which one is the prefered one for testing purposes?

Upvotes: 1

Views: 2115

Answers (2)

Thomas Mueller
Thomas Mueller

Reputation: 50107

I understand some people think that it's better to always use the same database. But there are a few disadvantages:

  • Unit tests might be very slow as each database call goes over TCP/IP.
  • Running the tests might be more complicated as you need to install and run the same database as you use for production on your local machine.
  • The database you use for production database might need a lot of memory, which you might not want on the developer machine.
  • There is a risk you inadvertently use features that are only available on one database, which will make switching to another database very hard (if you ever consider doing that in the future).

Using a different database for unit testing also has disadvantages of course. If you use database specific features, there is a risk that some tests can not be run or the tests don't behave exactly the same way. Because of that, I suggest to run the nightly builds using the same database as used for production.

Specially if you are using a database abstraction layer such as Hibernate, switching the database should be very easy. In this case it does make a lot of sense to consider an in-memory database such as H2, HSQLDB, or Derby. Specially if you consider writing many (or longer running) tests.

Upvotes: 2

Craig Ringer
Craig Ringer

Reputation: 324541

The best test database is the same version of the same type you're running in production, on the same host OS and architecture.

The question really tends to be "what requirements do you have in testing but not production that make you consider a different DB"?

For example, you might want an embedded database you can easily launch and destroy under unit testing control, without having to specify paths to external binaries, launch and stop a database server, etc. In exchange for that you accept a compromise that your tests are not as good a match for the real system you'll be running on.

When I was building software on Java app servers I ran my tests against PostgreSQL, just like the production machine. It's not like it's hard to initdb and pg_ctl start a DB; you can even do it from a test wrapper if you really feel the need. I think people are a bit too focused on fully in-process embedded DBs and don't properly consider the costs of that.

Upvotes: 4

Related Questions