Add a database migration and know when DbUp runs it
Use a migration journal for one-time data changes and a separate repeatable path for programmability. Understand how your runner chooses which scripts execute.
In this guide
Separate project migrations from vendor upgrade scripts
DbUp is a project deployment choice. Znode's version-specific database upgrade scripts have their own documented sequence and prerequisites. Apply the required vendor upgrade process separately, then use a project runner for the custom migrations your application owns.
This runner records Scripts files in dbo.SchemaVersions. Each journal identity includes its folder and relative filename, such as Scripts/001-add-support-label.sql. Programmability files use NullJournal and run again on each successful invocation. Use CREATE OR ALTER or another convergent definition for those repeatable objects.
Create a console project targeting net8.0 and add the NuGet package with dotnet add package dbup-sqlserver --version 7.2.0. Save this as Program.cs. Supply an absolute release directory as the single argument and provide GCG_MIGRATION_CONNECTION through the deployment secret mechanism. Both folders must contain SQL files so a missing release artifact fails visibly.
Program.cs csharp
using System;
using System.IO;
using System.Linq;
using System.Text;
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 || !Path.IsPathFullyQualified(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 SqlScript[] Load(string root, string folder)
{
string path = Path.Combine(root, folder);
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 + "/" + Path.GetRelativePath(path, file).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;
}
}
Add a forward-only change
Add a newly ordered file to Scripts for each data or schema change. Preserve deployed filenames and contents: DbUp journals names, so editing an old file does not cause it to run again, and renaming it creates a different journal identity. Introduce this runner with an explicit mapping of any existing journal naming convention.
The Scripts path uses WithoutTransaction because each file owns its BEGIN, COMMIT, and rollback logic. This matches the Store attribute migration in P31, which rejects an ambient transaction. Each such migration must also be safe after a commit-before-journal interruption: the next run can encounter completed SQL whose name was not recorded.
Programmability uses WithTransactionPerScript. Keep explicit transaction control out of those files and keep each object definition independently rerunnable. The two stages are sequential, so a later failure leaves earlier successful commits in place.
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.
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.