We have happily spent some time making changes to the database using the Update-Database command throughout this series of entries, however, this is something we can not always do. Two reasons why we will not always use Update-Database:

1. If the database with which we want to work is already in production, it may not be a good idea, nor practical, to perform an Update-Database to update it.
2. If we do not have permissions to make changes on the database. In these cases, typically the protocol is to delegate the execution of the changes to a database administrator of our team, which has the responsibility to work with the database directly.

For both situations, the Script-Migration command can help us. With the Script-Migration command we can generate in SQL the changes that will be made in a database with a migration. That is, if we have a migration that says that we add a column to a table, then with the Script-Migration command we can generate the SQL statement that performs this action in the database. Then, this SQL script can be run in our database in production, or, if we do not have permission to do that, we can deliver this script to the database administrator to review it,  approve it, and execute it in the database.

In dotnet CLI, this command is:

dotnet ef migrations script

Some of the arguments and options that this command can use are the following:

• From: The initial migration from which the SQL will be generated. We remember that we can have several migrations, and, sometimes, we only want to generate the SQL of the migrations from a specific migration. If this parameter is omitted, then the generation of the SQL code will start from the first migration.
• To: The last migration from which the SQL will be generated. If it is omitted, by default the most recent migration will be taken as the last migration from which the SQL code will be generated.
• Output: Name of the file where the SQL code will be saved
• Idempotent: This options generates checks to see if a migration has been applied, if it has been applied before, then it won’t be applied again when the script is executed.

Examples

Basic use: The basic use would be to generate a script with all the migrations of your project. For this, in the Package Manager Console, we must use the following command:

Script-Migration

In the dotnet CLI:

dotnet ef migrations script

This will generate a script in the console or in Visual Studio with the SQL script necessary to apply all the migrations that exist in your project.

Generating the script in a file: It is more useful to generate the SQL script in a file rather than to have it in a console. We will generate the script in a file called script, in the Package Manager Console:

Script-Migration -Output "script.sql"

In the dotnet CLI:

dotnet ef migrations script --output "script.sql"

Note: In the case of Visual Studio and the Package Manager Console, it is possible that the file is generated in the solution directory and not in the project directory.

Generating the script of only some migrations: Sometimes we will only want to generate the script of some of the migrations that we have. For that, we can use the From and To options. With these options we can indicate from which migration we want to start generating the script, and in which migration we are going to stop.

Suppose we have the migrations: First, Second, Third, Fourth and Fifth; and we want to generate a script with the migrations from the Second to the Fourth, excluding then the First and the Fifth. We can do this in the Package Manager Console as follows:

Script-Migration -from Second -to Fourth

In the CLI dotnet:

dotnet ef migrations script Second Fourth

Note: Remember that previously we said that, if we omit the From parameter, then when generating the migrations script, it will start from the first migration. Similarly, if we omit the To parameter, then the most recent migration will be terminated.

Generating scripts for migrations that have not been applied: You probably want to generate the scripts for migrations that have not yet been applied in the database. Although this is certainly something you can do with the From and To parameters, there is a simpler way to use the idempotent option.

This option is to generate a script which makes migration migration checks to verify which ones apply and which ones do not.

Let’s see an example, in the Package Manager Console, we can do it in the following way:

Script-Migration -Idempotent

In the dotnet CLI:

dotnet ef migrations script --idempotent

If you look at the generated script, you will see that it uses conditionals so that, if an X migration was already applied, it won’t be applied again.

———–

These are other commands we have seen so far:

• Getting Help
• Add Migration
• Update-Database
• Remove-Migration
• Scaffold-DbContext
• Drop-Database
• Get-DbContext