Speaking on SPb ALT.Net with overview of database migration tools

On this week (February, 11) I made an overview of database migration and versioning tools on SPb ALT.Net meeting. Materials and details can be found here. There were several tool concerned:

• Tarantino.Net and Red Gate SQL Compare (http://code.google.com/p/tarantino)
• Migrator.Net (http://code.google.com/p/migratordotnet)
• Subsonic project(http://subsonicproject.com)

Lets briefly summarize advantages and disadvantages of the mentioned tools.

Tarantino.Net (used in http://code.google.com/p/codecampserver open source ASP.Net MVC project) – uses NHibernate schema export feature and SQL Compare tool for generating update scripts. Advantage of such approach is that developers don’t work directly with SQL when they need to synchronize modified model (expressed in c# code) with db schema as Tarantino will make it automatically. And this is really great feature. But in real world there can be situations when NHibernate schema export don’t provide exactly desired result by default and in this case some efforts should be made to fine tune it (e.g. if return to examples shown on meeting – NHibernate generated nullable column for decimal value type – but more desired behaviour was generating of not-nullable column with default value). Also NHibernate schema export affects those entities which are persisted with NHibernate and if you need to modify schema of table which is not included in ORM (or you use several ORM frameworks in your project) then you will need to do extra efforts also.

Migrator.Net supports code-first approach. Thus you have compile time checking and more control over resulting SQL schema as all migrations are written by developer via c# code. Migrator.Net has a good and intuitive API which helps to write migrations. But with this you will have more manual work as all database assets should be described in code one by one. Also it have some bugs (what program doesn’t?) – if return again to examples on meetings there was little confuse when Migrator.Net failed to downgrade db to version = 0 (version of database without schema) because it couldn’t drop table “Order” because Order is a keyword in SQL (yes we tried “[Order]” also without result :) ). Nevertheless Migrator.Net made an impression on me as nice and intuitive tool.

Subsonic has the same idea as Migrator.Net – it uses migrations written in c# code to change db schema. Subsonic has command line utility sonic.exe to perform migrations (for Tarantino.Net and Migrator.Net I used nant tasks). So it also has advantages of code-first approach. But for me its API became less intuitive comparing with Migrator (Table.AddColumn, DataService.GetSchema().AddColumn() or simple AddColumn() – what should I used when just want to add column? Only one of them worked in my sample migration – simple AddColumn(). And to investigate what went wrong I forced to use SQL profiler – because sonic.exe didn’t show necessary output diagnostics information). I saw the same confusing questions in forums also. Also it should be mentioned that current verion 3.0 doesn’t have sonic.exe utility in distributive package (the latest version which contains migration utility is 2.2 built on .Net 2.0). Of course mentioned disadvantages may be caused by my poor experience in Subsonic and there are other ways to do migrations in 3.0 version.

One important thing which was mentioned on meeting is that all mentioned tools are suitable for scenarios with “flat” database schema lifetime (i.e. when versions are increased in one direction). But in real life there are often scenarios when there are multiple branches of db schema (e.g. for different set of features or for different customers) which are developed in parallel. And these tools do not greatly help to address these situations – they help to migrate schema only within single branch. And additional efforts should be performed to migrate schemas from different branches.

As result of comparing mentioned tools I made a decision that advantages of Tarantino.Net is more valuable for me and I will use this tool for automation of db migrations in my work. Concluding I would also thanks all guys from SPb ALT.Net for organizing this meeting.