Database migrations

Chapter 15 uses EnsureCreated() to quickly create a practice database. Once an app has data, adding a property to an entity does not update the existing table for you. That is when you need a migration: a schema change recorded as code that can be reviewed, committed, and applied.

Chapter 23 includes the complete example. Here is the migration file generated by the EF Core tools to add a Note column to the Todos table:

AddTodoNote.cs

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Deployment.Data.Migrations
{
    /// <inheritdoc />
    public partial class AddTodoNote : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "Note",
                table: "Todos",
                type: "TEXT",
                nullable: true);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "Note",
                table: "Todos");
        }
    }
}

Here is the corresponding complete entity file:

Features/Todos/Todo.cs

namespace TodoApi.Features.Todos;

public class Todo
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public bool Done { get; set; }
    public string? Note { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; } = null!;
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Todo> Todos { get; set; } = [];
}

Up describes the upgrade; Down describes how to undo it. This change adds a nullable column, so existing tasks have null for Note. Reversing this migration deletes the entire column and any notes already written to it; reverting the schema does not restore data.

Run the migrations in the repository

Run from the repository root:

cd samples/23-deployment
dotnet tool restore
dotnet ef migrations list
dotnet ef database update

This chapter pins the dotnet-ef version in a local tool manifest. The first update of a new todos-23.db applies both InitialCreate and AddTodoNote, creating the category and task tables and adding two fixed categories. Running it again does not create the tables twice.

The tool output shows migration names with timestamps; use the files under Data/Migrations to see their full names. The __EFMigrationsHistory table in the database records which migrations were applied successfully.

Do not regenerate migrations already in the repository

InitialCreate and AddTodoNote are already committed in the example, so run them directly. Use dotnet ef migrations add to generate a new migration only after you change the entity schema again, then review the generated files.

The tools create the context through a design-time factory, so you do not need to start the API or configure the production identity service first:

Data/TodoDbContextFactory.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace TodoApi.Data;

// dotnet ef only needs a context; authentication and the HTTP service do not need to start.
public class TodoDbContextFactory : IDesignTimeDbContextFactory<TodoDbContext>
{
    public TodoDbContext CreateDbContext(string[] args)
    {
        var config = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json").AddEnvironmentVariables().AddCommandLine(args).Build();
        var connectionString = config.GetConnectionString("Todos")
            ?? throw new InvalidOperationException("Missing ConnectionStrings:Todos configuration.");
        return new TodoDbContext(new DbContextOptionsBuilder<TodoDbContext>()
            .UseSqlite(connectionString).Options);
    }
}

Design time means the stage when you run EF commands to generate or apply migrations. During normal HTTP requests, the app still gets the context from the dependency injection container.

Confirm that old data is still there

The migration command succeeding does not prove that an upgrade preserved data. The complete test below creates the old table, inserts a task, and then applies the migration that adds the column:

Tests/MigrationTests.cs

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;

public class MigrationTests
{
    [Fact]
    public async Task Adding_note_keeps_existing_rows()
    {
        var ct = TestContext.Current.CancellationToken;
        await using var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync(ct);
        await using var db = new TodoDbContext(new DbContextOptionsBuilder<TodoDbContext>()
            .UseSqlite(connection).Options);
        var migrator = db.GetService<IMigrator>();

        await migrator.MigrateAsync("InitialCreate", ct);
        var title = "Created before upgrade";
        await db.Database.ExecuteSqlAsync(
            $"INSERT INTO Todos (Title, Done, CategoryId) VALUES ({title}, {false}, {1})", ct);

        await migrator.MigrateAsync(cancellationToken: ct);
        var todo = await db.Todos.SingleAsync(ct);
        Assert.Equal(title, todo.Title);
        Assert.Null(todo.Note);
        Assert.Equal(2, await db.Categories.CountAsync(ct));

        todo.Note = "Added after upgrade";
        await db.SaveChangesAsync(ct);
        await migrator.MigrateAsync(cancellationToken: ct);
        Assert.Single(await db.Todos.AsNoTracking().ToListAsync(ct));
        Assert.Equal("Added after upgrade", await db.Todos.Select(t => t.Note).SingleAsync(ct));
    }
}

Run from this chapter's example directory:

dotnet test --project Tests/TodoApi.Tests.csproj -c Release -p:TreatWarningsAsErrors=true

All 13 tests should pass. The migration test confirms the old task and category still exist and the new column starts as null. It then writes a note and runs the migration again to confirm that records are not duplicated and the note is not cleared.

Steps for future database changes

1. Change the entity or model configuration to describe the new schema.
2. Run dotnet ef migrations add with a name that describes the change to generate the migration.
3. Review Up, Down, and the model snapshot, then verify the migration against a test database. If a column disappears and a new one appears, the generated operations may not represent the rename you intended.
4. Commit the migration files with the application code. Back up data and schedule the upgrade before applying it in production.

The model snapshot records the model after the previous migration. The tools compare it with the current model to generate the next change. Do not manually delete the snapshot to “fix” a mismatch.

Chapter 23 applies upgrades with a separate --migrate command and starts the service only after the command succeeds. Real systems can also review SQL scripts or use a migration bundle. Deployment style and database provider affect which option is appropriate; see the official documentation on applying migrations.

What about a database created with EnsureCreated?

EnsureCreated() does not create a migration history. Applying InitialCreate directly to such a database usually fails because the tables already exist.

Chapter 23 uses a separate new database file, so it does not overwrite data from earlier chapters. If an old database contains data you need to keep, back it up first, plan a data import or migration-baseline approach, and rehearse it on a copy. Do not skip this step by deleting the old database or fabricating migration records. See the official guidance on database creation for when each approach is appropriate.

Summary

• Migrations record how the schema changes; EnsureCreated is for database creation scenarios that do not need migrations.
• After changing the model, generate and review a migration before applying it. Commit the migration and snapshot together.
• Verify upgrades with existing data; testing only an empty database is not enough.
• Adding a nullable column can preserve old rows, but operations such as dropping a column still lose data.
• Back up before a production upgrade and schedule it before the application starts using the new schema.

Back to Publishing and deployment. Continue with Advanced topics.