In recent posts, I have been writing about EF Core concepts. EF Core code first approach allows to write C# code first and then this C# code can be then converted to migrations. These migrations help to create or modify database and database entities.

In this post, let’s discuss about how to apply migrations via SQL scripts and what are advantages of this approach.

Multiple ways to apply migrations

There are various ways to apply migrations.

• NuGet Package Manager commands (old way). This was mostly used with the .NET Framework and Entity Framework.
• DOTNET CLI commands (the recommended approach) – refer this blogpost for more details
• Applying migrations at runtime – by using context.Database.Migrate() API
• Generate SQL Scripts from migrations and then apply those scripts to the database.

We already have discussed 2nd and 3rd approach in previous blogposts. You can refer the links provided above to get more information about those approaches.

Cons of applying migrations via Commands

In my previous post, I have explained an approach to apply migrations using commands. Below are the commands that are explained in previous blogpost.

If the project is in early stages and database has not gone to production yet, developers may have luxury of deleting the database completely and then recreating the database using above commands.

But if one version of database is already live on production, this luxury is not applicable. Deleting an existing database in production means data loss and it is not acceptable in real world applications.

Also, if we want to analyze the migrations to ensure there is not data loss, then CS files generated by above commands might be difficult to analyze.

Apply migrations via SQL Scripts

Applying migrations to the existing database in production, is very critical task. If anything goes wrong, it may damage the business. Hence, the migrations should be carefully reviewed and tested before applying them to existing database.

The documentation recommends using SQL scripts for applying migrations to the database. There are various advantages of this approach:

• SQL Scripts can be validated and reviewed. In my opinion, it is probably easier to review SQL scripts than reviewing the C# migration files.
• Automatically generated scripts can be further optimized for serving any specific needs.
• Like CLI commands, SQL scripts can also be generated as part of CI builds if required. Or there can be a separate process to generate and commit them to repository beforehand after they are reviewed. Any strategy can be selected based on needs of the project.

How to generate SQL Scripts?

Below command shows how database scripts can be generated using CLI commands. This command generates all the scripts starting from blank database till latest migration. The output option in command is required to generate a file. if it is not provided, the SQL script would be printed on console and file will not be generated.

With Migration & To Migration Options

Practically, EF core changes are applied to database step by step. Developers may start working on application and the team may come up with initial schema. Initially, one migration is created corresponding to that state and it is applied to the database. Next, developers may continue making changes and after every set of logical modifications, a migration can be created.

Hence, for every set of logical changes to the EF Core models and context, you would have a migration associated.

Hence, while generating SQL script, we may wish to:

• EITHER To generate the scripts only for changes done from a particular migration till latest migration
• OR to generate the scripts from a migration to another migration

The DOTNET CLI provides options to specify either one or two migration names in the command.

• If only one name is specified, then all the changes starting from specified migration till latest migration are considered while generating SQL script.
• If two migration names are specified in the command, then all changes applied from first migration to the second migration are included in the SQL script.

In both the cases, the from migration should be the last migration applied to the database. The to migration would become the last migration applied to the database after running the commands.

Below are the examples:

Idempotent Scripts

While generating SQL scripts, it is our responsibility to make sure that the database is in correct state. The SQL scripts generated are corresponding to some logical group of modifications. These modifications probably were built on top of previous modifications. Hence, the script should take care of ensuring that it should apply changes only if those changes were not yet applied.

Wrapping Up

We have seen what may be problems if the migrations are directly applied by CLI commands and how generating SQL scripts from migrations help to solve those issues. The SQL scripts can then be applied via tools which runs SQL script against the underlying database. The executed scripts can also be maintained in source control, just in case history of the scripts is required.

I hope you find this information helpful. Let me know your thoughts.