# EF Core Migrations: A Detailed Guide > In this newsletter, we'll break down the essentials of EF Migrations. We'll explore creating migrations, SQL scripts, applying migrations, migration tooling, and more. Published: 2024-05-18. Author: Milan Jovanović. Canonical: https://milanjovanovic.tech/blog/efcore-migrations-a-detailed-guide EF Core migrations version your database schema in code instead of hand-written SQL scripts. Each migration captures one change to the data model and contains `Up` and `Down` methods, so you can apply or revert it. You can apply migrations with SQL scripts, the CLI, a migration bundle, or from code at startup. Managing database schemas as your applications grow can quickly become a headache. Manual changes are error-prone and time-consuming. This can easily lead to inconsistencies between development and production environments. I've seen these issues firsthand on countless projects, and it's not pretty. How can we do better? Enter Entity Framework (EF) Migrations, a powerful tool that lets you version your database schemas. Imagine this: Instead of writing SQL scripts, you define your changes in code. Need to add a column? Rename a table? No problem - EF Migrations has you covered. It tracks every modification to the data model. You can review, test, and apply changes confidently, even across different environments. In this newsletter, we'll break down the essentials of EF Migrations: - **Creating Migrations**: Defining and generating migrations that capture your schema changes. - **Migration SQL Scripts**: Understanding the SQL generated by your migrations and how to use it. - **Applying Migrations**: Different ways to apply migrations to your database. - **Migration Tools**: Exploring additional tools and frameworks for managing database migration. - **EF Migration Best Practices:**: I'll share my recommendations from using EF for years. We have many examples to cover, so let's dive in. ## Creating Migrations If you're completely new to EF migrations, I recommend checking out the [EF migrations docs](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations) to grasp the fundamentals. Moving forward, I'll assume you have some prior knowledge of EF Core. We'll need an entity and a database context before we can create migrations with EF. Let's define a simple `Product` entity: ```csharp public class Product { public int Id { get; set; } public string Name { get; set; } = string.Empty; public string? Description { get; set; } public decimal Price { get; set; } } ``` We will also need a `DbContext` implementation, so let's define the `AppDbContext` class. In the `OnModelCreating` method, we're going to configure the `Product` entity. ```csharp public class AppDbContext : DbContext { public AppDbContext(DbContextOptions options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity(builder => { builder.ToTable("Products", tableBuilder => { tableBuilder.HasCheckConstraint( "CK_Price_NotNegative", sql: $"{nameof(Product.Price)} > 0"); }); builder.HasKey(p => p.Id); builder.Property(p => p.Name).HasMaxLength(100); builder.Property(p => p.Description).HasMaxLength(1000); builder.Property(p => p.Price).HasPrecision(18, 2); builder.HasIndex(p => p.Name).IsUnique(); }); } } ``` Let's break down a few of the methods we're using: - `ToTable` - Configures the table name for the specific entity. It also allows us to provide `TableBuilder` delegate. We can use it to configure a check constraint using `HasCheckConstraint`. - `HasKey` - Configures the table's primary key. EF will also pick up the `Id` property by convention, so this step is optional. - `HasProperty` - Represents the entry point for configuring individual properties of the entity. - `HasIndex` - Defines an index on the specified property (or properties). We can also declare that the index should be unique by calling `IsUnique`. We're now ready to create our first migration. I'm going to use the PowerShell syntax: ``` Add-Migration Create_Database ``` This will create the first database migration called `Create_Database`. The migration will apply the configuration we defined in the `OnModelCreating` method. It contains the `Up` and `Down` methods, allowing us to apply or revert changes to the database. Note that some operations are destructive (like removing a column) and can't be easily reverted. It's up to you to examine the generated migration and prevent any possible data loss. ```csharp using Microsoft.EntityFrameworkCore.Migrations; using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata; public partial class Create_Database : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.CreateTable( name: "Products", columns: table => new { Id = table.Column(type: "integer", nullable: false) .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn), Name = table.Column(type: "character varying(100)", maxLength: 100, nullable: false), Description = table.Column(type: "character varying(1000)", maxLength: 1000, nullable: true), Price = table.Column(type: "numeric(18,2)", precision: 18, scale: 2, nullable: false) }, constraints: table => { table.PrimaryKey("PK_Products", x => x.Id); table.CheckConstraint("CK_Price_NotNegative", "Price > 0"); }); migrationBuilder.CreateIndex( name: "IX_Products_Name", table: "Products", column: "Name", unique: true); } protected override void Down(MigrationBuilder migrationBuilder) { migrationBuilder.DropTable( name: "Products"); } } ``` ## Customizing Migrations We can also modify the migration files if we need to apply some custom changes. A notable example is renaming a column. Let's say we rename the `Description` property to `ShortDescription`. In some EF versions, this would result in the following migration: ```csharp migrationBuilder.DropColumn( name: "Description", table: "Customers"); migrationBuilder.AddColumn( name: "ShortDescription", table: "Products", nullable: true); ``` What's the problem here? By calling `DropColumn` first, we will remove the column from the database and lose valuable data. What we actually want to do is rename the existing column. So, we can modify the migration file to use the `RenameColumn` method: ```csharp migrationBuilder.RenameColumn( name: "Description", table: "Products", newName: "ShortDescription"); ``` Another example is executing custom SQL commands from your migrations. Custom SQL commands are helpful when we can't express something through the EF fluent API. I've used it in the past to migrate data from one column to another or define a complex index. ```csharp public partial class Update_Products : Migration { protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.Sql(""); } protected override void Down(MigrationBuilder migrationBuilder) { // You are also responsible for reverthing any changes. } } ``` ## Migration SQL Scripts You can use the `Script-Migration` command to generate SQL scripts from your migrations. This is useful for reviewing changes before applying them to the database. SQL scripts allow us to execute migrations in environments without direct access to the EF tooling. Remember, you are responsible for preventing any data loss when executing EF migrations. Review the migrations carefully before applying them to the database. Here are a few ways you can execute the `Script-Migration` command: ``` Script-Migration Script-Migration Script-Migration ``` The `` argument should be the name of the last migration applied to the database. It's your responsibility to apply the script appropriately, and only to databases in the correct migration state. Here's what the SQL script for the `Create_Database` migration looks like: ```sql CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" ( "MigrationId" character varying(150) NOT NULL, "ProductVersion" character varying(32) NOT NULL, CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId") ); START TRANSACTION; CREATE TABLE "Products" ( "Id" integer GENERATED BY DEFAULT AS IDENTITY, "Name" character varying(100) NOT NULL, "Description" character varying(1000), "Price" numeric(18,2) NOT NULL, CONSTRAINT "PK_Products" PRIMARY KEY ("Id"), CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0) ); CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name"); INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion") VALUES ('20240516095344_Create_Database', '8.0.5'); COMMIT; ``` You can also specify an `-Idempotent` argument to the `Script-Migration` command. The `Script-Migration` command will generate SQL scripts that only apply migrations that haven't been applied already. This is useful if you're not sure what the last migration applied to the database. ``` Script-Migration -Idempotent ``` Here's what the idempotent SQL script looks like: ```sql CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" ( "MigrationId" character varying(150) NOT NULL, "ProductVersion" character varying(32) NOT NULL, CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId") ); START TRANSACTION; DO $EF$ BEGIN IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN CREATE TABLE "Products" ( "Id" integer GENERATED BY DEFAULT AS IDENTITY, "Name" character varying(100) NOT NULL, "Description" character varying(1000), "Price" numeric(18,2) NOT NULL, CONSTRAINT "PK_Products" PRIMARY KEY ("Id"), CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0) ); END IF; END $EF$; DO $EF$ BEGIN IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name"); END IF; END $EF$; DO $EF$ BEGIN IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion") VALUES ('20240516095344_Create_Database', '8.0.5'); END IF; END $EF$; COMMIT; ``` ## Applying Migrations How do we apply EF migrations to the database? We have a few options: - SQL scripts - Command-line tools - Apply migrations through code - Migration bundles We discussed SQL scripts in the previous section, so I won't mention them again. ### Command-line Tools The most common approach to applying database migrations is using the CLI. You can use either the `dotnet ef` tool or the PowerShell commands. For example, you can execute the `Update-Database` command from PowerShell to apply any pending migrations. ``` Update-Database -Migration -Connection ``` Here are the documentation links if you want to learn more: - [EF Core CLI documentation](https://learn.microsoft.com/en-us/ef/core/cli/) - [EF Core PowerShell documentation](https://learn.microsoft.com/en-us/ef/core/cli/powershell) ### Applying Migrations through Code Here's a helper method for applying database migrations. It uses an `IServiceScope` to resolve a `DbContext` instance and uses it to call the `Migrate` method. ```csharp public static void ApplyMigration(IServiceScope scope) where TDbContext : DbContext { using TDbContext context = scope.ServiceProvider .GetRequiredService(); context.Database.Migrate(); } ``` You can apply migrations when the application is starting. I **don't recommend** using this approach for production environments. Migration can fail, concurrency issues exist, and rolling back migrations is challenging. However, this approach can be helpful in local development and when scaffolding databases for integration testing. ```csharp var builder = WebApplication.CreateBuilder(args); var app = builder.Build(); if (app.Environment.IsDevelopment()) { using IServiceScope scope = app.ApplicationServices.CreateScope(); ApplyMigration(scope); } app.Run(); ``` ### Migration Bundles Migration bundles are executable files that you can use to apply database migrations. They're self-contained and can be executed from CI pipelines. You can use the `Bundle-Migration` command to create a migration bundle: ``` Bundle-Migration -Connection ``` This will create an `efbundle.exe` file that we can run to apply any pending database migrations. I recommend reading the [migration bundles documentation](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying#bundles) to learn more. ## Additional Database Migration Tools What can you do if you don't want to use EF Core migrations? I wanted to mention some additional tools you can use for database schema versioning and running migrations: - [FluentMigrator](https://github.com/fluentmigrator/fluentmigrator): A migration framework for .NET with a fluent API for defining migrations. - [DbUp](https://github.com/DbUp/DbUp): A lightweight library for applying SQL scripts to databases. - [Grate](https://erikbra.github.io/grate/): An automated database deployment (change management) system that relies on SQL scripts. - [Flyway](https://flywaydb.org/): An open-source database migration tool that simplifies the management and versioning of database schema changes. We won't do a deep dive on these tools, but I recommend you check out their documentation. ## EF Core Migrations Best Practices I want to wrap up this issue with a few tips from my experience of working with EF Core migrations over the years: - **Use meaningful migration names**: Don't name migrations with dates or generic descriptions. Use clear, descriptive names that indicate the purpose of the migration. Good examples: `AddProductsTable`, `RenameDescriptionToShortDescription`. This makes it much easier to understand your migration history and find specific changes. - **Keep migrations small and focused**: Avoid creating massive migrations containing multiple unrelated changes. Smaller migrations are easier to review, test, and troubleshoot if something goes wrong. Aim for one migration per feature or logical change. - **Test migrations thoroughly**: Before applying migrations to production, test them in a development or staging environment. Development and staging environments should mirror your production setup as closely as possible. This will help catch any unexpected issues or data loss risks before they affect real users. - **Beware of destructive changes**: Some operations, like dropping columns or tables, can lead to irreversible data loss. Carefully consider the consequences before including such changes in migrations. Provide a way to migrate data or create a backup plan. - **Avoid merge conflicts**: Solving merge conflicts for EF migration snapshots can be a real headache. Be mindful of this when working in a team that creates many database migrations. It's recommended to always be up-to-date with the latest migration before creating a new one. This should minimize the chance of creating merge conflicts. My preferred approach to applying migrations is using SQL scripts. Depending on the project scope and complexity, we could do this manually or through a tool that automates the process. This allows me to review the migration and identify any potential problems. I hope this was helpful! Thanks for reading, and I'll see you next week. --- ## Frequently asked questions ### What are EF Core migrations? EF Core migrations let you version your database schema in code instead of writing SQL scripts by hand. Each migration captures a change to the data model and contains Up and Down methods, so you can apply or revert changes consistently across environments. ### How do you generate a SQL script from EF Core migrations? Use the Script-Migration command, optionally passing from and to migration names. Adding the -Idempotent argument produces a script that only applies migrations the database has not seen yet, which helps when you are unsure of its current migration state. ### Should you apply EF Core migrations on application startup? Not in production. Migrations can fail, concurrent instances can race each other, and rolling back is challenging. Applying migrations in code at startup is fine for local development and for scaffolding databases in integration tests. ### How do you rename a column in an EF Core migration without losing data? Check the generated migration. Some EF versions scaffold a rename as DropColumn followed by AddColumn, which destroys the data in that column. Edit the migration to call RenameColumn instead, which renames the existing column and preserves its data. ### What are EF Core migration bundles? Migration bundles are self-contained executable files, created with the Bundle-Migration command, that apply any pending migrations to the database. Because they need no EF tooling on the target machine, they work well in CI pipelines. ### What are the best practices for EF Core migrations? Use meaningful migration names, keep each migration small and focused, test migrations in an environment mirroring production, and watch for destructive operations like dropped columns. In teams, stay current with the latest migration before adding a new one to avoid snapshot merge conflicts.