Migrating Sequences from Dynamo to MySQL

Introduction:

In the realm of database migration, even seemingly small tasks like transitioning a Sequence Generator can present unique challenges. In this article, we'll explore the complexities of migrating a Sequence Generator from DynamoDB to MySQL in a .NET environment. Join us as we delve into the details of this migration, uncovering the solutions we employed to ensure a seamless transition.

Context:

During a recent database migration project, we encountered the need to migrate our Sequence Generator from DynamoDB to MySQL. While this might appear to be a straightforward task, it posed unexpected complexities. Notably, MySQL does not natively support sequences, necessitating the implementation of a custom solution.

The Challenge:

As we embarked on this migration, we realized that MySQL's lack of built-in support for sequences demanded a thoughtful approach. We needed to devise a custom solution that would enable us to maintain the integrity of our Sequence Generator while capitalizing on the capabilities of MySQL.

Leveraging Dapper for MySQL Integration:

To address this challenge, we turned to Dapper, a lightweight and efficient Object-Relational Mapping (ORM) tool. By utilizing Dapper, we could seamlessly integrate our Sequence Generator with MySQL, bridging the gap between our existing codebase and the unique requirements of the new database.

While considering our options for the Sequence Generator migration, we initially explored the possibility of using raw SQL execution with Entity Framework. However, we encountered a hurdle as our application was still running on EF Core 6, which unfortunately did not support this feature at that time. This compelled us to find an alternative solution that would allow us to achieve our goals within the constraints of our technology stack.

Solution

Sequence Generator in DynamoDB:

public class SequenceGenerator : ISequenceGenerator
{
    private static readonly SemaphoreSlim Semaphore = new(1, 1);

    private readonly DynamoDbContext _dbContext;

    public SequenceGenerator(DynamoDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public Task<int> GetNextAsync(SequenceType sequenceType)
    {
        return Policy<int>.Handle<ConditionalCheckFailedException>().WaitAndRetryAsync(
            3,
            retryAttempt => TimeSpan.FromMilliseconds(50) * retryAttempt
        ).ExecuteAsync(() => GetNextNumberFromSequenceAsync(sequenceType));
    }

    private async Task<int> GetNextNumberFromSequenceAsync(SequenceType sequenceType)
    {
        var sequenceKey = Sequence.GetKey(sequenceType);
        int result;

        try
        {
            await Semaphore.WaitAsync();
            var sequence = await _dbContext.GetItemAsync<Sequence>(sequenceKey, sequenceKey);
            if (sequence is null)
            {
                sequence = Sequence.Init(sequenceType);
                result = sequence.Get();
                await _dbContext.PutItemAsync(sequence);
            }
            else
            {
                var condition = Condition<Sequence>.On(x => x.CurrentValue).EqualTo(sequence.CurrentValue);
                result = sequence.Get();
                await _dbContext.UpdateItem<Sequence>()
                    .WithPrimaryKey(sequenceKey, sequenceKey)
                    .WithCondition(condition)
                    .On(x => x.CurrentValue).Assign(result)
                    .On(x => x.LastUsed).Assign(sequence.LastUsed)
                    .ExecuteAsync();
            }
        }
        finally
        {
            Semaphore.Release();
        }

        return result;
    }
}

As you may see, code was quite complex. Nevertheless, it was getting stuff done!

Sequence Generator in MySql:

public class SequenceGenerator : ISequenceGenerator
{
    private readonly IDbConnection _dbConnection;

    public SequenceGenerator(DatabaseConfiguration databaseConfiguration)
    {
        _dbConnection = new MySqlConnection(databaseConfiguration.UserVariablesConnectionString);
    }

    public async Task<int> GetNextAsync(SequenceType sequenceType)
    {
        var current = await _dbConnection.ExecuteScalarAsync<int>(
            $@"
UPDATE Sequences SET Value = (@next := Value + 1) WHERE SequenceType = {(int)sequenceType};
SELECT @next;"
        );

        return current;
    }
}

Worth noticing that we needed to create new database table and support new connection string, which allowed us to use User Defined Variables:

public class DatabaseConfiguration
{
    public string ConnectionString => "";
    public string UserVariablesConnectionString => $"{ConnectionString};Allow User Variables=True";
}

public class Sequence
{
    public Sequence(int value, SequenceType sequenceType)
    {
        Value = value;
        SequenceType = sequenceType;
    }

    public int Value { get; private set; }
    public SequenceType SequenceType { get; }
}

public class SequenceEntityConfiguration : IEntityTypeConfiguration<Sequence>
{
    public void Configure(EntityTypeBuilder<Sequence> builder)
    {
        builder.ToTable("Sequences");

        builder.HasKey(x => x.SequenceType);
        builder.Property(x => x.Value);

        builder.HasData(
            new Sequence(100, SequenceType.SequenceA), // start from 100
            new Sequence(200, SequenceType.SequenceB) // start from 200
        );
    }
}

Testing the Sequence Generator:

To validate the successful migration and functioning of our Sequence Generator, we conducted two crucial tests:

Sequential Calls:

public class SequenceGeneratorTests : BaseDatabaseTests
{
    private readonly ISequenceGenerator _sut;

    public SequenceGeneratorTests()
    {
        _sut = new SequenceGenerator(DatabaseConfiguration);
    }

    [Fact]
    public async Task GetNextAsync_GivenTwoSequenceCalls_GivesIncreasedValues()
    {
        //When
        var firstNumber = await _sut.GetNextAsync(SequenceType.Submerchant);
        var secondNumber = await _sut.GetNextAsync(SequenceType.Submerchant);

        //Then
        secondNumber.Should().BeGreaterThan(firstNumber);
    }
}

Parallel Calls:

public class SequenceGeneratorTests : BaseDatabaseTests
{
    private readonly ISequenceGenerator _sut;

    public SequenceGeneratorTests()
    {
        _sut = new SequenceGenerator(DatabaseConfiguration);
    }

    [Fact]
    public async Task GetNextAsync_GivenFiveParallelCallsFromNewInstances_GivesIncreasedValues()
    {
        // Given
        var tasks = Enumerable.Repeat(5, 5).Select(
            _ => new SequenceGenerator(DatabaseConfiguration).GetNextAsync(SequenceType.Submerchant)
        );

        //When
        var result = await Task.WhenAll(tasks);

        //Then
        result.Should().OnlyHaveUniqueItems();
    }
}

Conclusion:

Migrating a Sequence Generator from DynamoDB to MySQL might seem like a minor aspect of a larger database migration project. However, the challenges arising from MySQL's lack of built-in sequence support required careful consideration and the implementation of a custom solution. By leveraging Dapper and our expertise, we successfully migrated our Sequence Generator while maintaining reliability and ensuring accurate ID generation.