Add a database migration and know when DbUp runs it
Build a Framework 4.8 DbUp runner that journals numbered migrations, reruns object definitions, and makes transaction and recovery behavior explicit.
In this guide
- Ordered Scripts files
- Script-owned transactions
- dbo.SchemaVersions journal
- Programmability files
- Per-script transactions
- NullJournal
- Repeatable object definitions
Separate project migrations from vendor upgrade scripts
Keep Znode's vendor upgrade scripts on their documented release process. This DbUp console runner handles the custom database changes your project owns. Save Program.cs beside DatabaseRunner.csproj in a separate Framework 4.8 project with Scripts and Programmability subfolders.
The runner loads both folders before it changes the database. Scripts files run in name order and are recorded in dbo.SchemaVersions. Programmability files run afterward on every successful invocation because their NullJournal records no completion history. Use rerunnable definitions such as CREATE OR ALTER in that folder.
Build on Windows with the Framework 4.8 targeting pack. Deploy the whole output directory, provide GCG_MIGRATION_CONNECTION through the deployment secret mechanism, and pass the absolute deployment directory to DatabaseRunner.exe. Both SQL folders must contain nonempty files; a missing artifact is an error.
Program.cs csharp
using System;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using DbUp;
using DbUp.Engine;
using DbUp.Helpers;
internal static class Program
{
public static int Main(string[] args)
{
// Invoke once per database from a serialized deployment stage.
// Pass an absolute release folder containing Scripts and Programmability.
string connection = Environment.GetEnvironmentVariable("GCG_MIGRATION_CONNECTION");
if (args.Length != 1 || !IsAbsoluteWindowsPath(args[0]) || string.IsNullOrWhiteSpace(connection))
{
Console.Error.WriteLine("Supply an absolute release folder and GCG_MIGRATION_CONNECTION.");
return 2;
}
try
{
var migrations = Load(args[0], "Scripts");
var programmability = Load(args[0], "Programmability");
// These files own their BEGIN/COMMIT/ROLLBACK transaction, as in P31.
// Each must also tolerate a rerun after SQL commits but before journaling.
var data = DeployChanges.To.SqlDatabase(connection)
.WithScripts(migrations)
.JournalToSqlTable("dbo", "SchemaVersions")
.WithoutTransaction()
.WithExecutionTimeout(TimeSpan.FromMinutes(3))
.Build().PerformUpgrade();
if (!data.Successful)
{
Console.Error.WriteLine("Versioned migration stage failed. Stop and inspect the database.");
return 1;
}
// Repeatable object definitions use runner-owned per-script transactions.
// Keep explicit transaction management out of this folder's files.
var objects = DeployChanges.To.SqlDatabase(connection)
.WithScripts(programmability)
.JournalTo(new NullJournal())
.WithTransactionPerScript()
.WithExecutionTimeout(TimeSpan.FromMinutes(3))
.Build().PerformUpgrade();
if (!objects.Successful)
{
Console.Error.WriteLine("Programmability stage failed. Earlier committed changes remain.");
return 1;
}
Console.WriteLine("Database release completed.");
return 0;
}
catch (Exception)
{
// Send detailed failures to approved restricted deployment diagnostics.
// Do not print connection strings, SQL bodies or database values here.
Console.Error.WriteLine("Database release failed during configuration or execution.");
return 1;
}
}
private static bool IsAbsoluteWindowsPath(string path)
{
// Accept drive-rooted or UNC share paths, not drive-relative paths.
return path != null && Regex.IsMatch(path,
@"\A(?:[A-Za-z]:[\\/]|\\\\[^\\/]+[\\/][^\\/]+(?:[\\/]|\z))");
}
private static SqlScript[] Load(string root, string folder)
{
string path = Path.GetFullPath(Path.Combine(root, folder))
.TrimEnd(Path.DirectorySeparatorChar, Path.AltDirectorySeparatorChar) + Path.DirectorySeparatorChar;
if (!Directory.Exists(path))
throw new DirectoryNotFoundException("A release folder is missing.");
var scripts = Directory.EnumerateFiles(path, "*.sql", SearchOption.AllDirectories)
.Select(file => new SqlScript(
folder + "/" + file.Substring(path.Length).Replace('\\', '/'),
File.ReadAllText(file, Encoding.UTF8)))
.OrderBy(script => script.Name, StringComparer.Ordinal)
.ToArray();
if (scripts.Length == 0 || scripts.Any(script => string.IsNullOrWhiteSpace(script.Contents)) ||
scripts.GroupBy(script => script.Name, StringComparer.OrdinalIgnoreCase).Any(group => group.Count() > 1))
throw new InvalidDataException("Release files must be nonempty with unique names.");
return scripts;
}
}
After Scripts/001-add-support-label.sql succeeds, DbUp records that full relative name. The next run skips it and runs Programmability definitions again. A script failure stops the runner with a nonzero exit code. Earlier committed scripts remain committed, so the deployment process must inspect completed work before resuming.
Add a forward-only change
Add a new numbered Scripts file for every new data or schema change. Preserve deployed filenames and contents: editing a journaled file does not rerun it, while renaming it gives DbUp a new identity. If introducing this runner into an existing project, map the previous journal naming convention deliberately.
Scripts uses WithoutTransaction because each file owns its BEGIN, COMMIT, and rollback logic, as the Store attribute migration does. A process can stop after SQL commits but before DbUp journals its name. The file must therefore recognize its completed state and tolerate that rerun.
Programmability uses WithTransactionPerScript. Leave explicit transaction control out of those definitions and make each independently rerunnable. There is no single transaction around the whole release.
Prove both paths in a disposable database
In a disposable project database, run one migration and inspect its journal identity. Run again and confirm the data script stays skipped while a repeatable view or procedure returns to its declared definition. Exercise a failed script and verify rollback, absence of its journal row, and a nonzero process exit.
Also cover missing folders, empty files, invalid connection configuration, and duplicate filenames under the journal's comparison rules. Capture restricted deployment diagnostics and exact schema readbacks. Keep connection strings, SQL bodies, and customer values out of ordinary console output.
Recover safely
Stop the release when either stage returns a nonzero exit code. Determine which files committed and which journal records exist before choosing the next action. Repair a deployed change with a new forward migration, and use the same preconditions and ownership checks as the original change.
Do not remove journal rows to force a rerun. A repeatable script or an idempotent migration can be resumed according to its defined contract; an inconsistent state needs a scoped correction. Run one deployment at a time for the database, including vendor scripts and project migrations.
Package the DbUp runner and its SQL files together
Save this beside Program.cs and keep SQL under Scripts and Programmability. On Windows with the .NET Framework 4.8 targeting pack, run dotnet build DatabaseRunner.csproj -c Release. Deploy the entire bin/Release/net48 directory, including dependencies, DatabaseRunner.exe.config, and both SQL folders. Run DatabaseRunner.exe with that absolute directory as its argument on a Framework 4.8 Windows host. The dbup-sqlserver 7.2.0 dependency targets .NET Standard 2.0, which Framework 4.8 supports; the application itself still targets net48.
DatabaseRunner.csproj xml
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net48</TargetFramework>
<LangVersion>6</LangVersion>
<Nullable>disable</Nullable>
<ImplicitUsings>disable</ImplicitUsings>
<AutoGenerateBindingRedirects>true</AutoGenerateBindingRedirects>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="dbup-sqlserver" Version="7.2.0" />
<None Update="Scripts/**/*.sql;Programmability/**/*.sql"
CopyToOutputDirectory="PreserveNewest" />
</ItemGroup>
</Project>
References and further reading
Bring your next engineering question.
Need a release-safe database change reviewed?
Independent guidance from GCG. Znode is a trademark of its owner. Examples use fictional data and are not official platform documentation. Suggest a correction.