Build a Multi-Tenant App With Multiple Databases – Free Guide (Part 2)

In the last article, we learned the basics of building a multi-tenant application with ASP.NET and Entity Framework. In this article, we will build on that same project and refactor the solution to use a per-tenant database approach.

If you haven’t read the previous article on Building Multi-Tenant Applications with Entity Framework and ASP.NET 7, please check that out here. If you don’t already have it, download the finished GitHub project of that article because that will serve as the starting point for this article.

Download the finished version of Part 1: Single-Database Multi-Tenant App here from GitHub

The completed version of the multi-tenant solution we will build in this article is also available as a finished GitHub repository.

Download the finished version of this tutorial Part 2: Multiple-Database Multi-Tenant App here from GitHub

Download the Postman collection for testing here.

Overview

To support multiple databases, we will need to make a few critical changes:

• Save a connection string per each tenant
• Add an OnConfiguring method to our database context model to allow for using a different connection string per each request.
• Add a tenant service & controller to allow creating new tenants
• Run migrations when creating a new tenant
• During startup, run any pending migrations on tenant databases

Adding a Connection String Per Tenant

To begin, let’s start by adding a new string field to the Tenant table and call it ConnectionString. This is where we will specify the connection string for the tenant-specific database or choose to leave null and have the tenant use the default shared database. Make sure to allow null values on the ConnectionString property by adding a question mark.

public class Tenant
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Id { get; set; }
    public string Name { get; set; }
    public string? ConnectionString { get; set; }

}

Next we should update our CurrentTenantService class and interface to include this new ConnectionString field so that the ConnectionString property is always available.

In the SetTenant method, we will retrieve the connection string from the database and set the property at the same time that we set the tenant ID.

With that out of the way, we’ll update the ApplicationDbContext class to use the connection string when there is one in its OnConfiguring override method, which fires on every request.

Modify the Database Context OnConfiguring Override

In the last tutorial, we added two key overrides in the ApplicationDBContext to get our multi-tenant application working. The first was the OnModelCreating override where we added a query filter to select only those records which had matching a tenant Id. The second was the SaveChanges override where we saved the current tenant Id to any entity that implemented the IMustHaveTenant interface.

We will now add a third override to tell entity framework to use a different connection string whenever there’s one specified by the current tenant. The override we will add is the OnConfiguring method, which fires on every request.

 public class ApplicationDbContext : DbContext
 {
     private readonly ICurrentTenantService _currentTenantService;
     public string CurrentTenantId { get; set; }
     public string CurrentTenantConnectionString { get; set; }


     // Constructor 
     public ApplicationDbContext(ICurrentTenantService currentTenantService, DbContextOptions<ApplicationDbContext> options) : base(options)
     {
         _currentTenantService = currentTenantService;
         CurrentTenantId = _currentTenantService.TenantId;
         CurrentTenantConnectionString = _currentTenantService.ConnectionString;

     }

     // Application DbSets -- create for entity types to be applied to all databases
     public DbSet<Product> Products { get; set; }

     // On Model Creating - multitenancy query filter, fires once on app start
     protected override void OnModelCreating(ModelBuilder builder)
     {         
         builder.Entity<Product>().HasQueryFilter(a => a.TenantId == CurrentTenantId); 
     }

     // On Configuring -- dynamic connection string, fires on every request
     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     {
         string tenantConnectionString = CurrentTenantConnectionString;
         if (!string.IsNullOrEmpty(tenantConnectionString)) // use tenant db if one is specified
         {
             _ = optionsBuilder.UseSqlServer(tenantConnectionString);
         }
     }
     
     // On Save Changes - write tenant Id to table
     public override int SaveChanges()
     {        
         foreach (var entry in ChangeTracker.Entries<IMustHaveTenant>().ToList()) 
         {
             switch (entry.State)
             {
                 case EntityState.Added:
                 case EntityState.Modified:
                     entry.Entity.TenantId = CurrentTenantId; 
                     break;
             }
         }
         var result = base.SaveChanges();
         return result;
     }
 }

The code in the OnConfiguring override checks for any tenant specific connection string for the current tenant and when there is one, passes that connection string as an option optionsBuilder. Doing this will tell entity framework to use this connection string for the current request. If the current tenant connection string is null, the optionsBuilder code isn’t triggered and the request will use the default connection string like it normally would.

Update the Migrations

In a multiple database setup, we need to consider that there are two types of databases. One being the main ‘central’ shared database that stores the tenant table itself, along with any other configuration-related tables that we may add in the future like subscription, billing, or even identity. And then there are all the other databases, which are per-tenant and should not contain those central configuration tables. The tenant-specific databases should only contain application-related tables, like Products and any more that we add.

To achieve this, we will need manage two sets of migrations: one set for entities that are exclusive to the central database, and another set for all the other tables which can be found across all the databases.

Let’s begin by removing the migrations and database snapshot we created in the previous tutorial (there should only be one called initial).  We should also remove the Tenants DbSet from the ApplicationDbContext because we don’t want that table to be generated in the tenant-specific databases.

If we were being strict about every tenant having their own database, we could actually remove the tenant query filters and tenant ID columns. Its better to leave it though because it will allow for tenants to share the same database and still retain data isolation. This is what you would call a hybrid multi-tenant implementation.

In our single-database multi-tenant setup, we used the TenantDbContext purely for looking up tenant information when a request comes in. It was essentially a read-only context and had no responsibilities in terms of migrations. In this multi-database setup, we’ll still be using it as a lookup context for incoming request, but we’ll also use it to manage migrations on tables specific to the main central database.  The ApplicationDbContext will manage migrations on tables found across every tenant database.

Open up Package Manager Console and create an initial migration for the central database. For that we will need to target the TenantDbContext. To keep things organized, we can also specify an output directory for migrations to keep the two groups separate with the -o switch. Here is the command:

add-migration Initial -context TenantDbContext -o Migrations/TenantDb

Lets apply the migration by typing the following:

update-database -context TenantDbContext 

Now if we go explore our databases, can see that one was created and it contains one table for tenants. If we expand the migrations folder in the solution explorer, we should see there is a new subfolder called TenantDb and that contains our migration. If that’s the case for you, then you’ve done everything correctly up to this point.

Next, we’ll create another migration this time for the ApplicationDbContext. Back in Package Manager Console type the following command, this time notice we specify a new context and a new output directory:

add-migration InitialApp -context ApplicationDbContext -o Migrations/AppDb

Let’s apply the migration by typing the following:

update-database -context ApplicationDbContext 

If we check the database now, we’ll see that the products table has been created. In the solution explorer, there is another folder in migrations with the new migration we just added.

Create a Tenant Service

In the single-database setup from the previous tutorial, we never actually made a service or controller to handle creation of new tenants. In that simplistic setup, we just added the tenants manually in the table itself. With a multiple database setup however, that will not suffice because we will need to run some automation when we create new tenants. Every time a new tenant is created, we’ll need to generate a connection string, spin up a new database, and apply any pending migrations.

Start by creating a new folder in Services called TenantService and add an interface called ITenantService. In this interface define one method called CreateTenant. In a real application, there should be methods for retrieving lists, and updating tenants. For brevity, we will only worry about creating tenants in this tutorial.

public interface ITenantService
{
    Tenant CreateTenant(CreateTenantRequest request);
}

Create a new DTOs folder within the TenantService folder and call it CreateTenantRequest. It will contain an ID and Name property like the Tenant entity itself and also a Boolean field called Isolated to indicate if this tenant should have its own isolated database.

public class CreateTenantRequest
{
    public string Id { get; set; }
    public string Name { get; set; }
    public bool Isolated { get; set; }
}

Finally let’s create the service itself, called TenantService.

public class TenantService : ITenantService
{

    private readonly TenantDbContext _context; // database context
    private readonly IConfiguration _configuration;
    private readonly IServiceProvider _serviceProvider;

    public TenantService(TenantDbContext context, IConfiguration configuration, IServiceProvider serviceProvider)
    {
        _context = context;
        _configuration = configuration;
        _serviceProvider = serviceProvider;
    }

    public Tenant CreateTenant(CreateTenantRequest request)
    {

        string newConnectionString = null;
        if (request.Isolated == true)
        {
            // generate a connection string for new tenant database
            string dbName = "multiTenantAppDb-" + request.Id;
            string defaultConnectionString = _configuration.GetConnectionString("DefaultConnection");
            newConnectionString = defaultConnectionString.Replace("multiTenantAppDb", dbName);

            // create a new tenant database and bring current with any pending migrations from ApplicationDbContext
            try
            {
                using IServiceScope scopeTenant = _serviceProvider.CreateScope();
                ApplicationDbContext dbContext =   scopeTenant.ServiceProvider.GetRequiredService<ApplicationDbContext>();
                dbContext.Database.SetConnectionString(newConnectionString);
                if (dbContext.Database.GetPendingMigrations().Any())
                {
                    Console.ForegroundColor = ConsoleColor.Blue;
                    Console.WriteLine($"Applying ApplicationDB Migrations for New '{request.Id}' tenant.");
                    Console.ResetColor();
                    dbContext.Database.Migrate();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        Tenant tenant = new() // create a new tenant entity
        {
            Id = request.Id,
            Name = request.Name,
            ConnectionString = newConnectionString,
        };

        _context.Add(tenant);
        _context.SaveChanges();

        return tenant;
    }
}

In this service, we’ll use the TenantDbContext to interact with tenant data since its data that is managed only in the central database. The CreateTenant method takes the DTO as a request and in the case of an isolated tenant, performs several additional steps.

The first step is to generate a new connection string. We could just hard code a base connection string, but a slightly more elegant solution is to use the IConfiguration interface, which allows us to read fields from appsettings.json. We read the DefaultConnection from appsettings.json and simply add a dash with the Id of the tenant to the central database name. In a real application, you might want to ensure that no whitespace or illegal characters are present in the Id of the tenant before proceeding further.

The next step is critical. Using the help of the IServiceProvider interface, we pull down a new scoped instance of the ApplicationDbContext from the service provider. With this instance, we set the new connection string on it and then check for any pending migrations. Of course there will be pending migrations because there isn’t even a database created yet. The framework will detect that and apply all pending ApplicationDbContext migrations.

Isolated database or not, the final step of the create tenant method is to save the new Tenant to the main database.

Lastly, in Program.cs we’ll need to add the Tenant service to the service container as a transient service so it can be used throughout our application. Add the following line after the product service we created in the previous tutorial.

builder.Services.AddTransient<ITenantService, TenantService>();

Create the Tenant Controller

To make use of the new tenant service and interact with it via REST API calls, create a new API controller in the Controllers folder called TenantsController. We’ll inject the tenantService in the constructor and use it in the Post method as follows:

[Route("api/[controller]")]
[ApiController]
public class TenantsController : ControllerBase
{
    private readonly ITenantService _tenantService;

    public TenantsController(ITenantService tenantService)
    {
        _tenantService = tenantService;
    }

    public ITenantService TenantService { get; }

    // Create a new tenant
    [HttpPost]
    public IActionResult Post(CreateTenantRequest request)
    {
        var result = _tenantService.CreateTenant(request);
        return Ok(result);
    }
}

With that out of the way, run the application and we can open up Postman to test everything out.

Testing Multi-Tenant Operations with Postman

Make a new Post API call with the following JSON in the body. The Name of the tenant can be however you want but the Id cannot contain any special characters or whitespace. The Id remember is what will be appended to the database name to create the new tenant-specific database. Set the Isolated boolean to true so that a new database is created when you send the request.

Here is a postman collection you can use to make your life easier.

Once you click send, you should receive a 200 OK status response and a new database should appear when you refresh your database explorer! Create a few more tenants both isolated and shared to see what happens.

The query filters and dynamic connection string should be working too. Try creating some sample products to see what happens. Remember to change the tenant ID in the header when sending requests. New products will be saved to their tenant databases when a connection string is specified. Now obviously this is in no way secure since anyone can just change the request header and access another tenant. To secure the application you would need to integrate ASP Identity and store/read the tenant key in a token. The Nano ASP.NET Boilerplate has that all taken care of already along with a lot of other great features and will definitely save you a lot of time messing with that.

Alas we have multi-tenant behavior implemented in a per-tenant database model! But we can’t call it a day just yet. We need to consider what happens when we create new migrations. If we add a new field to Product and run a migration, that migration won’t be applied to all the tenant databases, only the central one. That is a problem that we will address in the following section.

Automating migrations

Running the update-database command will apply migrations on only the one database specified in the default connection string found in appsettings.json. What we need is to loop through each tenant, and apply migrations for every database found – not just the main one. Luckily, there is a way to programmatically apply migrations instead of having to type the command into PMC using the .migrate() method. We’ll rely on this method to achieve the goal.

In summary what we need to do is:

• Tap into the app startup process using a service extension
• Apply any migrations to the main database (TenantDbContext)
• Read the Tenant table
• Loop through each tenant
• When a tenant has a connection string, apply any ApplicationDbContext migrations for this database instance

Start by creating a new folder in the solution explorer called Extensions. What we are creating here is an extension method to the app configuration process. I’ve named this static class MultipleDatabaseExtensions and it has one method AddAndMigrateTenantDatabases. The code is as follows:

public static class MultipleDatabaseExtensions
{
    public static IServiceCollection AddAndMigrateTenantDatabases(this IServiceCollection services,   IConfiguration configuration)
    {

        // Tenant Db Context (reference context) - get a list of tenants
        using IServiceScope scopeTenant = services.BuildServiceProvider().CreateScope();
        TenantDbContext tenantDbContext = scopeTenant.ServiceProvider.GetRequiredService<TenantDbContext>();

        if (tenantDbContext.Database.GetPendingMigrations().Any())
        {
            Console.ForegroundColor = ConsoleColor.Blue;
            Console.WriteLine("Applying BaseDb Migrations.");
            Console.ResetColor();
            tenantDbContext.Database.Migrate(); // apply migrations on baseDbContext
        }


        List<Tenant> tenantsInDb = tenantDbContext.Tenants.ToList();

        string defaultConnectionString = configuration.GetConnectionString("DefaultConnection"); // read default connection string from appsettings.json

        foreach (Tenant tenant in tenantsInDb) // loop through all tenants, apply migrations on applicationDbContext
        {
            string connectionString = string.IsNullOrEmpty(tenant.ConnectionString) ? defaultConnectionString : tenant.ConnectionString;

            // Application Db Context (app - per tenant)
            using IServiceScope scopeApplication = services.BuildServiceProvider().CreateScope();
            ApplicationDbContext dbContext = scopeApplication.ServiceProvider.GetRequiredService<ApplicationDbContext>();
            dbContext.Database.SetConnectionString(connectionString);
            if (dbContext.Database.GetPendingMigrations().Any())
            {
                Console.ForegroundColor = ConsoleColor.Blue;
                Console.WriteLine($"Applying Migrations for '{tenant.Id}' tenant.");
                Console.ResetColor();
                dbContext.Database.Migrate();
            }
        }

        return services;
    }

}

This class is going to take care of migrations for both contexts, TenantDbContext and ApplicationDbContext. First it will create a scoped instance of the TenantDbContext on the main database. We know it’s the main database because we aren’t specifying any connection string options and it will therefore use the default one. If there any TenantDbContext migrations pending, they will get applied.

Next, we read a list of Tenants using this same context instance. We loop through each tenant in the list and if there is a connection string present, we use it. Otherwise, in a case of null, we set the connection string to the default database. Any pending ApplicationDbContext migrations are then applied to the target database instance.

Finally, we need to hook this into the app configuration process back in program.cs. Right after the code that adds database contexts, add this line. Now this extension will run whenever the app starts.

builder.Services.AddAndMigrateTenantDatabases(builder.Configuration);

This is how the Program.cs class should look at this point

using Microsoft.EntityFrameworkCore;
using multiTenantApp.Extensions;
using multiTenantApp.Middleware;
using multiTenantApp.Models;
using multiTenantApp.Services;
using multiTenantApp.Services.ProductService;
using multiTenantApp.Services.TenantService;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();

// Current tenant service with scoped lifetime (created per each request)
builder.Services.AddScoped<ICurrentTenantService, CurrentTenantService>();

// adding a database service with configuration -- connection string read from appsettings.json
builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddDbContext<TenantDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddAndMigrateTenantDatabases(builder.Configuration);

// Product CRUD service with transient lifetime
builder.Services.AddTransient<ITenantService, TenantService>();
builder.Services.AddTransient<IProductService, ProductService>();

var app = builder.Build();

app.UseHttpsRedirection();
app.UseAuthorization();
app.UseMiddleware<TenantResolver>();
app.MapControllers();

app.Run();

To test out our new extension, create a new string field in the Products table called Supplier. Generate a new migration with the following command, remember to specify the context and the output folder. Since Product is an application table found in every database, we will use the ApplicationDbContext:

add-migration AddFieldToProduct -context ApplicationDbContext -o Migrations/AppDb

Now that our migrations are handled programmatically, all we need to do is run the application. There’s no need to run the update-database command. If you do run the update-database command, you will get a message saying that no migrations were applied. This is because before Entity Framework applies the migrations, it builds the application. During that build process, our application applies the pending migrations. As a result, by the time the command runs, no migrations are pending.

Run the app and inspect the databases. You will see that the new field has been added to our main database and all of the tenant databases as well. We now have a completely functional multiple database multi-tenant setup.

Any migrations on both the TenantDbContext and ApplicationDbContext will be handled programmatically by this new extension method.

Design Time Factory

We may find ourselves feeling like Ripley from the original Alien film now, having nuked the USCSS Nostromo and thinking our problems are behind us. But suddenly the alien crawls out of the wall for one final fight. And here is that scene for us:

There is one last issue and that is that you will get an error if you try to add any fields to the Tenant table. Strangely, adding new entities to the TenantDbContext or modifying any of those entities wont result in any problem. Only the Tenant table will give you an error. Try adding a new field to the Tenant entity called SubscriptionLevel and create a new migration. Remember to specify the TenantDbContext and the output directory:

add-migration AddFieldToTenant -context TenantDbContext -o Migrations/TenantDb

Now run the app to apply the migration. This is the error you will receive

This, as you could image, took hours to figure out just what in the actual F&#! was going on when I first encountered the issue.

The problem is in our AddAndMigrateTenantDatabases method, where we read a list of Tenants from the Tenants table. We cannot read from a table that is being modified during design time. Well, we can but we need to tell the Entity Framework scaffolding tool explicitly which connection string we want to use in design time.

Entity Framework Core provides an interface we can use to specify criteria we want to use which will override and default behavior during design time. That interface is aptly named IDesignTimeDbContextFactory and it takes the DbContext in question as a type. Create a new class in the Models folder called TenantDbContextFactory and follow the code example below:

public class TenantDbContextFactory : IDesignTimeDbContextFactory<TenantDbContext>
{
    public TenantDbContext CreateDbContext(string[] args) // neccessary for EF migration designer to run on this context
    {

        // Build the configuration by reading from the appsettings.json file (requires Microsoft.Extensions.Configuration.Json Nuget Package)
        IConfigurationRoot configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        // Retrieve the connection string from the configuration
        string connectionString = configuration.GetConnectionString("DefaultConnection");


        DbContextOptionsBuilder<TenantDbContext> optionsBuilder = new();
        _ = optionsBuilder.UseSqlServer(connectionString);
        return new TenantDbContext(optionsBuilder.Options);
    }
}

The critical line of code is the DbContextOptionsBuilder<TenantDbContext> part near the end where we specify the connection string. The rest of the code is just making things clean by reading the default connection string from appsettings.json to avoid hard coding it in this class. To read from configuration in this class you will need to install one Nuget package, Microsoft.Extensions.Configuration.Json.

With that class added, run the app to apply the new migration. The app should start without issue and the new field will be added to the tenant table.

In Conclusion

Well, that was fun, wasn’t it? Now you can tell all your friends you know how to create a multi-tenant application with a database per tenant implementation and they will no doubt think that that’s awesome. They may even proceed to tell you about their new app idea and ask you to build it for them.

There are a few things to consider before embarking down the database per tenant path. One is that this setup is only complete on our local development environment. You would need to add additional code to handle creating new databases on your cloud provider’s infrastructure. Cloud providers like Azure and AWS provide .NET SDKs for this very thing.

The other concern is that managing migrations can be difficult in situations where things go wrong. Imagine a scenario though where you have 50 tenant databases and for some reason one of them throws an error. Reverting to a previous state or doing any kind of backtracking is notoriously difficult. You should plan for these kinds of issues before deciding to run multiple databases.

Consider all the pros and cons of a multiple database setup before choosing it. A multi-database setup may be more efficient when it comes to scaling but single database multi-tenant setups are much easier to manage in regards to deployment and migrations.

If you are planning to build an MVP and are looking for it to scale and have robust SaaS features, consider using the Nano ASP.NET Boilerplate. You’ll yourself a lot of time getting your project off the ground.