Reputation: 27036
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.
Upvotes: 2
Views: 2811
Reputation: 952
Two options I can think of
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
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
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
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.
More about different type of Powershell sessions
https://www.sconstantinou.com/windows-powershell-sessions-pssessions/
Upvotes: 3
Reputation: 7215
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.
Upvotes: 3