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):

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: