After Part 1: Intro and Part 2: Set up DbUp, there is only one thing left: to deploy the database such that it always knows which migrations are already applied to it.

Get the list of migrations

The list of migrations is the list of filenames. Because we have all those files in one folder, we are actually looking for a list of files in that folder. That can be done with the command line, and we can run that command in a pre-build event.

Pre-build event is set up in the project properties: Properties -> Build Events -> Pre-build event command line.

dir $(ProjectDir)MigrationsScripts /b /o > $(ProjectDir)migration-files.txt

What does this command do?

dir lists the files in the directory.
\b is for bare – so that we get only filenames, without sizes and other attributes.
\o is for order – filenames are ordered alphabetically. That is the same order as the order of applying the migrations because we are naming them Migration_YYYYMMDD.sql.
$(ProjectDir) is a macro provided by Visual Studio.

Now we have the list of migrations in a text file, and we will need to the path to this file later. We make use of $(ProjectDir) macro again and save the complete path $(ProjectDir)migrations.txt into a SQLCMD variable called PathToList (the setting is located in the project properties).

Seed the migrations

Let’s now create the SeedMigrations.sql script that will insert the names of the migration files into the database.

We need to take the list of the files, add current date and put it all into the SchemaVersions table.

Theoretically, this could be done in one step. Practically, it isn’t easy to do, mostly due to the nature of SQL commands for bulk insert.

Our solution first inserts names of applied scripts into a temporary table #AppliedMigrations. Then it reads them and together with the current date it inserts them into SchemaVersions table.

The PathToList variable is used in the BULK INSERT command – it requires an absolute path to the file as a parameter.

CREATE TABLE #AppliedMigrations
(
    [Migration] VARCHAR(1000) NOT NULL
)

/* load names of applied migrations scripts into a temporary table */
BULK INSERT #AppliedMigrations
FROM '$(PathToList)'
WITH
(
    ROWTERMINATOR = '\n'
)

DECLARE @applied DATETIME = GETUTCDATE()

/* insert names of applied migration scripts and current date into a schema version table */
INSERT INTO [dbo].[SchemaVersions] ([ScriptName], [Applied])
SELECT m.Migration, @applied
FROM #AppliedMigrations m

And when will this script get called? After the database deployment, from a post-deployment script.

First, add the script to your project if you haven’t done already done so.
Then, append the following line into it:

:r .\Scripts\SeedMigrations.sql

Publish the database

There is one last step: When you are going to publish the database, you need to click on the Load values button in the publish window – this will populate the PathToList variable with the correct value.

And that’s it! When you publish the database, it will contain all the applied migrations and you can later safely apply any new migration onto it.

Previous parts

Last modified on 2021-04-30