9

I have an application where Hibernate creates all my table schemata whenever the application is run on a machine for the first time. This works nicely.

Now I was however wondering if Hibernate has some sort of mechanism to keep the database under version control, i.e. will Hibernate know how to migrate one schema to another when I run a different version of my application and Hibernate finds a different database schema from an older version present? I somehow think that this should be possible, considering that Hibernate can read the existent schema and could compare the schema to the mapping description. I would however not know how I could tell Hibernate to migrate old data as when creating change scripts with e.g. Liquibase / Flyway.

I might not have googled the right things since Hibernate and versioning will show you a lot of hits on auditing and field versioning but I am thinking more in terms of Liquibase / Flyway kind of versioning. I never considered the both together but since Hibernate does not create update scripts but directly manipulates the database, I would not know how to make the both work together.

This is the first time I let Hibernate create my schema instead of writing my own scripts. I do this in order to make use of Hibernate Envers what makes the manual script creation extra tedious. Maybe I am missing something obvious. Thanks for any input on this matter!

Update: I got to talk to the developer of Flyway today and he told me that he would not know of a good solution. Maybe there is nothing?

Rafael Winterhalter
  • 42,759
  • 13
  • 108
  • 192
  • Hibernate has a hbm2ddl.auto value of "update" which will try to do this but it is not dependable. In my experience with hibernate, these schema updates should always be handled by manually written schema update sql scripts. Generally the db user the app is using shouldn't have create/drop privileges either, though this may not apply in your case. – Taylor Sep 11 '13 at 19:32
  • In my opinion, the schema writing in the end is some kind of code duplication since it simly mirrors a domain model already described by implementing my Java objects. Usually, this duplication is not that expensive, but with Envers, it is. Therefore I wish I had a solution to this. In what context did you experience the hbm2dll.auto to be insufficient. It might be a great entry point to implement a custom Flyway migrator. – Rafael Winterhalter Sep 11 '13 at 21:26
  • Almost every context. Any reorganization of existing columns or any change other than "add column with a single default value" or "drop this column" is beyond the capability of hibernate to update. – Taylor Sep 12 '13 at 01:51

1 Answers1

11

We had the same problem with our Java/Hibernate project, and did not want any code duplication effort. Hibernate "update" feature is not reliable at all, LiquidBase is better but not 100% fool proof either. In the end we developed a simple script to manage the following process:

  1. The "current" database schema is always generated by Hibernate, against a DEV database directly.
  2. A "previous" database schema is generated by a series of LiquiBase change sets.
  3. Every time a migration is needed, a LiquiBase "diff" function is invoked between the "previous" and "current" databases (two actual databases, yes, more reliably that way), generating a new LiquiBase change set.
  4. This change set needs to be manually reviewed. All change sets are kept in source code control.
  5. The PRODUCTION database has its schema generated by applying all the LiquiBase change sets.

A key command in our script reads like the following:

${LIQB_COMMAND} ${PREV_DB_OPTIONS} --changeLogFile=${LIQB_CHGLOG_FILE_NEW}  \
    diffChangeLog \
                --referenceUsername=${DEV_DB_USER} \
                --referencePassword=${DEV_DB_PWD} \
                --referenceDriver=com.mysql.jdbc.Driver \
                --referenceUrl=${DEV_DB_URL}

This way, our migration process is quite reliable and you don't write the schema code twice. There is a manual review of the generated change set in XML, but most of the time there is no problem, and it sure is much easier than manually writing the schema change operations.

stevel
  • 720
  • 4
  • 7
  • Yes. Liquibase is what you want to use. Hibernate's auto-ddl feature is a dev/test tool primarily. I'm pretty sure this is mentioned in most good Hibernate books, so I'm not sure why folks try to use auto-ddl to do production schema migration. – Joshua Davis Sep 15 '13 at 14:04
  • 1
    Because it is convenient, I guess. But I thought that I needed to go towards such a solution. You did not happen to open source your solution? – Rafael Winterhalter Sep 16 '13 at 07:35
  • You know, I actually don't mind doing that. It's just this part of the code is kind of embedded in our project. It'll take a little bit of effort to further clean it up and make it useful to you and others. Let me know for sure you want to go through route, and can perhaps help clean up the code to make it useful to others. – stevel Sep 16 '13 at 07:56