Better support for team development of database migrations

[br3nt]

The problematic migration process

How do you rebase or merge a branch containing migrations specifically in the case where the parent branch has new migrations that do not exist in the branch being rebased or merged?

The only fool-proof method I know of is to delete the new migrations in the feature branch, do a rebase onto the parent branch, then recreate the migration with Add-Migration. The main problem with this method is that the developer is forced to squash the migrations into a single migration. There are many situations where multiple migrations are required to correctly modify a database schema and ensure integrity of the data. A developer could instead use interactive rebase; stopping at each commit containing a migration, and running the Remove-Migration then Add-Migration commands. But as you can imagine, this is quite error prone. This gets repetitive and tedious when you need to rebase your branch multiple times.

The impact on development

The currently tooling is insufficient to effectively handle most migration conflicts. It is high risk. This leads to a fear of making database schema changes in parallel, and instead forces database development to occur in serial. This dramatically reduces a development teams ability to move quickly, as features that require database schema changes are delayed until database schema changes already in the pipeline are completed.

This situation is an artefact of the current migration process. It is entirely avoidable and can be resolved by improving the current migration process.

The problem to solve

There are at least three problems that need to be address:

• the timestamps of the migrations in the feature branch may be earlier than one or more of the timestamps of the migrations in the parent branch
• the content of DbContextModelSnapshot.cs from the feature and parent branches may have conflicting changes
• The database objects being modified by a migration in the feature branch may no longer exist in the parent branch

There are probably other scenarios I have not captured.

The proposed solution

I am proposing a new option for managing database migrations that developers can opt into.

The new option should have the following features:

• migrations are decoupled from timestamps
• migrations can be rebased on top of the latest migration
• the DbContextModel is dynamically built from the commands within the migration

E.g:

• Instead of ordering migrations by the timestamp in the filename, the migration definition will specify the parent migration
  • Preferably, an incrementing index should be used to maintain file ordering rather than a timestamp. This index could also be referenced when performing rollbacks. Much easier than typing a migration name
• The DbContextModel is built up as the migrations run in order
  • The up/down migration should be the single source of truth for schema changes, so the .Design should also be removed.
  • An error is thrown if a migration modifies the DbContextModel in an undefined way
    • E.g. a column is modified when the column doesn't exist
• If two or more migrations point to the same parent migration, the migrations need to be rebased
  • The developer can run a command, Rebase-Migration, specifying the migration to be rebased onto the latest parent migration
  • The developer repeats this process until all migrations have a single parent
  • The developer needs to resolve any issues identified when building the DbContextModel
  • After a rebase, it's possible the DbContextModel will be built in an undefined way and throw an error
    • The developer should be given sufficient details so they can modify their rebased migration(s) to complete without errors
  • At each step, the develop can commit the changes into their version control system
• It is expected that Add-Migration works in the same way as it currently does
  • except no hardcoded DbContextModel is generated

These changes would allow allow multiple developers to merge in their database schema changes with minimal conflicts, and in a way that is compatible with version control systems.

[victor-priceputu]

Another thing that is really bad in migrations is the rollbacks. Instead of having an order or number for each batch of migrations that ran, so you can do an easy command like dotnet ef database rollback that does a rollback on the last batch of migrations that ran, you have to do black magic to solve it, know which migration was last, and hope for dear life that they are in proper order.

[br3nt]

I'm glad you mentioned the rollbacks and the need for better commands.

First. Rails has excellent tooling for managing migrations. You can rollback just the last applied migration by default, or you can specify how many migrations to roll back. Additionally, there are commands that list all migrations along with a status of whether they are applied or not. It would be beneficial to copy that functionality.

Second. It is impossible to roll back, unless you have the migration in the assembly. When you switch from a feature branch with an applied migration under development, then switch to the parent branch , you are unable to roll back the applied migration as the code for it only exists in the feature branch. The other possibility is that migrations are applied to a database in an unintended order that may also lead to problems when migrating or rolling back.

For this reason, I always include a call to the following code in my program.cs to prevent possible database migration corruption when switching branches:

// <summary>
// This function performs two checks to ensure the database is migrated in a safe manner.
// 1) Checks that all applied migrations exist in the assembly
//    An applied migration may be missing if a migration from a different git branch was applied, but that migration
//    has not been merged into the current git branch.
//    Before proceeding, merge the git branch containing the other migration, or checkout the other git branch and rollback the migration.
// 2) Checks that pending migrations will be applied chronologically after applied migrations
//    A migration may be applied out of chronological order when switching to a git branch with a pending migration that has an earlier timestamp.
//    Before proceeding, either, remove the migration and recreate it with an updated timestamp, or, rollback the database to a previous migration
//    and then apply the migrations in order.
// </summary>
static void ProtectDatabaseFromCorruption(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade database)
{
    // Check that all applied migrations exist in the assembly
    var assemblyMigrations = database.GetMigrations();
    var appliedMigrations = database.GetAppliedMigrations();
    var pendingMigrations = database.GetPendingMigrations();
    if (appliedMigrations.Any(a => !assemblyMigrations.Contains(a)))
        throw new Exception("There are applied migrations that do not exist in this assembly. All applied migrations must exist in the assembly. Aborting the migration.");

    // Check that pending migrations will be applied chronologically after applied migrations
    var appliedTimestamps = appliedMigrations.Select(m => Convert.ToUInt64(m.Substring(0, 14)));
    var pendingTimestamps = pendingMigrations.Select(m => Convert.ToUInt64(m.Substring(0, 14)));
    if (appliedTimestamps.Any(a => pendingTimestamps.Any(p => a > p)))
        throw new Exception("There are pending migrations with a timestamp earlier than the timestamps of one or more applied migrations. Migrations must be applied chronologically. Aborting the migration.");
}

[br3nt]

Can anyone tell me where I should start looking in the codebase to tackle this?

[ajcvickers]

@br3nt It seems you want to re-write a major part of the migrations system. I don't think this is feasible for an external contributor to take this on, and it's not clear we would accept the work once done. Is there any chance you could build an external migrations system based on your ideas? That way .NET users would be able to choose either.

[br3nt]

@ajcvickers thanks for your feedback. I'm happy to give it a go. I'd want to base it off the existing migration framework.

Where can I find some useful entry points into the code? E.g. where can I find the code for Add-Migration? I assume from there I can work out things like:

• How migrations are generated
• How the diff is determined between db schema and DbContext, and how the DbContextModelSnapshot and *.Design.cs are created

[br3nt]

@br3nt See add-migration search results

function Add-Migration calls into function EF($project, $startupProject, $params, $applicationArgs, [switch] $skipBuild)

Ok, that was a bit of a rabbit hole. It's effectively executing dotnet migrations add ....

MigrationsAddCommand.Configure and MigrationsAddCommand... again seems to be abstracting over something else... executor = CreateExecutor(args) and executor.AddMigration(..) and base.Execute(args). Again a bit of a rabbit hole.

[ErikEJ]

dotnet/EntityFramework.Docs#4569