Automated database deployments with DbUp

I was playing with Octopus Deploy when I found DbUp tool…
DbUp is a nice open source project that allows you to deploy Sql-scripts via a .Net executable, enabling deployment via a pipeline like Octopus Deploy. DbUp tracks which scripts have already been run on your database so you don’t have to worry about double-updates to your database.

One of the biggest strengths of DbUp is that it works really effectively with OctopusDeploy but can be equally useful as a standalone application. Since we have been using SQL scripts in one of our projects (planning to move to .dacpac) and a console application to define and configure the changes in the DB, any developer in the team can run the migration scripts on their development environment.

Let’s have a look at what database providers DbUp supports:

  • 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

If you are in the .NET world and use Entity Framework, you can use EF Migrations to manage your database changes; but not everyone uses an ORM (EF,nHibernate) when working with a database…

DbUp is very easy to use. All you need is to 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. The name that you defined the scripts will be the order of the execution of the script.

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.

Installation

The simplest and most convenient way to use DbUp is within a command line application. This project can be added to your solution and then it only focuses on modifying your database. The installation itself is done by NuGet by entering this command into the Package Manager Console:

Install-Package DbUp

Setup

DbUp needs some code to configure the connection string to your database and the location of the scripts you want to run. You can take the code from the official documentation:

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="
YourDBConnectionName" connectionString="Server=(local)\\SqlExpress; Database=MyApp; Trusted_connection=true" />
</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:

All you need to do is create a new console project and add your scripts to the project as Embedded Resources. This helps to prefix your scripts with a tag that will make sure they run in the correct order, such as “Script0001_”.

You now have a console application that can run change scripts against your database! After running it, you’ll see something like this:

The next time you run it, you’ll see:

Where does the DbUp keep the execution of the scripts?

To prevent scripts from running over and over again, DbUp tracks the execution of your scripts in a table called SchemaVersions. Every executed script will get an entry with the date and the script name:

On the next run DbUp will check which scripts need to be executed and only run them.

How can I ‘force’ DbUp to always run the same script?

Let’s say that you’ve got a script that updates the stored procedure or your functions all the time and you’d like to make sure that DbUp runs it every time.

If you want to ensure certain scripts are always applied — typically idempotent scripts that drop and create things like functions, views, procedures, and permissions — you can use the NullJournal class:

The Null Journal (using .JournalTo(new NullJournal()) in your configuration) is the simplest way to run scripts every time:

DeployChanges.To 
.SqlDatabase(connectionString)
.WithScriptsEmbeddedInAssembly(
Assembly.GetExecutingAssembly(),
s => s.Contains("everytime"))
.JournalTo(new NullJournal())
.Build();

Example:

From the line code numbers 1 to 9 if you run the console application more than once in the same database, the scripts defined as a schema would run only once.

From the line code numbers 11 to 20 if you run the console application more than once in the same database, the scripts defined as a stored procedure would run every time.

Limitations

DbUp is so simple because it only allows you to go forward with your migrations. There is no rollback to a specific version. While there are some discussions about changing that, so far you only can change the database from one state to the next.

Conclusion

DbUp is a great little tool to manage your database. Simple SQL commands added to text files in a Scripts-folder are all you need to keep your database up-to-date with your code. It’s so simple that you should definitely give it a try.

Another benefit of using scripts is that we can deploy schema changes or data with a single tool. We don’t need one tool for schema and another for data.

References:

https://dbup.github.io/

Show your support

Clapping shows how much you appreciated Carlos Menezes’s story.