CODEWITHSUNDEEP
entity-framework-coreentity-framework-migrations
Krishna Sarthi
Krishna Sarthi

Reputation: 95

EF core migrations with schema based multi-tenancy

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

Upvotes: 0

Views: 952

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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.

Upvotes: 2

Related Questions