Updating schema of production database with NHibernate and adding default data

Question

I'm looking for some ideas on how other people are managing the following situation - I've got some ideas but they seem a little messy and I can't help thinking I'm missing something.

Here's the situation:

• You're using NHibernate with Fluent NHibernate for mappings.
• You have an application in production with a database with live data
• You're adding a new feature in development and it requires a new database column.

The new column cannot be blank. For example, I recently had to a DateCreated column to a table and the app now uses that date. As the only time the data will be missing is now, it seems unnecessary to add code to check for errors.

In my application I have an updater which can execute a SchemaUpdate to add the new database column - however, the application will starting crashing as it is expecting a value in the new column.

I need to get some sensible default data into that column. In this case I manually ran an Update to set the date to the current date (good enough for the situation). In this particular case I believe that you cannot set the column default to getdate() using fluent mappings.

My Idea

• Keep a schema version number in a config file
• In the updater that runs SchemaUpdate start adding upgrade methods for each version. These methods would run updates to add default data (or other required actions).
• After the schema update has been run, call all methods required for a version greater than the current version (i.e. those that haven't previously been run). So if the app is now version 4 and version 2 is installed, methods 3 and 4 would be run.
• Update the saved version number

How are other people handling this situation?

Answer 1

Use DBDeploy (http://dbdeploy.com/)

The way this works is: * Create a bunch of serially numbered alter scripts to create your schema * Tell DBDeploy to create the script to apply. * DBDeploy will look at your alter scripts and the db to apply the scripts to and create a consolidated script which can be run against the production DB. * DBDeploy maintains a list of applied scripts in the production DB.

Also take a look at http://goforthandcode.blogspot.com/2007/12/brief-history-of-database.html if you need a bit of an explanation of alter scripts

Disclosure: I work for ThoughtWorks and some of my colleagues created DBDeploy and DBDeploy.Net

Answer 2

For solving data base versioning problems there are a lot of different frameworks. I'm using MigratorDotNet. It is open source and provides really simple API. MigratorDotNet does the same thing that you want to, but stores data base version in special table that will be created on the first run.

Also it is created as a console application and that is why you can use it on your CI server very easily.

Answer 3

Use SchemaUpdate, a feature of nHibernate.

For reference data, I use simple importation code from standard formatted data (such as CSV). When the application is updated, new data is imported from those files. The logic update the data only if it has changed or is new. For that I have an extra column which is ModificationDate.

I keep new reference data in a zipped file with another extension to avoid confusion (.dat).

It works very well, and I used it in small desktop application and large enterprise projects. Version of the database is stored within the database in a table called DatabaseSetting.