Skip to content

ef-migration.md

Latest commit

 

History

History
300 lines (208 loc) · 9.3 KB

ef-migration.md

File metadata and controls

300 lines (208 loc) · 9.3 KB
Raw

How to Create an EF Core Migration (PostgreSQL)

This guide explains how to add, apply, and roll back Entity Framework Core migrations against the PostgreSQL database. The project uses EF Core 10 with the Npgsql provider.

Prerequisites

  • .NET SDK installed (dotnet --version)
  • PostgreSQL running (locally or via docker-compose up -d postgres)
  • appsettings.Development.json configured with a valid DefaultConnection string

Step 1 – Modify the Domain Entity or Add a New One

All EF-tracked entities live in src/APITemplate/Domain/Entities/. Add or change a property:

// Domain/Entities/Product.cs
public string? Sku { get; set; }   // ← new property

Step 2 – Update the Entity Configuration (if needed)

Entity type configurations live in src/APITemplate/Infrastructure/Persistence/Configurations/. Open (or create) the relevant file and express any constraints:

// Infrastructure/Persistence/Configurations/ProductConfiguration.cs
builder.Property(p => p.Sku)
    .HasMaxLength(50)
    .IsRequired(false);

builder.HasIndex(p => p.Sku)
    .IsUnique()
    .HasFilter("\"Sku\" IS NOT NULL");  // partial index — nulls not indexed

The AppDbContext (Infrastructure/Persistence/AppDbContext.cs) picks up the configuration automatically via ApplyConfigurationsFromAssembly.

Adding Migrations Per Module

The template uses a mixed approach:

  • Migrations-based modules: Notifications, FileStorage. These use Database.MigrateAsync() in their IDatabaseStartupContributor and ship EF migrations under Persistence/Migrations/. Each has a *DbContextDesignTimeFactory that enables dotnet ef to connect at migration-time via DesignTimeConnectionStringResolver.
  • EnsureCreated modules: all others call EnsureCreatedAndTablesAsync() which creates tables on first run but does not alter existing schemas. Fine for greenfield/template use; switch to migrations when schema evolution matters.

Per-module migration command:

dotnet ef migrations add <MigrationName> \
    --project src/Modules/<Module>/<Module>.csproj \
    --startup-project src/APITemplate/Api \
    --context <Module>DbContext \
    --output-dir Persistence/Migrations

Example (FileStorage):

dotnet ef migrations add AddSomething \
    --project src/Modules/FileStorage \
    --startup-project src/APITemplate/Api \
    --context FileStorageDbContext \
    --output-dir Persistence/Migrations

Step 3 – Add the Migration (legacy single-context flow)

Run from the solution root (where APITemplate.slnx lives) or from the project folder.
The --project flag points to the project that owns AppDbContext.

dotnet ef migrations add <MigrationName> \
    --project src/APITemplate \
    --output-dir Migrations

Example:

dotnet ef migrations add AddSkuToProduct \
    --project src/APITemplate \
    --output-dir Migrations

This generates two files in src/APITemplate/Migrations/:

File Purpose
<timestamp>_AddSkuToProduct.cs Up() / Down() migration logic
<timestamp>_AddSkuToProduct.Designer.cs EF metadata snapshot (do not edit)

It also updates AppDbContextModelSnapshot.cs.

Step 4 – Review the Generated Migration

Always open the generated migration and verify EF inferred the right SQL. For the Sku example:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "Sku",
        table: "Products",
        type: "character varying(50)",
        maxLength: 50,
        nullable: true);

    migrationBuilder.CreateIndex(
        name: "IX_Products_Sku",
        table: "Products",
        column: "Sku",
        unique: true,
        filter: "\"Sku\" IS NOT NULL");
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropIndex(name: "IX_Products_Sku", table: "Products");
    migrationBuilder.DropColumn(name: "Sku", table: "Products");
}

Workflow: Migrations That Contain Raw SQL

Some migrations don't change the EF model at all — they only add database-level constructs that EF can't express (raw indexes using unsupported annotations, extensions, stored procedures, etc.). The correct workflow is:

1. Always generate first, edit second

Never write a migration file from scratch. Always let EF Core generate it:

dotnet ef migrations add <MigrationName> \
    --project src/Modules/<Module> \
    --startup-project src/APITemplate/Api \
    --context <Module>DbContext

If the model has no EF-tracked changes, EF generates an empty migration with the correct .Designer.cs and updates the snapshot. That is the right starting point.

2. Never edit the .Designer.cs

The .Designer.cs contains the embedded model snapshot at that migration's point in time. It is generated by EF and must stay that way. Editing it by hand risks breaking migration history and dotnet ef migrations script output.

3. If dotnet ef migrations add fails — fix the root cause

A failure during generation usually means a library or annotation conflict. Common example in this project: EFCore.ComplexIndexes.PostgreSQL intercepts the migration differ and rejects annotations it doesn't know (e.g. Npgsql:TsVectorConfig from IsTsVectorExpressionIndex).

The fix is to remove the conflicting EF configuration and implement the construct as raw SQL instead. Do not try to work around the error by patching tool invocations.

Typical resolution:

  1. Remove the unsupported EF configuration from the entity type configuration file.
  2. Write an equivalent SQL file (see below).
  3. Re-run dotnet ef migrations add — it should now succeed.

4. Add raw SQL via embedded resources

Once you have a generated (possibly empty) migration file, add raw SQL calls inside it:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(ModuleSqlResource.Load("my_index_v1_up.sql"));
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(ModuleSqlResource.Load("my_index_v1_down.sql"));
}

SQL file naming convention: {name}_v{version}_{up|down}.sql. Always provide a _down.sql — even if it is a no-op comment, so that Down() is never left broken.

5. Order matters in Down()

Down() must reverse Up() in the opposite order. If Up() is:

pg_trgm → index_a → index_b

then Down() must be:

index_b → index_a → pg_trgm

Step 5 – Embed SQL Resources (Optional)

If the migration needs to create or drop a PostgreSQL function / stored procedure, embed the SQL as a resource file and load it with SqlResource.Load():

  1. Create src/APITemplate/Infrastructure/Database/Functions/my_function.sql
  2. Set Build Action → Embedded Resource in your IDE (or add to .csproj):
<ItemGroup>
  <EmbeddedResource Include="Infrastructure\Database\Functions\my_function.sql" />
</ItemGroup>
  1. Call it from the migration:
migrationBuilder.Sql(SqlResource.Load("my_function.sql"));
  1. Drop it in Down():
migrationBuilder.Sql("DROP FUNCTION IF EXISTS my_function(UUID);");

See Migrations/20260302153430_AddCategory.cs for a real example.

Step 6 – Apply the Migration

Migrations are applied automatically at application startup via UseDatabaseAsync() in Extensions/ApplicationBuilderExtensions.cs. You can also apply them manually:

dotnet ef database update \
    --project src/APITemplate

To apply up to a specific migration:

dotnet ef database update AddSkuToProduct \
    --project src/APITemplate

Step 7 – Roll Back a Migration

To undo the last migration (database must be reverted first):

# Revert the database to the previous migration
dotnet ef database update <PreviousMigrationName> \
    --project src/APITemplate

# Remove the pending migration files
dotnet ef migrations remove \
    --project src/APITemplate

Step 8 – Generate a SQL Script (CI / Production Deployments)

For environments where running EF tooling directly is not appropriate, generate an idempotent SQL script:

dotnet ef migrations script --idempotent \
    --project src/APITemplate \
    --output migration.sql

Apply migration.sql through your preferred DB deployment pipeline.

Checklist for a New Entity

  • Create entity class in Domain/Entities/
  • Create configuration class in Infrastructure/Persistence/Configurations/
  • Add DbSet<T> to AppDbContext
  • Register the repository in ServiceCollectionExtensions.AddPersistence()
  • Run dotnet ef migrations add <Name>
  • Review the generated migration file
  • Run dotnet ef database update (or let startup auto-migrate)

Key Files Reference

File Purpose
Domain/Entities/ EF-tracked entity classes
Infrastructure/Persistence/AppDbContext.cs EF DbContext with DbSets
Infrastructure/Persistence/Configurations/ Fluent API table/column configuration
Migrations/ Auto-generated migration history
Infrastructure/Database/SqlResource.cs Helper to load embedded .sql files
Infrastructure/Database/Functions/ Embedded SQL scripts
Extensions/ApplicationBuilderExtensions.cs UseDatabaseAsync() — auto-migrate on startup