Increment Upgrade Example

Migrator.cs

using System;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySqlConnector;
using Crawler.Service.Enums;
using Crawler.Service.Models;

public class Migrator
{
    private static readonly int CurrentVersion = 1008;

    private readonly ILogger<Migrator> _logger;
    private readonly string _connectionString;

    public MySqlRepositoryMigrator(ILogger<Migrator> logger, IOptions<Config> config)
    {
        _logger = logger;

        _connectionString = config.ToConnectionString();
        _recreateIfNotExist = config.Value?.RecreateIfNotExist ?? false;
    }
        
    public async Task<int> GetDatabaseVersionAsync()
    {
        var sql = "SELECT v FROM kvmeta WHERE k = 'version'";
        _logger.LogTrace("SQL: {Sql}", sql);

        await using var connection = new MySqlConnection(_connectionString);
        return await connection.ExecuteScalarAsync<int>(sql);
    }

    public int GetVersion()
    {
        return CurrentVersion;
    }

    public async Task UpgradeAsync()
    {
        _logger.LogInformation("Trying to upgrade the database...");

        var dbVersion = await GetDatabaseVersionAsync();

        if (dbVersion == GetVersion())
        {
            // No need to upgrade, versions are same
            _logger.LogInformation("No need to upgrade, versions are equal: {DatabaseVersion} == {LocalVersion}", dbVersion, GetVersion());
            return;
        }

        _logger.LogInformation("Database must be upgraded");

        await using var con = new MySqlConnection(_connectionString);

        if (dbVersion == 1001)
        {
            var sql = $@"ALTER TABLE `calls` 
                    ADD COLUMN `{nameof(VideoRoomCall.Status)}` VARCHAR(255) NOT NULL DEFAULT '{nameof(VideoCallRoomStatus.Started)}'";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1002);
        }

        if (dbVersion == 1002)
        {
            var sql = $@"ALTER TABLE `calls` 
                    ADD COLUMN `{nameof(VideoRoomCall.Description)}` VARCHAR(255)";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1003);
        }

        if (dbVersion == 1003)
        {
            var sql = $@"ALTER TABLE `users` 
                    ADD COLUMN `{nameof(CallUser.Latitude)}` DOUBLE NOT NULL DEFAULT 0,
                    ADD COLUMN `{nameof(CallUser.Longitude)}` DOUBLE NOT NULL DEFAULT 0";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1004);
        }

        if (dbVersion == 1004)
        {
            var sql = $@"ALTER TABLE `users` 
                    ADD COLUMN `{nameof(CallUser.FullName)}` VARCHAR(255)";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1005);
        }

        if (dbVersion == 1005)
        {
            var sql = $@"ALTER TABLE `calls` 
                    ADD COLUMN `{nameof(VideoRoomCall.RoomType)}` VARCHAR(25) NOT NULL DEFAULT 'call',
                    ADD COLUMN `{nameof(VideoRoomCall.LiveStreamId)}` BIGINT NOT NULL DEFAULT 0";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1006);
        }

        if (dbVersion == 1006)
        {
            var sql = $@"ALTER TABLE `calls` 
                    ADD COLUMN `{nameof(VideoRoomCall.TopicFullName)}` VARCHAR(255),
                    ADD COLUMN `{nameof(VideoRoomCall.InitiatorFullName)}` VARCHAR(255)";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1007);
        }

        if (dbVersion == 1007)
        {
            var sql = $@"ALTER TABLE `users` 
                    ADD COLUMN `{nameof(CallUser.Orientation)}` VARCHAR(100)";

            _logger.LogTrace("SQL: {Sql}", sql);
            await con.ExecuteAsync(sql);

            dbVersion = await UpdateVersionAsync(1008);
        }

        var latestDbVersion = await GetDatabaseVersionAsync();
        if (latestDbVersion != CurrentVersion)
        {
            _logger.LogError("Failed to perform database upgrade to version {NewVersion}, database is still at {DatabaseVersion}", CurrentVersion, latestDbVersion);
        }
    }

    private async Task<int> UpdateVersionAsync(int newVersion)
    {
        _logger.LogInformation("Updating the database version to {NewVersion}...", newVersion);

        var sql = "UPDATE kvmeta SET v = @Version WHERE k = 'version'";
        _logger.LogTrace("SQL: {Sql}", sql);

        await using var connection = new MySqlConnection(_connectionString);
        await connection.ExecuteAsync(sql, new {Version = newVersion});

        _logger.LogInformation("Database version updated to {NewVersion}", newVersion);

        return newVersion;
    }
}