Multi-Tenancy with Separate Databases Approach in .NET Core

Introduction

Multi-tenancy refers to a software architecture where a single instance of the software runs on a server and serves multiple tenants.

In the context of a SAAS-based platform, a tenant refers to a customer who is using the platform to run their business operations. Each tenant has their own data, user accounts, and configuration settings that are isolated from other tenants.

Multi-tenancy allows for efficient use of resources as the same software instance can serve multiple customers, reducing the need for separate servers and infrastructure for each customer.

It also allows for easier maintenance and updates as changes can be made to a single instance of the software rather than having to update multiple instances for each customer.

Different approaches to implementation

We can differentiate multi-tenancy implementation into three different approaches.

Single Database

All tenants share a single database instance and schema. This approach is the most resource-efficient, and analyzing data across tenants becomes more straightforward. This is a cost-effective approach and simplifies the management.

But with this approach, Security becomes a critical issue; as the number of tenants increases, the potential for performance bottlenecks grows. Compliance with data regulations, especially when tenants operate in different regions with distinct data protection laws, can become complex and challenging to manage.

Single database with separate schema

All Tenants will share single database but each tenant will have their own schema, this approach is more resource-efficient than separate databases, but still provides a high level of isolation and security. Managing a single database reduces operational costs, as administrative tasks, such as backups and updates.

However, in scenarios with high contention for resources, such as frequent read and write operations across different schemas, there is a potential for data contention and performance degradation.

Separate Database

Every tenant will have their own database, and the application will have one common database to store tenant-specific information. Multiple databases provide stronger security and isolation, reducing the risk of data breaches and unauthorized access among tenants.

Scalability is enhanced as each tenant's database can be scaled independently based on their individual requirements and usage patterns.

However, managing multiple databases adds complexity to administrative tasks, Synchronization challenges may arise, leading to the potential for inconsistent data across different databases. Higher resource consumption in terms of storage, processing, and infrastructure costs.

Today, In this blog, we will learn how to implement a separate database approach of multi-tenancy in the .Net Core application and how to provide a dynamic connection string to DbContext.

I will give a demo of an Application where I will take two DbContext classes.

One DB Context for the main database, which will store tenant-related stuff such as tenant details, tenant user logins, etc.
Second DB Context for each tenant database, we will connect this database based on TenantId

We will add a request header with the name x-tenant-id, which will have the tenantId for which we want to fetch data.

Step 1. Create a Tenant Pod table. We can use this table to store tenant connection strings for the database. If we don’t want to store the connection string directly in DB, we can Azure Key Vault or Aws Secret Manager and, in this DB, we can store Secret Name.

// Tenant.cs 
[Table("tenants")]
public class Tenant
{
    [Key]
    public Guid Id { get; protected set; }
    public string Name { get; protected set; }
    public Tenant(Guid id, string name)
    {
        Id = id;
        Name = name;
    }
}

// TenantPod.cs
[Table("tenant_pods")]
public class TenantPod
{
    [Key]
    public Guid Id { get; protected set; }
    public Guid TenantId { get; protected set; }
    public string ConnectionString { get; protected set; }
    [ForeignKey(nameof(TenantId))]
    public virtual Tenant Tenant { get; protected set; }
    public TenantPod(Guid id, Guid tenantId, string connectionString)
    {
        Id = id;
        TenantId = tenantId;
        ConnectionString = connectionString;
    }
}

Step 2. Create a Method to get the connection string based on the request header.

// TenantsService.cs
public interface ITenantsService
{
    string GetConnectionByTenant();
}

public class TenantsService: ITenantsService
{
    private readonly HttpContext? _httpContext;
    public TenantsService(IHttpContextAccessor httpContextAccessor)
    {
        _httpContext = httpContextAccessor.HttpContext;
    }
    public string GetConnectionByTenant()
    {
        var tenantId = Guid.Empty;
        try
        {
            if (_httpContext == null)
                throw new Exception("Tenant Not Found");

            var clientId = _httpContext.Request.Headers["x-tenant_id"];
            if (string.IsNullOrEmpty(clientId))
            {
                throw new Exception("Tenant Request Header is missing.");
            }
            Guid.TryParse(clientId.ToString(), out tenantId);
            // Logic to get DB connection based on tenant
            // Dummy Connection string Her you should have your logic to get connection string and return it
            return "Server=127.0.0.1;Database=multiDemoMain;Port=5432;User Id=postgres;Password=123456";
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            throw;
        }
    }
}

Step 3. In the Db Context, add logic to use the above method, Override OnConfiguring Method of the DBContext class.

// MultiTenantDbContext.cs 
public class MultiTenantDbContext : DbContext
{
    private readonly ITenantsService _tenantsService;
    public MultiTenantDbContext(DbContextOptions<MultiTenantDbContext> options, ITenantsService tenantsService) :
        base(options)
    {
        _tenantsService = tenantsService;
    }
    public DbSet<Tenant> Tenants { get; set; }
    public DbSet<Document> Documents { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = _tenantsService.GetConnectionByTenant();
        if (string.IsNullOrEmpty(connectionString))
        {
            throw new Exception("Tenant Connection Not found");
        }
        optionsBuilder.EnableSensitiveDataLogging();
        optionsBuilder.UseNpgsql(connectionString, b =>
        {
            b.SetPostgresVersion(new Version(9, 6));
            b.EnableRetryOnFailure(10, TimeSpan.FromSeconds(30), null);
            b.CommandTimeout(300);
        });
        optionsBuilder.UseNpgsql(connectionString).UseSnakeCaseNamingConvention();
    }
}

That’s It. Now, you can use this DB Context in the repository or service layer, and this DbContext will fetch the connection string based on the TenantId passed. We need to make sure every API has a tenant in the request header.

Note. Here, in this example, I have a Tenant table that is duplicated in both Db, but to use the constraint, we need to duplicate the table, and we should have logic to synchronize the data across the database. Also, we can implement Redis Cache or In Memory Cache Service to fetch the connection string from In Memory so that this can be faster. In my next article, we will learn how to use Memory Cache and Redis Cache in .Net Core.

Conclusion

In this article, we learned about multi-tenancy and its different approaches. Also, we explored how to connect Databases dynamically based on TenantId.