SharedFlat and Databases

Introduction

This post is part of a series on SharedFlat . Seehere the first (introduction) andhere the second (UI). This time I will be explaining how SharedFlat handles multitenant databases.

There are essentially three strategies for doing multitenancy when it comes to databases:

1. One connection string per tenant, which effectively means a different database for each tenant

2. One schema per tenant, meaning, one table will exist on a schema for each tenant

3. A single database where multitenant tables have a column that is used to distinguish tenants

Let’s see how we can have this with SharedFlat! First, it’s needed to say that this uses Entity Framework Core 3.1 and that there is a TenantDbContext abstract class that we should inherit from.

Database per Tenant

Let’s start with database per tenant. In this case, it is expected that we store in the configuration (typically the appsettings.json file) one connection string for each for each tenant. For example:

{
  "ConnectionStrings": {
    "abc": "<connection string for abc>",
    "xpto": "<connection string for xpto>"
}
}

We just need, when registering the services, to use the DifferentConnectionPerTenant extension method:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.DifferentConnectionPerTenant();

And SharedFlat will know what to do. If, for any reason, you wish to use a differently-named connection string, there is an overload that lets you customize this:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.DifferentConnectionPerTenant(options =>
{
options.Mapping["xpto"] = "xpto2";
});

Schema per Tenant

Next is different schema per tenant. The extension method to call is DifferentSchemaPerTenant :

services
.AddTenantService()
.AddTenantDbContextIdentification()
.DifferentSchemaPerTenant();

By default, the schema will be identical to the tenant; should we need to change that, there is an overload:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.DifferentSchemaPerTenant(options =>
{
options.Mapping["xpto"] = "xpto2";
});

This will set the schema on each entity that implements the ITenantEntity interface (a simple marker interface) when the TenantDbContext -derived class is initialized. To be clear, using SQL Server as an example, it will change the default (“ dbo ”) for the tenant value, so a table named “ dbo.Product ” will become “ abc.Product ”, “ xpto.Product ”, etc.

Filter by Tenant

The last technique depends on having a column on each table that we want to make multitenant, as dictated by its entity implementing ITenantEntity . The method to call is FilterByTenant , unsurprisingly:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.FilterByTenant();

As is usual, the method has some defaults, like, the name of the column (“ Tenant ”), but we can change it:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.FilterByTenant("TenanantCol");

Or, in the eventual case where you need to change the mapping:

services
.AddTenantService()
.AddTenantDbContextIdentification()
.FilterByTenant(options =>
{
options.Mapping["xpto"] = "xpto2";
});

Filtering by tenant means that whenever the context is querying for a multitenant-aware entity, a filter is added:

SELECT p.[ProductId], p.[Name], p.[Price]
FROM [dbo].[Product] p
WHERE p.[Tenant] = 'abc'

Conclusion

And this is it for multitenant databases. Keep tuned for more on SharedFlat, and, as always, let me hear your thoughts!