Creating Deployment & Rollback SQL Scripts from EntityFrameworkCore migrations

If you have worked on an application implemented in dotnet core, chances are high that changes in data models and database schemas are managed using EF Core. The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application’s data model while preserving existing data in the database.

There are various strategies for applying EF Core migrations, with some being more appropriate for production environments, and others for the development lifecycle.

Microsoft’s EF Core documentation suggests that the recommended way to deploy migrations to a production database is by generating SQL scripts. The advantages of this strategy are stated as following:

• SQL scripts can be reviewed for accuracy; this is important since applying schema changes to production databases is a potentially dangerous operation that could involve data loss.
• In some cases, the scripts can be tuned to fit the specific needs of a production database.
• SQL scripts can be used in conjunction with a deployment technology, and can even be generated as part of your CI process.
• SQL scripts can be provided to a DBA, and can be managed and archived separately.

Let’s have a look at how to generate SQL scripts for your migrations

Generating SQL scripts for applying migrations

The below command can be executed using the .NET core CLI to generate SQL script for your migrations. This command generates a SQL script from a blank database to the latest migration.

dotnet ef migrations script

Script generation accepts the following two arguments to indicate which range of migrations should be generated:

• The from migration should be the last migration applied to the database before running the script. If no migrations have been applied, specify 0 (this is the default).
• The to migration is the last migration that will be applied to the database after running the script. This defaults to the last migration in your project.

You can also mention the migration from which you want to create the SQL Script to the latest migration by adding the from migration name as mentioned below

dotnet ef migrations script FromMigrationName

If you prefer to generate a SQL script from the specified from migration to the specified to migration, you could mention the from & to migration as mentioned below

dotnet ef migrations script FromMigrationName ToMigrationName

Generating Rollback SQL Scripts for your migrations

Whenever you deploy changes to any higher environment such as a UAT environment, any other testing environment or a Production/DR environment it is always necessary to have a rollback script ready in case there are some issues faced during deployment and you need to rollback the changes so that the user experience/testing is not impacted.

You can generate rollback scripts using the ef core script generation command. The only difference would be the from and to migrations would be inverted.

If your command that generates SQL script for applying migrations looks like below

dotnet ef migrations script ThirdMigrationName FifthMigrationName 

then the command to generate Rollback SQL Script would be :

dotnet ef migrations script FifthMigrationName ThirdMigrationName

If you want to rollback all migrations then you can specify 0 in the to argument and from argument would contain the migration from which you want the rollback script to start.

Idempotent SQL scripts

EF Core also supports generating idempotent scripts, which internally check which migrations have already been applied (via the migrations history table), and only apply missing ones. This is useful if you don’t exactly know what the last migration applied to the database was, or if you are deploying to multiple databases that may each be at a different migration.

The following generates idempotent migrations :

dotnet ef migrations script --idempotent

It is a good practice to always generate idempotent scripts so that you don’t end up adding the same migration multiple times on your database leaving the database in an inconsistent state.

Before deploying to production the DBA should always review the script for accuracy. This is to ensure that the SQL script is updated in case the production database might have some minor changes which are not present in the other environments and a DBA might be aware of these changes.

Thus, we saw how we can generate SQL scripts and rollback scripts for EF Core migrations.

I hope you found this informative and helpful.

Thank you for reading!