Starting a production-grade .NET 8 Web API backed by PostgreSQL involves more than installing a NuGet package and wiring a connection string. There are provider choices, DI registration patterns, and migration tooling decisions to get right from day one. This walkthrough takes you from an empty folder to a running API with a verified database connection, a typed DbContext, and your first migration applied to a live PostgreSQL instance.
By the end you will have a clean, runnable project: an ASP.NET Core Web API wired to PostgreSQL through Npgsql and EF Core, with one entity, one DbContext, and a migration history table confirmed on disk.
Before starting, make sure you have:
- .NET 8 SDK (8.0.x or later)
- PostgreSQL 15 or later, running locally or via Docker
- Visual Studio 2022 (17.8+) or VS Code with the C# Dev Kit extension
- The EF Core global CLI tool:
dotnet tool install --global dotnet-ef
docker run --name pg-dev -e POSTGRES_PASSWORD=dev -p 5432:5432 -d postgres:15 spins up a ready instance in seconds and keeps your machine clean.
Scaffold a controller-based Web API, then add the Npgsql EF Core provider and the design-time tools required for migrations.
dotnet new webapi -n Catalog.Api --use-controllers
cd Catalog.Api
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.DesignNpgsql.EntityFrameworkCore.PostgreSQL is the official EF Core provider maintained by the Npgsql team. It bundles the ADO.NET driver, the EF Core provider, and PostgreSQL-specific query translations in a single package. Microsoft.EntityFrameworkCore.Design is required only at design time for dotnet ef commands; add PrivateAssets="All" to that package reference in the .csproj if you want to keep it out of your published output.
Add the connection string to appsettings.Development.json. This keeps it out of the production configuration file and makes it easy to override through environment variables in any hosted environment.
// appsettings.Development.json
{
"ConnectionStrings": {
"Catalog": "Host=localhost;Port=5432;Database=catalog_dev;Username=postgres;Password=dev"
}
}ConnectionStrings__Catalog (double underscore). ASP.NET Core's configuration system maps double-underscore separators to nested keys, so no code changes are needed between environments.
Add a Product entity and a typed CatalogDbContext before touching the DI registration. Separating these from Program.cs keeps the bootstrapping code clean.
The entity
// Models/Product.cs
namespace Catalog.Api.Models;
public class Product
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public decimal Price { get; set; }
public DateTimeOffset CreatedAt { get; set; }
}The DbContext
// Data/CatalogDbContext.cs
using Catalog.Api.Models;
using Microsoft.EntityFrameworkCore;
namespace Catalog.Api.Data;
public class CatalogDbContext : DbContext
{
public CatalogDbContext(DbContextOptions<CatalogDbContext> options)
: base(options) { }
public DbSet<Product> Products => Set<Product>();
}The constructor that accepts DbContextOptions<CatalogDbContext> is the standard DI-compatible pattern. EF Core uses it at runtime to pick up the provider and connection string registered in the container. Using Set<Product>() in the property body, rather than an auto-property with a default, avoids a nullable analysis warning that is common in .NET 8 projects with nullable reference types enabled.
Wire the DbContext into the ASP.NET Core DI container and point it at the Npgsql provider.
// Program.cs
using Catalog.Api.Data;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<CatalogDbContext>(options =>
options.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseAuthorization();
app.MapControllers();
app.Run();UseNpgsql activates the Npgsql provider and binds it to the connection string resolved from the configuration system. Because the value comes from configuration rather than being hard-coded, the same Program.cs works across development, staging, and production without modification.
Generate the migration file and apply it to the development database. EF Core will compare the current model against the migration snapshot and emit the SQL needed to bring the schema into sync.
dotnet ef migrations add InitialCreate
dotnet ef database updateAfter database update completes, connect to your PostgreSQL instance and run \dt in psql (or open pgAdmin). You will see two tables: Products, which holds your entity data, and __EFMigrationsHistory, which EF Core uses to track which migrations have been applied.
decimal to PostgreSQL numeric and DateTimeOffset to timestamp with time zone. Both are safe defaults for new schemas. The next post in this series shows how to override column types explicitly using Fluent API when the defaults are not precise enough for production use.
You now have a .NET 8 Web API with a working Npgsql EF Core provider, a typed DbContext, a Product entity, and a confirmed migration on disk. The project structure follows conventions that hold up cleanly as the schema and team grow.
The next post covers how to design your DbContext and entity models with PostgreSQL in mind: using IEntityTypeConfiguration classes to keep Fluent API organized, choosing the right PostgreSQL column types explicitly, and placing index definitions in the right layer.
No comments: