EF Core and database replication.

Motive

At Innoloft We had a large code base, and we needed to use database replication to offload querying data from the main database (master database) to one of the replicas, and we needed to do so quickly because the system useability was low, so changing the service code to make it to connect to two databases was tedious and it would take so much time, so that was not possible.

Solution

We coded a library that sits on top of EntityFramework and below a service code, the main class was ReplicationDbContext, this class will be inherited by service code instead of DbContext

public class OffersContext : ReplicationDbContext
{

    public DbSet<Offer> Offers { get; set; }


    public OffersContext([NotNullAttribute] DbContextOptions options) : base(options)
    {
    }
...
}

The ReplicationDbContext contains two references to two DbContexts one of them will connect to the main DB and execute commands on it (INSERT, UPDATE, and DELETE), and the other reference is to a context that will connect to a database replica and queries data from it. 

public class OffersController
{
    [HttpPut("/{id:int}")]
    public async Task Update(int id, OfferVm vm)
    {
        var offer = _offersContext.Set<Offer>().FirstOrDefaultAsync(o => o.Id == id);
 // this will read from a replica and make the writing context track the offer object.

        offer.Name = vm.Name;

        _offersContext.Set<Offer>().Update(offer);
        await _offersContext.SaveChangesAsync(); // this will tell the writing context to save the changes that it has been tracking.
    }
}

Configuring the contexts to connect to different databases is fairly simple because the library exposed some extension methods:

services.AddDbContext<OffersContext>(
writeOptionsAction: options =>
{
    options.UseMySql(Configuration["OFFERS_DB_CONNECTION_STRING_MASTER"], new MySqlServerVersion(new Version(5, 7)));
}, readOptionsAction: options =>
{
    options.UseMySql(Configuration["OFFERS_DB_CONNECTION_STRING_RO"], new MySqlServerVersion(new Version(5, 7)));
});

By swapping the DBContext with ReplicationDbContext we didn’t need to change any other client code.

Alternatives

One good alternative to the above solution would be ProxySQL, with it you can “Build complex ProxySQL Query Rules to route writes to primaries, distribute reads across replicas and rewrite queries on the fly with highly granular criteria.”

Why didn’t we use ProxySQL?

With our custom solution, a developer using only code has the power to choose whether to read data from the main database or one of the replicas, because some areas of our system couldn’t afford the time that it takes the replica to update its data, and we needed to read data from the main database directly.

_offersContext.WritingContext.Set<Offer>().ToListAsync() // this will read data from the main database
_offersContext.Set<Offer>().ToListAsync() // this will read data from one of the replicas