DEV Community

Mohammad Abu Musa RABIUL
Mohammad Abu Musa RABIUL

Posted on

Multi-Stage Database Migration In .NET CORE

#postgres #migration #database #dotnet

In this section, we will look into multi-stage migration in .NET Core. Imagine you have different DB schema name for different development environment but same database table structure. For example you have dev, stag, and prod schema for development, staging, and production environment respectively.

multi-stage-migration

Setting up project

First, create a .NET Core project using dotnet new webapi

Required packages

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Design
  • Npgsql.EntityFrameworkCore.PostgreSQL
  • dotenv.net

Add these packages into your project from https://www.nuget.org

Multi-stage Migration Workflow

CI/CD pipeline

Create .env File

Create .env file in project root folder. This file will be different in each environment.
Sample .env file.

ConnectionStrings="Server=localhost;Port=5432;Database=token_store;User ID=postgres;Password=123456;"
DbSchema="dev"
Enter fullscreen mode Exit fullscreen mode

Create Our Models

Post.cs

public class Post : Common
    {
        [Key]
        public Guid Id { get; set; }
        public string Content { get; set; }

        public IList<PostCategory> PostCategories { get; set; }
    }
Enter fullscreen mode Exit fullscreen mode

Category.cs

public class Category : Common
    {
        [Key]
        public Guid Id { get; set; }
        public string Name { get; set; }
        public IList<PostCategory> PostCategories { get; set; }
    }
Enter fullscreen mode Exit fullscreen mode

PostCategory.cs

public class PostCategory : Common
    {
        public Guid PostId { get; set; }
        public Post Post { get; set; }
        public Guid CategoryId { get; set; }
        public Category Category { get; set; }
    }
Enter fullscreen mode Exit fullscreen mode

PostCategory model class maintains many-to-many relation between Post and Category.

Model Configuration

DbContext instance maintains session with the database and can be used to query or save model object to database. Besides, It is also used for database migration.
let's define our PostDBContext and configure EF models.
Additionally, we create the interface IDbContextSchema containing just the schema property to be able to inject it into PostDbContext.

public interface IDbContextSchema
    {
        string Schema { get; }
    }

public class PostDbContext : DbContext, IDbContextSchema
    {
        public string Schema { get; }
        public PostDbContext(DbContextOptions<PostDbContext> options, IDbContextSchema schema = null) : base(options)
        {
            Schema = schema.Schema ?? throw new ArgumentNullException(nameof(schema));
        }

        // Table objects
        public DbSet<Post> Posts { get; set; }
        public DbSet<Category> Categories { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>();
            optionsBuilder.ReplaceService<IMigrationsAssembly, DbSchemaAwareMigrationAssembly>();

            base.OnConfiguring(optionsBuilder);
        }

        // Alternative way to define your table, relationship, constraints etc..
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            base.OnModelCreating(modelBuilder);

            modelBuilder.HasPostgresExtension("uuid-ossp");
            modelBuilder.HasDefaultSchema(Schema);  //Required for dynamic schema change
            // Post Table
            modelBuilder.Entity<Post>(
                p =>
                {
                    p.Property(x => x.Id).HasDefaultValueSql("uuid_generate_v4()");
                }
            );

            // Category Table
            modelBuilder.Entity<Category>(
                p =>
                {
                    p.Property(x => x.Id).HasDefaultValueSql("uuid_generate_v4()");
                }
            );
            //PostCategory Table (Many to many relation)
            modelBuilder.Entity<PostCategory>().HasKey(pc => new { pc.PostId, pc.CategoryId });
            modelBuilder.Entity<PostCategory>()
                .HasOne<Post>(pc => pc.Post)
                .WithMany(p => p.PostCategories)
                .HasForeignKey(pc => pc.PostId);

            modelBuilder.Entity<PostCategory>()
                .HasOne<Category>(pc => pc.Category)
                .WithMany(c => c.PostCategories)
                .HasForeignKey(pc => pc.CategoryId);

        }
    }
Enter fullscreen mode Exit fullscreen mode

Next, We have to change how EF is caching database model definitions. By default just the type of the DbContext is used but we need to differentiate the models not just by type but by the schema as well. For that we implement the interface IModelCacheKeyFactory.

Now generate our migration using following command

dotnet-ef migrations add <migration-name> --context PostDbContext -o <relative-dir>
Enter fullscreen mode Exit fullscreen mode

It generates following migration.

 public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: "dev");

            migrationBuilder.AlterDatabase()
                .Annotation("Npgsql:PostgresExtension:uuid-ossp", ",,");

            migrationBuilder.CreateTable(
                name: "Categories",
                schema: "dev",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "uuid", nullable: false, defaultValueSql: "uuid_generate_v4()"),
                    Name = table.Column<string>(type: "text", nullable: true),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Categories", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "Posts",
                schema: "dev",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "uuid", nullable: false, defaultValueSql: "uuid_generate_v4()"),
                    Content = table.Column<string>(type: "text", nullable: true),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Posts", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "PostCategory",
                schema: "dev",
                columns: table => new
                {
                    PostId = table.Column<Guid>(type: "uuid", nullable: false),
                    CategoryId = table.Column<Guid>(type: "uuid", nullable: false),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_PostCategory", x => new { x.PostId, x.CategoryId });
                    table.ForeignKey(
                        name: "FK_PostCategory_Categories_CategoryId",
                        column: x => x.CategoryId,
                        principalSchema: "dev",
                        principalTable: "Categories",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_PostCategory_Posts_PostId",
                        column: x => x.PostId,
                        principalSchema: "dev",
                        principalTable: "Posts",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_PostCategory_CategoryId",
                schema: "dev",
                table: "PostCategory",
                column: "CategoryId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "PostCategory",
                schema: "dev");

            migrationBuilder.DropTable(
                name: "Categories",
                schema: "dev");

            migrationBuilder.DropTable(
                name: "Posts",
                schema: "dev");
        }
    }
Enter fullscreen mode Exit fullscreen mode

Next, we add a constructor to provide the migration with IDbContextSchema and change static "dev" schema with _schema.Schema.

public partial class Initial : Migration
    {
        private readonly IDbContextSchema _schema;

        public Initial(IDbContextSchema schema)
        {
            _schema = schema ?? throw new ArgumentNullException(nameof(schema));
        }
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: _schema.Schema);

            migrationBuilder.AlterDatabase()
                .Annotation("Npgsql:PostgresExtension:uuid-ossp", ",,");

            migrationBuilder.CreateTable(
                name: "Categories",
                schema: _schema.Schema,
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "uuid", nullable: false, defaultValueSql: "uuid_generate_v4()"),
                    Name = table.Column<string>(type: "text", nullable: true),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Categories", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "Posts",
                schema: _schema.Schema,
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "uuid", nullable: false, defaultValueSql: "uuid_generate_v4()"),
                    Content = table.Column<string>(type: "text", nullable: true),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Posts", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "PostCategory",
                schema: _schema.Schema,
                columns: table => new
                {
                    PostId = table.Column<Guid>(type: "uuid", nullable: false),
                    CategoryId = table.Column<Guid>(type: "uuid", nullable: false),
                    createdAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: false),
                    updatedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true),
                    deletedAt = table.Column<DateTime>(type: "timestamp without time zone", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_PostCategory", x => new { x.PostId, x.CategoryId });
                    table.ForeignKey(
                        name: "FK_PostCategory_Categories_CategoryId",
                        column: x => x.CategoryId,
                        principalSchema: _schema.Schema,
                        principalTable: "Categories",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                    table.ForeignKey(
                        name: "FK_PostCategory_Posts_PostId",
                        column: x => x.PostId,
                        principalSchema: _schema.Schema,
                        principalTable: "Posts",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_PostCategory_CategoryId",
                schema: _schema.Schema,
                table: "PostCategory",
                column: "CategoryId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "PostCategory",
                schema: _schema.Schema);

            migrationBuilder.DropTable(
                name: "Categories",
                schema: _schema.Schema);

            migrationBuilder.DropTable(
                name: "Posts",
                schema: _schema.Schema);
        }
    }
Enter fullscreen mode Exit fullscreen mode

Now, If we try update migration to our database, we will get a MissingMethodException: No parameterless constructor defined for this object error. That is because EF Core needs a paremeterless constructor to be able to create an instance of the migration.
However, We can solve this by changing CreateMigration method of MigrationsAssembly. It checks whether we need instance of IDbContextSchema in our migration.

public class DbSchemaAwareMigrationAssembly : MigrationsAssembly
    {
        private readonly DbContext _context;

        public DbSchemaAwareMigrationAssembly(ICurrentDbContext currentContext,
              IDbContextOptions options, IMigrationsIdGenerator idGenerator,
              IDiagnosticsLogger<DbLoggerCategory.Migrations> logger)
          : base(currentContext, options, idGenerator, logger)
        {
            _context = currentContext.Context;
        }

        public override Migration CreateMigration(TypeInfo migrationClass,
              string activeProvider)
        {
            if (activeProvider == null)
                throw new ArgumentNullException(nameof(activeProvider));

            var hasCtorWithSchema = migrationClass
                    .GetConstructor(new[] { typeof(IDbContextSchema) }) != null;

            if (hasCtorWithSchema && _context is IDbContextSchema schema)
            {
                var instance = (Migration)Activator.CreateInstance(migrationClass.AsType(), schema);
                instance.ActiveProvider = activeProvider;
                return instance;
            }

            return base.CreateMigration(migrationClass, activeProvider);
        }
    }
Enter fullscreen mode Exit fullscreen mode

Register the DbSchemaAwareMigrationAssembly in OnConfiguring method of PostDbContext.

Finally, register our DbContext and IDbContextSchema service in the IServiceCollection.

 string SCHEMA = Environment.GetEnvironmentVariable("DbSchema");
            services.AddDbContext<PostDbContext>(options =>
           {
               options.UseNpgsql(Environment.GetEnvironmentVariable("ConnectionStrings"),
                      npSqlOptions =>
                      {
                          npSqlOptions.CommandTimeout(3300);
                          npSqlOptions.MigrationsHistoryTable("__EFMigrationsHistory", SCHEMA);
                      });

           }).AddSingleton<IDbContextSchema>(new DbContextSchema(SCHEMA));
Enter fullscreen mode Exit fullscreen mode

Note that we also change where EF Core will keep migration histories. In our case it resides inside specified schema.

You can reach the project from here.

References:

Top comments (1)

Collapse
 
rfriendzilo profile image
Richard Friend •

This is great, i managed to get this working without needing to change migrations by just replacing the IMigrationsSqlGenerator - this is for postgres.

It basically loops through all the operations and replaces with the schema set on the db context before generating the SQL

public class PostgresSchemaAwareMigratorSqlGenerator : Npgsql.EntityFrameworkCore.PostgreSQL.Migrations.NpgsqlMigrationsSqlGenerator
{


    public PostgresSchemaAwareMigratorSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, 
        INpgsqlSingletonOptions npgsqlSingletonOptions) : base(dependencies, npgsqlSingletonOptions)
    {
    }


    public override IReadOnlyList<MigrationCommand> Generate(IReadOnlyList<MigrationOperation> operations, IModel? model = null,
        MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default)
    {
        foreach (var operation in operations)
        {
            if (operation is EnsureSchemaOperation ensureSchemaOp)
            {
                ensureSchemaOp.Name = GetSchema(ensureSchemaOp.Name) ?? ensureSchemaOp.Name;
            }
            var schemaProp = operation.GetType().GetProperty("Schema");
            if (schemaProp == null) continue;
            var newSchema = GetSchema(schemaProp.GetValue(operation)?.ToString());
            if (!string.IsNullOrEmpty(newSchema))
            {
                schemaProp.SetValue(operation, newSchema);
            }
        }
        return base.Generate(operations, model, options);
    }


    private string? GetSchema(string? currentSchema)
    {
        var dbContextSchema = Dependencies?.CurrentContext?.Context as IDbContextSchema;
        return dbContextSchema?.Schema ?? currentSchema;
    }
}
Enter fullscreen mode Exit fullscreen mode

Read next

aman_yadav profile image

Local AI Knowledge Base with Next.js, Ollama, and PostgreSQL

Aman Yadav -

nozibul_islam_113b1d5334f profile image

Database Monitoring: Importance, Tools, and Best Practices for Performance Optimization

Nozibul Islam -

stringintech profile image

Optimizing PostgreSQL Mass Deletions with Table Partitioning

stringintech -

davkharbayar profile image

AI-Powered CV Analysis Platform

Davkharbayar -