This post will be continuing our exploration of DbUp with the addition of transactions as part of migration execution. If you are new to DbUp check out the following post to catch up on this series.
Database Migrations with DbUp
Code-based Database Migrations with DbUp
Always Run Migrations with DbUp
Logging Script Output with DbUp
Transaction Types and Restrictions
DbUp has three different options for transactions. The default is no transaction, which is what we have been using so far. The other two options are a transaction per migration and finally a single transaction for all the migrations in a single upgrader. As far as I can tell so far there is no way to use the same transaction across multiple upgraders so that means in our sample application we could have our normal migrations work correctly and get committed, but have an issue in our always run migrations. The other thing to keep in mind is that some database providers don’t support transactions when changing the structure of the database. As pointed out by the DbUp docs, the Postgres docs have a good summary of transactional support for DDL of different database providers.
Adding Transactions
In the following example, we have changed the upgrader to use a single transaction. This is the full code for the upgrader with the change highlighted.
var upgrader =
DeployChanges.To
.SqlDatabase(connectionString)
.WithScriptsAndCodeEmbeddedInAssembly(Assembly.GetExecutingAssembly(),
f => !f.Contains(".AlwaysRun."))
.LogToConsole()
.WithTransaction()
.Build();
The other options are WithTransactionPerScript for, surprise, a transaction per migration, and WithoutTransaction for no transactions (or just leave it out since this is the default value). The following is a sample of the output with transactions on with two different upgraders in play and the begin transactions highlighted.
Beginning transaction Beginning database upgrade Checking whether journal table exists.. Fetching list of already executed scripts. No new scripts need to be executed - completing. Beginning transaction Beginning database upgrade Executing Database Server script 'DbupTest.Scripts.AlwaysRun.01-EverytimeNoPrints.sql' Executing Database Server script 'DbupTest.Scripts.AlwaysRun.02-EverytimeNoPrintsNoCountOn.sql' Executing Database Server script 'DbupTest.Scripts.AlwaysRun.03-EverytimePrints.sql' Executing Database Server script 'DbupTest.Scripts.AlwaysRun.04-EverytimePrintNoCountOn.sql' Upgrade successful Success!
Wrapping Up
DbUp makes adding transactions to your migrations super simple so this was a really short post. It would be nice to have an option to use a transaction across multiple upgraders. The official DbUp docs on transactions can be found here.
Also published on Medium.