How to generate idempotent scripts from EF Core migrations

23 Mar 2020 by Irina Scurtu

Is it possible to generate idempotent scripts from EF Core migrations? Automatically? Yes, it is! But first, let's see what idempotency means.

What is an idempotent script? #

A subroutine with side effects is idempotent if the system state remains the same after one or several calls, in other words, if the function from the system state space to itself associated to the subroutine is idempotent in the mathematical sense given in the definition;

Generally speaking, an idempotent script, no matter if it is written in PowerShell, bash or SQL on any other scripting language, will give you the luxury to run it over and over again, without the fear of breaking something. More than that, any script that might fall under unsupervised hands/system should have some 'safety' measures built-in.

Now, we know from HTTP concepts that GET and HEAD requests are idempotent and safe. But POST requests aren't neither safe nor idempotent.

The question is why? Well, first of all, by sending a POST request you end up creating new resources, and if you don't have a business rule to prevent requests with the same request body, you'll just have a lot of duplicates and nothing stopping you from creating them.

Why can't we apply the same rules when writing database scripts?

General idempotency guidelines in database scripts #

You must write it in such a way that you check at least the most likely scenarios.
For example:

for a table creation - check first if the table exists, if yes, do nothing
for deleting a column - check if exists
for altering a column - check if exists

So on and so forth, but you get the point. You need to cover all the scenarios that can end up corrupting your database.

There are several options to manage your database scripts. With so many third-party or out-of-the-box tools, sometimes is difficult to even choose.
A few years back I remember using PostgreSQL as a database for a project, and I really enjoyed the syntax. We used to manually make idempotent scripts, tested them locally, then added them to a different repository and shipped them to production with Flywayy.

Generating idempotent scripts from EF Core migrations #

Assuming you already have a migration added there are a few commands that are handy and with a few flags

generate script in CLI
dotnet ef migrations script {lastAppliedMigration} --startup-project MyApp --idempotent
By running the command you will get as a response the script in the CLI. Make sure you specify the last working migration in the placeholder. Without that, you will obtain the script for all migrations ever applied in the project.
generate the script in an .sql file under a specific location and in a specific file:dotnet ef migrations script {lastAppliedMigration} --idempotent --output {FolderName}/{sqlscriptname}.sql --startup-project MyApp

No matter the script you are running you can always add the --verbose flag to see what is executed with that script.

Real-life example: generating a script that adds Addresses to Person

dotnet ef migrations script MadeRequired --idempotent --output Scripts/Addresses.sql --startup-project Persons

Will generate this:


IF NOT EXISTS(SELECT \* FROM \[\_\_EFMigrationsHistory\] WHERE \[MigrationId\] = N'20200314111307\_Addresses')
 BEGIN
     CREATE TABLE \[Address\] (
         \[Id\] int NOT NULL IDENTITY,
         \[StreetName\] nvarchar(max) NULL,
         \[ZipCode\] nvarchar(max) NULL,
         \[City\] nvarchar(max) NULL,
         \[PersonId\] int NOT NULL,
         CONSTRAINT \[PK\_Address\] PRIMARY KEY (\[Id\]),
         CONSTRAINT \[FK\_Address\_Persons\_PersonId\] FOREIGN KEY (\[PersonId\]) REFERENCES [Persons]([Id]) ON DELETE CASCADE
     );
 END;
 GO
 IF NOT EXISTS(SELECT \* FROM \[\_\_EFMigrationsHistory\] WHERE \[MigrationId\] = N'20200314111307\_Addresses')
 BEGIN
     CREATE INDEX \[IX\_Address\_PersonId\] ON [Address]([PersonId]);
 END;
 GO
 IF NOT EXISTS(SELECT \* FROM \[\_\_EFMigrationsHistory\] WHERE \[MigrationId\] = N'20200314111307\_Addresses')
 BEGIN
     INSERT INTO \[\_\_EFMigrationsHistory\] (\[MigrationId\], \[ProductVersion\])
     VALUES (N'20200314111307\_Addresses', N'3.1.2');
 END;
 GO

As you can see, this script relies heavily on the __EFMigrationsHistory table, which is not exactly very...idempotent. So, my recommendation is to have another look at the generated scripts and customize and test them.