Use Entity Framework Core with PostgreSQL to migrate multiple schemas

This approach can also be used to create a database schema for each tenant in a multi-tenant application. In this blog post, I will show you how to use Entity Framework Core with PostgreSQL to migrate multiple schemas.

1. The problem with the default EF Core migrations

To make EF Core to use a specific schema, you would usually set the schema in the OnModelCreating method of the DbContext class, using HasDefaultSchema:

public class MyDbContext(DbContextOptions<MyDbContext> options, 
        IOptions<OperationalStoreOptions> operationalStoreOptions, 
        NpgsqlConnectionStringProvider npgsqlConnectionString)
    : ApiAuthorizationDbContext<User>(options, operationalStoreOptions)
{    
    protected override void OnModelCreating(ModelBuilder builder)
    {
        // ...
        builder.HasDefaultSchema("Users");
        // ...
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // ..
        optionsBuilder.UseNpgsql(npgsqlConnectionString.ConnectionString);
        // ..
    }
}

When creating the migrations, EF Core will create the tables in the specified schema. But, when you run the migrations, EF Core will only create the tables in the schema, which was provided during the creation of the migration script. This will look like this:

public partial class InitIdentityTables : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.EnsureSchema(
            name: "Users");

        migrationBuilder.CreateTable(
            name: "DeviceCodes",
            schema: "Users",
            columns: table => new
            {
                // ...
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_DeviceCodes", x => x.UserCode);
            });
        // ...
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        //...
    }
}

As you can see, the tables are created in the Users schema. When you run the migration, EF Core will only create the tables in the Users schema. But, what if you want to create the tables in multiple schemas?

2. Use multiple schemas in EF Core migrations

It would be great when we could specify the schema during the migration time (basically, when the migration is applied). But, unfortunately, this is not possible with the default EF Core migrations. In the case of Npgsql, you can use the NpgsqlMigrationSqlGenerator to generate the SQL for the migrations. This way, you can specify the schema during the migration time.

Let’s see how we can do this.

2.1 Create an IDbContext interface

public interface IMyDbContext
{
    string? Schema { get; }
}

Then we extend the MyDbContext class with the IMyDbContext interface and implement the Schema property:

public class MyDbContext(DbContextOptions<MyDbContext> options, 
        IOptions<OperationalStoreOptions> operationalStoreOptions, 
        NpgsqlConnectionStringProvider npgsqlConnectionString)
    : ApiAuthorizationDbContext<User>(options, operationalStoreOptions), IMyDbContext
{    

    public string? Schema => databaseOptions.Value.Schema;
    
    protected override void OnModelCreating(ModelBuilder builder)
    {
        // ...
        builder.HasDefaultSchema(Schema);
        // ...
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // ..
        optionsBuilder.UseNpgsql(npgsqlConnectionString.ConnectionString);
        // ..
    }
}

This way, we have a more flexible way to set the schema in the MyDbContext class.

In the next section we will create the MultiSchemaNpgsqlMigrationSqlGenerator class, which will set the schema for the migration operations.

2.2 Create the NpgsqlMigrationSqlGenerator class

#pragma warning disable EF1001
public class MultiSchemaNpgsqlMigrationSqlGenerator : NpgsqlMigrationsSqlGenerator
{
    public MultiSchemaNpgsqlMigrationSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        INpgsqlSingletonOptions npgsqlSingletonOptions) 
        : base(dependencies, npgsqlSingletonOptions)
    {
    }

    public override IReadOnlyList<MigrationCommand> Generate(
        IReadOnlyList<MigrationOperation> operations,
        IModel? model = null,
        MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default)
    {
        ArgumentNullException.ThrowIfNull(operations);

        var dbContextSchema = Dependencies?.CurrentContext?.Context as IMyDbContext;
        var schema = dbContextSchema?.Schema;

        if (string.IsNullOrEmpty(schema))
        {
            return base.Generate(operations, model, options);
        }
        
        foreach (var operation in operations)
        {
            switch (operation)
            {
                case EnsureSchemaOperation ensureSchemaOperation:
                    ensureSchemaOperation.Name = schema;
                    break;
                
                case CreateTableOperation { ForeignKeys.Count: > 0 } createTableOperation:
                {
                    foreach (var foreignKeyOperation in createTableOperation.ForeignKeys)
                    {
                        foreignKeyOperation.PrincipalSchema = schema;
                    }
                    
                    break;
                }
            }

            // Check the operation for the 'Schema' property and replace it with the schema from the DbContext.
            var operationSchema = operation.GetType().GetProperty("Schema");
            operationSchema?.SetValue(operation, schema);
        }

        return base.Generate(operations, model, options);
    }
}

2.3 Register the MultiSchemaNpgsqlMigrationSqlGenerator in the DBContext

public class MyDbContext(DbContextOptions<MyDbContext> options, 
        IOptions<OperationalStoreOptions> operationalStoreOptions, 
        NpgsqlConnectionStringProvider npgsqlConnectionString)
    : ApiAuthorizationDbContext<User>(options, operationalStoreOptions), IMyDbContext
{    
    // ...
    // left out code
    // ...
    
    public string? Schema => databaseOptions.Value.Schema;
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace the migration generator.
        optionsBuilder.ReplaceService<IMigrationsSqlGenerator, MultiSchemaNpgsqlMigrationSqlGenerator>();
        optionsBuilder.UseNpgsql(npgsqlConnectionString.ConnectionString, options =>
        {
            options.MigrationsHistoryTable(HistoryRepository.DefaultTableName, Schema);
            options.MigrationsAssembly(GetType().Assembly.GetName().Name);
        });
        // ..
    }
}

3. Conclusion

In this blog post, I showed you how to use Entity Framework Core with PostgreSQL to migrate multiple schemas. This approach can be used to create a database schema for each tenant in a multi-tenant application or a schema per microservice in a microservice architecture. Now, when you run the migrations, EF Core will create the tables in the specified schema. This way, you can use multiple schemas in PostgreSQL with Entity Framework Core.