Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Combine Hibernate's automatic schema creation and database versioning

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?

like image 489
Rafael Winterhalter Avatar asked Aug 30 '13 15:08

Rafael Winterhalter


People also ask

What is the purpose of schema versioning?

Schema versioning deals with the need to retain current data, and the ability to query and update it, through alternate database structures.

How do you make a versioning database?

Versioning a database means sharing all changes of a database that are neccessary for other team members in order to get the project running properly. Database versioning starts with a settled database schema (skeleton) and optionally with some data.

What does versioning mean in database?

What is Data Versioning? Fundamentally to version data means to create a unique reference for a collection of data. This reference can take the form of a query, an ID, or also commonly a datetime identifier.


1 Answers

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.

like image 148
stevel Avatar answered Nov 06 '22 06:11

stevel