DEV Community

Cover image for Multi-Tenant SaaS Architecture - Database Per Tenant
Developer Partners
Developer Partners

Posted on • Updated on • Originally published at developerpartners.com

Multi-Tenant SaaS Architecture - Database Per Tenant

SaaS applications benefit from the multi-tenant architecture. It makes the maintenance and enhancements of the software easier and less costly. We discussed multi-tenancy in details in the Multi-Tenant SaaS Architecture with Entity Framework article. In the previous article, we explored ways for building multi-tenant applications where all tenants (clients) shared the same application instance and database. In this article, we will talk about slightly different architecture where all tenants will still share the same application instance, but each one of them will have their own database.

Architecture

Let’s talk about the architecture of our multi-tenant application. We are building a .NET application with multiple databases. We are going to store the connection strings in appsettings.json file. Please note that it’s not secure to store production connection strings in the appsettings.json file. Please follow the best software security practices for overriding your production connection strings. The ConnectionStrings section of our appsettings.json file will have an entry for each tenant with their domain name. The application will determine which database’s connection string to use based on the site’s domain. For example, if the sites URL is https://domain-a.com, we will look for a connection string with name “domain-a.com”. Similarly, if a site’s URL is https://domain-b.com, we will look for a connection string with name “domain-b.com”. The following diagram describes our architecture:

Architecture diagram of the Database Per Tenant architecture

Setup Connection Strings

First, let’s setup our development environment to use at least 2 URLs, so we can test it from our local machines. For example, we are going to use https://localhost:7000 for Tenant A, and https://localhost:7001 for Tenant B. Let’s modify our launchSettings.json file to reflect that:

Launch Settings for Development URLs

Next, let’s add the connection strings for each of those tenant URLs. As a reminder, each tenant is going to have their own database with this architecture, so we are going to create at least 2 databases. In the screenshot below, the localhost_7000 connection string is for the Tenant A database and localhost_7001 is for the Tenant B database. Please note that port numbers are added after a colon ( : ) sign in URLs. However, the : sign is a special character used for separating sections in the .NET Core configurations, so we are replacing it with an underscore ( _ ) sign in our connection string property names in appsettings.json file.

{
  "ConnectionStrings": {
    "localhost_7000": "Server=(localdb)\\MSSQLLocalDB;Database=TenantA;Integrated Security=SSPI;",
    "localhost_7001": "Server=(localdb)\\MSSQLLocalDB;Database=TenantB;Integrated Security=SSPI;"
  }
}
Enter fullscreen mode Exit fullscreen mode

Connect to Tenant Databases

The next step would be to configure our application to use a different tenant’s database connection string depending on the request URL, so let’s add a service called TenantProviderService and add that logic to it. Our TenantProviderService will need the following dependencies to be able to select the correct connection string from our app settings:

public class TenantProviderService : ITenantProviderService
{
    private readonly IHostEnvironment _environment;
    private readonly IHttpContextAccessor _contextAccessor;
    private readonly IConfiguration _configuration;

    public TenantProviderService(
        IHostEnvironment environment,
        IHttpContextAccessor contextAccessor,
        IConfiguration configuration
    )
    {
        _environment = environment;
        _contextAccessor = contextAccessor;
        _configuration = configuration;
    }
}
Enter fullscreen mode Exit fullscreen mode

Next, let’s add a method called GetConnectionString to TenantProviderService that will get the correct connection string from application settings based on the domain of the calling URL.

private string GetCallingDomain()
{
    var request = _contextAccessor.HttpContext!.Request;

    var callingUrl = string.Format("{0}://{1}{2}{3}",
        request.Scheme, request.Host, request.Path, request.QueryString);

    var uri = new Uri(callingUrl);

    return _environment.IsDevelopment()
        ? $"{uri.Host}:{uri.Port}"
        : uri.Host;
}

public string GetConnectionString()
{
    if (_contextAccessor.HttpContext != null)
    {
        var domain = GetCallingDomain();
        var cleanedDomain = domain.Replace(":", "_");

        var connectionString = _configuration.GetConnectionString(cleanedDomain);

        if (!string.IsNullOrEmpty(connectionString))
        {
            return connectionString;
        }
    }

    throw new InvalidOperationException("Connection string not found.");
}
Enter fullscreen mode Exit fullscreen mode

Next, we have to use the TenantProviderService in our Entity Framework DbContext class for getting the connection string to the databases we need to connect. To do that, we have to override the OnConfiguring method of the DbContext class and set the database connection string there:

public class AppDbContext
{
    private readonly ITenantProviderService? _tenantProvider;

    public AppDbContext(ITenantProviderService tenantProvider)
    {
        _tenantProvider = tenantProvider;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        if (_tenantProvider != null)
        {
            var connectionString = _tenantProvider.GetConnectionString();
            optionsBuilder.UseSqlServer(connectionString);
        }
        else
        {
            throw new InvalidOperationException("Either tenant provider must not be null.");
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Entity Framework Migrations

Our current code selects the correct connection string for each tenant during runtime. However, we also need to use a connection string during design time when adding Entity Framework migrations. It needs to connect to a database to be able to compare the database schema with our C# model to determine what should be included in the migrations we add.

There are multiple ways to provide a connection string for Entity Framework migrations. We are going to explore an option using the IDesignTimeDbContextFactory interface. The IDesignTimeDbContextFactory interface provides a way to initialize an instance of an Entity Framework DdbContext class for migration command line commands (learn more here).

In the code below, we create an instance of the AppDbContext class using localhost_7000 tenant’s connection string from application settings. It doesn’t matter which connection string we use, we just need a connection string for generating the migration files.

public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        var configuration = new ConfigurationBuilder()
           .SetBasePath(Directory.GetCurrentDirectory())
           .AddJsonFile("appsettings.json")
           .Build();

        var connectionString = configuration.GetConnectionString("localhost_7000")
            ?? throw new InvalidOperationException("No connection string was configured for migrations.");

        return new AppDbContext(connectionString);
    }
}
Enter fullscreen mode Exit fullscreen mode

We have to add another constructor to the AppDbContext class that receives a string parameter for the design time connection string used in Entity Framework migrations. Then we have to update the OnConfiguring method of the AppDbContext class to use the design time connection string if it’s not null or empty.

public class AppDbContext : DbContext
{
    private readonly ITenantProviderService? _tenantProvider;
    private readonly string? _migrationConnectionString;

    public AppDbContext(string migrationConnectionString)
    {
        _migrationConnectionString = migrationConnectionString;
    }

    public AppDbContext(ITenantProviderService tenantProvider)
    {
        _tenantProvider = tenantProvider;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        if (_tenantProvider != null)
        {
            var connectionString = _tenantProvider.GetConnectionString();
            optionsBuilder.UseSqlServer(connectionString);
        }
        else if (!string.IsNullOrEmpty(_migrationConnectionString))
        {
            optionsBuilder.UseSqlServer(_migrationConnectionString);
        }
        else
        {
            throw new InvalidOperationException("Either tenant provider must not be null.");
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Now, we can add our first migration. Let’s create a simple model called Product and add our migration. Our Product model:

public class Product
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; } = null!;
}
Enter fullscreen mode Exit fullscreen mode

Next, let’s add the Product model as a DbSet property to our DbContext class and add our first migration:

dotnet ef migrations add Products
Enter fullscreen mode Exit fullscreen mode

The command above will generate a migration file. To update our database with the schema changes of the Products migration, please run the following command:

dotnet ef database update
Enter fullscreen mode Exit fullscreen mode

The command above will update the database schema for the localhost_7000 because that’s what our AppDbContextFactory class uses. By default, it will always update the database of the localhost_7000 tenant. If we want to update the databases of other tenants, we have to use the —connection option of the dotnet ef database update command.

dotnet ef database update --connection "Server=(localdb)\MSSQLLocalDB;Database=TenantB;Integrated Security=SSPI;"
Enter fullscreen mode Exit fullscreen mode

Please note that we can’t just pass localhost_7001 for the —connection option. We have to pass the actual connection string to it.

Launch The Application

Let’s see the results. I created a simple MVC application for demonstrating connecting to a different tenant’s database based on the domain of the request URL. The Home page shows a list of products. It will show a different list of products depending on what tenant we use. I added some data to the Products table in each database.

  1. Tenant A (https://localhost:7000) is an electronics store.
  2. Tenant B (https://localhost:7001) is an apparel store.

Tenant A:

Tenant A

Tenant B:

Tenant B

Conclusion

If you are developing a SaaS solution, it makes a lot of sense to use the multi-tenant architecture because it makes the maintenance and adding new features really easy for new and existing clients. There are different ways to implement this architecture. One way is to share the application instance for all, but connect to a separate database for each tenant. To implement the multi-tenant architecture with the database per tenant approach, follow the steps below:

  1. Setup a connection string for each tenant in application settings. Make sure to follow best practices for securely storing production connection strings.
  2. Determine the connection string for each request. For example, a connection string can be determined by the domain of the request URL.
  3. Dynamically provide the connection string from step 2 to the Entity Framework DbContext by overriding the OnConfiguring method.

Top comments (1)

Collapse
 
bigboybamo profile image
Olabamiji Oyetubo

Gonna try this out. Thank you