• Flyway
• Integrating with Flyway
• Team-based Development

Working with Flyway and Entity Framework Code First: Automation

Entity Framework Code First is a fixture of the database development process for many .NET development teams. Of course, the premise is an attractive one: the developers just adapt the data model, in code, while a tool automatically keeps track of the database schema changes and generates migration scripts.

However, as discussed in my previous article, Working with Flyway and Entity Framework Code First: An Overview, there are downsides too, around a lack of visibility into changes, often lack of testing, a tendency to produce complex, poorly performing SQL, all of which can lead to trouble at deployment time.

I went on to propose a ‘hybrid’ Entity Framework-Flyway model where an EF-managed development process still produces the database migration scripts, but we use Flyway to increase the visibility and reliability of the deployment process, making it much easier to automate. The last article was the overview and now we get to implementation. I’ll show you the basics of how to automate each part of the EF-Flyway hybrid process using PowerShell and Azure DevOps pipelines.

Automating The EF-Flyway hybrid model

In the primary EF-Flyway hybrid model, we convert C#-based EF Core migrations to Flyway SQL migration scripts, and then apply those scripts to a Flyway-controlled database (the Flyway shadow database). At this point, we can use Flyway Desktop to extract and save a ‘schema model’ for the database, which we can commit to the version control repository. This automation process requires the Enterprise edition of Flyway.

This workflow of generating the migration script first and then retrospectively saving the schema model changes is an ‘inversion’ of the usual Flyway Desktop model (where we usually save the schema model changes and then generate the migration), but it works well for us here and is easy to automate:

Step 1: Develop C#

The .NET developers update the database model directly from their C# classes. I’ve provided a sample EF Core project on GitHub.

Step 2: Script and convert SQL migrations.

Every time the .NET developer commits and pushes a C# migration, we want to export it and convert it to a Flyway SQL migration script. To automate this C#-to-Flyway script conversion process, we need a PowerShell script that will:

1. Export the C# migrations to SQL, using the EF Core CLI migrations script command
2. Convert the scripts to the Flyway migration format – the EF “Up” script becomes a Flyway versioned (V) migration script and the EF “Down” script becomes a Flyway versioned undo (U) script

First, we specify the paths to the EF Core and Flyway migration files and then get a list of the EF Core migration files:

Before we attempt to convert any EF migration files to Flyway migrations, we check that it hasn’t already been done. We collect all Flyway migrations, chop off the flyway version number and then check for name matches against EF Core migrations:

When there are existing Flyway migration files, the last matching file name indicates the current Flyway version and is our starting point. When no file is found, the conversion starts from scratch.

For each EF Core migration that we need to convert, the script will perform the following steps:

• Check that the file is not a Model Snapshot or Designer file. We are only interested in the C# migrations.
• Skip already converted migrations but do log them to the console.
• Convert the “Up” EF Core migration file to a Flyway versioned (V) migration file and save it to disk
  • The flyway version number will be determined by the iteration number.
  • With regular expressions:
    • Add an extra column (FlywayInstallRank) to the EF migration history table:

• • • Remove non-SQL output from the EF Core tool output.
• Convert the “Down” EF Core migration file to a Flyway undo (U) file and save to disk.
• Update the previous migration file name for the next iteration.

If we run the script above on the repository that is available on GitHub, the script will transform the three EF Core migrations to six Flyway migrations SQL files – three V scripts and three U scripts:

Running the script, a second time won’t do anything else than informing the user that the available migrations are already converted. The result on disk of this action is that the files in the red square are created:

That is nice, but we are now only halfway; it’s time for the next part of the script.

Step 3. Object-level versioning (schema model)

This stage marks the start of the Flyway-managed process. Our script will use the Flyway CLI to a) apply the converted migrations to the shadow database, and then b) use the Flyway Desktop CLI (flyway-dev) to extract the schema model and save it to disk.

a. Migrate the shadow database

This section of the script starts by retrieving the URL for the shadow database from the flyway-dev.user.json project file. The shadow database is a feature of Flyway Enterprise, used to generate and verify migrations. We use this URL, along with the locations of the Flyway project and the current working directory (which we already set in the previous section), to define the parameters for the Flyway migrate command, and then execute it:

The shadow database now reflects the current ‘state’ of the data model, as defined in EF, and has a Flyway version number (V003, in this example). The eagle-eyed among you may have spotted, in the previous screenshot showing the newly converted Flyway migrations, an afterMigrate.sql script. This is a Flyway callback script, which runs on successful completion of a Flyway migration run and, in this case, inserts the new flyway version number into the FlywayInstallRank column that we added earlier to the EF schema history table.

This ability to sync the EF and Flyway migration history tables can be useful when applying undo migrations and keeping track of what Flyway migration is linked to which EF Core migration.

b. Save the schema model

We now want to capture the schema model for this version of the database. We extract this from the shadow database (at V003) using the diff command in flyway-dev , with the migrations folder (which Flyway builds in the shadow database) as source and the schema model folder, on disk, as target. The FWD CLI will use the SQL Compare CLI to compare the two and saves the differences in a diff artifact, identified by a unique GUID. By default, this artifact will be saved to use the user’s temporary directory – in a specific FWD folder, so we stick with that. We then take the differences from the diff artifact and apply them to the schema model folder:

Running this script produces a lot of output, as shown in this video:

The result is that we extracted the schema model changes of the EF Core migrations, via the shadow database, to disk:

In order to ensure that we don’t accidentally include the __EFMigrationsHistory table in the schema model, we filter it out of the comparison using the SQL Compare filter file, which you can find in the root of the Flyway Desktop project folder:

Step 4: Commit changes to Git

The final step is to commit these changes, the schema model and the flyway migrations, to the git repository. The migrations can be used to migrate various upstream environments, but only after an approval of the changes that are in the schema model, which can be reviewed by a colleague (DBA, database engineer, etc).

This review step can take place in the Pull Request. This formal administrative step will contain:

• The migrations (converted).
• The schema model changes.
• The why, via a commit message, PR comments or a linked work item (optional)

Of course, we need to tackle the question of what happens if the changes aren’t approved, for example if the reviewer finds that the way the changes were implemented will cause performance or security issues. What’s the workflow at that point? I’ll tackle this issue in the next article!

An alternative hybrid model with Flyway-generated migrations

In my previous article, I also suggested an alternate approach where, instead of converting the EF-generated migrations, we use them to update a flyway-managed build database, at the current production version. We then capture the changes to the schema model and let Flyway Desktop generate the equivalent V and U migrations.

This workflow is more akin at a conventional Flyway Desktop workflow, as described in Flyway Desktop in Database Development Work: An Overview. However, can we still automate it?

We can! I’ve provided a small script in the GitHub repo to allow the EF Core tool to run the dotnet ef database update command against the Flyway-managed Build DB. After that, the Flyway Desktop CLI (flyway-dev) takes over. We use it to automatically save the schema model and generate the migration scripts, in response to each update of the Flyway-managed build database. I demonstrate all the flyway-dev steps in The Flyway Desktop CLI: Automate your Database Development Process.

Once the schema model is saved and the migration files regenerated, in Flyway, we commit all the changes, which will be reviewed in a Pull Request as before.

Conclusion

Both the “EF script conversion” and “Flyway script regeneration” workflows have their pros and cons. I’d personally choose the conversion model, for a simple example like this, but with more complex code, or using an RDBMS than SQL Server, conversion problems could arise. The choice of workflow depends on the use case.

However, regardless of the chosen workflow, the EF-Flyway hybrid model provides improved visibility into database changes, earlier detection of potential issues, and better collaboration between development and database teams. It allows developers to work with the ease and familiarity of C# code during development, while relying simple, versioned SQL migrations provided by Flyway for automated deployments.

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more

Flyway Teams

Ideal for organizations looking to improve collaboration and fine tune their processes during development and the deployment of database changes.

Find out more