PostgreSQL with Entity Framework Core

I needed a way to switch a new ASP.NET Core Web App (MVC) create with Authentication Type of Individual Accounts over to use PostgreSQL. Microsoft ship the template to use MS SQL, I mean why would they not push their own product 😂

Inital Steps

  1. Create the Solution and Project, I normally just create using VS 2022 but it can be done in the CLI
1
2
dotnet tool install --global dotnet-aspnet-codegenerator
dotnet new mvc --auth Individual -o MyPostgresWebApp
  1. Install EntityFrameworkCore for PostgreSQL
1
2
3
4
cd MyPostgresWebApp

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL     # Add the PostgreSQL EF Core provider, at the time I used `v9.0.4`
dotnet add package Microsoft.EntityFrameworkCore.Tools       # Add the EF Core tools package for managing migrations is not installed already, the template for me came with `v9.0.8`
  1. Update Program.cs to configure the application to use PostgreSQL instead of SQL Server.
1
2
3
4
5
6
7
// before
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(connectionString));

// after
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));
  1. Update DbContext to set the schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
// Data/ApplicationDbContext.cs
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

namespace MyPostgresWebApp.Data
{
    public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);            
            builder.HasDefaultSchema("my_schema"); // Specify the database schema for all tables
        }
    }
}
  1. Update the connection string in appsettings.json, generally I delete the ConnectionStrings:DefaultConnection node from appsettings.json and just add my local connection string to appsettings.Development.json as I inject the production connection string when deploying, normally with Github Actions because its amazing and TeamCity & Octpus Deploy are just too complicated for my brain ❤️ 
1
2
3
4
5
6
7
8
9
// before
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-PSC.Web-7fbea9ba-ba79-404c-b841-62e54b58246f;Trusted_Connection=True;MultipleActiveResultSets=true"
  },

// after
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=5432;Database=my_db;UserId=my_webapp_user;Password=00000000-0000-0000-0000-000000000001;"
  },
  1. Spin up a docker postgresql instance, cool kids use docker right? Generally its best practice to follow principle of least privlage, so my_webapp_user should not be the admin user as Im creating it here, this doesnt matter too much for local but in productionm systems you should have a admin_user with full access to the db, schema manager which can create tables/schemas for migration, and webapp user which does basic CRUD from the WebApp/Api
1
docker run --name salestracker_db -p 5432:5432 -e POSTGRES_USER=my_webapp_user -e POSTGRES_PASSWORD=00000000-0000-0000-0000-000000000001 -e POSTGRES_DB=psc_db -v "$(pwd)/initialize-database.sql:/docker-entrypoint-initdb.d/initialize-database.sql" -d postgres:17.2

Also here initialize-database.sql is a basic bootstrap script like

1
CREATE SCHEMA my_schema;

  1. Clear old migrations that may have been created with the old MS SQL provider, for me there were in \Data\Migrations\

  2. Create new migration, for me this was created in the root of my app in \Migrations\ and I ran the command in the context of the .csproj file

1
dotnet ef migrations add InitialCreate           # Add a new migration (this creates the migration files)
  1. Apply the migration
1
dotnet ef database update                        # Apply the migration to your PostgreSQL database
  1. Check the data

The migration history will be in the public schema

1
2
SELECT "MigrationId", "ProductVersion"
	FROM public."__EFMigrationsHistory";

The actual migration tables will be in the my_schema set in step 3.

Future Migrations

Future entity first migrations can then be applied.

  1. Create the entity

  2. Apply DbSet and any mappings the same as step 3 in MySQL with Entity Framework Core

  3. Create the migration the same as step 8 in this post, dont call it InitialCreate, use something related to your change

  4. Apply the migration with the update command the same as step 9 above

  5. Check the data the same as step step 10 above.

That should get you rocking EF Core with PostgreSQL.