DbUp is a great tool to manage database migration scripts as I explained in the past. For more than two years we used it in multiple projects and never needed more than the setup example from the documentation. However, in the case of an error this snipped isn’t the best configuration and needs manual work to clean-up your database. Let’s look on the underlying problem and how one can fix it.
Every SQL command could fail. That’s not a big problem when you only have one or two statements in a migration script. It’s a whole different story when your scripts contain many commands, for example when you made a big refactoring and that affected multiple tables. If now an error occurs, you need a lot of manual checking and fixing until your database is back at a consistent state.
If you would execute those commands manually, you would use a transaction block around so that you could roll back to a consistent state. The same approach should be used with DbUp. The setup code needs only one additional line to use transactions:
|
1 2 3 4 5 6 7 |
var upgrader = DeployChanges.To .SqlDatabase(connectionString) .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly()) .WithTransactionPerScript() .LogToConsole() .Build(); |
The fluent configuration WithTransactionPerScript() creates a transaction for every migration script. If one fails it will only rollback the changes made for this specific script. If you prefer one transaction for the whole migration that could span multiple scripts, change the option to WithTransaction().
This little extra line doesn’t look like much but can save you a lot of time.