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.

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

You can check this brief comparison of some of the schema migration tools. http://stackoverflow.com/a/28055590/2658613 — gaboroncancio, Jan 20 '15 at 21:38

score 7 · Accepted Answer · edited Nov 14 '12 at 10:11

7

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.

edited Nov 14 '12 at 10:11

Axel Fontaine

34,542
16
106
137

answered Apr 15 '12 at 12:05

Tomasz Nurkiewicz

334,321
69
703
674

Thanks, we will check it. Have a nice day. – Ido Ran Apr 15 '12 at 13:51

score 6 · Answer 2 · answered Apr 15 '12 at 12:41

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.

score 3 · Answer 3 · edited Aug 27 '20 at 09:57

3

And to finalize list of database migration tools, I'm quite happy with solidbase. We launch it as a part of deployment script, but the idea launching it in a singleton bean is quite interesting. Solidbase's wiki does not document such embedded usage, but from quick skim of source code class Runner is what you're looking for.

edited Aug 27 '20 at 09:57

Cédric

334
2
8

answered Apr 15 '12 at 18:49

pdudits

896
6
9

migrate database schema on update of Java EE application

3 Answers3