NHibernate and database changes/deployment

Question

I'm looking into using NHibernate and Fluent NHibernate for my next project but one thing I'd like clearing up before hand is how do you manage changes to the database?

eg, I have NH + FNH working, application has been deployed and is live but we make a change in the development environment, for example say we add a new property to a entity which is mapped using NH/FNH.

How do you apply that change to the database without having to drop the table?

Thank you GE

Answer 1

AFAIK , you need to do it manually. You can export your schema in each initialization , but you can't alter your tables automatically. Because the schema will only contain the create statements.

Maybe , you might use a tool like Red Gate , to find your database differences , and could create the statements for you automatically. The idea is ,

-Generate the schema on a test database
-Run the Red Gate tool , and find the DIFF on schema level.
-Create the diff script automatically
-Apply the diff on your main db

Answer 2

Whatever ORM tool you are using, if you make schema changes, you are going to have to deploy code and schema changes simultaneously. For our project, this means that for each release that contains schema changes, we write a sql script that is used to update the database. That script is run by our installer at the same time the code that contains the new hibernate mappings is deployed to the server, and the new application isn't started until the schema modifications have completed.

Using that technique, we have been able to alter tables, drop tables, add tables, and migrate data from one table to another without any major issues. Having the script to perform the database modifications allows you to test it thoroughly before deploying into production.

Answer 3

Thank you guys for your replies.

GE

Answer 4

I have very good experience using this framework:

http://code.google.com/p/migratordotnet/

Basically you create a class for each database change and annotate the class with a timestamp. The final assembly is then applied in timestamp order.

This means that during development it becomes easy after each checkout to just blindly run the db upgrade process, and know that you are synchronized with the world.

using Migrator.Framework;
using System.Data;

namespace DBMigration
{
    [Migration(20080401110402)]
    public class CreateUserTable_001 : Migration
    {
            public void Up()
            {
                    Database.CreateTable("User",
                            new Column("UserId", DbType.Int32,
                            ColumnProperties.PrimaryKeyWithIdentity),
                            new Column("Username", DbType.AnsiString, 25)
                            );
            }

            public void Down()
            {
                    Database.RemoveTable("User");
            }
    }

Answer 5

This is the process I've worked out. I think its fairly slick.

The process is something like this, supposing you have a db called 'MyDb'

1. Make your changes in your model and NH mapping
2. Run a script to drop then recreate a db called MyDb_Next, and to execute NH SchemaExport to write the new schema to it
3. Use my tool MigrationScriptGenerator to compare MyDb and MyDb_next. This will generate your change scripts (or as much of it as is possible) by comparing the schema and creating statements to add new columns etc. The script gets saved into a xxxx - updatescript.sql.suggested file in a VS project
4. Fix the suggested sql script if necessary (it needs human input when it detects changes that could lose data), and rename to .sql (so it can be picked up in the next step)
5. A post build event on the project containing the sql scripts runs my tool simplescriptrunner this applies the sql script to MyDb, bringing it up to date

You just repeat this process whenever you change your entities. I generally store my SQL scripts inside a .csproj, and have a post build event that runs simplescriptrunner. This way, any other developer who does an update and rebuild will have the latest version of the db generated on their machine from the checked in numbered scripts.

MigrationScriptGenerator uses OpenDBDiff (an open source tool similar to Red Gate SQL Compare) to do script generation. You might find that useful if you want to create the scripts yourself.

PS these tools only work with SQL Server - sorry!

Answer 6

NHibernate supports a SchemaUpdate class that can make some schema modifications, but not all. Some more details here:

http://stackoverflow.com/questions/366176/how-to-update-database-table-schemas-with-nhibernate-schema-generation/602822#602822

Answer 7

http://www.headspringsystems.com/community/open-source/tarantino/