Building a REST API with ASP.NET Core and PostgreSQL

REST APIs are the foundation of modern web applications. In this article, I will demonstrate how to build a simple REST API using ASP.NET Core and PostgreSQL. This combination provides excellent performance, scalability, and flexibility for modern applications.

Technologies Used
ASP.NET Core
C#
PostgreSQL
Entity Framework Core
Visual Studio
Project Overview

The API will manage a simple Product database with the following operations:

Create Product
Get All Products
Get Product By Id
Update Product
Delete Product
Setting Up PostgreSQL

First, create a PostgreSQL database:

CREATE DATABASE productdb;

Create a Products table:

CREATE TABLE Products (
Id SERIAL PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10,2)
);
Creating the ASP.NET Core Project

Create a new Web API project:

dotnet new webapi -n ProductApi

Install required packages:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore
Product Model
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
Database Context
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions options)
: base(options)
{
}

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

}
Connection String

Add the PostgreSQL connection string in appsettings.json:

{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Database=productdb;Username=postgres;Password=password"
}
}
Product Controller

Example endpoint for retrieving products:

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
private readonly AppDbContext _context;

public ProductsController(AppDbContext context)
{
    _context = context;
}

[HttpGet]
public async Task<IActionResult> GetProducts()
{
    return Ok(await _context.Products.ToListAsync());
}

}
Running the Application

Run the project:

dotnet run

Open Swagger and test the API endpoints.

Benefits of ASP.NET Core and PostgreSQL
High performance
Cross-platform support
Open-source ecosystem
Strong database reliability
Easy deployment to cloud platforms
Conclusion

ASP.NET Core and PostgreSQL provide an excellent stack for building modern REST APIs. With Entity Framework Core, database operations become simple while maintaining strong performance and maintainability.

As I continue developing .NET applications, I look forward to exploring authentication, microservices, and cloud deployment in future projects.