migrate database schema on update of Java EE application

Question

I have a Java EE application which use SQL Server Express as backend database. When we deploy new version sometime we need to change the database: add tables, add columns, remove columns, merge tables, merge column, etc.

Until today we use offline SQL scripts to modify the database but this solution is not scalable and very error-prune.

We start use a singleton startup bean which has @PostConstruct decorated method to add and remove data which is used by the system during deployment.

We now want to also issue DML statements so we can upgrade our database during deployment and eliminate the SQL scripts once and for all.

1. Has anyone try to do it, is that good direction?
2. Should we use entityManager.unwrap method or simply inject the JDBC DataSource resource into our singleton startup bean?

Answer 1

We have recently started using flyway and are very happy with it. The documentation is very good. In short: you include a set of SQL scripts with versions. The Flyway engine will pick up the SQL scripts that were not yet applied (there is a special metadata table created) and run them. You can either use SQL with versions or plain Java.

Answer 2

In addition to Tomasz's answer, you may also use LiquiBase. The solution it offers forces developers to "version-control" their schema changes so when eventually you get to production you've a fully understandable schema changelog. Then, you can use LiquiBase to apply that changelog, or any subset of it, to the production DB. You can also rollback subsets of the changelog.

It handles lots of kinds of changes out-of-the-box and also enables using custom migration java classes.

Never used it myself but it's open sourced since 2007.

Here's a quick overview.