Database deployment automation with DbUp

Posted in Database, SQL, devops

How do you manage your database deployments? You'll be surprised but this is one subject that seems to be a problem in many places I've worked. It all depends, of course, on how you run your deployments in general. So, what are the usual options?

  1. Manual - you copy all the files to your target server(s) manually and you deploy your database scripts by running them separately on each database server.

  2. Scripted - you use scripts, like PowerShell, to semi-automate your deployments by taking the steps from the manual process described above and running them in an automated way. There may or may not be a need for manual intervention

  3. Automation tools - you use something like [OctopusDeploy](https://octopus.com/" target="_blank) to bundle and deploy your code in a fully automated way. For your database deployment, you use either a script, command tool or a 3rd party tool such RedGate to push the necessary changes.

If you're still stuck on #1, it's time you moved on. Invest on a solid, repeatable process that can be run multiple times and always generate consistent results. Is your development team in position to deploy multiple times daily? Yes, you heard me right, multiple times daily. It doesn't have to be deployment to production, but to "an" environment, somewhere. The focus should be in getting the process in place.

And part of that process is usually a script or scripts to update a database. If you are in the .NET world and use [Entity Framework](http://www.asp.net/entity-framework" target="_blank), you can use EF Migrations to manage your database changes. However, there is a good chance you may not want to relegate all control to EF and instead manage the upgrade yourself. EF allows you to create a delta file that contains all the SQL scripts necessary to upgrade the target database manually and you can run that file at any point.

Using DbUp to deploy your database scripts

But not everyone uses an ORM (EF, nHibernate) when working with a database. So, how do you come up with the right process to update your database in a predictable and consistent way? Enter [DbUp](https://dbup.readthedocs.org/en/latest/" target="_blank) the open source .NET library that:

helps you to deploy changes to SQL Server databases. It tracks which SQL scripts have been run already, and runs the change scripts that are needed to get your database up to date.

DbUp is crazy easy to use. All you need to do is add a console application to your solution and a NuGet package. The main() method is used to retrieve the SQL scripts and run them in sequence. DbUp has a number of script providers providing a flexibility when defining the database changes:

  • EmbeddedScriptsProvider - SQL scripts as embedded resources
  • FileSystemScriptProvider - SQL scripts from the file system
  • StaticScriptProvider - SQL scripts defined in code
  • EmbeddedScriptAndCodeProvider - SQL scripts defined both as embedded resources and in code

DbUp is beautifully simple yet powerful at what it does. Once you run a set of scripts, it's intelligent enough to know not to run them again. This means that you can keep all your environments consistent by always running the necessary scripts to keep the database up-to-date.

It supports for all major database providers as per the list below:

  • SQL Server 2000, 2005, 2008, 2012, 2014, 2016
  • SQL Azure
    • .NET 3.5
  • SQLite
    • .NET 3.5
  • SQLite.Mono (Same as above, but using Mono driver)
    • .NET 4.0
  • SqlCe
    • .NET 4.0
  • MySql
    • .NET 3.5
  • PostgreSQL
    • .NET 4.0
  • Firebird
    • .NET 4.0
  • Coming soon: Xamarin, PCL and other targets

One of the biggest strengths of DbUp is that it works greatly with OctopusDeploy but can be equally useful as a standalone application. Since we use a console application to define and configure the changes, any developer in the team can run the migration scripts on their development environment.

Sample code

In Visual Studio, open the NuGet Command window and select your Console application as the default project. Run the following command to install the DbUp NuGet package:

Install-Package dbup

In your Program -> Main() method, add the following code

You'll also need to add the desired database connection string to your app.config as per the example below:

<connectionStrings>
      <add name="YourConnectionName" connectionString="Data Source=(LocalDB)\v11.0;Initial Catalog=YourDatabaseName;Integrated Security=True;Pooling=False" />
    </connectionStrings>

Finally, copy/create your SQL scripts to the project and ensure they are added as Embedded Resources. Use the screenshot below as a guide:

Set the Console project as the startup and run it. Job done!

  • Share this post on