Database.DatabaseUpgrader

DatabaseUpgrader a lightweight migration management tool to help developers commit structure changes to the database automatically without relying on Entity Framework and it's migration system.

Upgrading the database

1. Create a folder in your project to store your migrations eg. MyProject/Migrations.
   Your folder structure should look something like this:

- MyProject
  - Migrations
    - v0001_InitDatabase.cs
    - v0002_AddFavoritesTable.cs
    - v0003_AlterFavoritesTable.cs

2. Add a class named v0001_InitDatabase in this folder which implements IMigrations
   The name can be anything but ideally it should be in the format of vXXXX_MigrationDescription

public class v0001_InitDatabase : IMigration
{
    public int DbVersion => 1;

    public string MigrationSql => @"
        CREATE TABLE accounts (id integer, username varchar(40));
        CREATE TABLE posts (id integer, title varchar(40));
        ";
}

3. In Program.cs or anywhere you prefer, after setting up your database connectiom, run the following code to apply any upgrades:

// Set up your connection
var conn = new MySqlConnection("server=127.0.0.1;user=root;password=root;port=3306;database=mydbname");

// Upgrade database
var dbUpgrader = new DatabaseUpgrader<MySqlConnection>(dbConnection);
await dbUpgrader.UpgradeAsyc("ConsoleApp1.Migrations");

Supported Databases

By default, the following database providers are supported, but you can extend it to support any other database provider which inherits from System.Data.Common.DbConnection.

• PostgreSQL
  • Npgsql.NpgsqlConnection
• MySQL / MariaDB
  • MySql.Data.MySqlClient.MySqlConnection
  • MySqlConnector.MySqlConnection
• SQLite
  • Microsoft.Data.Sqlite.SqliteConnection
  • System.Data.SQLite.SQLiteConnection
• MSSQL
  • System.Data.SqlCMicrosoft.Data.SqlClient.SqlConnectionlient.SqlConnection
  • Microsoft.Data.SqlClient.SqlConnection

Extending database support

You can write your own replacement queries for the internal queries used by DatabaseUpgrader by creating your own instance of MigrationInitQueries and passing it to DatabaseUpgrader like so:

var alternateQueries = new MigrationInitQueries
{
    FirstInstallQuery = @"BEGIN TRANSACTION;
        CREATE TABLE dbmigration (dbversion integer);
        INSERT INTO dbmigration (dbversion) VALUES (0);
        COMMIT;",
    IsMigrationInstalledCheckQuery = 
        @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'dbmigration'",
    VersionChangeQuery =
        @"UPDATE dbmigration SET dbversion = {0};",
    CheckCurrentMigrationVersionQuery =
        @"SELECT dbversion FROM dbmigration",
};
DatabaseUpgrader dbUpgrader = new DatabaseUpgrader(conn, alternateQueries);
dbUpgrader.UpgradeAsync()

Restricting the namespace for IMigrations

By default, DatabaseUpgrader will look for any instance of IMigration in the solution AND any imported packages, and attempt to push it to the database.
This of course can cause some issues if you have multiple databases or when running tests.
To prevent problems, you can restrict the namespace from which IMigrations will be loaded by passing it to UpgradeAsync() like so:

DatabaseUpgrader dbUpgrader = new DatabaseUpgrader(conn);
dbUpgrader.UpgradeAsync("MyProject.Migrations")

With this change, only migrations which are located inside this namespace will be included.