CodeFirst migrations: How to run a database script (C# or SQL) after completion of "update-database" automatically?

Question

I am using EF.Core with code-first migrations to update a SQL database.

Whenever I added migrations (Package Manager Console: add-migration), I am updating the database with the well-known update-database command. Is there a way to run a SQL batch script after completion of this command automatically (like you can do with post-build events in Visual Studio) ?

That script could backup the database or do other tasks (like setting up user roles etc).

I don't want to modify existing migrations to do this, I know you can add something like

   protected override void Up(MigrationBuilder migrationBuilder)
   {
        var sqlFile = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, 
                       @"Migrations\20200701103006_MySQLBatch_Up.sql");
        var sqlCommands = System.IO.File.ReadAllText(sqlFile);
        migrationBuilder.Sql(sqlCommands);

        // ...
   }

But I don't want to do that, because this way you would have to do it every time you're adding a new migration.

Is there an event or a method that one can override to achieve it? Or something that can be triggered, like a script?

Effectively, what I want to achieve is having a script or method invoking:

update-database
pg_dump -h localhost -U postgres -p 5432 myDatabase > C:\Temp\myDatabase.sql

---

Note: update-database runs in the Package Manager Context, pg_dump runs in the command shell (cmd.exe) - hence, you cannot run update-database in a .cmd or .bat script directly.

Answer 1

I can't seem to find anything in the documentation to do what you want in the way that you want.

However, an alternative solution I thought of is to, instead of running the scripts from the commandline or as part of the migration class, you run them on Startup as part of your automatic migration.

So you can do something like this:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DataContext dataContext)
{
    // migrate changes on startup
    dataContext.Database.Migrate();
    foreach(var file in GetMigrationFiles())//you can write the code that searches a folder for SQL scripts to execute
    {       
       dataContext.Database.ExecuteSqlRaw(File.ReadAllText(file));
    }
}

Make sure to order your GetMigrationFiles() properly.

The problem here is that you'll need to also write SQL to roll back your custom SQL in the event that you'd want to roll back. Put those in a different folder though.

Answer 2

Two options I can think of

1. Simpler: Why not have a simple batch file that executes both commands in sequence, and then you can run the batch file instead of Update-Database command? You could even get the most parameters from a standard project config file and that way you could use the same script in multiple projects without changing anything except the project config file. This way you make sure your additional scripts runs in response to Update-Database command when it actually is intended

2. If this is for a Powershell session that may involve multiple "update-commands" in a dynamic manner and you don't want the above approach, then you can try subscribing to Powershell engine Exiting event (i.e. [System.Management.Automation.PsEngineEvent]::Exiting) and execute a script in response automatically via its -Action parameter anytime Update-Database is executed (but only if in the same Powershell session).

See Register-EngineEvent command for details and examples

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/register-engineevent?view=powershell-7

Inside your action script, you can get the event details (like $Events[0].MessageData) and search for the text " Database-Update ", then execute your your desired commands in response. This can get erroneous if " Database-Update " text appears in any unintended context in the session.

You can review Get-Event command details and examples here

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/get-event?view=powershell-7

You can setup a persistent session with local or remote computer via New-PSSession session command so that commans executed in multiple files can be considered for the event subscriber.

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/new-pssession?view=powershell-7

More about different type of Powershell sessions

https://www.sconstantinou.com/windows-powershell-sessions-pssessions/