What is the best practice for migrating a database to support a change to schema?

For example, an application is deployed with a Hibernate version of A; where the database managed by Hibernate is also, implicitly so, version A, too. Changes are made to the schema requiring a new version of the application be deployed. Now existing installs of older versions with version A of the schema want to update their application; but they do not want to lose all their data. What is the best strategy for updating a physical database, managed by Hibernate, from one schema to another?

I suspect that we need to create a connection to the DB via JDBC and update the database schema directly. However, given that we do not directly manage the database the SQL necessary to migrate from version A to version B is not immediately obvious.

I appreciate this is a non-trivial activity. However, what approaches have people found to work best -- minus the obvious "don't change the schema".

Thanks,
Ward

---

In the interim I have come across HBM2DDL which is a start at getting the DDL statements for a given schema version. There is also LiquiBase which appears to be an alternative to HBM2DDL.

How does these tools rate? Gotchas using them?
[/url]

use DbUnit. There is a way to use Maven 2 + Maven-hibernate-plugin +DbUnit. It can export all data in database into xml file and reload into a new database easily.

I have used hbm2ddl. It is easy to use and generates ddls based on the hbm files. You can generate create and drop ddl and save it to a file or directly export the schema to the database. You can invoke hbm2ddl by setting a property in the hibernate configuration file or by writing an ant task to suit your needs.

You may also want to take a look at schemaupdate. I have not used it though.

This tool appears to en masse copy data from a DB to XML and then back again; it does not appear to support the updating of a DB schema, though.

How do you propose using DbUnit in relation to updating a DB schema?

We adapted rails for all db schema migrations.

I am convinced it is the best way to keep all your various databases in sync.
As long as you follow the rule that you only EVER migrate the database through rails you have an instant view through the 'schema_info' table as to what version each database is on.

No longer are people writing adhoc migration scripts or doing things directly to one db and then can not remember the next day how to do it again to another db.

It is dead easy to set up especially if you are on linux.
Give it a try.

hbm2ddl appears to be for development purposes only. At least, in this post here there is mention not to use it; or if you do then to test appropriately. Naturally, before we'd deploy a new build of our app we'd go through the necessary testing to ensure schema updates do not corrupt data.

My one question that I was going to ask, but I believe I have answered for myself is the following: "In order to update a schema, a delta (current - prior schema) is required. HBM2DLL appears to specify only the current schema; how does it know about the prior schema?"

My understanding is that hbm2ddl relies upon JDBC meta to determine the schema in the physical db (aka prior schema). Given that we pass to it the current schema the tool then can compute the delta schema and enact changes needed to migrate the prior schema to the current schema. Is my understanding correct?

Are we talking about Ruby on Rails? If so, can you elaborate as I do not have sufficient familiarity with Ruby to see the connection with schema migrations.

Thanks!
Ward

Yep we certainly are.

Where to start....

You obviously need to download Ruby on rails.
If on a windows PC then look here

You will need the relevant driver.

Once you have created a new rails project you edit your config/database.yml file.
Here is an Oracle version for the development connection:


In this file you can specify any number of connections to say 'live', 'test' etc...
'development' is the default that is run if no RAILS_ENV parameter is set.

An example migration might be to add a new column to a table. EG.



This file was created by the command:
script/generate migration add_version_to_groups_table.

This then created a file in db/migrate directory called nnn_add_....
where nnn is the next migration.

To run the migartions you have a number of options.

rake db:migrate - will run all the required migrations
rake db:migrate VERSION=3 will run the migrations either fwds or back to '3'
rake db:migrate RAILS_ENV=prod will run the migrations on the 'prod' database as defined in the database.yml file.

My mates blog Mr B is very useful for some other tips.

Hope this helps.
It really is the future in my meagre opinion.
google for more info.

Bill,

The use of Ruby does appear interesting. For us, all our schema changes are handled via annotations to our Java classes. We do not explicitly control the database schema, we control the object/relation schema. That said, I do not see how I can use this (yet). I will need to research more on Ruby to see its merits in context to our needs; however, as a general comment it certainly appears to be going in the right direction.

Thanks,
Ward

no worries.

I have just changed jobs & they also do all their migarions by hibernate config.

It maybe that I am used to the rails way but I prefer it.

It allows you to see how the d/b has grown biologically.
It does have the disadvantage in that it is disjointed from the java so I take your point.

Give it a play tho. It is fun.

While steering off-topic, I do concur that off-loading to Hibernate or another ORM can obscure some really hard edge conditions; namely, managing the database schema. Frankly, by and large the use of Hibernate has really helped us with our velocity. But, I always worry whether we will get to a place that will require us to get really deep under the hood (i.e. back in the physical database) such that we are so disconnected from it that we will have a hard time maintaining it.