Page 12 - MSDN Magazine, October 2019

Data Points JULIE LERMAN
Hybrid Database Migrations with EF Core and Flyway
During development, it’s common to use EF Core’s migration commandstonotonlycreatemigrationfilesbuttoalsoexecute migrations directly on your development database. You can apply the migrations with the PowerShell update-database command or the command-line interface (CLI): dotnet ef database update. What’s nice about letting EF Core update the database this way is that you have total control over when the database is migrated to a new schema. And I love having control over things. I can trigger the database migration, then open up the database to verify I got what I expected. But this can only happen during design time. It’s an explicit action to perform.
Another path for triggering EF Core to migrate the database using the migration files is to call the Database.Migrate or the Database.EnsureCreated method somewhere in your code. For example, in the first installation of my recent article series, “EF Core in a Docker Containerized App” (msdn.com/magazine/mt833405), I worked out a way to trigger EF Core to migrate my database at application startup. This is handy during development (and for demos, such as the article’s code) if you don’t want to manually use commands to update your database—especially during early stages of development when your model is evolving rapidly.
But applying migrations at application runtime has some critical drawbacks. A major one is that you’re depending on your software to be responsible for the database. Is that what you want? Is that what your DBA wants? Even if you’ve added custom migration files to handle administrative tasks, it’s probably not going to make your DBA happy. What if you have multiple APIs that use the same database? Which one should be responsible for the database?
But there’s also a deeper technical problem you’re inviting by call- ing Migrate from within the software. In that earlier article, I was publishing my API into a Docker image. What if I were to start up several containers to serve up that API? Each container would be hitting that Migrate method on startup. While unlikely, it’s possible that multiple servers might call Migrate at the same time and try to perform a migration on the database concurrently. This could create unexpected (and detrimental) side effects.
So, when it’s time to move from development and QA to produc- tion, letting EF Core update the database for you is generally not the best approach. Even the EF Core documentation (bit.ly/2XVQEHy) makes a note about applying migrations at runtime: “While it’s great
for apps with a local database, most applications will require more robustdeploymentstrategylikegeneratingSQLscripts.”
EF Core migrations do let you create SQL scripts, even idempotent scripts (that avoid applying schema modifications that have already been applied). An app like Octopus Deploy can use deployment steps to read EF Core migrations, generate the SQL scripts and then execute them. Or you can generate the SQL yourself using EF Core migration commands (PowerShell: Script-Migration or CLI: dotnet ef migrations script). You (or the database guru in your org) can tweak these scripts to add more database knowledge into them if needed. Either way, this gives you the ability to then feed them to some other application that’s designed to manage your database updates, rather than you having to manage and execute the SQL manually. Redgate’s SQL Change Automation (formerly called Ready Roll), for exam- ple, is an ace at managing SQL Server updates with your scripts. There are also tools that aren’t just for SQL Server, such as Flyway (flywaydb.org), now part of the Redgate stable, or Liquibase (liquibase.org).
During development, it’s common to use EF Core’s migration commands to not only create migration files but to also execute migrations directly on your development database.
Recently I used a combination of EF Core migrations and Flyway to demonstrate using Docker containers to apply database migra- tions. This alternative allows integrating the evolution of the database schema into the same automated Continuous Integration/Contin- uous Deployment (CI/CD) process you use to deploy new versions of your application code. You can see a video of this from my recent NDC Oslo session at youtu.be/BL3TfA0UEr8. Having the database servers hosted in a Docker container for developer and tester machines, as well as in an automated CI/CD pipeline, eliminates the need to install the server anywhere. And I used a separate Docker container for the short period needed to run Flyway, letting it apply any migrations to the database and then shut itself down.
Code download available at msdn.com/magazine/1019magcode.
8 msdn magazine