Building .Net Core API for easy database switch

Building a solution from the start to be able to switch database can be a very smart move. In some cases it could be argued that too much software is built for re-usability and with layers of abstraction that are never actually used for anything. But in some cases it makes a lot of sense to make sure that you build your solution in a way that would afford you a good number of options going forward.

So I will explain how you can easily build your .Net Core solutions to enable easy switch of database, but also a little story of a case where I was actually very glad that I had build my solution with this option in mind.

The startup

I am currently working on a startup in the health care sector. We defined the product as requiring an app for both iOS and Android. This pointed me towards React Native as I already had knowledge with React. But it also required a backend API which in theory could be built in just about anything. I am a long time .Net developer as I have worked with .Net since 2006, but I also had an itch towards trying a new programming language.

So my thoughts circled around building this new backend API in Python for instance, as that was one of the languages I wanted to learn. But building something new from scratch in a technology that is unknown and with a deadline, is no easy feat, so Python was not the way to go.

I also wanted something that did not narrow my options for hosting too much, but was still familiar enough for me to be proficient and get something out of the door quickly. My decision ended up being .Net Core with a Microsoft SQL database underneath, handled by Entity Framework via code first. But keeping to my promise of not narrowing my hosting options too much, I wanted to make it easy for me to change the underlying database from MSSQL to something else.

This effort proved useful although the choice of database provider I ended up with was not what I had predicted, but the theory of having to replace MSSQL proved correct.

The code

Enabling the database to be switched altogether has actually never been easier than with Entity Framework and.Net Core. Basically I have the following structure in my API solution, ignoring test projects:

Solution
 - DataModel
    - Context
    - Entities
 - MsSqlDatabase
    - Migrations
 - MySqlDatabase
    - Migrations
 - WebApi
    - Dtos
    - Controllers
    - ....

So basically all my classes for code first generation reside in the DataModel project. This is just a basic class library apart from one thing. It also houses the context. This is the interface that enables the easy switching of database providers, which we will get to in a few seconds.

Then I have two Database projects, one is MSSQL and the other is MySQL as my initial theory was that if I was to switch then it would probably be to MySQL (spoiler alert: it wasn't). And then finally I have the actual WebApi project that exposes the Api and hosts the Startup.cs file we will look at later.

But lets start with the context file I promised earlier:

public interface IEfContext
{
    DbSet<User> Users { get; set; }
    DbSet<Program> Programs { get; set; }
    DbSet<Exercise> Exercises { get; set; }
    ...

    void SaveChanges();

    Task<int> SaveChangesAsync();
}

This is basically the interface that enables us to easily switch providers. By having the entire context described in this interface we can use the brilliant dependency injection capabilities in .Net Core to easily switch between actual databases. Having the two SaveChanges methods exposed via this interface also enables us to override the "normal" SaveChanges method and set some properties on save automatically, which I have also talked more about here: Override SaveChanges

For this project I automatically set CreatedTms and UpdatedTms using this method. And I have created an DataEntityBase class with these properties that I can use for all entities that I want this information present on:

public abstract class DataEntityBase
{
    public int Id { get; set; }
    public DateTime CreatedTime { get; set; }
    public DateTime UpdateTime { get; set; }
}

If we then look inside the MsSqlDatabase project we find two files that are not auto-created:

MsSqlDatabase
 - MsSqlEfContext.cs
 - MsSqlEfContextFactory.cs

Let us start by looking at MsSqlEfContext:

public class MsSqlEfContext : DbContext, IEfContext
{
    public MsSqlEfContext(DbContextOptions<MsSqlEfContext> options)
        : base(options)
        {
        }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserProgram>()
            .HasKey(k => new { k.UserId, k.ProgramId });


        base.OnModelCreating(modelBuilder);
    }

    public override int SaveChanges()
    {
        ContextHelper.UpdateTimestamps(ChangeTracker);

        return base.SaveChanges();
    }



    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
    {
        ContextHelper.UpdateTimestamps(ChangeTracker);

        return base.SaveChangesAsync(cancellationToken);
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Program> Programs { get; set; }
    public DbSet<Exercise> Exercises { get; set; }
    ...

    void IEfContext.SaveChanges()
    {
        this.SaveChanges();
    }

    async Task<int> IEfContext.SaveChangesAsync()
    {
        return await this.SaveChangesAsync();
    }
}

Unsurprisingly this MsSqlEfContext implements our IEfContext interface, which gives our DbSets and then the SaveChanges and SaveChangesAsync methods. As explained before, the primary reason for having these is that we want to be able to call SaveChanges on our IEfContext which we will be passing around, but also to be able to set some properties automatically on save. In order to obtain this ability to call SaveChanges on our IEfContext and still override the "normal" SaveChanges for the DbContext, we need to do some juggling as you can see from the code.

It also has a little logic to handle many-to-many relationships for a couple of our tables. This is handled in the OnModelCreating method.

Finally in order to be able to have ef migrations work from inside the project folder, we need the following. At least this class was needed at the time of me creating this project - it may have changed since. But as the saying goes - if it ain't broke - don't fix it.

public class MsSqlEfContextFactory : IDesignTimeDbContextFactory<MsSqlEfContext>
    {
        public MsSqlEfContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<MsSqlEfContext>();
            optionsBuilder.UseSqlServer<MsSqlEfContext>("Server=(localdb)\\mssqllocaldb;Database=myDb;Trusted_Connection=True;ConnectRetryCount=0;");

            return new MsSqlEfContext(optionsBuilder.Options);
        }
    }

With this class in place we have everything we need in order to be able to call "dotnet ef migrations add initial" and be on our way with Entity Framework code first migrations.

The only thing we need to be on our way with our MSSQL implementation is to wire it up in the Startup class and then pass around the IEfContext where ever it is needed. First in the Startup.cs file we need to add the following two lines inside the ConfigureServices method:

// To set MsSqlEfContext as the implementing class of our context
services.AddScoped<IEfContext, MsSqlEfContext>();

// To set SqlServer as the database and use our MsSqlEfContext - it then fetches the connection string from my appSettings class
services.AddDbContext<MsSqlEfContext>(options => options.UseSqlServer(appSettings.MsSqlConnectionString));

Having done this we can piggyback on the dependency injection offered in .Net Core and use the IEfContext inside the classes where it is needed. This is just a random example copied from a service in our solution but it shows the concept:

public class ExerciseService : IExerciseService
    {
        private readonly IEfContext _dbContext;
        private readonly IServiceProvider _serviceProvider;

        // IEfContext will be instantiated and handled by .Net Core dependency injection
        // The life-cycle will depend on how the service is added AddScoped is recommended
        // as shown in Startup.cs
        public ExerciseService(IEfContext dbContext, IServiceProvider serviceProvider)
        {
            _dbContext = dbContext;
            _serviceProvider = serviceProvider;
        }

        public IEnumerable<Exercise> GetExercises(int userId, int trainingProgramId)
        {
            var trainingProgram = _dbContext.TrainingPrograms.Where(x => x.UserId == userId && x.Id == trainingProgramId).FirstOrDefault();
            if (trainingProgram != null)
                return _dbContext.TrainingPrograms.Where(x => x.UserId == userId && x.Id == trainingProgramId).FirstOrDefault().Exercises;
            else
                throw new ArgumentException("Invalid trainingProgramId");
        }

        public Exercise AddNewExercise(Exercise exercise, int userId)
        {
            var trainingProgram = _dbContext.TrainingPrograms.Where(x => x.UserId == userId && x.Id == exercise.TrainingProgramId)
                    .Include(ex => ex.Exercises)
                    .FirstOrDefault();
            if (trainingProgram == null)
                throw new ArgumentException("Invalid training program for user");

            // We just create empty Exercise and add to context to create Id
            exercise.Index = trainingProgram.Exercises.Count > 0 ? trainingProgram.Exercises.Max(x => x.Index) + 1 : 0;

            trainingProgram.Exercises.Add(exercise);

            // SaveChanges on our IEfContext interface
            _dbContext.SaveChanges();

            return exercise;
        }
   }

This pretty much sums up the total implementation. But what it did not touch upon was changing the underlying database provider. Because this implementation worked a treat for very long with the underlying MSSQL database. But as I had anticipated the world changed.

The change

Relying on MSSQL did exactly what was planned; it got us up and running as quickly as possible. I hosted the backend at AspHostPortal, deployed easily from Visual Studio Publish method and that worked fine for internal testing.

But as we matured, both in terms of product offering and product maturity we needed to expand our abilities for scaling. This meant looking into more scalable offerings for hosting, especially to be able to handle storage of a lot of images and videos. Looking at the cheapest options also made it rather clear that change of database from MSSQL to PostgreSQL would keep a lot more doors open.

So finally it was time to put the theory to the test. How hard would it be to actually change database provider. I had as earlier mentioned created a MySqlEfDatabase project, as I thought that would be the other option we might use - but never really come around to using it. But at least the entire project was built on the assumption that we would meet this crossroad.

The new code

First of all I created the new project to hold PostgreSql. Then I added the Postgres Nuget package to that project (Npgsql.EntityFrameworkCore.PostgreSQL). The project follows the exact same structure as the MsSqlDatabase project explained earlier. The PostgreSqlEfContext class is apart from name even an exact copy from its sibling MsSqlEfContext.

This actually led me down a road to consider whether I needed two different projects at all. The theory was that I could potentially just wire them to use either .UseSqlServer or .UseNpgsql, but apart from that use the exact same implementation of IEfContext and hence not have two projects: MsSqlDatabase and PostgreSqlDatabase - but only just a generic Database project.

However looking at the migration files that is automatically created by entity framework it becomes apparent that they are distinct for the underlying database provider and hence trying to make a generic would probably lead to all sorts of strange behavior. Then a little duplication is much preferred.

The only thing in the PostgreSqlDatabase project that is different is hence the Nuget package for Postgres and then the PostgreSqlEfContextFactory class that looks like this:

public class PostgreSqlEfContextFactory : IDesignTimeDbContextFactory<PostgreSqlEfContext>
    {

        public PostgreSqlEfContext CreateDbContext(string[] args)
        {
            var optionsBuilder = new DbContextOptionsBuilder<PostgreSqlEfContext>();
            optionsBuilder.UseNpgsql<PostgreSqlEfContext>("User ID =user;Password=password;Server=server;Port=5432;Database=postgres;Integrated Security=true;Pooling=true;");

            return new PostgreSqlEfContext(optionsBuilder.Options);
        }
    }

Then creation of our Postgres database is as easy as calling: "dotnet ef migrations add initial" and then "dotnet ef database update" and voila! We have a Postgres database with the exact same tables and relations as we had in MSSQL.

In order to wire up Postgres we of course also need to add the Postgres Nuget package to the WebApi project. But then finally we just need to change two lines of code in the Startup.cs class. I have commented out the old ones here for easier reading:

// To set MsSqlEfContext as the implementing class of our context
// First comment out the MSSQL
// services.AddScoped<IEfContext, MsSqlEfContext>();

// Then add the Postgres specific
services.AddScoped<IEfContext, PostgreSqlEfContext>();

// To set SqlServer as the database and use our MsSqlEfContext - it then fetches the connection string from my appSettings class
// First comment out the MSSQL
//services.AddDbContext<MsSqlEfContext>(options => options.UseSqlServer(appSettings.MsSqlConnectionString));

// Then add the Postgres specific
services.AddDbContext<PostgreSqlEfContext>(opt =>
                opt.UseNpgsql(appSettings.PostgreSqlConnection));

And that is it! We just switched from one database provider to another in a smooth and painless way. A lot of it comes from .Net Core's clever dependency injection combined with the strengths of Entity Framework, we just took full advantage of it and combined it with some good old interfaces.

I hope you enjoyed this little walk through of building a .Net Core API for easy database switch. A further advantage of having switched to Postgres is also easier development of .Net Core in Ubuntu, which I have become extremely fond of. And of course also in OSx. But much more of that at another time.