Managing database migrations is an important part of a project. Everyone on the team needs to be on the same page when it comes to the database, while still having the ability to make database changes when necessary.

I've used a variety of tools to do this on various projects, but one tool that I especially like is Fluent Migrator.

The idea with Fluent Migrator is to create a series of classes that create and modify the database schema (and data). Each class represents a single "migration" (in the sense that you are "migrating" from an empty database to a database with something in it). Here's an example of a migration that I might create if I were creating a table to store user information:

Several things to note:

Fluent syntax. This just means that you can chain stuff together in a natural and fluent way (see NDecision, Fluent NHibernate, Fluent Validation, etc for other examples). This was kinda a "trend" for a while in C# projects, but it does make sense in a number of contexts. The goal is to make the API discoverable and easy to use and also to make the code easier to read. Note that there are some sensible defaults when you create columns, but it never hurts to be explicit.

Migration attribute and Migration base class. This attribute and base class needs to be on each migration class. The number in the attribute determines the order in which the migrations will be executed (we only have 1 so far, but the next one would be 2, for instance).

Up and Down. Override the "Up" method to put in the code that will create/alter the database. Override the "Down" method to put in the code that will revert the changes made in the "Up" method. In some cases, it may not make sense to create a "Down".

Okay, let's say I create that migration, execute it (more on how to execute it later) against a database, write some code that uses that table, and check everything in. The next day, I learn that I need to create an optional "url" field for users. To do that, I'll create another migration class (I'm not editing the existing migration).

These migrations get compiled into a plain DLL, so to actually execute them against a database, you'll need some sort of migration runner. I've been using the command line runner, but there are runners for NAnt, MSBuild, and Rake available too if you use any of those tools on your build server to automatically deploy the database migrations alongside your application.

My next step usually involves creating some convenience batch files to use the command line migration runner. You can use these locally while you develop, or possibly call them as part of your build process. Here's an example of a set of batch files that I use to "Up" my local database to run all the latest migrations and one to "Down" my migration back a number of steps (both of these operations are very common while developing).

I put these in the project, but I always have them deploy to the bin folder (where the compiled migrations are). You could do it vice versa if you want.

So that's it. Works against SQL 2012 and SQL Azure, in case you were wondering.

One other thing I do for the sake of organization is that I name all my migration files like NN_MigrationName. When I look at the list of files, I'll see them in migration order. Here's a screenshot from a real project of mine:

My friend Mark Greenway introduced me to Brandoo WordPress recently. I had never heard of it, so I thought I would share with you.

I've heard of WordPress, of course. I taught a class where all the labs used WordPress on Azure for three semesters at Capital University. Brandoo WordPress is a branch of WordPress that is designed with Windows Azure in mind. It uses a Microsoft SQL/Azure SQL database instead of MySQL. And, best of all, it's available in the Windows Azure gallery for an easy install.

Why would you want to use Brandoo instead of plain WordPress? On Azure, MySQL is available through ClearDb. Although my experience with plain WordPress and ClearDb has been pretty good, Azure SQL is a first-class citizen of Azure, with built-in scaling and tooling right through the Azure dashboard.

It does appear as though the Brandoo installation through the Azure gallery is a slightly older release of WordPress. But since WordPress has a self-updating feature built in, that's not really much of an issue.

So, if you use Azure and WordPress, consider giving Brandoo a shot.

Welcome to another "Weekly Concerns". This is a post-a-week series of interesting links, relevant to programming and programmers. You can check out previous Weekly Concerns posts in the archive.

• If you are having trouble understanding how SQL joins work, check out this diagram on SQL joins for a useful visualization.
• Sometimes it's hard keeping up with security issues with all the software you have installed on your computer(s). I've been using the (free) Secunia PSI (Personal Software Inspector) for years--it keeps me up-to-date on the latest security exploits and can automatically update your software for you.
• TwitchPlaysPokemon - an experiment to "crowdsource" gameplay. Imagine 60,000+ people using the same controller. This is interesting for its entertainment value, of course, but also a study of emergence. A critic of emergence might point to this as a failure. But I think it demonstrates that emergence might not work if there aren't any real constraints. If each user was limited to maybe one command (per hour, maybe), or there was some sort of majority vote on each command, or some constraint like that, I think the resultant gameplay would be much more interesting. As it stands now, it's probably not any different from just randomly hitting buttons. EDIT: As of today, there is a "democracy/anarchy" feature that allows users to introduce constraints, which appears to be making the game run much smoother.
• Maybe RSS is going out of style, but I still use it every day. If you wish a web site had an RSS feed, but it doesn't, you might want to check out Feed43 (Feed for Free). It's a scraping tool that I use from time to time when nothing else is available. And remember that Cross Cutting Concerns has an RSS feed.
• More Pete on Software, as he continues his blog series on PostSharp. This time he explores the automated design patterns feature for background threading that comes with the full commercial version of PostSharp.

If you have an interesting link that you'd like to see in Weekly Concerns, leave a comment or contact me.

I just open-sourced a tool that I wrote that I've been using internally at Zimbra (previously Telligent) for almost a year. It's an ugly little tool that I wrote out of frustration, but I've gotten so much use and time savings out of it that I thought I would share it. It's called SQL Profiler Query Cleaner.

SQL Profiler is a great tool to show you exactly what SQL is being executed, along with the parameters. This is especially useful when debugging, because sometimes you aren't sure what SQL is being executed. However, often times I will find myself having to pull that SQL+parameters out of SQL Profiler and paste it into SQL Server Studio in order to analyze, tweak, debug, etc. However, notce the output of SQL Profiler (bottom part of the screenshot):

The SQL string is there, but there are escaped characters. The parameters and arguments are there, but they're first declared, then assigned, "doubling" the syntax. It executes fine in SQL Studio, but if there's a syntax error, you can't really pinpoint exactly where in the query the error is, because you have to unescape the strings, manipulate the parameters (in two spots), and it's just time-consuming if you're doing this a lot (which I was).

So, fire up Sql Profiler Query Cleaner, paste the SQL Profiler output into the top text box, click the "Clean" button, and the bottom text box will have a more SQL Studio friendly query. I pasted the query from the above screenshot, and this is the result:

Please let me know if you find this useful. My guess is that if you use SQL Profiler a lot, this tool will save you some time.