2

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.

Improve this question
3
  • What is in your DB update SQL? It might not be necessary to run a batch script each and every time depending on your changes. Commented Aug 3, 2020 at 7:52
  • @CaptainKenpachi - Database here is PostgreSQL, and I want to run a script pg_dump -h localhost -U postgres -p 5432 myDatabase > C:\Temp\myDatabase.sql which creates a SQL file with structure and data in it that I can forward to the DBA. Ideally that would be triggered right after update-database is finished successfully. In case of migration errors it should not run. Commented Aug 3, 2020 at 9:40
  • You're basically describing what a DACPAC does. I think you should explore those as a migration strategy. Commented Aug 3, 2020 at 9:58

2 Answers 2

Reset to default
3
+50

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I think I could go for approach 1. provided that I can run update-database from a batch file. For that, I found this answer mentioning the EF commandline tools, I will try that out and if it works, then I can write a two-liner which will do the job. Those tools can be installed via dotnet tool install --global dotnet-ef. That batch file could be run as a post-build action from VS passing the project directory as a parameter.
It's possible. See here: stackoverflow.com/questions/39641932/…
Interesting approach. The powershell script reads the app.config to get the database name and does some modifications to prepare a development database. It uses update-database to build it up from the migrations.
3

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.

Improve this answer

9 Comments

That's right, I have tried dataContext.Database.Migrate(); but it does not always work. I don't know why, but there are some situations where only update-database, run from Package Manager Console succeeds. I suppose that is when you have breaking changes in the database structure. This is why I don't want to run automatic migrations in the production, only in development environments (and provide the SQL script, that can be reviewed before the DBA deploys it).
Ah okay. There is also the option of DACPAC migrations. It's complicated to set up, but pretty easy to use once done. This would take the place of automated migrations entirely. You would only use your migrations for local development.
You've meant this, right? That is for MS-SQL server. Do you know, how that integrates with the code-first approach? (With code-first, you generate decorated model classes, add the migrations and update-database applies that to the local database)
I think I could use the approach you mentioned if it gets triggered only in the dev environment - so the Configure method needs a check being added if we're running it in DEV - I could think of #if DEBUG or something similar.
Well, I was asking because in earlier projects I used database first approach, which is also fine, together with the schema compare tool VS provides and SQL server database projects. If you need just a simple database, then code first is of course easier.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.