EF core migrations with schema based multi-tenancy

Question

The application we are building is a multi-tenant SAAS, with each tenant on same database with different schema.

EF core (7.0.4)

Database Provider -> Postgres

Package -> Npgsql.EntityFrameworkCore.PostgreSQL (7.0.3)

Database schema pattern -> Code First

The objective is to create a single version of migration scripts that can be executed against every tenant. We don't want to create separate migration scripts for each tenant.

Issue/Concern: It seems the created migration script always contains a schema name.

migrationBuilder.EnsureSchema(name: "sysdba");

migrationBuilder.CreateTable(name: "Test",schema: "sysdba",

We want to have a placeholder for schema name during creation and able to inject a schema name during execution/update of migration scripts.

I looked for similar queries, like Multi-Tenant With Code First EF6. It provides a solution, but its an old thread and would be looking for a solution that part of ef core itself.

Another reference https://learn.microsoft.com/en-us/ef/core/miscellaneous/multitenancy#multiple-schemas mentions that this is not recommended by Microsoft.

Tried to override IDesignTimeDbContextFactory to create a dbcontext aware of schema, but it will also create multiple migration scripts for each tenant. Will have issues when managing updates to database if we maintain separate migrations per tenant

Just tried, it is possible. But how do you plan to pass schema? In connection string? — Svyatoslav Danyliv, Apr 11 '23 at 08:53
@SvyatoslavDanyliv I believe adding a property to DBContext or via connection string either approach will work. — Krishna Sarthi, Apr 11 '23 at 10:08

score 0 · Answer 1 · answered Apr 11 '23 at 10:43

Idea is to replace migration service and pass new Schema to Migration Operations.

Introduce new extension method, which replaces NpgsqlMigrationsSqlGenerator

public static class NpgsqlDbContextOptionsBuilderExtensionsTenant
{
    public static DbContextOptionsBuilder UseNpgsqlWithSchemaTenant(
        this DbContextOptionsBuilder optionsBuilder,
        string connectionString,
        Action<NpgsqlDbContextOptionsBuilder>? npgsqlOptionsAction = null)
    {
        optionsBuilder.UseNpgsql(connectionString, npgsqlOptionsAction);
        optionsBuilder
            .ReplaceService<IMigrationsSqlGenerator, NpgsqlMigrationsSqlGenerator,
                NpgsqlMigrationsSqlGeneratorSchemaTenant>();
        return optionsBuilder;
    }
}

With this extension instead of UseNpgsql, you have to use UseNpgsqlWithSchemaTenant.

And introduce NpgsqlMigrationsSqlGeneratorTenant implementation:

public class NpgsqlMigrationsSqlGeneratorSchemaTenant : NpgsqlMigrationsSqlGenerator
{
#pragma warning disable EF1001 // Internal EF Core API usage.
    public NpgsqlMigrationsSqlGeneratorSchemaTenant(MigrationsSqlGeneratorDependencies dependencies, INpgsqlSingletonOptions npgsqlSingletonOptions) : base(dependencies, npgsqlSingletonOptions)
#pragma warning restore EF1001 // Internal EF Core API usage.
    {
    }

    protected virtual MigrationOperation ChangeSchema(MigrationOperation operation, string newSchemaName)
    {
        switch (operation)
        {
            case EnsureSchemaOperation ensureSchemaOperation:
                ensureSchemaOperation.Name = newSchemaName;
                break;
            case TableOperation tableOperation:
                tableOperation.Schema = newSchemaName;
                break;
            case ColumnOperation columnOperation:
                columnOperation.Schema = newSchemaName;
                break;
            case AddCheckConstraintOperation addCheckConstraintOperation:
                addCheckConstraintOperation.Schema = newSchemaName;
                break;
            default:
                // handle other operations
                break;
        }

        return operation;
    }

    protected override void Generate(MigrationOperation operation, IModel? model, MigrationCommandListBuilder builder)
    {
        var newOperation = ChangeSchema(operation, CurrentSchema);
        base.Generate(newOperation, model, builder);
    }

    public string CurrentSchema
    {
        get
        {
            // retrieve schema from your connection string or other approach
            // var schema = Dependencies.CurrentContext

            var schema = "overriden";
            return schema;
        }
    }
}

How to retrieve needed schema name is another task. But you can get it from connection string of some other available settings.

thanks for the quick response, and I will give a try this. One question though is it going to create a new migration script per schema (whatever is set to the CurrentSchema) or there will be only one migration script created but updating the database with dynamic schema? — Krishna Sarthi, Apr 11 '23 at 11:11
For each tenant you have to run script generation separately. This code replaces schema only for one run. This is very raw solution but you have direction what has to be done if you need to correct migrations. In this sample you can remove schema and schema mentioning and generate one script. — Svyatoslav Danyliv, Apr 11 '23 at 11:17

EF core migrations with schema based multi-tenancy

1 Answers1