Multi-Tenant SaaS Architecture - Database Per Tenant
Tags: dotnet, saas, csharp, sql
Published: 2024-05-11 21:52:39

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](https://developerpartners.com/blog/f/multi-tenant-saas-architecture-with-entity-framework-core) 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.jso`n 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fsas11iowmprpokrbndt.png)


#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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b0wg8nqaapofckf8jsxp.png)

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;"
  }
}
```

#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;
    }
}
```

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.");
}
```

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.");
        }
    }
}
```

#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 `IDesignTimeDbContextFactor`y interface provides a way to initialize an instance of an Entity Framework `DdbContext` class for migration command line commands ([learn more here](https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=dotnet-core-cli)).

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);
    }
}
```

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.");
        }
    }
}
```

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!;
}
```

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

```
dotnet ef migrations add Products
```

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
```

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;"
```

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zagsd845wurlvolyvu7n.png)

Tenant B:

![Tenant B](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hpdbf30fdeazn1naqw7c.png)

#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.