Sep 10, 2011

Database-related technology for Java application

One of the crucial things to develop a system is to maintain the version of the database schema.

Rails has the built-in procedure, which makes the development painless.

I have been looking into an ORM framework together with a database migration framework.

H2, Cayenne with Flyway are chosen.

H2 is chosen because it supports standard SQL and it is embeddable, yet still a powerful database.

H2 can be run with a mixed mode inside your Java application. The mixed mode means that your Java application can access H2 natively, while other applications (e.g. Rails) can access H2 through TCP.

And it performs well with many concurrent connections. (This is a big win over SQLite.)

Flyway is chosen (over Liquibase) because it offers Java APIs. This means that I can run migration at every startup of the server. You can also run migration from command-line, while Liquibase offers only command-line tools.

The good thing is that Liquibase offers portable migration script because it uses XML instead of raw SQL. However, we do not switch database that often.

Cayenne is chosen over TopLink and Hibernate because of its simplicity. Cayenne offers a GUI tool to manipulate the schema and generate SQL migration (that can be saved and used by Flyway.

TopLink and Hibernate are too good and, of course, more complicated.

Performance is not considered here because there are not many clients (50 at most). Therefore, any normal ORM will be ok.

-----

I have just found one con. The Cayenne GUI tool cannot create indexes...