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.
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
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
public class Post : Common
{
[Key]
public Guid Id { get; set; }
public string Content { get; set; }
public IList<PostCategory> PostCategories { get; set; }
}
Category.cs
public class Category : Common
{
[Key]
public Guid Id { get; set; }
public string Name { get; set; }
public IList<PostCategory> PostCategories { get; set; }
}
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; }
}
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);
}
}
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>
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");
}
}
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);
}
}
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);
}
}
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));
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.
Top comments (1)
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