EF Core migrations feel straightforward until you add PostgreSQL to the picture. Enum type management, case-sensitive identifiers, schema-qualified objects, and the question of who runs dotnet ef database update in production all demand answers before your first deployment. Getting those answers wrong produces corrupted migration histories, failed releases, and schema drift that is hard to untangle.
This post covers how EF Core migrations work with Npgsql under the hood, the pitfalls that are specific to PostgreSQL, and the patterns that make migration-based deployments safe to run in CI/CD pipelines without surprises.
Before starting, make sure you have:
- A .NET 8 Web API project with
Npgsql.EntityFrameworkCore.PostgreSQL8.x installed - EF Core CLI tool:
dotnet tool install --global dotnet-ef - A working DbContext with at least one entity and an existing migration history
When you run dotnet ef migrations add MigrationName, EF Core compares your current model against the compiled model snapshot stored in Migrations/CatalogDbContextModelSnapshot.cs. The difference produces a new migration file with Up and Down methods. Npgsql translates those method calls into PostgreSQL-compatible SQL when you apply the migration.
// A generated migration file (simplified)
public partial class AddProductCategory : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<string>(
name: "category",
table: "products",
type: "character varying(64)",
maxLength: 64,
nullable: false,
defaultValue: "");
migrationBuilder.CreateIndex(
name: "ix_products_category",
table: "products",
column: "category");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropIndex(
name: "ix_products_category",
table: "products");
migrationBuilder.DropColumn(
name: "category",
table: "products");
}
}EF Core records each applied migration in __EFMigrationsHistory. Before running any migration, it checks this table and skips migrations already present. This is why the history table is the source of truth for schema state, not the migration files themselves.
Use descriptive names that make the migration's intent clear from the file listing alone. Avoid generic names like Update1 or Fix; they are meaningless in a git blame six months later.
// Good migration names
dotnet ef migrations add AddOrdersAndOrderLines
dotnet ef migrations add AddProductCategoryIndex
dotnet ef migrations add RenameCustomerEmailToContactEmail
dotnet ef migrations add SeedDefaultRolesFor large teams where multiple feature branches produce migrations in parallel, each developer should generate migrations against a local database and rebase before merging. Two migrations generated from the same snapshot base will conflict in the snapshot file. Rebasing one branch on top of the other and regenerating its migration resolves the conflict cleanly.
Several PostgreSQL behaviors catch developers off guard after working primarily with SQL Server.
1. Case sensitivity and identifier quoting
PostgreSQL folds unquoted identifiers to lowercase. EF Core, by default, generates table and column names in PascalCase (for example, "Products" and "CreatedAt"). Npgsql quotes these identifiers in the generated DDL, which means your schema works but every query requires quoted names. If a developer runs a raw query without quoting, it silently targets different names.
The fix is to establish snake_case naming in your entity configurations using ToTable and HasColumnName, or to apply a global naming convention through a model convention class.
// Global snake_case convention using EF Core 8 model-building conventions
public class SnakeCaseNamingConvention : IModelFinalizingConvention
{
public void ProcessModelFinalizing(
IConventionModelBuilder builder,
IConventionContext<IConventionModelBuilder> context)
{
foreach (var entity in builder.Metadata.GetEntityTypes())
{
entity.SetTableName(ToSnakeCase(entity.GetTableName()!));
foreach (var property in entity.GetProperties())
property.SetColumnName(ToSnakeCase(property.GetColumnName()!));
foreach (var key in entity.GetKeys())
key.SetName(ToSnakeCase(key.GetName()!));
foreach (var index in entity.GetIndexes())
index.SetDatabaseName(ToSnakeCase(index.GetDatabaseName()!));
foreach (var fk in entity.GetForeignKeys())
fk.SetConstraintName(ToSnakeCase(fk.GetConstraintName()!));
}
}
private static string ToSnakeCase(string name) =>
System.Text.RegularExpressions.Regex
.Replace(name, "(?<=[a-z0-9])([A-Z])", "_$1")
.ToLower();
}
// Register in DbContext
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
configurationBuilder.Conventions.Add(_ => new SnakeCaseNamingConvention());
}2. Enum types
Npgsql supports PostgreSQL-native enum types through MapEnum, but they require extra steps in migrations. When you rename a value or drop an enum type, PostgreSQL requires the column referencing it to be migrated first. The simpler approach for most APIs is to store enums as text using HasConversion<string>(). Reserve native enums for cases where you need database-level type checking or PostgreSQL functions that operate on them.
3. Schema-qualified tables
If your PostgreSQL database uses multiple schemas (for example, catalog and orders), set the default schema on the DbContext rather than qualifying every table name individually.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("catalog");
modelBuilder.ApplyConfigurationsFromAssembly(typeof(CatalogDbContext).Assembly);
}EF Core includes the schema in all generated DDL and in __EFMigrationsHistory. If you change the default schema after migrations exist, generate a new migration; EF Core will emit the necessary MoveTable calls.
There are two mainstream strategies for applying EF Core migrations in a pipeline. Choose based on how much schema downtime you can tolerate.
Strategy A: Apply at application startup with MigrateAsync
This approach is simple and works well for small teams and staging environments. The application applies any pending migrations when it starts, before accepting traffic.
// Program.cs - apply migrations on startup
var app = builder.Build();
using (var scope = app.Services.CreateScope())
{
var db = scope.ServiceProvider.GetRequiredService<CatalogDbContext>();
await db.Database.MigrateAsync();
}
app.UseAuthorization();
app.MapControllers();
app.Run();The downside is that if two instances start simultaneously (common in rolling deployments), both will try to acquire a migration lock. Npgsql handles this gracefully with an advisory lock, but the startup time increases proportionally to the number of pending migrations.
Strategy B: Dedicated migration step in the pipeline
For production deployments, run migrations as a separate pipeline step before the new application version is deployed. This decouples schema changes from application restarts and gives you a clear gate to stop the pipeline if the migration fails.
// A minimal migration runner console app or a separate entrypoint
// Add this to your csproj: <ItemGroup><ProjectReference Include="..\Catalog.Api\Catalog.Api.csproj" /></ItemGroup>
using Catalog.Api.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
var services = new ServiceCollection();
services.AddDbContext<CatalogDbContext>(options =>
options.UseNpgsql(Environment.GetEnvironmentVariable("ConnectionStrings__Catalog")));
var provider = services.BuildServiceProvider();
using var scope = provider.CreateScope();
var db = scope.ServiceProvider.GetRequiredService<CatalogDbContext>();
await db.Database.MigrateAsync();
Console.WriteLine("Migrations applied.");Before any production deployment, generate an idempotent SQL script and review it manually (or have a DBA review it). An idempotent script checks __EFMigrationsHistory before applying each migration, so it is safe to run multiple times without duplicating changes.
dotnet ef migrations script --idempotent --output ./deploy/migrations.sqlThe generated file contains one SQL block per migration, wrapped in an existence check against the history table. Commit this file to source control alongside your migration files so reviewers can see the exact DDL that will run in production without needing a local EF Core toolchain.
-- Example of idempotent guard generated by EF Core
IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240315120000_AddOrdersAndOrderLines')
BEGIN
CREATE TABLE orders ( ... );
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240315120000_AddOrdersAndOrderLines', '8.0.0');
END;You now have a clear picture of how EF Core migrations interact with PostgreSQL, where the common pitfalls are (case sensitivity, enum types, schema qualification), and two deployment strategies to choose from based on your team's needs. The idempotent script workflow gives you a reviewable, repeatable artifact for every deployment.
The next post in this series tackles a question that comes up on almost every API project: should you wrap your DbContext in a Repository pattern, or use it directly in your controllers? Both approaches are defensible and the right answer depends on factors that are worth examining carefully.
No comments: