toutas
commented
11 months ago DbUp is a great alternative solution to the EF migrations problem (schema, data, schema) since the migration scripts are executed in-order and are only applied once (schemaversions). integrating a migrations run at startup / build pipeline is as simple as
var internalScriptName = scriptName;
var db = scriptName.Split('-')[3];
var connectionString = ConfigurationManager.ConnectionStrings[db];
var upgrader = DeployChanges.To
.PostgresqlDatabase(connectionString.ConnectionString)
.WithExecutionTimeout(new TimeSpan(0, executionTimeoutMinutes, 0))
.WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly(), s => s == internalScriptName)
.LogToConsole()
.Build();
var result = upgrader.PerformUpgrade();would this be a viable solution alternative?
tjementum
Trubador
Problem
In .NET Entity Framework, there can be several database schema migrations. However, between each Database migration, there might be the need to have data migration don by code code.Example:
This is just a simple example. The solution should handle that many complex migrations are run in a certain order, if a system has not been updated for some time (e.g. Schema, Data, Schema, Schema, Data, Schema, Data).
How can I create a good migration strategy that runs as part of the Application startup?
Solution 1
Use SQL .dbproj and DacPac: Power your DevOps Development with SQL Server Containers
Solution 2
Managing database migrations and code transformations in a sequential and reliable manner is indeed an important aspect of maintaining a robust application, especially in a complex domain-driven design project. Your problem is about orchestrating these operations in a way that ensures database integrity and data consistency.
For .NET and Entity Framework, you have a tool called EF Migrations which can handle database schema changes. However, EF Migrations doesn't inherently support code migrations that transform data between schema migrations.
Here is an approach you might consider:
001_AddColumns,002_PopulateColumns,003_RemoveNullCheck). This will ensure that migrations are executed in the correct order.Continue with the rest of the EF Migrations.
This could be achieved by using the
DbContext.Database.Migrate()method to apply the EF Migrations and a custom method to execute the data migrations. The data migration scripts could be SQL scripts or C# scripts depending on the complexity of the data transformations.This solution allows you to manage both schema migrations and data transformations in a sequential manner. It also makes sure the order of operations is preserved when you deploy to different environments.
Remember that handling migrations this way could potentially cause longer downtimes during deployment, especially for large data migrations. So, you need to plan your deployments carefully.
Solution 3
A more naïve approach would be to run migration as part of the application startup. If not done carefully this might cause race conditions. One way to avoid Race conditions is to use "Init containers":