9 Handling database migrations

 

This chapter covers

  • The different ways you create commands to update a database’s structure
  • The three starting points from which you create database structure changes
  • How to detect and fix database structure changes that would lose data
  • How the characteristics of your application affect the way you apply a database change

This chapter covers the different ways of changing the structure of a database, referred to as migrating a database. The structure of the database is called the database schema—this consists of the tables, columns, constraints, and so on that make up a database. Creating and updating a database schema can seem simple because EF Core provides a method called Migrate to do it all for you: you create your entity classes and add a bit of configuration, and EF Core builds you a nice, shiny database.

The problem is that EF Core’s Migrate method hides a whole series of database migration issues that aren’t immediately obvious. For instance, renaming a property in an entity class will, by default, cause that property’s database column to be deleted, along with any data it had! So, in this chapter, as well as detailing how to build and apply database migrations, I cover the key issues that you must consider when updating a database. No one wants to be the person that breaks your ‘live’ database.

9.1     How this chapter is organized

9.2     Understanding the complexities of changing your application’s database

9.2.1     A view of what databases need updating

9.2.2     Handling a migration that can lose data

9.3     Part 1: Introduction to the three approaches for creating a migration

9.4     Creating a migration using EF Core’s add migration command

9.4.1     Requirements before you can run any EF Core migration command

9.4.2     How to run the add migration command

9.4.3     Seeding your database via an EF Core migration

9.4.4     Handling EF Core migrations with multiple developers

9.4.5     Using a custom migration table to allow multiple DbContexts to one database

9.5     Editing an EF Core migration to handle complex situations

9.5.1     Adding/removing MigrationBuilder methods inside the migration class

9.5.2     Adding SQL commands to a migration

9.5.3     Adding your own custom migration commands

9.5.4     Altering a migration to work for multiple database types

9.6     Using SQL scripts to build migrations

9.6.1     Using a SQL database comparison tools to produce migration

9.6.2     Hand-coding a SQL change scripts to migrate the database

9.6.3     Using a tool to check you SQL change scripts matches EF Core’s Model of the database

9.7     Using EF Core’s reverse engineering tool

9.7.1     How to run EF Core’s reverse engineering command