How to update database table schemas with NHibernate schema generation?

Question

I'm trying to figure out how to use NHibernate configuration with mapping to update table schemas, rather than dropping and recreating them.

Currently I'm using the NHibernate.Tool.hbm2ddl.SchemaExport obj with FluentNHibernate to generate the database schema for a mysql database. While I can't say it's a huge problem, whenever I call SchemaExport.Execute on the database, it's going to drop all the tables and then recreate them.

What would be way cooler is if I could just have it update the existing table structures retaining data where possible. But I don't really want to use a commerical product, or a code generator, because I don't like code generation in general, and I don't need this enough that I would consider paying for it. So hopefully any answer would keep these caveats in mind.

Solution

The SchemaUpdate object provides database schema updating, by apparently generating and executing a series of SQL UPDATE statements (as well as constraint statements) when it's void Execute(bool script, bool doUpdate) function is called. The SchemaUpdate class is in the NHibernate.Tool.hbm2ddl namespace, which can be found in the Nhibernate.dll file.

SchemaUpdate is mentioned in chapter 15 of the nhibernate 1.0.2 toolset guide, here (section 15.1.5).

"The NHibernate FAQ" had (link now expired) a more complete example of how to use SchemaUpdate:

[Test]
public void Update_an_existing_database_schema()
{
    _cfg = new Configuration();
    _cfg.Configure();
    _cfg.AddAssembly(Assembly.LoadFrom("DataLayer.dll"));
    var update = new SchemaUpdate(_cfg);
    update.Execute(true, false);
}

Comments

[c.sokun]

we can't modify the setting of field once created for instance modify string length from nvarchar(255) to nvarchar(45), can we?

[Mark Rogers]

That's a good question, the create version of hbm2ddl would allows you to do this, but I've noticed sometimes I have to drop the tables before the create will run successfully.

[Contango]

This works brilliantly, this technique is also suited for Fluent NHibernate.

[Astaar]

I can confirm that at least on SQL Server (and therefore on Azure), the fields are not updated, you have to manually do it.

[Algoman]

@Astaar: do you mean that changing from nvarchar(255) to nvarchar(45) doesn't work? or do you mean that schema update doesn't work at all?