Automating Database Migrations in .NET: A Complete Guide to Using DeployHQ with DbUp

Database migrations are one of those critical pieces of infrastructure that can make or break your deployment process. Get them wrong, and you're looking at downtime, data inconsistencies, or worse. Get them right, and deployments become smooth, predictable, and stress-free.

In this guide, we'll walk through how to combine DeployHQ's deployment automation with DbUp, a popular .NET database migration library, to create a robust and reliable database deployment pipeline. Whether you're working on a greenfield project or modernizing an existing application, this approach will help you ship database changes with confidence.

What is DbUp?

Before we dive into the integration, let's quickly cover what DbUp is and why it's an excellent choice for .NET database migrations.

DbUp is a lightweight .NET library that helps you deploy changes to SQL Server, PostgreSQL, MySQL, and other database systems. Unlike Entity Framework migrations or other ORM-based approaches, DbUp takes a SQL-first approach. You write plain SQL scripts, and DbUp handles the execution order, tracks which scripts have been run, and ensures each script runs exactly once.

This approach has several advantages. First, you have complete control over your SQL, which is crucial for performance-critical migrations or complex database operations. Second, your migration scripts are portable and easy to review. Third, DbUp works with any data access technology, whether you're using Entity Framework, Dapper, ADO.NET, or a mix of different approaches.

What is DeployHQ?

DeployHQ is a deployment automation platform that helps you ship code from your repository to your servers automatically. It supports various deployment targets including FTP, SFTP, Amazon S3, and custom servers. More importantly for our use case, it allows you to run custom commands during deployment, which is exactly what we need to execute DbUp migrations.

The Architecture

Here's how the pieces fit together in our deployment pipeline:

1. You push code changes to your Git repository (GitHub, GitLab, Bitbucket, etc.)
2. DeployHQ detects the changes and triggers a deployment
3. Your application code is deployed to your server
4. DeployHQ runs a custom command that executes your DbUp migration console application
5. DbUp reads all migration scripts, determines which haven't been run yet, and executes them in order
6. Your database is now in sync with your application code

The beauty of this approach is that database migrations happen automatically as part of your standard deployment process. No manual SQL execution, no forgotten migration scripts, no drift between environments.

Setting Up DbUp in Your .NET Project

Let's start by adding DbUp to your .NET solution. The recommended approach is to create a separate console application that will handle database migrations.

Step 1: Create the Migration Console Application

Open your solution and add a new console application. You can name it something like YourProject.DatabaseMigration. This application will be responsible for executing your database migrations.

Add the DbUp NuGet package to this project. The specific package depends on your database, but for SQL Server, you would install dbup-sqlserver. For PostgreSQL, use dbup-postgresql.

Step 2: Structure Your Migration Scripts

Create a folder in your migration project called Scripts. This is where you'll place all your SQL migration scripts. The naming convention is crucial here. DbUp executes scripts in alphabetical order, so use a consistent naming pattern like:

• 0001-CreateUsersTable.sql
• 0002-AddEmailIndexToUsers.sql
• 0003-CreateOrdersTable.sql

The numeric prefix ensures scripts run in the correct order, and the descriptive name makes it clear what each script does.

Mark each SQL file as an Embedded Resource in its properties. This embeds the SQL into your compiled assembly, making deployment simpler since you don't need to deploy loose SQL files.

Step 3: Write the Migration Application

Your Program.cs file should look something like this:

var connectionString = args.FirstOrDefault() 
    ?? throw new ArgumentException("Connection string required");

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
    .LogToConsole()
    .Build();

var result = upgrader.PerformUpgrade();

if (!result.Successful)
{
    Console.ForegroundColor = ConsoleColor.Red;
    Console.WriteLine(result.Error);
    Console.ResetColor();
    return -1;
}

Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Success!");
Console.ResetColor();
return 0;

This code does several important things. It reads the database connection string from command-line arguments, configures DbUp to use SQL Server, tells it to look for scripts embedded in the assembly, enables console logging so you can see what's happening, and returns appropriate exit codes for success or failure.

The exit code is particularly important for DeployHQ integration. A non-zero exit code signals that something went wrong, and DeployHQ will halt the deployment.

Step 4: Build and Test Locally

Before integrating with DeployHQ, test your migration application locally. Build the project and run it from the command line:

dotnet run --project YourProject.DatabaseMigration "Server=localhost;Database=YourDb;Trusted_Connection=True;"

You should see DbUp create a table called SchemaVersions in your database and execute your migration scripts. Run it again, and you'll see that DbUp recognizes the scripts have already been executed and skips them.

Configuring DeployHQ

Now that your DbUp application works locally, let's integrate it with DeployHQ.

Step 5: Set Up Your DeployHQ Project

Log into your DeployHQ account and create a new project if you haven't already. Connect it to your Git repository by providing the repository URL and authentication credentials.

Configure your deployment target. This might be an SFTP server, an Amazon S3 bucket, or another supported target. For a typical .NET application, you're probably deploying to a Windows or Linux server via SFTP or directly to an IIS server.

Step 6: Configure Build Commands

In your DeployHQ project settings, navigate to the Build Pipeline section. This is where you'll configure the commands that build your application before deployment.

Add a build command to compile your solution:

dotnet publish -c Release -o ./publish

This publishes your application in Release configuration to a publish folder. Make sure this command includes both your main application and your database migration console application.

Step 7: Configure Deployment Settings

In the deployment configuration, specify which files to deploy. Typically, you'll deploy everything in your publish folder to your server.

The key to making database migrations work is configuring the right paths. Your migration console application needs to be deployed to a location where it can be executed on the server.

Step 8: Add the Migration Command

Here's the crucial part. In your DeployHQ project configuration, go to the Commands section. You'll add a command that runs after your files are deployed but before the deployment is marked as complete.

Add a new command with these settings:

Command Type: Run on Server

Trigger: After files are copied

Command:

cd /path/to/your/deployment/YourProject.DatabaseMigration && dotnet YourProject.DatabaseMigration.dll "$CONNECTION_STRING"

Replace /path/to/your/deployment with the actual path where your application is deployed on the server.

Step 9: Configure Environment Variables

The connection string in the command above references an environment variable. In DeployHQ, go to your project's Environment Variables section and add a variable named CONNECTION_STRING with your database connection string as the value.

This approach keeps sensitive connection strings out of your code and allows you to use different connection strings for different environments. You can configure separate environment variables for staging and production deployments.

Step 10: Test the Deployment

Commit a change to your repository, or manually trigger a deployment from DeployHQ. Watch the deployment log carefully. You should see:

1. Your code being pulled from the repository
2. The build commands executing
3. Files being transferred to your server
4. The migration command executing
5. DbUp's console output showing which scripts are being run
6. A success message

If anything goes wrong, the deployment will stop, and you'll see error messages in the log. This fail-fast behavior is exactly what you want, ensuring your database and application code never get out of sync.

Advanced Configuration

Once you have the basic integration working, there are several enhancements you can make to your setup.

Handling Multiple Environments

You likely have multiple environments: development, staging, and production. DeployHQ supports creating separate deployment configurations for each environment.

Create a deployment configuration for each environment, each with its own connection string environment variable. This ensures staging deployments only affect the staging database, and production deployments only affect production.

Transaction Management

By default, DbUp wraps each script in a transaction. If a script fails partway through, the changes are rolled back. However, for some operations like creating or altering database structures, you might want to control transaction behavior explicitly.

You can configure this in your DbUp setup:

var upgrader = DeployChanges.To
    .SqlDatabase(connectionString)
    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
    .WithTransactionPerScript()
    .LogToConsole()
    .Build();

The WithTransactionPerScript() method ensures each script runs in its own transaction, which is generally the safest approach.

Handling Rollbacks

One question that often comes up is how to handle rollbacks. DbUp doesn't have built-in rollback functionality, and this is intentional. Database rollbacks are complex and often impossible, especially once data has been modified.

Instead, the recommended approach is to handle rollbacks through forward-only migrations. If you need to undo a change, you write a new migration script that reverses the previous change. This creates a complete audit trail of all database changes and is much safer than attempting to automatically reverse SQL operations.

Zero-Downtime Deployments

For high-availability applications, you might need zero-downtime deployments. This requires careful coordination between your code and database changes.

The key principle is to make database changes backward-compatible with the previous version of your application code. For example:

• When adding a new required column, first add it as nullable
• Deploy the application code that uses the new column
• In a subsequent migration, make the column non-nullable

This ensures that during the brief period where old application instances are still running, they don't encounter database schema they can't handle.

Logging and Monitoring

In production environments, you'll want more sophisticated logging than just console output. Consider integrating a proper logging framework into your migration application.

You can also add custom logging to DeployHQ by having your migration application write to log files that you can later review. Another approach is to send notifications to Slack or email when migrations complete or fail.

Best Practices

After working with DbUp and DeployHQ across many projects, here are some best practices that will save you headaches:

Always test migrations locally first. Never commit a migration script without running it against a local copy of your database. This catches syntax errors and logic issues before they reach your server.

Keep migrations small and focused. Each migration script should do one thing. This makes them easier to review, test, and troubleshoot if something goes wrong.

Never modify existing migration scripts. Once a script has been deployed to any environment, consider it immutable. If you need to change something, create a new migration script.

Include rollback instructions in comments. Even though DbUp doesn't execute rollback scripts automatically, include comments in your migration scripts explaining how to manually undo the changes if necessary.

Review migrations as part of code review. Database changes are code changes. They should go through the same review process as your application code.

Monitor migration execution time. Some database operations can take a long time, especially on large tables. Be aware of how long your migrations take and plan deployments accordingly.

Troubleshooting Common Issues

Even with everything configured correctly, you might encounter issues. Here are solutions to common problems:

Issue: Migration fails but application still deploys. This usually means your migration command isn't returning the correct exit code. Make sure your DbUp application returns -1 on failure and that DeployHQ is configured to stop deployment on command failure.

Issue: Scripts run out of order. Check your script naming convention. Remember that DbUp sorts scripts alphabetically, so use leading zeros in your numeric prefixes.

Issue: Connection string issues. Connection strings can be tricky, especially with special characters. Make sure your connection string is properly escaped in your environment variables. Test the exact connection string locally before using it in DeployHQ.

Issue: Permission denied when executing migrations. Your deployment server needs permission to execute the migration application and connect to the database. Check both file system permissions and database user permissions.

Conclusion

Integrating DeployHQ with DbUp creates a powerful, automated database deployment pipeline for your .NET applications. Database changes become predictable, repeatable, and auditable. You reduce the risk of human error and ensure your database schema always matches your application code.

The initial setup requires some configuration, but once it's working, deployments become dramatically simpler. You can ship database changes with the same confidence you have deploying application code, knowing that the process is automated, tested, and reliable.

The combination of DeployHQ's deployment automation and DbUp's migration management gives you professional-grade database deployment without the complexity of enterprise tools. Your team can focus on building features instead of worrying about database deployment logistics.

Start with a simple setup as described in this guide, then evolve your process as your needs grow. The foundation you've built is solid and will scale with your application.