Page 16 - MSDN Magazine, October 2019

The PowerShell syntax uses the -To and -From parameters. From specifies the last run migration and therefore doesn’t include it in the generated script:
script-migration -To initial -Output createAgilistaCategoryTables.sql script-migration -From initial -To seeddata -Output SeedData.sql
The CLI syntax doesn’t use the parameter names. You only supply the names of the from and to migrations. While these aren’t required arguments, if you do want to specify which migrations to script, you must provide both arguments. Therefore, if you’re starting from the beginning, you need to use the number zero in the from position as I’m doing in the first of these two CLI commands:
dotnet ef migrations script 0 initial -o createAgilistaCategoryTables.sql dotnet ef migrations script initial seeddata -o SeedData.sql
Youcanseethedetailsofthesefilesintheaccompanyingdownload.
Transforming the Scripts for Flyway
There are some facets of the new script files that are specific to EF Core. Because of that, I need to do a little extra work to use them outside of EF Core migrations. There are five tasks:
• Create the database
• Remove EF Core migrations-specific logic
• Ensure the database is targeted prior to running SQL
• Rename the files to follow Flyway naming conventions • Move the files into a sub-folder called sql
Creating the Database
The first step is to ensure the database gets created. Why? Although the EF Core Migrations Update Database command creates a database if needed, that logic is internal to the API, not expressed in the migration files. So, you’ll need SQL that creates the data- base before the new scripts are run, and perhaps more tasks to be performed on the database server or the database itself, such as creating roles or users. I want to create a login, user and role on the SQL Server and then, after creating the new database, set up the same login, user and role in the database.
I’ve created another SQL file, InitDatabase.sql, to perform all of those tasks and, again, because of space constraints, you’ll need to grab the download to see the details. However, key in the file is the command to create the database:
CREATE DATABASE DB_Agilistas
Removing EF Core-Specific SQL
The second task I need to perform is to remove EF Core migrations- specific logic from the generated SQL scripts. That’s all of the references to the Migrations_History table that EF Core maintains. You won’t need these because you won’t be using EF Core to keep track of the migrations.
The first script file (createAgilistaCategoryTables.sql) contains SQL to create the table and to update it after the migration has been performed.
At the top of the file you’ll see:
IF OBJECT_ID(N'\[__EFMigrationsHistory\]') IS NULL BEGIN
CREATE TABLE \[__EFMigrationsHistory\] (
\[MigrationId\] nvarchar(150) NOT NULL,
\[ProductVersion\] nvarchar(32) NOT NULL,
CONSTRAINT \[PK___EFMigrationsHistory\] PRIMARY KEY (\[MigrationId\])
); END;
This needs to be removed, as well as any commands to insert data into that table (which you’ll find at the end of each SQL file). For example:
INSERT INTO \[__EFMigrationsHistory\] (\[MigrationId\], \[ProductVersion\]) VALUES (N'20190418172940_seeddata', N'2.2.3-servicing-35854');
Specifying the Correct Database
EF Core knows which database to execute migrations on. But your output script files don’t contain this information. Be sure to include it at the top of each of the SQL files, although not the one that’s creating the database. That way you don’t have to worry about the connection string defaulting to a different database such as master. I’m adding the following to the top of the two generated SQL files:
USE DB_Agilistas GO
Renaming the Files for Flyway
Finally, you’ll need to be sure the file names align with Flyway convention so they’ll be run in the proper order. Note again that I’m focusing only on forward-movement files that start with the letter V for Version, and not Flyway’s ability to have Undo or Repeatable scripts.
Currently my three files are named: • InitDatabase.sql
• createAgilistaCategoryTables.sql • SeedData.sql
So, you’ll need SQL that creates the database before the new scripts are run, and perhaps more tasks to be performed on the database server or the database itself, such as creating roles or users.
To ensure that Flyway will execute them in the proper order, I renamed them:
• V1_0__InitDatabase.sql
• V1_1__createAgilistaCategoryTables.sql • V1_2__SeedData.sql
They all start with V1 because they’re part of my first pass. In addition to one last reminder about the importance of that double underscore, I’ll admit it took me a number of tries before I real- ized that the name of one of the files (which Flyway kept ignoring) began with a lowercase v rather than upper.
After making all of these changes, I moved the three SQL files into a sub-folder in my project called sql, which follows a conven- tion of Flyway you can override if needed.
12 msdn magazine
Data Points