Working with databases

Trails extensively uses Hibernateas it's persistence layer. While you can provide a different "persistenceService" implementation, the only one provided by Trails is a HibernatePersistenceService, which as the name implies, implements a Hibernate based persistence mechanism. Hibernate is an ORM tool that maps your Java objects to a database schema.

 Trails by default uses in-memory HSQLDB, which means it's fast, super-simple to set up and only exists in-memory. HSQLDB is a nice database to develop with, and even nicer for running your db integration tests against (as reasoned for example in this Serverside article), because the start-up cost is minimal, you always start from a clean slate and you know it works no matter what the environment is.

With Hibernate it's very well possible and easy to use more than one type of database since the switching cost is minimal - you just provide a different database configuration and off you go.

However, one potential issue that you want to be on a look out for is using reserved words in the database, because Hibernate maps your entities directly to a database schema.

A simple example of what doesn't work is a property named "date", i.e. having getDate() operation in one of your entities. These are typically easy to spot from the error logs when you start up your application after adding a new entity. The theory is that by supporting (and testing with) more databases you make your implementation stronger.

Typical scenarios for different database use cases are:

• Development, where it's nice to have a persistent database so you don't need to seed the data all the time and one that is as fast as possible on a small dataset
• Integration testing(often automatically launched from you build), where you typically set the database state for a testing and roll back the changes after each test
• Production(and production testing), where you obviously need a persistent storage, the set-up costs are often less relevant, it needs to work fast on a larger dataset  and you may require some other features from the database software, like backups, recoverability and clustering

Choosing the right database 

This is not a generic database guide, but a few words about the choices. For development, your best bets are HSQLDB, Derby or MySQL depending on your preferences and what you are used to. If you want to look at the raw database results and are used to writing SQL, MYSQL has a good, common-sense syntax and is easy to set up. If you appreciate automating even the database setup for development, HSQLDB and Derby are easy to embed, and are especially useful when doing integration system with fully automated build systems.

Your production database of choice depends largely on your application type and your deployment environment. For a small-scale prototyping, free or open-source projects, HSLQDB, DERBY, MySQL and PostgreSQL are good choices. If the database choice isn't up to you, you should check that Hibernate first of all supports it and secondly, keep testing frequently that your domain model works with the particular database even if you don't use it during development.

Configuring Hibernate 

Your typical hibernate.properties for development should look something like this:

Using hibernate.hbm2ddl.auto=create-drop causes the database to be created at the start-up and then deleted after use which might be advantageous in some cases, like for integration testing. Hibernate also allows you specify hibernate.hbm2ddl.auto=validate, which may make sense in a production environment, but notice that loading the Spring context would fail if you use validate and Hibernate requires changes to be made in the database schema.