Multi-tenant applications (model building and migrations)

[rowanmiller]

See question in dotnet/efcore#4004 as an example of what we should cover

[rowanmiller]

See dotnet/efcore#4583

[Perustaja]

Any update on this? I understand that multi-tenancy has not been accepted as within the scope of EF Core, but nonetheless people are constantly asking questions on here about this.

Specifically on a database-per-tenant approach: After looking through the ef tool I don't see why it wouldn't be possible for a user to make a "mass update" command to call Update() on each database given a context and a list of tenant connection strings because of the --connection option now in 5.0 (either it could literally call "database update --connection ..." for each, or somehow specify the connection string when instantiating through Activator). I would say the only issue is that migrations require the constructor and OnConfiguring() to be run, which is often where the tenant connection string is injected via some route data, cookie, or token. However, I think IDesignTimeDbContextFactory solves this issue by providing a static dummy database to create them from.

We appreciate you working with those of us who are implementing our own solutions, it may be best to explicitly state that multi-tenancy is not supported but is available via libraries or on help-sites.

[ajcvickers]

@Perustaja No update, but the issue is not closed, which means we still plan to have guidance at some point.

[Marren85]

I've read countless articles over the last month trying to find a way to achieve this before stumbling onto this post and wish I'd seen this first and saved the time! I assumed there was already a way to accomplish migrations with multiple connection strings. There seems to be various methods for using multi tenant databases which is was surprised me that I couldn't use migrations. Seeing as this is now over 5 years old and still no information, I'll look into alternate providers / methods.

[smbadiwe]

I found a good and simple solution for this problem. (I like the title of dotnet/efcore#4004 better but I see the discussion continues here)

The scenario:
You want to use the tenant code / identifier as a schema in your DB

Steps:

1. Extend your MigrationsSqlGenerator for your provider. In my example, I'll be using Postgresql

    public class SchemaAwareMigrationsSqlGenerator : NpgsqlMigrationsSqlGenerator
    {
        private readonly string _schema;
        public SchemaAwareMigrationsSqlGenerator(
            MigrationsSqlGeneratorDependencies dependencies, 
            INpgsqlOptions npgsqlOptions
        ) : base(dependencies, npgsqlOptions) 
        {
             var dbContext = dependencies?.CurrentContext?.Context;
            _schema = getTenantCodeFromDbContext(dbContext);
        }

NB: The code in the constructor will be the same regardless of your DB provider. This is because the constructor will always include the depenencies parameter.
NB: The implementation for getTenantCodeFromDbContext will depend on how you represent the tenant code in your dbcontext.

2. Add the following override to the SchemaAwareMigrationsSqlGenerator class you just made.

        protected override void Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
        {
            var schemaProp = operation.GetType().GetProperty("Schema");
            if (schemaProp != null && schemaProp.CanWrite)
            {
                schemaProp.SetValue(operation, _schema);
            }
            
            // Updating 'NewSchema' is optional but you may likely need it
            schemaProp = operation.GetType().GetProperty("NewSchema");
            if (schemaProp != null && schemaProp.CanWrite)
            {
                schemaProp.SetValue(operation, _schema);
            }

            base.Generate(operation, model, builder);
        }

3. In your dbcontextbuilder (somewhere in ConfigureServices or wherever you kept it, add the following:

var options = new DbContextOptionsBuilder<YourSchemaAwareDbContext>()
// ...
.ReplaceService<IMigrationsSqlGenerator, SchemaAwareMigrationsSqlGenerator>();

Addendum:
You may also want to customize the dbcontext's IModelCacheKeyFactory to be schema-aware. See https://stackoverflow.com/a/41985226 for an example of how to do it.

[ReneKochITTitans]

After a long week reading nearly everything I could find about tenant by schema in EF Core I found this post and it helped me go 5 steps further.

One thing to add frmo the last posts.

For the foreign keys the SchemaAwareMigrationsSqlGenerator needs also the
schemaProp = operation.GetType().GetProperty("PrincipalSchema");
if (schemaProp != null && schemaProp.CanWrite)
{
schemaProp.SetValue(operation, tenantProvider.TenantSchemaName);
}

2. the insert data (e.g. for enum seeding) causes problems, the solution is to manuelly add the column types in the migration file