Using Ruby to Manage Sql Server db creation

One of the things I hate about programming is dealing with the database. There are just so many things about it that cause more pain than pleasure.  The rise of ORM’s has gone a long way in making that better, but we still have a ways to go.

One of the problems with developing against databases is that unless you go out of your way, there it’s difficult to include in your source version control repository.  The “best” way in the .NET world has been generating your sql server creation scripts and go from there.  I didn’t feel like this was a good solution, though, especially since the code that is generated utilizes a lot of SQL that I don’t understand, nor have any desire to learn.  So I set about trying to find a better way.

The first constraints I decided upon were pretty simple:

  1. There needs to be a way to include the database schema in a text file that can be versioned with the rest of the code.
  2. The text file should be able to be transformed into a database (i.e. executable).
  3. I should be able to insert base data into the system.  For example, if I want to populate a Settings table with some default values.
  4. It would be able to completely reset the database to a pristine state.

Being a previous Rails developer, active_record migrations jump to mind, but I ran into problems pretty quickly.  As it turns out, active_record doesn’t respect foreign keys, which is what the Entity Framework uses to find relationships.  Active_record was a no go.  I decided to stay in the ruby world, though, and check out what else they had for me.  After some searching, I found Sequel.  So far, it’s worked pretty well.

I decided not to go the migrations route, because that didn’t seem to really fit my development pattern.  Instead, I’m generating the entire database at once.  This may turn out to be the wrong decision.  Currently, here is where I”m at:

There are quite a few problems with this code, but it’s got me on a path I’m feeling pretty good about.  Since it’s all in ruby, this means I can easily integrate it with Albacore build system (for .NET).

Okay, so this is pretty standard stuff in the ruby world.  So how well does this fit in with the Entity Framework?  Pretty well so far.  I can run that script above and then right-click and choose “Update Models” from Visual Studio.  The models are updated, and I’m ready to go.

I’m going to continue working on this, but I am really starting to feel like I’m on the right track here.