Named Dbup Code Scripts

I’m a big fan of the DbUp library and its approach to database management. Rather than go over all the benefits of this approach, I’ll leave it to the docs which do a much better job than I could. Suffice it to say, all the things it brings to the table - tight control, predictability, ORM independence, the power of raw SQL - are all good things!

Historically I have been able to do most things with this raw SQL approach. I’ve created these scripts with a fairly typical (I think) naming convention like so:

0001 - This is a script.sql
0002 - This is another script.sql
0003 - And this is another script.sql

These scripts are then excuted and applied in the expected order:

Id          ScriptName
----------- -----------------------------------------------------------
1           Project.Data.Scripts.0001 - This is a script.sql           
2           Project.Data.Scripts.0002 - This is another script.sql     
3           Project.Data.Scripts.0003 - And this is another script.sql

Recently I required a bit more logic than usual, so updated my project to use the EmbeddedScriptAndCodeProvider so that it could utilise some code-based scripts, like so:

0004 - Code Script.cs

This is fine for a filename but not so much for a C# class, which by default gives us _0004___Code_Script (or a similar, conformant name). When executed this gives us:

Id          ScriptName
----------- -----------------------------------------------------------
1           Project.Data.Scripts._0004___Code_Script.cs
2           Project.Data.Scripts.0001 - This is a script.sql
3           Project.Data.Scripts.0002 - This is another script.sql
4           Project.Data.Scripts.0003 - And this is another script.sql

Not the order I wanted as the type name is used! I can rename the class some more and defer the problem to later if I really wanted:

Id          ScriptName
----------- -----------------------------------------------------------
1           Project.Data.Scripts.0001 - This is a script.sql
2           Project.Data.Scripts.0002 - This is another script.sql
3           Project.Data.Scripts.0003 - And this is another script.sql
4           Project.Data.Scripts.0005 - Back to sql scripts.sql
5           Project.Data.Scripts.Script0004_CodeScript.cs

From what I can tell a more common approach is to name scripts not just with a number, but with an additional prefix like this (taken from the DbUp Sample Application):

Id          ScriptName
----------- ---------------------------------------------------------
1           SampleApplication.Scripts.Script0001 - Create tables.sql
2           SampleApplication.Scripts.Script0002 - Default feed.sql
3           SampleApplication.Scripts.Script0003 - Settings.sql
4           SampleApplication.Scripts.Script0004 - Redirects.sql
5           SampleApplication.Scripts.Script0005ComplexUpdate.cs
6           SampleApplication.Scripts.Script0006 - Transactions.sql

I didn’t want to mess about trying to rename existing scripts, and I’m also a bit pedantic and want them to have a similar style of naming, even if they are tucked away in a database table out of sight.

I wasn’t able to find an existing solution to this, so decided to jump in to the DbUp code and have a look for myself. The script provider in question gives us:

private IEnumerable<SqlScript> ScriptsFromScriptClasses(IConnectionManager connectionManager)
{
    var script = typeof(IScript);
    return connectionManager.ExecuteCommandsWithManagedConnection(dbCommandFactory => assembly
        .GetTypes()
        .Where(type => script.IsAssignableFrom(type) && type.IsClass)
        .Select(s => (SqlScript)new LazySqlScript(s.FullName + ".cs", () => ((IScript)Activator.CreateInstance(s)).ProvideScript(dbCommandFactory)))
        .ToList());
}

As you can see the full name of the type is used.

I decided the quickest solution for myself would be to create my own, slightly modified script provider. To start with, a simple, custom attribute:

using System;

namespace Slumber.Data.DbUp
{
    public class ScriptName : Attribute
    {
        public string Name { get; }

        public ScriptName(string name)
        {
            Name = name;
        }
    }
}

Applied to my script:

[ScriptName("0004 - Code Script")]
internal class Script0004_CodeScript : IScript

With a modified script provider:

private IEnumerable<SqlScript> ScriptsFromScriptClasses(IConnectionManager connectionManager)
{
    var script = typeof(IScript);
    return connectionManager.ExecuteCommandsWithManagedConnection(dbCommandFactory => _assembly
        .GetTypes()
        .Where(type => script.IsAssignableFrom(type) && type.IsClass)
        .Select(s =>
        {
            var scriptNameAttribute = s.GetCustomAttribute<ScriptName>(false);
            var scriptName = scriptNameAttribute != null
                ? scriptNameAttribute.Name + ".cs"
                : s.FullName + ".cs";
            return (SqlScript) new LazySqlScript(scriptName, () => ((IScript) Activator.CreateInstance(s)).ProvideScript(dbCommandFactory));
        })
        .ToList());
}

And voila!

Id          ScriptName
----------- -----------------------------------------------------------
1           Project.Data.Scripts.0001 - This is a script.sql
2           Project.Data.Scripts.0002 - This is another script.sql
3           Project.Data.Scripts.0003 - And this is another script.sql
4           Project.Data.Scripts.0004 - Code Script.cs
5           Project.Data.Scripts.0005 - Back to sql scripts.sql

I’m now free to be as pedantic as I want. If there’s already a way to do this then please feel free to let me know!