I am new to the concept of migrations but am really liking the whole concept.

“Migrations represent a “non-violent” way, basically, to transition the schema of your database as your app grows.“ – Rob Conery from ”Musing on Simpler Migrations”

Having seen the way a schema grows after several releases of a product it can be a lot to manage. Traditionally, I have kept a folder of scripts for a clean install and a folder of scripts for upgrading from previous versions. However this approach requires testing and verification that there is rarely time to perform in the environment that I currently find myself. Did I mention that we also have to keep all the scripts in an Oracle and SQL Server flavor? Egad!

After fighting with this for a few years, it was time to take a step back and evaluate my options. Two things happened that helped me forge a new path. The first is that I came across Rob Conery’s blog and in particular the Manatee tool that he has written. Rob has been bringing the best of what the Ruby world has to offer to .Net developers as part of his TekPub series “Hacking WebMatrix”. The second was the announcement of a new version of SQL Server CE that supported multi-threaded web environments. By delivering a database solution we were no longer bound to support any platform our customers happened to be using.

Now that we have a plan let us see how it all fits together.

Using Manatee

Step one is to visit GitHub and pick up the latest cut of Manatee. If you are unfamiliar with Git you can download a zipped package of the source by clicking on ‘Downloads’. Once you have pulled the repository or unzipped the source, double click Manatee.sln to open Visual Studio. Press F5 to build and you have a command line tool to manage your databases schema.

“Sounds great but how do we build up the schema?”, you ask. Enter the migrations file.

Manatee uses a JSON object as the medium for describing each migration. Each file contains a definition of how to move the schema up and down a version. These definitions are found in the ‘up’ and ‘down’ properties of the migration’s JSON object. In simple cases, such as creating a table and adding a foreign key, the DOWN can be inferred from the UP and is not required. Each migration file contains a single change to the database. A single change being altering a field name, or adding a foreign key constraint. A migration file can also contain an execute script allowing up to insert default values into the database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
    up:{
        create_table:{
            name:'Project',
            columns:[
                {name:'Name', type:'string'},
                {name:'Description', type:'text'},
                {name:'Instructions', type:'text'},
                {name:'URL', type:'string'}
             ],
            timestamps:true
         }
    }
}

The above will create a new table named Project. There is an implied primary key named Id. Since I set the timestamps flag to true, fields for tracking created and updated dates will also be created automatically.

Making Manatee work with SQL Server CE

I ran into a few snags when building my database with Manatee. Luckily we have the source and are free to make changes as needed.

One issue I ran into is that Manatee will not create a SQL Server CE database for me. I could use the SqlCE Toolbox in Visual Studio but that just seems like too much work for me.

The first step to fixing this is to reference the SQL Server CE assembly in the Manatee project. I fired up NuGet and it quickly got me registered.

Next, I added a check to the SetupConnectionAndFactory method of the Migrator.cs. It looks at the DataProvider specified in the Manatee.config to see if we are using System.Data.SqlServerCe.4.0. If it is then, we will create a new database if we are unable to verify the connection string. (I am aware that there are several problems that will cause verification to fail other than simply not existing, but this will work for us for the time being)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

private void SetupConnectionAndFactory(string connectionStringName)
{
    if (connectionStringName == "")
    {
        connectionStringName = ConfigurationManager.ConnectionStrings[0].Name;
    }

    if (ConfigurationManager.ConnectionStrings[connectionStringName] == null)
    {
        throw new InvalidOperationException("Can't find a connection string with the name '" +
                                            connectionStringName + "'");
    }

    var providerName = "System.Data.SqlClient";
    var connectionStringsSettings = ConfigurationManager.ConnectionStrings[connectionStringName];

    if (!string.IsNullOrEmpty(connectionStringsSettings.ProviderName))
    {
        providerName = connectionStringsSettings.ProviderName;
    }

    _factory = DbProviderFactories.GetFactory(providerName);
    _connectionString = connectionStringsSettings.ConnectionString;

    if (providerName == "System.Data.SqlServerCe.4.0")
    {
        InitializeDatabase();
    }
}

private void InitializeDatabase()
{
    var engine = new System.Data.SqlServerCe.SqlCeEngine(_connectionString);

    if (!engine.Verify())
    {
        engine.CreateDatabase();
    }

    engine.Dispose();
}

After setting this up I ran into an issue creating foreign keys. SqlCE does not support the WITH NOCHECK clause when adding the constraint. The offending code is in the GetCommand method of the Migrator class. One small edit later and now I can add constraints.

1
2
3
4
5
6
7
8
9
10
11
12
13

...
if (op.foreign_key != null)
{
    string toColumn = op.foreign_key.to_column ?? op.foreign_key.from_column;

    var sql = @"ALTER TABLE {1}  ADD  
CONSTRAINT [FK_{1}_{0}] FOREIGN KEY ([{3}])
REFERENCES {0} ([{2}]);";

    sb.AppendFormat(sql, op.foreign_key.from_table,
        op.foreign_key.to_table, op.foreign_key.from_column, toColumn);
}
...

Why Run from ./Bin/Debug?

It seems that Manatee is envisioned to run from bin/debug. It looks for the Migrations folder two levels up from the current directory. I can see why you might want to leave it this way, but I would prefer for the Manatee to run in the same directory as the Migrations folder.

We need to update the LocateMigrations method in the Program class. We will change it to use the current directory instead of the it’s grandparent.

1
2
3
4
5
6
7

static string LocateMigrations()
{
    //this is the current directory
    var currentDirectory = new DirectoryInfo(Directory.GetCurrentDirectory());
    //return the Migrations directory
    return Path.Combine(currentDirectory.FullName, "Migrations");
}

Time to Get Started

With that change I am done for now. Manatee creates SqlCE databases and foreign keys now. I still need to put it through it’s paces, but I think I have a good start.

This post originally appeared on The DevStop.