Incremental schema upgrades using Hibernate
I have been inspired by recent discussions on upgrade frameworks to show how hibernate can be used to provide simple incremental database schema maintenance. Database schema maintenance is one of the more difficult aspects of upgrading applications, particularly when the application supports multiple databases, so I am very happy that hibernate helps out during upgrades.
SchemaUpdate
Hibernate provides a class called SchemaUpdate that is able to synchronise a set of hibernate mappings with a database schema. The following code snippet shows how easy it is:
Configuration config = new Configuration();
Properties props = new Properties();
props.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
props.put("hibernate.connection.provider_class",
"com.zutubi.pulse.upgrade.tasks.UpgradeTaskConnectionProvider");
// slight hack to provide hibernate with access to
// the configured datasource via a static variable
// on our ConnectionProvider implementation.
UpgradeTaskConnectionProvider.dataSource = dataSource;
// use spring to help load the classpath resources.
for (String mapping : mappings)
{
ClassPathResource resource =
new ClassPathResource(mapping);
config.addInputStream(resource.getInputStream());
}
// run the schema update.
new SchemaUpdate(config, props).execute(true, true);
This example uses the spring ClassPathResource to load the mappings file from the classpath, and the UpgradeTaskConnectionProvider to inject a datasource into the process.
.hbm.xml fragments
This by itself is not overly interesting. What people usually do not realise is that the mappings files do not need to hold your entire schema. When making incremental changes to your schema, all you need in the mappings are those incremental changes. This comes in very handy when you have lots of mappings to manage.
For example. You have the following mapping of a user:
<id name="id" type="java.lang.Long" column="ID">
<generator class="hilo"/>
</id>
<property name="login" column="LOGIN" type="string"/>
<property name="name" column="NAME" type="string"/>
</class>
Some time later, you want to store a password field with this user. By passing the following mapping to the SchemaUpdate, it will add that column to your existing table, leaving the existing schema as it is.
<id name="id" type="java.lang.Long" column="ID">
<generator class="hilo"/>
</id>
<property name="pass" column="PASS" type="string"/>
</class>
You still need to ensure that the mapping file is valid, hence the inclusion of the ID field in the second mapping.
Versioning
So, to support incremental schema upgrades within your application, you will need to keep two sets of hibernate mapping files. The first will be the latest version of your mappings. This is what is used for new installations. The second will be a set of versioned mapping fragments as described above.
You will need to version them so that you can track which fragments you need to apply and in which order, based on the version of the schema you are upgrading from. I use directory names like build_010101 to store my schema fragments and a properties file to store the current schema version. Other people use a special table in the database to hold the current schema version. Use which ever is most appropriate to your situation.
Generating upgrade SQL
For those of you that do not want or can not allow Hibernate to run the schema update, you can use the following code to generate the SQL that Hibernate would otherwise execute:
Connection connection = dataSource.getConnection();
DatabaseMetadata meta =
new DatabaseMetadata(connection, dialect);
String[] createSQL =
config.generateSchemaUpdateScript(dialect, meta);
This code would replace the last line in the first example.
Things to remember about SchemaUpdate
Okay, so just a couple of final things to be aware of with hibernates schema update.
The hibernate schema update will:
- create a new table
- add a new column
The hibernate schema update will not:
- drop a table
- drop a column
- change a constraint on a column
- add a column with a not-null constraint to an existing table
Final tip
Oh, and the class name that you provide in the update mapping can be anything you want. It is not checked, which is great, otherwise you would need to handle versioning of your class files as well.
Happy upgrading!
This entry was posted on Monday, August 28th, 2006 at 1:53 pm and is filed under Java, Programming Languages, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
August 29th, 2006 at 6:02 am
Thanks for the info.
October 17th, 2006 at 10:37 pm
SQL schema upgrades a thing of the past? says:[…] I consider Schema migration to be one of the more tedious and yet non-trivial tasks that is required by any application that employs relational persistence. Yes, Hibernate makes this task somewhat easier to deal with. However, even with Hibernate, you will still need to roll up your sleeves and write some SQL to handle the migration of the data. […]
December 8th, 2006 at 4:23 am
Rachel McConnell says:I currently handle schema updates with a custom process I wrote that uses a similar process to Rails: one initial schema.sql file, plus one 00X_update.sql file per schema version. It looks like using Hibernate’s method would trade in my one file per version for N files per version, one for each object whose interface changed, and not even let me do things like rename columns much less drop or change constraints or do data transformations. I have to say I’m not sold on it!
December 8th, 2006 at 12:05 pm
Rachel,
The primary benefit of using Hibernate is that it handles the slight variations between databases. There are not many, but enough to make life annoying if you are targetting multiple dbs.
Certainly, Hibernate will not automatically change column names or handle some of the more interesting common data transformations. It would be very nice if it did, that may be something for the future.
The way we handled it is with a mixture of the two approaches. We let hibernate handle all of the schema management since it is trivial for us to do so, and then if we need any added bits, we write some simple SQL that we are sure will work on different DBs. And in practice, we dont often end up with more than 2 or 3 .hbm.xml files per version.
Your approach sounds like it works well for you. If you are happy to write the SQL, then it certainly does have advantages.
Thanks for your comment.
September 14th, 2008 at 1:39 am
Anyone know how to do this if you are using annotations? Then the multiple mappings file part doesn’t make sense. I’m trying some things now. All I really want to do is add some persistent fields, not modify or delete any columns.
November 6th, 2008 at 8:01 am
Excellent writeup, thanks!
Might as well mention LiquiBase while I’m here. I haven’t tried it, but it looks promising as a generic and feature-rich schema migration tool.
January 10th, 2009 at 8:28 pm
hi
12eatsm6u0gvyizn
good luck
October 12th, 2009 at 7:07 pm
Hi there!
Thanks for sharing your thoughts!
I’m searching for a way to update my database schema without the hibernate SchemaUpdate Tool cause – as you say – it can’t drop tables or columns, and also it won’t work on unix systems for me
February 17th, 2010 at 3:15 am
Can anyone reassure me that converting my site from Rails to Spring is a good thing. I don’t understand why Spring hasn’t created a de facto standard for incremental schema updates. I miss rails already and need a pat on the back. Grrrrr…
May 2nd, 2011 at 8:47 pm
@Scott: if you go from Rails to Java I assure you there will be suffering. Not being a Rails, but a Django developer, I find everything related to ORM and migrations to be a pain in the Spring/Hibernate world, compared to how nicely this works in Django using South. I expect it will be the same for you.
July 15th, 2013 at 6:42 pm
A look at the Jack, Samsung’s latest expedition into the business professional market for Windows Mobile smartphones. One afternoon I tore my clothes to shreds after having arrived too late for an audition and a barman who served me later on in the day asked me if I’d been
involved in a fight. The only exception to this similarity in the range is the new “cough tablet”.
My blog ?may tinh bang gia re
September 22nd, 2013 at 6:57 pm
NHibernate | jaBlog says:[…] Incremental Schema Upgrades using NHibernate […]