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
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:
- SQL scripts can be passed on to database administrators for archival. They are in a syntax that they are more familiar.
- SQL scripts can be fine tuned by database experts.
- SQL scripts can always be reviewed for accuracy. This ensures a high fidelity migration.
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
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 -
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
Open the package manager console and type the command
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
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,
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.
This Blog Post/Article "(EF Core) SQL Scripts for Migration" by Parveen is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.