How to update a database schema without losing your data with Hibernate?

Question

Hi everybody!

Imagine you are developing a Java EE app using Hibernate and JBoss. You have a running server that has some important data on it. You release the next version of the app once in a while (1-2 weeks) and they have a bunch of changes in the persistence layer:

• New entities
• Removed entities
• Attribute type changes
• Attribute name changes
• Relationship changes

How do you effectively set up a system that updates the database schema and preserves the data? As far as I know (I may be mistaking), Hibernate doesn't perform alter column, drop/alter constraint.

Thank you, Artem B.

Answer 1

I personally keep track of all changes in a migration SQL script.

Answer 2

http://liquibase.org/

Answer 3

I use the hbm2ddl ant task to generate my ddl. There is an option that will perform alter tables/columns in your database.

Please see the "update" attribute of the hbm2ddl ant task:

http://www.hibernate.org/hib_docs/tools/reference/en/html/ant.html#d0e1137

update(default: false): Try and create an update script representing the "delta" between what is in the database and what the mappings specify. Ignores create/update attributes. (Do not use against production databases, no guarantees at all that the proper delta can be generated nor that the underlying database can actually execute the needed operations)

Answer 4

For one app I use SchemaUpdate, which is built in to Hibernate, straight from a bootstrap class so the schema is checked every time the app starts up. That takes care of adding new columns or tables which is mostly what happens to a mature app. To handle special cases, like dropping columns, the bootstrap just manually runs the ddl in a try/catch so if it's already been dropped once, it just silently throws an error. I'm not sure I'd do this with mission critical data in a production app, but in several years and hundreds of deployments, I've never had a problem with it.

Answer 5

LiquiBase is your best bet. It has a hibernate integration mode that uses Hibernate's hbm2ddl to compare your database and your hibernate mapping, but rather than updating the database automatically, it outputs a liquibase changelog file which can be inspected before actually running.

While more convenient, any tool that does a comparison of your database and your hibernate mappings is going to make mistakes. See http://blog.liquibase.org/2007/06/the-problem-with-database-diffs.html for examples. With liquibase you build up a list of database changes as you develop in a format that can survive code with branches and merges.

Answer 6

ChronicDB seems to allow schema reorganizations, but they also do it live without bringing the database down.

Answer 7

You can also use DBMigrate. It's similar to Liquibase :

Similar to 'rake migrate' for Ruby on Rails this library lets you manage database upgrades for your Java applications.