a little madness

A man needs a little madness, or else he never dares cut the rope and be free -Nikos Kazantzakis

Zutubi

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:

// manually setup the hibernate configuration
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:

<class name="com.zutubi.pulse.model.User" table="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.

<class name="com.zutubi.pulse.model.User" table="USER">

  <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:

Dialect dialect = Dialect.getDialect(props);
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!

Liked this post? Share it!

12 Responses to “Incremental schema upgrades using Hibernate”

  1. August 29th, 2006 at 6:02 am

    Tom says:

    Thanks for the info.

  2. 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. […]

  3. 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!

  4. December 8th, 2006 at 12:05 pm

    Daniel says:

    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.

  5. September 14th, 2008 at 1:39 am

    Tristan says:

    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.

  6. November 6th, 2008 at 8:01 am

    Adam Monsen says:

    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.

  7. January 10th, 2009 at 8:28 pm

    Lucretia Blanchard says:

    hi
    12eatsm6u0gvyizn
    good luck

  8. October 12th, 2009 at 7:07 pm

    Kostenlose Kreditkarte says:

    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 :-(

  9. February 17th, 2010 at 3:15 am

    Scott says:

    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…

  10. May 2nd, 2011 at 8:47 pm

    Carles says:

    @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.

  11. July 15th, 2013 at 6:42 pm

    ?may tinh bang gia re says:

    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

  12. September 22nd, 2013 at 6:57 pm

    NHibernate | jaBlog says:

    […] Incremental Schema Upgrades using NHibernate […]

Leave a Reply