Efficient Database Migrations with .NET Core, Entity Framework, and PostgreSQL

Introduction

In modern software development, managing database schema changes effectively is crucial. A smooth database migration process helps maintain consistency across environments (development, staging, production) and enables teams to iterate quickly. In this article, we’ll explore how to streamline database migrations in a .NET Core application using Entity Framework Core (EF Core) with PostgreSQL.

Why Migrations Matter?

Like Flyway in Java, EF Core migrations provide a way to evolve the database schema without losing data. It allows you to apply incremental changes to your schema while keeping track of the historical state of the database.

Persistence Layer

In this article, we will be using PostgreSQL as our database. You can maintain your database in any database management system. For a convenient deployment option, consider cloud-based solutions like Rapidapp, which offers managed PostgreSQL databases, simplifying setup and maintenance.

tip

Create a free database in Rapidapp in seconds here

Step-by-Step Implementation

We’ll be working with a simple .NET Core project using EF Core to interact with a PostgreSQL database. Below are the steps to get started.

Project Initialization

We will use .NET CLI to create a new .NET Core project. Let's start by creating a new project:

dotnet new webapi -n net-migration

Above command will initialize a new .NET Core Web API project named net-migration.

Installing Dependencies

We need to install the following packages:

• Npgsql.EntityFrameworkCore.PostgreSQL: The PostgreSQL provider for Entity Framework Core.
• Microsoft.EntityFrameworkCore.Design: The EF Core design package for migrations.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

Datasource Configuration

Configure PostgreSQL Connection in appsettings.json, add the PostgreSQL connection string:

{
  "ConnectionStrings": {
    "PostgresConnection": "Host=<host>;Port=<port>;Database=<database>;Username=<user>;Password=<password>;SSL Mode=require;Trust Server Certificate=true"
  }
}

If you are using Rapidapp for PostgreSQL, you can find the connection string in the Rapidapp console and it supports SSL by default.

If you are using a local PostgreSQL instance, you can replace the placeholders with your PostgreSQL credentials and you may need to disable SSL if you haven't configured it in your database server.

Creating the Database Context

Create a ApplicationDbContext class that inherits from DbContext, configure it to use PostgreSQL.

ApplicationDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace net_migration;

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}

This is a generic database context configuration and the connection parameters will be passed in Program.cs soon.

Creating the Product Model

Create a Product class to represent the product entity in the database:

Product.cs

namespace net_migration;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

Registering the Database Context

In the Program.cs file, register the ApplicationDbContext with the PostgreSQL connection string as follows.

Program.cs

...
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));
...

Program.cs contains a couple of more boilerplate for the controllers, swagger, etc. However, to keep it simple, we are only showing the relevant part about registering the database context. The connection string is fetched from appsettings.json and passed to the UseNpgsql method.

Running Migrations

The basic skeleton of the project is ready. Now, let's create the database schema by running the migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

The InitialCreate migration will create the Products table in the database. The database update command will apply the migration to the database. If you haven't ef sub command for .NET CLI tool, you can install dotnet-ef extension as follows.

dotnet tool install --global dotnet-ef

The migration history is stored in the __EFMigrationsHistory table, which keeps track of the applied migrations. Assume that you added a new field called Description field to your Product entity. You can create a new migration and update the database as follows:

Product.cs

namespace net_migration;

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Stock { get; set; }

    // New column
    public string Description { get; set; } // This is the new column you're adding.
}

dotnet ef migrations add AddDescriptionColumn
dotnet ef database update

You can see it will add a new column to Products table. You can also see the history in the __EFMigrationsHistory table.

Automate Migration on Startup

You can configure your application to automatically apply migrations at startup as follows.

using (var scope = app.ApplicationServices.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    dbContext.Database.Migrate();
}

Conclusion

Entity Framework Core makes database migrations straightforward in a .NET Core project. By managing schema changes through migrations, you ensure that your database remains in sync with your application's models across all environments. PostgreSQL, when combined with EF Core, provides a robust, scalable solution for modern web applications.

tip

You can find the complete source code for this project on GitHub.