How to generate idempotent scripts from EF Core migrations

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 Flyway.

Generating idempotent scripts from EF Core migrations

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

1. 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.
2. 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](%5BId%5D) ON DELETE CASCADE ); END; GO IF NOT EXISTS(SELECT \* FROM [\_\_EFMigrationsHistory] WHERE [MigrationId] = N'20200314111307\_Addresses') BEGIN CREATE INDEX [IX\_Address\_PersonId] ON [Address](%5BPersonId%5D); 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.

Conclusion

By using the right tools and knowing the right flags we can make our lives easier.

The post How to generate idempotent scripts from EF Core migrations appeared first on Irina Scurtu.