Database Migrations in Entity Framework

Contents:

Overview
How to make a migration via an example
How to remove the last migration
How to rollback the last migration applied to a database

Overview

Data models change as features get implemented.

The migrations feature in EF Core provides a way to incrementally update the database schema to keep it in sync with the application's data model while preserving existing data in the database.

The following is borrowed from the Entity Framework documentation.

At a high level, migrations function in the following way:

When a data model change is introduced, the developer uses EF Core tools to add a corresponding migration describing the updates necessary to keep the database schema in sync. EF Core compares the current model against a snapshot of the old model to determine the differences, and generates migration source files; the files can be tracked in your project's source control like any other source file.

Once a new migration has been generated, it can be applied to a database in various ways. EF Core records all applied migrations in a special history table, allowing it to know which migrations have been applied and which haven't.

How to make a migration via an example

Lets walk through an example of a simple migration

Task: Add a new column named NEW_USER_COLUMN to the APP_USERS table in the Reporting Hub.

High Level Overall Steps:

add new column name and definition to the model
Create and name the migration
Manually verify the migration file was generated in the "ReportsPortal.Database" project
Apply the pending migration
SRP Documentation: After the migration is applied, update the data dictionary

Technical Steps:

Add new column name and definition to the model
- Open the file "ReportsPortal.Database/Models/AppUser.cs"
- Add the following line to the AppUser class
```
[Column("NEW_USER_COLUMN")]
public string NEW_USER_COLUMN { get; set; }
```
It is best practice to manually verify migrations and see if there are any pending migrations
- Open a terminal window and navigate to the "ReportsPortal.Database" project
- Run the dotnet ef migrations list command
```
dotnet ef migrations list
```
At the moment there should be no pending migrations
- The output should look like the following
```
Build started...
Build succeeded.
...
20210601190000_AddNewUserColumnToAppUsersTable
```
Create and name the migration
- Open a terminal window and navigate to the "ReportsPortal.Database" project
- Run the dotnet ef migrations add command with a descriptive name for the migration
  
  Note how previous migrations were named and follow the same convention
  
  !!! note
  
  There might be cases where you need to add several changes or changes more than one table in a single migration. In that case, you can broaden the migration name a bit more: eg. AddEntityTypeCodeLookupFeatures. The most important thing to note here though is that if you are going to apply several changes at once that the changes do not span more than one ticket/branch. If they do, then you should create separate migrations for each ticket/branch.
```
dotnet ef migrations add AddNewUserColumnToAppUsersTable
```
  - The output should look like the following
```
Build started...
Build succeeded.
...
To undo this action, use 'ef migrations remove'
```
We can verify the migration was added
- Open a terminal window and navigate to the "ReportsPortal.Database" project
- Run the dotnet ef migrations list command
```
dotnet ef migrations list
```
- The output should look like the following
```
Build started...
Build succeeded.
...
20210601190000_AddNewUserColumnToAppUsersTable (Pending)
```

We can verify the migration file was generated in the "ReportsPortal.Database" project

Open the file "ReportsPortal.Database/Migrations/20210601190000_AddNewUserColumnToAppUsersTable.cs"
The file should contain the following

using Microsoft.EntityFrameworkCore.Migrations;

namespace ReportsPortal.Database.Migrations
{
    public partial class AddNewUserColumnToAppUsersTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "NEW_USER_COLUMN",
                table: "APP_USERS",
                type: "nvarchar(max)",
                nullable: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "NEW_USER_COLUMN",
                table: "APP_USERS");
        }
    }
}

Apply the pending migration
- Open a terminal window and navigate to the "ReportsPortal.Database" project
- Run the dotnet ef database update command
```
dotnet ef database update
```
- The output should look like the following
```
Build started...
Build succeeded.
...
Done.
```
Verify the migration was applied
- Open a terminal window and navigate to the "ReportsPortal.Database" project
- Run the dotnet ef migrations list command
```
dotnet ef migrations list
```
- The output should look like the following
```
Build started...
Build succeeded.
...
20210601190000_AddNewUserColumnToAppUsersTable
```
Note the migration is no longer pending
SRP Documentation: After the migration is applied, update the SRP Reporting Hub - Data Dictionary. This link is not public but can be shared with you if you need to update the data dictionary.

How to remove the last migration

See official Microsoft documentation for migration removal: https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=dotnet-core-cli#remove-a-migration

How to rollback the last migration applied to a database

If you need to undo the last migration applied to a database, follow these steps:

Check the current migrations:
- Open a terminal window and navigate to the database project. e.g. "ReportsPortal.Database"
- Run dotnet ef migrations list to see the list of migrations.
```
dotnet ef migrations list
```
Example output:
```
Build started...
Build succeeded.
...
20210601190000_AddNewUserColumnToAppUsersTable
20210602100000_AddAnotherColumn (Pending)
```
Identify the last migration in the list.
Rollback the database to the previous migration:
- Use the dotnet ef database update command, specifying the migration before the last one:
```
dotnet ef database update 20210601190000_AddNewUserColumnToAppUsersTable
```
This will revert the database changes introduced by the last migration.
Remove the last migration from the project:
- Run the dotnet ef migrations remove command to delete the migration files and update the snapshot:
```
dotnet ef migrations remove
```
Example output:
```
Build started...
Build succeeded.
Removed migration '20210602100000_AddAnotherColumn'.
```
Verify the migration removal:
- Run the dotnet ef migrations list command again to confirm the last migration is no longer present:
```
dotnet ef migrations list
```
Example output:
```
Build started...
Build succeeded.
...
20210601190000_AddNewUserColumnToAppUsersTable
```
The last migration has been successfully removed.