• Flyway
• Team-based Development
• Integrating with Flyway

Working with Flyway And Entity Framework Code First: An Overview

Many .NET developers prefer to work with Visual Studio and automatically create and update the database from their C# code (known as the “code first” approach). On the other hand, you have database developers and DBAs who will prefer to work with plain SQL scripts, to know what has changed and to review database changes before it hits any environment.

While the code first approach works well for the development team, it can cause problems at deployment time due in part to a lack of visibility into the database changes and difficulties handling failed deployments. The industry-wide standard for controlled, automated database deployments that you can rollback safely, is use of a dedicated SQL migrations tool, such as Flyway.

How do we combine the strengths of each, but without repeating work? This article suggests two possible workflows that we can adopt, each of which uses EF Core Code First to manage the development process and Flyway for the release process and deployments. My next article, Working with Flyway and Entity Framework Code First: Automation, demonstrates how to automate as much of these workflows as possible.

The EF Core ‘Code First’ approach

EF Core is an open source and cross-RDBMS version of the Entity Framework data access technology. As the name implies, the Code-First approach first defines the entity data model (EDM) in C# code and then, from this model, automatically creates and updates the database schema.

The following diagram shows a simple EF Core Code First development workflow:

1. Develop C#

A .NET developer writes code for C# classes (e.g., in VS Code) and generates the database model directly from them. For more info, please read this tutorial.

2. Generate C# Migration

From the C# code, the EF Core CLI tools can automatically generate the C# migration files describing the required database changes to the database schema objects. The EF tool will then check what migrations are already in the solution and add a new one reflecting all the changes made to the model since the previous migration. The C# migration file contains both an Up and a Down method, which are used to up or downgrade the target database.

3. Apply migration

Applying the generated migrations can be done with the EF Core CLI tools or it can be done within the applications during runtime. EF will use the methods defined in the C# migration files to generate to apply the changes to the database and then run them, tracking the code version in a table called __EFMigrationsHistory.

The good and the bad of EF Code First

For development and testing work, EF Code First offers a lot of advantages for .NET developers, especially those who may not have as much experience with SQL and prefer to work with C# code.

With Code First, developers can work with a database using .NET objects and eliminate much of the need to write and maintain SQL scripts. The team can make changes to the data model, in code and let EF apply and keep track of the required database schema changes. It makes collaboration during development work simpler, as everyone can get the latest database version just by creating it from the code model.

Of course, there are development challenges too, because when you’re generating the database structure automatically through the code, it becomes harder to optimize, harder to maintain effective referential integrity. Elaborate or non-standard data models can also lead to complex, poorly performing SQL that is harder to tune.

There is little visibility into the database changes and often a lack of testing. You can’t easily track changes to individual database objects, as the data model changes, so it’s very hard to see which version changed which database objects and how, making it tricky to troubleshoot database problems.

While allowing EF to automatically update a database at application runtime is very useful for development work, it is not the right way to manage production deployments. Even the Microsoft documentation advocates a more robust approach using SQL migrations.

Using SQL Migrations for deployments

It is also possible with the EF Core CLI tools to export EF-generated migrations to SQL files and use them “as is” and use them as artifacts during an automated deployment pipeline. This tends to work well for simple changes, but things get difficult very quickly when you start getting errors and need manual interventions that are outside of EF’s control.

In her article, Hybrid Database Migrations with EF Core and Flyway, Julie Lerman proposes that developers continue to use “Code First” for updating the database in development, test environment, but for production deployments, they generate SQL scripts from the EF code model and then feed them to a more capable database deployment tool, namely Flyway.

Flyway provides a command-line tool for tracking managing and automating database migrations across a large range of RDBMSs. It migrates a database from one version to another by running the series of versioned migrations scripts and will automatically rollback any failed migration leaving database in clean state, back at previous version.

While this gives a lot more control over deployments, it does not tackle the development issues identified earlier. If you get the application code model right for a new version and only worry about the SQL when it’s time to deploy to production, you’ll catch any database problems late when there’s a lot more to ‘unpick’.

To build and maintain a Database CI process, we need Flyway SQL Migrations that can be inspected, verified and tested continuously throughout development, and then applied to the database in a controlled, automated process.

Before we discuss this new hybrid workflow in more detail, let’s review the Flyway Desktop development model briefly, because it is rather different.

Flyway Desktop development and deployment

Flyway Desktop is a graphical user-interface (GUI) for database development that allows for the combined use of the Flyway CLI with Redgate’s schema comparison tools. There is also a Flyway Desktop CLI (in preview) that allows the development workflow to be automated.

Flyway Desktop GUI has already been described in detail and A Database DevOps Workflow Using Flyway Enterprise describes in details a typical database development cycle and deployment pipeline that it can enable.

A Flyway Desktop workflow looks like this and I describe the workflow in detail, my previous article: A Real-world Implementation of Database DevOps: People, Processes, Tools.

1. Develop SQL

Devs make changes directly to a development copy of the database using SSMS, SQL scripts, an ER modelling tool, or via any means they prefer.

2. Object-level versioning

As we make database changes, Flyway Desktop captures snapshots of the current state of each database object into a schema model.

With object-level versioning, we expose how every commit affects the database. We can see exactly which, tables, stored procedures, views or functions are being altered, and how. Flyway Desktop has an integrated version control (Git) client so when can commit the updated schema model, along with the and auto-generated migration scripts, to version control. This gives us an instant audit record of which objects changed between each version of the database.

3. Generate and verify SQL migrations

From the updated schema model, Flyway Desktop will auto-generate both a versioned migration (V) script to upgrade and an versioned undo migration (U) script to roll back the changes. Flyway Desktop, via its build in schema comparison engine, has a built-in set of deployment warnings that will warn if any changes in the generated migration risk data loss or for some reason can’t be deployed.

We can then run a build validation process where process verifies that it can successfully build the new version of the database from the migration scripts. This process can incorporate a lot of useful built-in checks that will improve the quality, and increase the visibility of the proposed database changes, including code analysis of migration scripts and detailed object-level change reports.

4. Deploy migrations

The versioned upgrade (V) migrations generated in step 3 are applied to all versioned databases down the line, from Test up to and including Production, using the Flyway migration engine.

During migrations, Flyway tracks the version and status of all migrations in a special table called flyway_schema_history, giving us control and visualisation of the migration status. We can automate Flyway’s checks and reports at each stage of the CI pipeline, also incorporating drift checks to verify that the target database is still at the expected version, eliminating a lot of avoidable deployment failures due to “unexpected” changes.

An EF-Flyway hybrid model for Database CI

In this section, I’ll describe an Entity Framework-Flyway model that combines the strengths of both tools, will add value to both the development and deployment phases, and should make a CI model much more viable.

Our goal is a process where every SQL commit can be reviewed and tested for integrity, performance, and stability, before being released, in the same way as any application change. It will give a lot more visibility into coming changes and so earlier warning of problems. This encourages earlier collaboration and problem solving, so fewer bugs get discovered at deployment time, when they tend to cause problems and delays, and often ‘hacked’ workarounds that cause more trouble later.

In my first proposed “hybrid” approach, the EF-managed development process produces the required SQL Migrations, which are then converted to Flyway SQL migrations and used in a Flyway-managed release process, as follows:

While our goal is a fully automated CI process, we don’t need to get to full automation in a single leap. Step 2 could be documented as a manual process, initially, allowing the Ops team to embed Flyway in the release process first, reasonably independently. This will give them time to understand fully how it works and its advantages. The team can then tackle how to feed the result of the development process (C# migrations) automatically into a stable Flyway release process.

1. Develop C#

The .NET developers update the database model directly from their C# classes, as described previously.

2. Script and convert SQL migration

Every time the .NET developer commits and pushes C# Migrations, we have an automated process that will produce a Flyway migration file.

As developers complete changes to the entity data model there is an automated step that exports the C# migrations to SQL, using the EF Core CLI migrations script command to generate both an “Up” and “Down” SQL migration.

We then have a script conversion process, ideally automated, that adjusts the script file names to fit Flyway’s naming pattern. The EF “Up” script becomes a Flyway versioned (V) migration script and the EF “Down” script becomes a Flyway versioned undo (U) script. Both are saved to the migrations folder of the Flyway project:

3. Object-level versioning

The typical Flyway Desktop workflow is to save changes to the schema model and then generate the migration scripts that capture those changes, but here we ‘invert’ it. We already have the migration script, and then retrospectively capture the object level changes in that script to the schema model.

To do this, we run the V migration on Flyway’s “shadow” database. Flyway will record the new version in its schema history table. The __EFMigrationsHistory table will remain untouched so we’ll need a mechanism to sync it, after the Flyway migration runs. We could do this with a Flyway callback script, as I describe in my next article, Working with Flyway and Entity Framework Code First: Automation.

Flyway Desktop will auto-detect which objects were changed when we applied the migrations and saves the changes to the schema model.

This gives much greater visibility into changes, at the database object level, and the team can get potentially problematic changes reviewed early. By committing object-level changes to the repository, alongside migration scripts, it becomes much simpler to audit database changes through the version control system.

4.Flyway-managed build and release

With the changes approved the Flyway release process begins, for example with the creation of a topic branch and pull request. Flyway can perform an automated build validation step, before release to Test, to prove that it can build a working database, from the new branch version. As discussed previously, Flyway’s build validation process can incorporate a built-in set of checks and commands that allow as much as possible of the process to be inspected for quality and correctness.

Once the team have a release candidate, deployments to QA, Staging and then Production will be automated using the Flyway CLI, in the same way as they are during development.

An alternative hybrid model with Flyway-generated migrations

An alternate approach, shown below, uses a more conventional Flyway Desktop workflow. Each time a change is committed and pushed by the development team, we apply the EF-generated migrations to a Flyway managed build database, at the current production version, and capture the changes to the schema model. These changes are reviewed and, when approved, included in a pull request for release. At this point, we generate the Flyway migration scripts, perform a build validation, and then release the changes for testing.

Next steps and challenges

The next milestone is to automate each of these EF-Flyway hybrid processes with PowerShell. In the next article, I’ll present an all-in-one automated solution.

In the first approach, with the C# to Flyway script conversion, we need one script that takes the C# code, exports it, and converts it to the flyway migration format. Then the script will use the Flyway CLI to apply the migration(s) to a database and by using the preview command line of Flyway Desktop getting the schema model programmatically extracted to saved them in version control.

In the second approach the automation script will apply the EF-generated migration to a Flyway-controlled database and save the schema model. Since we’re regenerating the migration, we’ll need to consider ‘checks’ to verify that the Flyway-generated scripts produce the database that the .NET developers intended.

In either approach, an obvious challenge that we’ll need to overcome is how to manage optimisations to the SQL code. Flyway might raise a deployment wanting for a migration script, or we may need to adjust a script for performance or to correctly preserve data. How then do we reverse engineer those changes back into the EF data model?

There is no golden path here, but there are ways to try to get the best of both worlds!

Tools in this post

Flyway

DevOps for the Database

Find out more

Flyway Enterprise

Standardize Database DevOps across the organization

Find out more