Do Fluent NHibernate and migratordotnet play nicely together?

Question

I love Fluent NHibernate for building my DBs and so far haven't found a restriction that has halted me in my tracks.

However on my current project I expect to release to production very early in the product lifecycle and hence expect there to be many small changes in the db schema as we progress.

I'd like to track these DDL amd DML changes in "migrations", using a tool like migratordotnet. But my question is: Is it possible to get these two tools (or similar tools) working together?

In the spirit of DRY, how can I derive my schema changes from my mappings in Fluent Nhibernate? Is this possible?

Or is a better approach to leave schema generation to a tool such migratordotnet and leave Fluent NHibernate with the responisibility of mapping only? Hmm, this does seem like a better seperation of concerns at a tool level.

Cheers!

Answer 1

I'm also running into this same issue, where I don't want to have to maintain migrations (using migratordotnet) and my mapping files independently. The only helpful thing I've found so far is NHibernate's SchemaUpdate, but that doesn't handle deleting columns or tables. For those types of changes you would still need to hand write a migration. Right now I'm leaning towards using migratordotnet exclusively for database changes instead of mixing SchemaUpdate generate DDL and migrations. This still seems error prone though, as you could incorrectly convert your mapping/domain layer changes to migrations.

Answer 2

Gordon,

I've had the same question on previous projects and we've settled on using migratordotnet exclusively for our database migrations and just skipping SchemaUpdate altogether.

I will still use SchemaUpdate for quick prototypes but once I start the project in earnest I only use migratordotnet.

With the migrations setup to run as part of our Nightly builds migratordotnet works very well once we have more than one person working on a project.

Answer 3

I'm facing the same issue. Here is my current workflow that avoids SchemaUpdate:

• build database from scratch (if any changes made)
• keep all reference data in spreadsheets
• reload all data through special command line 'dataloader' project

This workflow is good for development, as rebuilding and populating the DB from scratch every time solves the database version control issue. It also provides a good basis for testing around your DB and the actual data you are inserting.

Obviously this is not going to work in production once a live database is running that cant be dropped and rebuild willy-nilly. This is what I'm doing:

• Once a dev cycle is finished, I take a clone of the production database. Prod database is made read only for upgrade duration (ok for my app...not sure about more time critical apps)
• Use SQL Compare to migrate the changes and data across from dev to the prod database
• push this to test.
• If test is passed, push to production.

This is not ideal and makes use of the commercial SQL Compare product. It has been working for me but I'd like to hear some better ideas.

Answer 4

Yes - mostly. I am successfully using used both FNH and migratordotnet for a thick desktop client and it works quite well. I had to modify it for two things:

1. To allow the sql connection to be injected into the TransformationProvider (or more accuratelly, the SQLiteTransformationProvider)

2. rip out the references to the other non sqlite TransformationProviders as when I tried to package it with my app it would throw various errors about being unable to find Oracle, Postgres etc.

3. This is sqlite specific - hack it in order to better parse sqlite create table statements. Unfortunately it is unable to handle create table statements of the form CREATE TABLE FOO(id INT, ..., primary key(id)) (as opposed to CREATE TABLE FOO(id INT PRIMARY KEY, ...) which it does handle). Combine this with the way it performs column deletes (create new table sans column, transfer data over, delete original and rename new one to original), this means you can get pretty nasty behaviour like column deletion on a table making your primary key column a non-primary key column.