Multi-Stage Database Migration In .NET CORE
Tags: postgres, migration, database, dotnet
Published: 2021-09-26 12:42:39

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iahtyzdyoodadyl4m2xn.png)
##
## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dxjl0zmtlrtakrr3dwmi.png)
## 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"
``` 
## Create Our Models
`Post.cs`
```c#
public class Post : Common
    {
        [Key]
        public Guid Id { get; set; }
        public string Content { get; set; }

        public IList<PostCategory> PostCategories { get; set; }
    }
```
`Category.cs`
```c#
public class Category : Common
    {
        [Key]
        public Guid Id { get; set; }
        public string Name { get; set; }
        public IList<PostCategory> PostCategories { get; set; }
    }
```
`PostCategory.cs` 
```c#
public class PostCategory : Common
    {
        public Guid PostId { get; set; }
        public Post Post { get; set; }
        public Guid CategoryId { get; set; }
        public Category Category { get; set; }
    }
```
`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.
```c#
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);

        }
    }
```
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
```shell
dotnet-ef migrations add <migration-name> --context PostDbContext -o <relative-dir>
``` 
It generates following migration.
```c#
 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");
        }
    }
```
Next, we add a constructor to provide the migration with `IDbContextSchema` and change static "dev" schema with `_schema.Schema`. 
```c#
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);
        }
    }
```
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.

```c#
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);
        }
    }
```
Register the `DbSchemaAwareMigrationAssembly` in `OnConfiguring` method of `PostDbContext`.

Finally, register our `DbContext` and `IDbContextSchema` service in the `IServiceCollection`.
```c#
 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));
```
**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](https://github.com/silentrobi/dotnet-db-migration).
## References:
- https://www.thinktecture.com/en/entity-framework-core/changing-db-migration-schema-at-runtime-in-2-1/
- https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-5.0&tabs=visual-studio