Keeping your database up-to-date with your code is an important task. There are many different approaches and even more tools to support you. However, they often come with a price you may not be willing to pay.
A different way goes DbUp. This little tool helps you to update your database in a simple, understandable manner. It can’t do everything, but what it can works great. Let’s have a deeper look at DbUp.
Installation
The simplest and most convenient way to use DbUp is within a command line application. That project can be added to your solution and then only cares about modifying your database. The installation itself is done by NuGet by entering this command into the Package Manager Console:
|
1 |
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 or use mine with an exactly specified connection string name:
|
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 |
static int Main(string[] args) { var connectionString = ConfigurationManager.ConnectionStrings["Blog"].ConnectionString; var upgrader = DeployChanges.To .SqlDatabase(connectionString) .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly()) .LogToConsole() .Build(); var result = upgrader.PerformUpgrade(); if (!result.Successful) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(result.Error); Console.ResetColor(); return -1; } Console.ForegroundColor = ConsoleColor.Green; Console.WriteLine("Success!"); Console.ResetColor(); return 0; } |
Your DB Scripts
The next step is to create a folder called Scripts where you can put your migration scripts. You can change the name, but then you need to update the ScriptProvider as well.
The migration scripts are simple text files with SQL commands directly written to it. By using this simple approach, you can write your scripts in the SQL Server Management Studio and then copy and paste them into a migration file. Another great plus is that your database administrator could create the scripts for you, without any prerequisite knowledge for writing code.
If you start with an empty database, then your first migration may create a new table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Book ( Id INT IDENTITY(1,1), Title VARCHAR(255), ISBN VARCHAR(13), Pages SMALLINT, Summary VARCHAR(MAX), Rating NUMERIC(2,1), CONSTRAINT pk_Book_Id PRIMARY KEY (Id), ); |
With the current configuration of DbUp your scripts must be embedded resources. You therefore now need to change the Build Action to Embedded Resource:
The naming of the files is up to you. If you start the name with Script* it will be automatically detected. If you use a number directly after that, then the alphabetical sort order in Visual Studio matches the order of execution in DbUp. While this is not a must have, it makes the handling of those files much simpler.
Running the Migrations
To update your database, you need to build your command line application project and execute it. DbUp will connect to the database and run your scripts:
|
1 2 3 4 5 6 7 8 |
Beginning database upgrade Fetching list of already executed scripts. The [SchemaVersions] table could not be found. The database is assumed to be at version 0. Executing SQL Server script 'DBMigration.Scripts.Script0001 - Create Book Table.sql' Creating the [SchemaVersions] table The [SchemaVersions] table has been created Upgrade successful Success! |
To prevent scripts from running over and over again, DbUp tracks the execution of your scripts in the table 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.
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 definitely should give it a try.
Another usefull feature with DBup is that you can use it against LocalDB, which enables integration tests to run against an updated / migrated schema each time. Flyway’s driver can’t connect to LocalDb, so DbUp becomes a great choice for this.
I have table changes ,after that I need those changes effect to qa not for production ,How can we achieve using DbUp
Hi Srinivas,
Excuse my late reply. For your use case we use two different DbUp projects. One that should run in all environments and one that only runs in QA / Test. This setup allows us to be very specific in which environment we run the scripts.
Regards,
Johnny
Hi Team,
Can i call a Dbup project to a database project and execute script in database project by dbup-project
Hi Arun,
DbUp offers various ways to work with migration files. You can use the approach I wrote about here (https://improveandrepeat.com/2019/11/how-to-use-dbup-without-embedded-scripts/) to point DbUp to any folder with SQL scripts. They can be in different Projects and DbUp will still be able to run them. I hope this answers your question.
Regards,
Johnny