Running a .NET Core API with PostgreSQL Locally - Step-by-Step

When building modern web applications, using PostgreSQL as a relational database with .NET Core Web API is a powerful and scalable choice. PostgreSQL is an open-source, enterprise-grade database that supports advanced features like JSON storage, full-text search, and high concurrency.

In this guide, we will cover how to:
✅ Install PostgreSQL and pgAdmin for database management
✅ Configure Entity Framework Core (EF Core) to use PostgreSQL
✅ Create a .NET Core Web API that connects to PostgreSQL
✅ Run database migrations and seed initial data

By the end of this tutorial, you will have a working .NET Core API connected to a PostgreSQL database, with full CRUD operations and data seeding.

---

1. Installing PostgreSQL and pgAdmin

Before we start coding, we need to install PostgreSQL and its management tool, pgAdmin.

Step 1: Download and Install PostgreSQL

1. Visit the PostgreSQL official website:
   Download PostgreSQL
2. Select your operating system (Windows, macOS, Linux) and download the installer.
3. Run the installer and follow these steps:
   • Choose the default installation directory.
   • Select PostgreSQL Server, pgAdmin 4, and Command Line Tools.
   • Set a strong password for the PostgreSQL superuser (postgres).
   • Set the port (default is 5432).

Step 2: Verify PostgreSQL Installation

1. Open pgAdmin and connect to the PostgreSQL instance using the superuser (postgres).

2. Open a new query window and run:

   SELECT version();
   

   This should return the PostgreSQL version installed on your system.

---

2. Configuring EF Core to Use PostgreSQL

Step 1: Install Required EF Core NuGet Packages

In your .NET Core Web API project, install the following NuGet packages using the command line:

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

• Npgsql.EntityFrameworkCore.PostgreSQL – EF Core provider for PostgreSQL
• Microsoft.EntityFrameworkCore.Design – Enables design-time tools for EF Core

Step 2: Set Up the Connection String

Modify the appsettings.json file to include the PostgreSQL connection string:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=MyAppDb;Username=postgres;Password=yourpassword"
  }
}

Replace yourpassword with the actual PostgreSQL password you set during installation.

Step 3: Create the Database Context

Create a Data folder and add a new class called ApplicationDbContext.cs:

using Microsoft.EntityFrameworkCore;
using MyApp.Models;

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

        public DbSet<Product> Products { get; set; }
    }
}

Step 4: Register the Database Context in Program.cs

Modify Program.cs to configure EF Core with PostgreSQL:

using Microsoft.EntityFrameworkCore;
using MyApp.Data;

var builder = WebApplication.CreateBuilder(args);

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

builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();
app.UseSwagger();
app.UseSwaggerUI();

app.UseAuthorization();
app.MapControllers();

app.Run();

---

3. Creating a .NET Core Web API That Connects to PostgreSQL

Step 1: Create the Model (Entity Class)

Create a Models folder and add a new class called Product.cs:

namespace MyApp.Models
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }
}

Step 2: Create a Repository Service for CRUD Operations

Create a Services folder and add ProductService.cs:

using System.Collections.Generic;
using System.Linq;
using MyApp.Data;
using MyApp.Models;

namespace MyApp.Services
{
    public class ProductService
    {
        private readonly ApplicationDbContext _context;

        public ProductService(ApplicationDbContext context)
        {
            _context = context;
        }

        public List<Product> GetAllProducts() => _context.Products.ToList();
        
        public Product GetProductById(int id) => _context.Products.Find(id);

        public void AddProduct(Product product)
        {
            _context.Products.Add(product);
            _context.SaveChanges();
        }

        public void UpdateProduct(Product product)
        {
            _context.Products.Update(product);
            _context.SaveChanges();
        }

        public void DeleteProduct(int id)
        {
            var product = _context.Products.Find(id);
            if (product != null)
            {
                _context.Products.Remove(product);
                _context.SaveChanges();
            }
        }
    }
}

Step 3: Register ProductService in Program.cs

builder.Services.AddScoped<ProductService>();

Step 4: Create a Web API Controller

Create a Controllers folder and add ProductController.cs:

using Microsoft.AspNetCore.Mvc;
using MyApp.Models;
using MyApp.Services;

namespace MyApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductController : ControllerBase
    {
        private readonly ProductService _productService;

        public ProductController(ProductService productService)
        {
            _productService = productService;
        }

        [HttpGet]
        public IActionResult GetProducts() => Ok(_productService.GetAllProducts());

        [HttpGet("{id}")]
        public IActionResult GetProduct(int id)
        {
            var product = _productService.GetProductById(id);
            return product != null ? Ok(product) : NotFound();
        }

        [HttpPost]
        public IActionResult AddProduct(Product product)
        {
            _productService.AddProduct(product);
            return CreatedAtAction(nameof(GetProduct), new { id = product.Id }, product);
        }

        [HttpPut("{id}")]
        public IActionResult UpdateProduct(int id, Product product)
        {
            if (id != product.Id) return BadRequest();
            _productService.UpdateProduct(product);
            return NoContent();
        }

        [HttpDelete("{id}")]
        public IActionResult DeleteProduct(int id)
        {
            _productService.DeleteProduct(id);
            return NoContent();
        }
    }
}

---

4. Run Database Migrations and Seed Initial Data

Step 1: Run Migrations

Run the following command to generate and apply migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

Step 2: Seed Initial Data

Modify ApplicationDbContext.cs to add a seed method:

using System.Linq;
using Microsoft.EntityFrameworkCore;
using MyApp.Models;

namespace MyApp.Data
{
    public static class DbInitializer
    {
        public static void Seed(ApplicationDbContext context)
        {
            if (!context.Products.Any())
            {
                context.Products.AddRange(
                    new Product { Name = "Laptop", Price = 1000 },
                    new Product { Name = "Mouse", Price = 50 }
                );
                context.SaveChanges();
            }
        }
    }
}

Modify Program.cs to call the seed method:

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
    DbInitializer.Seed(context);
}

---

Conclusion

In this guide, we:
✅ Installed PostgreSQL and pgAdmin
✅ Configured Entity Framework Core to work with PostgreSQL
✅ Created a .NET Core Web API and connected it to PostgreSQL
✅ Ran database migrations and seeded initial data

Now, you can start developing your application with PostgreSQL as the backend. 🚀