DbUp.Downgrade.MySql 2.6.0

DbUp.Downgrade

Extension of DbUp engine that allows automatic downgrade / rollback / revert of scripts.

The library plugs into DbUp pipeline extending the Configuration options. UpgradeEngineBuilderExtensions class adds few simple methods that allow to developers to extend the SchemaVersion table through abstraction providing custom SQL Queries over DbUp supported databases. SchemaVersion now supports additional column DowgradeScript that will store the script that will revert executed upgrade script. The basic idea is to store the sql query it self in the SchemaVersion and in case of Rollback to be able to perform downgrade of the Database to perviews version. Currently the only provided implementation is SQL Server based but it can be easily extended to all DbUp supported databases

NOTE

All existing DbUp projects can integrate the library. It will automatically make the required schema changes in DbUp Journal table in order to operate correctly. All newly added upgrade scripts can have a corresponding downgrade script.

Usage

1. Install the package you need:

• Install-Package DbUp.Downgrade for SQL Server
• Install-Package DbUp.Downgrade.PostgreSQL for PostgreSQL
• Install-Package DbUp.Downgrade.MySql for MySql

After successful install of the package you can start using it by:

Include the namespace

using DbUp.Downgrade;

This namespace will add new extensions to DbUp configuration builder.

• SQL Server:

var upgradeEngineBuilder = DeployChanges.To
 .SqlDatabase(connectionString)
 .WithScriptsAndDowngradeScriptsEmbeddedInAssembly<SqlDowngradeEnabledTableJournal>(Assembly.GetExecutingAssembly(), DowngradeScriptsSettings.FromSuffix())
 .LogToConsole();

• PostgreSQL:

var upgradeEngineBuilder = DeployChanges.To
 .PostgresqlDatabase(connectionString)
 .WithScriptsAndDowngradeScriptsEmbeddedInAssembly<PostgresDowngradeEnabledTableJournal>(Assembly.GetExecutingAssembly(), settings)
 .LogToConsole();

• MySql:

var upgradeEngineBuilder = DeployChanges.To
 .MySqlDatabase(connectionString)
 .WithScriptsAndDowngradeScriptsEmbeddedInAssembly<MySqlDowngradeEnabledTableJournal>(Assembly.GetExecutingAssembly(), settings)
 .LogToConsole();

If you are using scripts embedded in the executing assembly you can directly use WithScriptsAndDowngradeScriptsEmbeddedInAssembly method. It will require an instance of DowngradeScriptsSettings class. The settings has two static methods for fast and easy setup FromSuffix(string suffixName) and FromFolder(string folderName). By using them the code will rely that you have either downgrade scripts in a folder named same as the upgrade scripts:

* Scripts # The scripts that will perform the upgrade of the Database
 * Script0001 - Create tables.sql
 * Script0002 - Default feed.sql
 * Script0003 - Settings.sql
* DowngradeScripts # Scripts that will revert the changes made from the Upgrade Scripts
 * Script0003 - Settings.sql # NOTE: Must be with same name as the upgrade script

Or with a suffix:

* Scripts # The scripts that will perform the upgrade of the Database
 * Script0001 - Create tables.sql
 * Script0002 - Default feed.sql
 * Script0003 - Settings.sql
 * Script0003 - Settings_downgrade.sql

After the folder structure and naming convention is placed correctly you can build the DowngradeEnabledUpgradeEngine.

var upgrader = upgradeEngineBuilder.BuildWithDowngrade(true);

The boolean parameter indicates will on detected difference an automated downgrade will be executed. If this is set to false you will need to manually call upgrader.PerformDowngrade() method and handle the returned DatabaseUpgradeResult.

If your implementation is not based on scripts embedded in the executing assembly you will need to provide corresponding upgradeScriptProvider and downgradeScriptProvider. Both scenarios are showcased in the SampleApplication of the project