(EF Core) SQL Scripts for Migration

Why SQL Scripts are Recommended for Migrations

We have seen in the previous tutorials that migrations can be quickly done by using ef core commands like Update-Database and Add-Migration. This approach is based on code files generated through a software program.

These files could be suitable for straightforward alterations, but they might not able to handle complex migrations with a high degree of fidelity.

For these reasons, the recommended approach for migrations is through the use of SQL scripts.

There are various reasons in favor of the SQL scripts:

1. SQL scripts can be passed on to database administrators for archival. They are in a syntax that they are more familiar.
2. SQL scripts can be fine tuned by database experts.
3. SQL scripts can always be reviewed for accuracy. This ensures a high fidelity migration.

Video Explanation

Please watch the following youtube video:

How to generate a SQL Script

Let us now examine how to generate an SQL script for your migrations.

The easiest way to generate a complete script from the initial blank database to the current state is to use the command Script-Migration

PM> Script-Migration

// package manager console 

This command generates a single SQL file containing all the statements that lead to the latest migration, starting from a blank initial database.

We shall later see how to obtain an SQL script between a starting and an ending migration.

But first let us see how to generate a script with visual studio.

Practical on generating a SQL Script

Let us come back to our project where we already have two migrations - MyDBCreate and MyColAddition.

We shall now generate an SQL script that can lead us from a blank database to the latest migration. So this script will implicitly start from MyDBCreate and reach to MyColAddition.

Open the package manager console and type the command Script-Migration. The from and to are implied because we haven't specified any parameters.

Hit enter to allow the command to execute.

As soon as it completes, the SQL script file is created and opened as shown.

This file is located in the Release or Debug directory as per the current configuration.

The script can be examined, fine-tuned and executed through a database utility.

Generating a SQL Script between two Migrations

Finally, let us learn how to generate an SQL script between two specific migrations.

Suppose we want to generate the SQL script from a migration, say, DeleteColumn to, say, AddIndex.

In this case the two parameters must be passed in the order as you see here.

PM> Script-Migration DeleteColumn AddIndex

// package manager console 

If the second parameter is not specified then the script is generated till the current latest state.

Generating an Idempotent SQL Script

EF Core tools allow us to generate an SQL script consisting of just the missing migrations.

The command can be used like this

PM> Script-Migration -Idempotent

// package manager console 

The migration tools generate this script on the basis of the migrations history table that it maintains in your database.