Provide Migrations hooks to execute SQL and/or seeding before and after each migration has been applied

[riedd2]

Hello

I would like to include some custom sql (create stored procedure) in the generate migration script. This code should be run whenever the migration is executed, so no version constraint.
I tried to accomplish this with a custom MigrationOperation / SqlServerMigrationsSqlGenerator approach, but this does not work if there are no new migrations.

Is there another way to achieve this?

Thank you in advance for your Feedback.
Cheers
David

EF Core version: 5.0.5
Database provider: Microsoft.Data.SqlClient
Target framework: NET 5.0
Operating system: Windows
IDE: Visual Studio 2019 16.8

[roji]

@riedd2 take a look at this section in our docs. tl;dr you can directly insert raw SQL to be executed as part of any migration - no need for either a custom MigrationOperation or touching SqlServerMigrationsSqlGenerator.

[riedd2]

Hey @roji
Thanks for your answer.
I'm aware that I can execute raw sql in any migration but that does not solve my problem.

I would like to be able to run the custom sql even if there are no new migrations on every deployment (like seeding data but part of the migration script). My first approach was to try to add this every time the migration script gets created but that didn't seem to work. If I'm adding the raw sql in a migration it would only run once when the migration runs for the first time.

Sorry if my question was not clear.

[roji]

EF Core requires for there to be an unapplied migration in order to run any migration code - so if I'm understanding you correctly that isn't possible.

Can you provide a bit more context on what kind of SQL you would want to run on every deployment, regardless of whether new migrations have been created?

[riedd2]

That's what I feared.

I have a list of stored procedures (create or alter statements) which are kept in a folder within my solution. These can change independent of other migration relevant code. I would like to apply them with every deployment to keep them up to date, hence my question if it would be possible to add them to the migration somehow.

I could make a migration every time one changes but that would make it harder to have a clean source control history, since I would need to check all the migration and not just the sql files itself.

Another avenue would be to run them on application start but since we are running multiple instances of the application (which can be restarted at any time) with the same database, this could introduce some problems with db locks and such.

It would also be possible to change our pipeline and have a separate step dedicated to run such code, this would be a clean solution but It's my second choice since it would mean a change to our pipeline.

Run them within the migration script would be the cleanest solution with the easiest integration in the existing pipeline.

[roji]

If I understand you correctly, you're basically looking to drop/recreate (or re-define) your stored procedures every time you deploy your application to production, is that right?

If you're already using EF Core migrations and want to use them to manage your stored procedures as well, then we indeed recommend creating a new migration for any change; creating a new stored procedure would imply a new migration, as well as changing an existing one. I'm not sure why this would result in less clean source control history - it's seems to be purely a question of whether your raw SQL is in independent files, or embedded within migration files.

Otherwise, if you really just want to run arbitrary SQL DDL to manage your stored procedures (e.g. drop/create), then I don't really see what advantage there would be in doing so via EF Core... Just like you're applying new migrations to your production database on deployment, you could run your custom, raw SQL script from outside EF Core. One of the main points of migrations is that they're tracked in the database, so that EF Core can only apply pending ones; but you want to re-run the same raw SQL every time.

[ajcvickers]

@bricelam I couldn't find the old-style seeding issue you mentioned in triage.

[bricelam]

I can't find it either...

[riedd2]

I just wanted to add how we accomplished this here before I close the issue.

Disclaimer: It's hacky, we track it as technical dept and we will fix it in the future. The usual things you tell yourself if you have tight project deadlines and time constraints.

The Implementation:

1. Create a custom migration, which reads the embedded resources sql and creates custom MigrationOperation based on it
2. A SqlServerMigrationsSqlGenerator implementation which takes care of adding the sql to the migration
3. Use a custom Migrator to add our custom migration to the migrations to apply
4. Replace the default SqlServerHistoryRepository implementation to not add a migration history check sql for our custom migration.

With this in place, changes and addition made to the sql files, will automatically be picked up and added to the end of every migration script that gets generated.

Thanks everyone for contributing to the discussion.

[lonix1]

My similar issue #26976 was closed in favour of this one, so since this is now the wishlist for migrations events, let me add to it:

Various events have been added to EF recently for various purposes.

Please consider adding pre and post events for migrations, that would be raised when migrating both programmatically (context.Database.Migrate()) or by the CLI (dotnet ef database update).

It would be nice if we could run such code as async, but if it's only sync (like Migration.Up() is now) then that's fine too.

---

Note for other readers: there's a workaround but it's imperfect. One can add a blank migration and place code in Up(). However that would run just one time. Not a problem programmatically (you can "work around" that :) ), but via the CLI there's no solution.

[lonix1]

I found a workaround - delete the last migration from the history table:

[DbContext(typeof(MyContext))]
[Migration("99999999999999_Last1")]
public class Last1 : Migration {
  protected override void Up(MigrationBuilder migrationBuilder) {
    Task.Run(() => callPostMigrationCodeThatIsIdempotent()).GetAwaiter().GetResult();
    migrationBuilder.DeleteData(HistoryRepository.DefaultTableName, nameof(HistoryRow.MigrationId), "string", "99999999999999_Last2", null);
    //migrationBuilder.Sql($"DELETE FROM {HistoryRepository.DefaultTableName} WHERE {nameof(HistoryRow.MigrationId)}='99999999999999_Last2'");  // or this way
  }
}

[DbContext(typeof(MyContext))]
[Migration("99999999999999_Last2")]
public class Last2 : Migration {
  protected override void Up(MigrationBuilder migrationBuilder) {
    Task.Run(() => callPostMigrationCodeThatIsIdempotent()).GetAwaiter().GetResult();
    migrationBuilder.DeleteData(HistoryRepository.DefaultTableName, nameof(HistoryRow.MigrationId), "string", "99999999999999_Last1", null);
    //migrationBuilder.Sql($"DELETE FROM {HistoryRepository.DefaultTableName} WHERE {nameof(HistoryRow.MigrationId)}='99999999999999_Last1'");  // or this way
  }
}

There are two "last" migrations:

• their ids are chosen so they run last ("99999999999999")
• both call the custom code, which must be idempotent - because it runs after every migration (also the first time this code is used, both migrations will run)
• each deletes the other from the history table
• use Task.Run for async over sync

After every migration, one of the two will run the custom code and prepare the history table for the next run (at which time the roles will be reversed).

This works in my testing environment.

The same double setup can be used for a pre-migration hook.

So my question: this feels like a dirty hack, and I fear it could blow up for some unknown reason; do you think it's reasonable/safe to use? Is there some way to make it better?

[aktxyz]

[DbContext(typeof(MyContext))]
[Migration("99999999999999_Last1")]
public class Last1 : Migration {
  protected override void Up(MigrationBuilder migrationBuilder) {
    Task.Run(() => callPostMigrationCodeThatIsIdempotent()).GetAwaiter().GetResult();
    migrationBuilder.DeleteData(HistoryRepository.DefaultTableName, nameof(HistoryRow.MigrationId), "string", "99999999999999_Last2", null);
    //migrationBuilder.Sql($"DELETE FROM {HistoryRepository.DefaultTableName} WHERE {nameof(HistoryRow.MigrationId)}='99999999999999_Last2'");  // or this way
  }
}

[DbContext(typeof(MyContext))]
[Migration("99999999999999_Last2")]
public class Last2 : Migration {
  protected override void Up(MigrationBuilder migrationBuilder) {
    Task.Run(() => callPostMigrationCodeThatIsIdempotent()).GetAwaiter().GetResult();
    migrationBuilder.DeleteData(HistoryRepository.DefaultTableName, nameof(HistoryRow.MigrationId), "string", "99999999999999_Last1", null);
    //migrationBuilder.Sql($"DELETE FROM {HistoryRepository.DefaultTableName} WHERE {nameof(HistoryRow.MigrationId)}='99999999999999_Last1'");  // or this way
  }
}

+1 on this clever work around !
my postgres column is named migration_id (not MigrationId) so had to make a small tweak to get this working
now I don't have to manually add my 1 line of code to the migration while I iterate !

[RyanMarcotte]

I just wanted to add how we accomplished this here before I close the issue.

Disclaimer: It's hacky, we track it as technical dept and we will fix it in the future. The usual things you tell yourself if you have tight project deadlines and time constraints.

The Implementation:

1. Create a custom migration, which reads the embedded resources sql and creates custom `MigrationOperation` based on it
2. A `SqlServerMigrationsSqlGenerator` implementation which takes care of adding the sql to the migration
3. Use a custom `Migrator` to add our custom migration to the migrations to apply
4. Replace the default `SqlServerHistoryRepository` implementation to not add a migration history check sql for our custom migration.

With this in place, changes and addition made to the sql files, will automatically be picked up and added to the end of every migration script that gets generated.

Thanks everyone for contributing to the discussion.

@riedd2 , are you able to share some code snippets from your implementation listed above? My team's use case is identical to yours: we have a collection of stored procedures defined in SQL files that we want to use for CREATE OR ALTER STORED PROCEDURE and we want this to run every time dotnet ef database update is run and on every deployment.

EDIT: Found this which appears to address step 4

[RyanMarcotte]

After doing a bunch of my own tinkering, I figured it out 🙂 . I have the following migration I want applied on every database update.

// the accompanying Designer.cs file must be modified to use 'CreateOrAlterStoredProceduresForReportSQL' as the migration ID instead of 'xxxxx_CreateOrAlterStoredProceduresForReportSQL'
public partial class CreateOrAlterStoredProceduresForReportSQL : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) => CreateOrAlterStoredProcedures(migrationBuilder);

    protected override void Down(MigrationBuilder migrationBuilder) => CreateOrAlterStoredProcedures(migrationBuilder);

    private static void CreateOrAlterStoredProcedures(MigrationBuilder migrationBuilder)
    {
        // dummy implementation is for prototyping purposes only
        // this would load SQL files and use them to generate the individual stored procedures

        var sql = $@"CREATE OR ALTER PROC [dbo].[DummySproc]
        (
            @no1 INT,
            @no2 INT
        )
        AS
        BEGIN
            RETURN @no1 * @no2;
        END";
            
        migrationBuilder.Sql($"EXEC('{sql}')");
    }
}

I defined custom implementations of some EF Core services...

// ReSharper disable once ClassNeverInstantiated.Global
[System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "See https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table?source=recommendations#other-changes for more information.")]
internal class CustomHistoryRepository : IHistoryRepository
{
    private readonly SqlServerHistoryRepository _baseRepository;

    public CustomHistoryRepository(HistoryRepositoryDependencies dependencies)
    {
        _baseRepository = new SqlServerHistoryRepository(dependencies);
    }


    public IReadOnlyList<HistoryRow> GetAppliedMigrations()
        => GetAppliedMigrations_Impl(_baseRepository.GetAppliedMigrations());

    public async Task<IReadOnlyList<HistoryRow>> GetAppliedMigrationsAsync(CancellationToken cancellationToken = new CancellationToken())
        => GetAppliedMigrations_Impl(await _baseRepository.GetAppliedMigrationsAsync(cancellationToken));

    private static IReadOnlyList<HistoryRow> GetAppliedMigrations_Impl(IReadOnlyList<HistoryRow> appliedMigrationCollection)
    {
        return appliedMigrationCollection
            .Where(row => row.MigrationId != nameof(CreateOrAlterStoredProceduresForReportSQL))
            .ToList();
    }

    public bool Exists() => _baseRepository.Exists();
    public Task<bool> ExistsAsync(CancellationToken cancellationToken = new CancellationToken()) => _baseRepository.ExistsAsync(cancellationToken);
    public string GetCreateScript() => _baseRepository.GetCreateScript();
    public string GetCreateIfNotExistsScript() => _baseRepository.GetCreateIfNotExistsScript();
    public string GetInsertScript(HistoryRow row) => _baseRepository.GetInsertScript(row);
    public string GetDeleteScript(string migrationId) => _baseRepository.GetDeleteScript(migrationId);
    public string GetBeginIfNotExistsScript(string migrationId) => _baseRepository.GetBeginIfNotExistsScript(migrationId);
    public string GetBeginIfExistsScript(string migrationId) => _baseRepository.GetBeginIfExistsScript(migrationId);
    public string GetEndIfScript() => _baseRepository.GetEndIfScript();
}



[System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "For applying custom migration on every database update.")]
internal class CustomMigrator : Migrator
{
    public CustomMigrator(
        IMigrationsAssembly migrationsAssembly,
        IHistoryRepository historyRepository,
        IDatabaseCreator databaseCreator,
        IMigrationsSqlGenerator migrationsSqlGenerator,
        IRawSqlCommandBuilder rawSqlCommandBuilder,
        IMigrationCommandExecutor migrationCommandExecutor,
        IRelationalConnection connection,
        ISqlGenerationHelper sqlGenerationHelper,
        ICurrentDbContext currentContext,
        IDiagnosticsLogger<DbLoggerCategory.Migrations> logger,
        IDiagnosticsLogger<DbLoggerCategory.Database.Command> commandLogger,
        IDatabaseProvider databaseProvider)
        : base(migrationsAssembly,
            historyRepository,
            databaseCreator,
            migrationsSqlGenerator,
            rawSqlCommandBuilder,
            migrationCommandExecutor,
            connection,
            sqlGenerationHelper,
            currentContext,
            logger,
            commandLogger,
            databaseProvider)
    {

    }

    protected override IReadOnlyList<MigrationCommand> GenerateUpSql(Migration migration)
    {
        if (migration is not CreateOrAlterStoredProceduresForReportSQL)
            return base.GenerateUpSql(migration);

        // per PopulateMigrations below, CreateOrAlterStoredProceduresForReportSQL is always the last migration applied
        // never add CreateOrAlterStoredProceduresForReportSQL to the migration history table in the database
        var migrationCollection = base.GenerateUpSql(migration);
        return migrationCollection.Take(migrationCollection.Count - 1).ToList();
    }

    protected override void PopulateMigrations(IEnumerable<string> appliedMigrationEntries, string targetMigration, out IReadOnlyList<Migration> migrationsToApply, out IReadOnlyList<Migration> migrationsToRevert, out Migration actualTargetMigration)
    {
        base.PopulateMigrations(appliedMigrationEntries, targetMigration, out var baseMigrationsToApply, out migrationsToRevert, out actualTargetMigration);

        // ensure that CreateOrAlterStoredProceduresForReportSQL is always the last migration applied
        migrationsToApply = baseMigrationsToApply
            .Where(m => m is not CreateOrAlterStoredProceduresForReportSQL)
            .Append(new CreateOrAlterStoredProceduresForReportSQL())
            .ToList();
    }
}

... then used those implementations as replacements for existing EF Core services.

// in the application's DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .ReplaceService<IMigrator, CustomMigrator>()
        .ReplaceService<IHistoryRepository, CustomHistoryRepository>();

    // other configuration
}

[riedd2]

Hey @RyanMarcotte

Yeah you got quite the similar solution as I implemented, here are some snippets:

CustomMigrator to add the custom migration:

[ExcludeFromCodeCoverage(Justification = "Migration logic, covered by sysint tests")]
[SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "We are aware of the risk.")]
public class CustomMigrator : Migrator
{
    public CustomMigrator(IMigrationsAssembly migrationsAssembly, IHistoryRepository historyRepository, IDatabaseCreator databaseCreator, IMigrationsSqlGenerator migrationsSqlGenerator, IRawSqlCommandBuilder rawSqlCommandBuilder, IMigrationCommandExecutor migrationCommandExecutor, IRelationalConnection connection, ISqlGenerationHelper sqlGenerationHelper, ICurrentDbContext currentContext, IModelRuntimeInitializer modelRuntimeInitializer, IDiagnosticsLogger<DbLoggerCategory.Migrations> logger, IRelationalCommandDiagnosticsLogger commandLogger, IDatabaseProvider databaseProvider)
        : base(migrationsAssembly, historyRepository, databaseCreator, migrationsSqlGenerator, rawSqlCommandBuilder, migrationCommandExecutor, connection, sqlGenerationHelper, currentContext, modelRuntimeInitializer, logger, commandLogger, databaseProvider)
    {
    }

    protected override void PopulateMigrations(IEnumerable<string> appliedMigrationEntries, string? targetMigration, out IReadOnlyList<Migration> migrationsToApply, out IReadOnlyList<Migration> migrationsToRevert, out Migration? actualTargetMigration)
    {
        base.PopulateMigrations(appliedMigrationEntries, targetMigration, out migrationsToApply, out migrationsToRevert, out actualTargetMigration);

        // add a custom migration at the end of the regular migrations
        migrationsToApply = migrationsToApply.Concat(new List<Migration> { new StoredProcedureMigration(new StoredProcedureLoader()) }).ToList();
    }
}

I like you approach better for the HistoryRespository implementation, I might steal that 😈 here is my "hack":

[ExcludeFromCodeCoverage(Justification = "Custom migration logic, covered by sysint tests")]
[SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "We are aware of the risk and tracking it.")]
public class CustomServerHistoryRepository : SqlServerHistoryRepository
{
    public CustomServerHistoryRepository(HistoryRepositoryDependencies dependencies)
        : base(dependencies)
    {
    }

    public override string GetBeginIfExistsScript(string migrationId)
    {
        if (migrationId.Equals(nameof(StoredProcedureMigration), StringComparison.OrdinalIgnoreCase))
        {
            return string.Empty;
        }

        return base.GetBeginIfExistsScript(migrationId);
    }

    public override string GetInsertScript(HistoryRow row)
    {
        if (row.MigrationId.Equals(nameof(StoredProcedureMigration), StringComparison.OrdinalIgnoreCase))
        {
            // Don't create an insert into the history table statement since this migration should run every time.
            return $"Select 1;{Environment.NewLine}";
        }

        return base.GetInsertScript(row);
    }
}

Additionally I also created a custom SqlServerMigrationsSqlGenerator implementation:

[ExcludeFromCodeCoverage(Justification = "Custom migration logic, covered by sysint tests")]
public class CustomMigrationSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public CustomMigrationSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations)
        : base(dependencies, migrationsAnnotations)
    {
    }

    protected override void Generate(
        MigrationOperation operation,
        IModel? model,
        MigrationCommandListBuilder builder)
    {
        if (operation is CreateValidationProcedureOperation createUserOperation)
        {
            Generate(createUserOperation, builder);
        }
        else
        {
            base.Generate(operation, model, builder);
        }
    }

    private void Generate(
        CreateValidationProcedureOperation operation,
        MigrationCommandListBuilder builder)
    {
        ISqlGenerationHelper? sqlHelper = Dependencies.SqlGenerationHelper;

        builder
            .Append("EXEC('")
            .Append(operation.Sql)
            .Append("')")
            .AppendLine(sqlHelper.StatementTerminator)
            .EndCommand();
    }
}

[ajcvickers]

Note from triage: If possible, don't create any new "design-time" dependencies for this.

[margohpolo]

The CustomMigrator helped me too! Thanks @RyanMarcotte :)

I'm still tinkering about to find a neat approach, so bear with me.

• I used a separate folder with all the StoredProc scripts (& CustomMigration files):

(I did DBFirst with WideWorldImporters DB for general tinkering, so all the scripts are from there.)

• And I only used a single EF Migration script. Pls ignore the Down():

• For the Designer.cs, maybe it's not needed (not sure yet if it'll break anything; too knackered to keep going tonight):

• Therefore I didn't need to intercept via a CustomHistoryRepository. Did use the CustomMigrator in exactly the same way, though.

• One thing I also did was to add this to each StoredProc script:

Probably not the best approach, but it's the best I can think of for now to ensure the StoredProc is always the most updated version, whenever there are changes made to the StoredProc scripts.

Overall though, would this be a neater approach?

[michaelmalonenz]

@RyanMarcotte thank you! That's the least terrible solution I've seen around.

For others, the problem as I see it, with not having a post migration hook is:

• Stored Procedures, Views, TVFs, Functions, etc all rely on an underlying table structure that might have changed underneath us. The DB Server will validate that when its created, but not if a dependent table changes, meaning we only find out at runtime instead of compile time. Fine if you have 100% test coverage, but I've yet to see a commercial project that does.
• Having to replicate the whole stored proc in a migration each time makes it very difficult for reviewers / code archaeologists to see what's changed - it's counter to working in a team. It's also difficult to work out which version is supposed to be the correct one (at as given point in time) when debugging.
• From my vantage point (though I'm new to .NET core - in the process of migrating from .NET Framework) it looks like the "recommended" solution to create seed data (which is where I added this sort of modification in EF6) on app initialization means granting extra permissions to a web app that I don't want it to have. If I accidentally let through a sql injection, I don't want the web app's DB user to have permission to drop or create tables. There is also the locking/contention issues mentioned above. I also don't want my app start-up performance hindered by something that doesn't need to happen in-process.

[roji]

The DB Server will validate that when its created, but not if a dependent table changes, meaning we only find out at runtime instead of compile time.

Assuming you're in SQL Server, you may be interested in "schema binding" (link) - this forbids changing a table's schema if e.g. it affects a view that's defined on top of it. Other databases typically have similar mechanisms.

[roji]

As suggested by @bricelam here, this could also cover calling ReloadTypes() in Npgsql, to reload any PostgreSQL types that may have been added as part of the migrations (e.g. an extension was added). This would require giving access to the DbConnection.

[CodyBatt]

@roji is there a reason why people shouldn't run idempotent SQL on every deployment? Seems like there are a few people that want this bad enough to work around it. Is this a bad idea for some reason other than "EF doesn't work this way"? These workarounds are both cool and ridiculous.

[jacekmlynek]

@roji Thanks for point me to interceptors. I thought I know well EF Core docs, but it looks like I missed that. If I may suggest something, from my perspective it will be nice to have dedicated page in doc for EF Migration customization rather than under logging and events parent page. It is just my feeling

In terms of idempotency and migration scripts. As far as I understood idempotent migration scripts operations work by checking if migration has been already run and if that is true not running such migration again. If we talk about such idempotency, it is very limited one. Typical create and replace command are idempotent, if you run them, they will truly change state of DB, while in EF nothing will happen once migration has been applied. If I can be honest my interpretation of idempotent migration scripts (maybe bad one) was that they are more to handle gracefully use cases where it is hard to know in advance if migration has been run or not. The other thing is, that even if I will not add --idempotent argument to script call, I cannot re-run same migration to apply diff change update on it. To sum up my understanding: idempotent migration scripts are far different concept to what is supported in liquibase or flyway as the repeatable migration and what I think other were comment in this issue.