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!
August 29th, 2006 at 6:02 am
Thanks for the info.
October 17th, 2006 at 10:37 pm
[…] 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
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.