Resetting SQL Sequence for multiple test environments

In the last post, I showed how we use SQL Sequence to achieve sequential number ID in a specific range. As mentioned in the last post, we and each party that we integrate with have a defined range of number to be used as ID and this ID must be unique globally among all the parties. One problem we have is that we have multiple test environments (i.e. Test1, Test2 and Test3) and they all connect to one same test environment from other parties. This means that we might create duplicate IDs across our test environments and might cause random validation exception from other parties.

We decided to solve this by dividing our range into smaller ranges for each test environment. Let’s say our range is 300 000 000 0001 -> 600 000 000 000. Then for our 3 test environments, it would look like this

Test1: 300 000 000 0001 -> 400 000 000 000

Test2: 400 000 000 0001 -> 500 000 000 000

Test1: 500 000 000 0001 -> 600 000 000 000

We make these to be configurable via appsettings. Each test environment will override with their own ranges.

// appsettings.json
"DbSequences": [
  {
    "Name": "ExternalBookId",
    "MinValue": 300000000001,
    "MaxValue": 600000000000,
    "Increment": 1
  }
]

// appsettings.Test1.json
"DbSequences": [
  {
    "Name": "ExternalBookId",
    "MinValue": 300000000001,
    "MaxValue": 400000000000,
    "Increment": 1
  }
]

Then on start up, we will create the sequences based on the configs or reset them if they already exist and are different to the configs.

First we have a class for our SequenceConfig

    public class SequenceConfig : IEquatable<SequenceConfig>
    {
        public const string ConfigSection = "DbSequences";

        public string Name { get; set; }
        
        public long MinValue { get; set; } 
        
        public long MaxValue { get; set; }
        
        public int Increment { get; set; }

        public bool Equals(SequenceConfig other)
        {
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;

            return Name == other.Name &&
                   MinValue == other.MinValue &&
                   MaxValue == other.MaxValue &&
                   Increment == other.Increment;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;

            return Equals((SequenceConfig)obj);
        }

        public override int GetHashCode()
        {
            return HashCode.Combine(Name, MinValue, MaxValue, Increment);
        }
    }

Then we have an extension method for DatabaseFacade which will be responsible to creating and/or resetting the sequences according to the configs.

    public static class DatabaseFacadeExtensions
    {        
        public static void UseSqlSequences(this DatabaseFacade database, IConfiguration configuration)
        {
            var sequenceConfigs = (configuration
                .GetSection(SequenceConfig.ConfigSection)
                ?.Get<List<SequenceConfig>>() ?? new List<SequenceConfig>())
                .ToDictionary(x => x.Name);

            if (sequenceConfigs.Count == 0)
                return;

            var existingSequences = GetExistingMatchingSequences(database, sequenceConfigs.Keys);

            var sequencesToCreate = sequenceConfigs.Keys
                .Except(existingSequences.Keys)
                .Select(seqName => sequenceConfigs[seqName])
                .ToList();

            var sequencesToUpdate = existingSequences.Values
                .Except(sequenceConfigs.Values)
                .Select(dbSequence => sequenceConfigs[dbSequence.Name])
                .ToList();

            sequencesToCreate.ForEach(x => CreateSequence(database, x));
            sequencesToUpdate.ForEach(x => ResetSequence(database, x));
        }

        private static Dictionary<string, SequenceConfig> GetExistingMatchingSequences(
            DatabaseFacade database, IEnumerable<string> sequenceNames)
        {
            var connection = database.GetDbConnection();

            // Using Dapper here to keep it simple. Alternatively, you can use ADO.NET if you don't like Dapper
            return connection
                .Query<SequenceConfig>(@"
                    select name as Name,
                           minimum_value as MinValue,
                           maximum_value as MaxValue,
                           increment as Increment
                    from sys.sequences
                    where name in @SequenceNames;",
                    new { SequenceNames = sequenceNames })
                .ToDictionary(seq => seq.Name, seq => seq);
        }

        private static void ResetSequence(DatabaseFacade database, SequenceConfig config)
            => database.ExecuteSqlRaw(@$"
                alter sequence {config.Name}
                restart with {config.MinValue}
                increment by 1
                minvalue {config.MinValue}
                maxvalue {config.MaxValue}
                no cycle;");

        private static void CreateSequence(DatabaseFacade database, SequenceConfig config)
            => database.ExecuteSqlRaw(@$"
                create sequence {config.Name}
                start with {config.MinValue}
                increment by 1
                minvalue {config.MinValue}
                maxvalue {config.MaxValue}
                no cycle;");
    }

And finally, in the StartUp.cs after you have run the database migration (this to ensure the database has been created), call the new extension method to set up the sequences.

public override void Configure(IApplicationBuilder app)
{
    using var serviceScope = app.ApplicationServices.CreateScope();
    var context = serviceScope.ServiceProvider.GetService<DbContext>();
    context.Database.Migrate();
    context.Database.UseSqlSequences(Configuration);
}

That’s it. This now allows us to set up sequences with different ranges per test environment.