Building a CRUD API with ASP.NET Core Web API and PostgreSQL

This content originally appeared on DEV Community and was authored by M. Oly Mahmud

In this guide, we'll walk through the process of building a CRUD API using ASP.NET Core, Entity Framework Core (EF Core), and PostgreSQL. The application will follow clean architecture principles, structuring the code into repository and service layers. This structure promotes separation of concerns, making the codebase more maintainable and testable.

1. Setting Up the Project

Creating a New ASP.NET Core Web API Project

We begin by creating a new ASP.NET Core Web API project, which will serve as the foundation for our application.

Open the terminal or command prompt.
Run the following commands to create the project:

mkdir PostgresCrud
cd PostgresCrud
dotnet new webapi -n PostgresCrud
cd PostgresCrud

dotnet new webapi creates a new Web API project with default configurations.
cd PostgresCrud navigates into the project folder.

Now, we have an ASP.NET Core Web API project named PostgresCrud.

2. Configuring PostgreSQL

Setting Up Connection String in `appsettings.json`

The connection string for PostgreSQL will be stored in the appsettings.json file. This file holds the configuration settings required by the application, such as the database connection details.

Example appsettings.json Configuration:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=webapi_demo;Username=postgres;Password=mysecretpassword"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Host=localhost: The PostgreSQL database is hosted locally.
Database=webapi_demo: The name of the database.
Username=postgres: PostgreSQL username.
Password=mysecretpassword: PostgreSQL password.

3. Installing Dependencies

Before we can start interacting with PostgreSQL, we need to install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Swashbuckle.AspNetCore

Microsoft.EntityFrameworkCore: Adds the Entity Framework Core (EF Core) ORM to handle database operations.
Npgsql.EntityFrameworkCore.PostgreSQL: Adds support for PostgreSQL as the database provider in EF Core.
Swashbuckle.AspNetCore: Adds Swagger for API documentation and testing.

4. Database Context (`ApplicationDbContext.cs`)

What is `ApplicationDbContext`?

The ApplicationDbContext class acts as the bridge between our application and the PostgreSQL database. It holds DbSet properties that represent tables in the database.

File: Data/ApplicationDbContext.cs

using Microsoft.EntityFrameworkCore;
using PostgresCrud.Entities;

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

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

DbContext: The base class provided by EF Core for database operations.
DbSet<Product> Products: Represents the Products table in PostgreSQL. Each property of DbSet corresponds to a table.

5. Defining the Entity (`Product.cs`)

What is an Entity?

Entities are classes that define the structure of database tables. In this case, the Product entity will represent products stored in the Products table.

File: Entities/Product.cs

namespace PostgresCrud.Entities
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public decimal Price { get; set; }
    }
}

Id: This is the primary key of the product table.
Name: The name of the product.
Price: The price of the product.

6. Implementing Repository Pattern

The repository pattern helps us encapsulate database operations into a separate class, allowing for cleaner, more organized code. The repository will be responsible for interacting with the database via EF Core.

Defining the Repository Interface

File: Repositories/IProductRepository.cs

using PostgresCrud.Entities;

namespace PostgresCrud.Repositories
{
    public interface IProductRepository
    {
        Task<IEnumerable<Product>> GetAllAsync();
        Task<Product> GetByIdAsync(int id);
        Task AddAsync(Product product);
        Task UpdateAsync(Product product);
        Task DeleteAsync(int id);
    }
}

The IProductRepository interface defines five CRUD operations:

GetAllAsync(): Retrieves all products.
GetByIdAsync(int id): Fetches a product by its ID.
AddAsync(Product product): Adds a new product.
UpdateAsync(Product product): Updates an existing product.
DeleteAsync(int id): Deletes a product by its ID.

Implementing the Repository

File: Repositories/ProductRepository.cs

using Microsoft.EntityFrameworkCore; // Importing Entity Framework Core for database operations
using PostgresCrud.Data; // Importing the application database context
using PostgresCrud.Entities; // Importing the Product entity

namespace PostgresCrud.Repositories
{
    public class ProductRepository : IProductRepository
    {
        private readonly ApplicationDbContext _context; // Database context for interacting with the database

        public ProductRepository(ApplicationDbContext context)
        {
            _context = context; // Injecting database context via constructor
        }

        // Retrieves all products from the database
        public async Task<IEnumerable<Product>> GetAllAsync()
        {
            // Converts the Products table into a list and returns it asynchronously
            return await _context.Products.ToListAsync();
        }

        // Retrieves a product by its ID
        public async Task<Product> GetByIdAsync(int id)
        {
            // Uses FindAsync to search for a product by its primary key (ID)
            return await _context.Products.FindAsync(id);
        }

        // Adds a new product to the database
        public async Task AddAsync(Product product)
        {
            // Adds the product entity to the database context
            await _context.Products.AddAsync(product);

            // Saves the changes to the database asynchronously
            await _context.SaveChangesAsync();
        }

        // Updates an existing product in the database
        public async Task UpdateAsync(Product product)
        {
            // Marks the product entity as updated in the database context
            _context.Products.Update(product);

            // Saves the updated product data to the database asynchronously
            await _context.SaveChangesAsync();
        }

        // Deletes a product by its ID
        public async Task DeleteAsync(int id)
        {
            // Finds the product in the database using the provided ID
            var product = await _context.Products.FindAsync(id);

            // If the product exists, remove it from the database
            if (product != null)
            {
                _context.Products.Remove(product);

                // Saves the changes to the database asynchronously
                await _context.SaveChangesAsync();
            }
        }
    }
}

The ProductRepository class implements the CRUD methods defined in the IProductRepository interface. It directly interacts with the database using the ApplicationDbContext.

7. Implementing the Service Layer

The Service Layer in an ASP.NET Core application acts as a bridge between the Controller (API Layer) and the Repository (Data Access Layer). It contains business logic and ensures proper data transformation before sending it to the API consumer.

Defining the Service Interface

File: Services/IProductService.cs

using PostgresCrud.DTOs;

namespace PostgresCrud.Services
{
    public interface IProductService
    {
        Task<IEnumerable<ProductResponseDto>> GetAllProductsAsync();
        Task<ProductResponseDto> GetProductByIdAsync(int id);
        Task AddProductAsync(ProductRequestDto productDto);
        Task UpdateProductAsync(int id, ProductRequestDto productDto);
        Task DeleteProductAsync(int id);
    }
}

This service interface includes methods that:

Retrieve products from the repository.
Transform Entity models into DTOs for data transfer.

Implementing the Service

File: Services/ProductService.cs

using PostgresCrud.Entities; // Importing the Product entity
using PostgresCrud.DTOs; // Importing Data Transfer Objects (DTOs) for request and response
using PostgresCrud.Repositories; // Importing the Product repository for data access

namespace PostgresCrud.Services
{
    public class ProductService : IProductService
    {
        private readonly IProductRepository _productRepository; // Repository instance for database operations

        public ProductService(IProductRepository productRepository)
        {
            _productRepository = productRepository; // Injecting the repository via constructor
        }

        // Retrieves all products, converts them to DTOs, and returns the list
        public async Task<IEnumerable<ProductResponseDto>> GetAllProductsAsync()
        {
            var products = await _productRepository.GetAllAsync(); // Fetch all products from repository

            // Convert each product entity into a ProductResponseDto and return the list
            return products.Select(p => new ProductResponseDto 
            { 
                Id = p.Id, 
                Name = p.Name, 
                Price = p.Price 
            });
        }

        // Retrieves a product by ID and converts it to a DTO
        public async Task<ProductResponseDto> GetProductByIdAsync(int id)
        {
            var product = await _productRepository.GetByIdAsync(id); // Fetch product by ID

            // If the product is not found, throw an exception
            if (product == null)
                throw new KeyNotFoundException("Product not found");

            // Convert entity to DTO and return it
            return new ProductResponseDto 
            { 
                Id = product.Id, 
                Name = product.Name, 
                Price = product.Price 
            };
        }

        // Adds a new product using a request DTO
        public async Task AddProductAsync(ProductRequestDto productDto)
        {
            // Convert DTO to entity
            var product = new Product
            {
                Name = productDto.Name,
                Price = productDto.Price
            };

            // Add the new product to the database
            await _productRepository.AddAsync(product);
        }

        // Updates an existing product with new data
        public async Task UpdateProductAsync(int id, ProductRequestDto productDto)
        {
            var product = await _productRepository.GetByIdAsync(id); // Fetch the product by ID

            // If the product does not exist, throw an exception
            if (product == null)
                throw new KeyNotFoundException("Product not found");

            // Update product fields with new values from DTO
            product.Name = productDto.Name;
            product.Price = productDto.Price;

            // Save the updated product in the database
            await _productRepository.UpdateAsync(product);
        }

        // Deletes a product by ID
        public async Task DeleteProductAsync(int id)
        {
            var product = await _productRepository.GetByIdAsync(id); // Fetch the product by ID

            // If the product does not exist, throw an exception
            if (product == null)
                throw new KeyNotFoundException("Product not found");

            // Delete the product from the database
            await _productRepository.DeleteAsync(id);
        }
    }
}

The service layer transforms the entity model into DTOs (Data Transfer Objects), which makes the data more presentable for the API consumer. It handles any business logic before calling the repository layer.

Breakdown of Service Methods

Method	Description
`GetAllProductsAsync`	Fetches all products, converts them into DTOs, and returns them.
`GetProductByIdAsync`	Fetches a single product by ID. If not found, throws a `KeyNotFoundException`.
`AddProductAsync`	Converts a `ProductRequestDto` into a `Product` entity and saves it in the database.
`UpdateProductAsync`	Checks if a product exists, updates its fields, and saves changes. Throws an exception if the product doesn’t exist.
`DeleteProductAsync`	Checks if a product exists and deletes it. Throws an exception if not found.

8. Setting Up API Controllers

Creating the Product Controller

The ProductController will define the endpoints for our CRUD operations.

File: Controllers/ProductController.cs

using Microsoft.AspNetCore.Mvc; // Importing ASP.NET Core MVC framework
using PostgresCrud.Services; // Importing the service layer
using PostgresCrud.DTOs; // Importing Data Transfer Objects (DTOs)

namespace PostgresCrud.Controllers
{
    [ApiController] // Specifies that this is an API controller
    [Route("api/[controller]")] // Defines the route as 'api/product'
    public class ProductController : ControllerBase
    {
        private readonly IProductService _productService; // Service instance for business logic

        public ProductController(IProductService productService)
        {
            _productService = productService; // Injecting the service via constructor
        }

        // Handles HTTP GET request to fetch all products
        [HttpGet]
        public async Task<IActionResult> GetAll()
        {
            var products = await _productService.GetAllProductsAsync(); // Calls service to get all products
            return Ok(products); // Returns 200 OK response with product data
        }

        // Handles HTTP GET request to fetch a single product by ID
        [HttpGet("{id}")]
        public async Task<IActionResult> GetById(int id)
        {
            try
            {
                var product = await _productService.GetProductByIdAsync(id); // Calls service to fetch product by ID
                return Ok(product); // Returns 200 OK response if found
            }
            catch (KeyNotFoundException)
            {
                return NotFound(); // Returns 404 Not Found if product does not exist
            }
        }

        // Handles HTTP POST request to add a new product
        [HttpPost]
        public async Task<IActionResult> Add(ProductRequestDto productDto)
        {
            await _productService.AddProductAsync(productDto); // Calls service to add a new product
            return CreatedAtAction(nameof(GetById), new { id = productDto.Id }, productDto); 
            // Returns 201 Created response with location header pointing to the new product
        }

        // Handles HTTP PUT request to update an existing product
        [HttpPut("{id}")]
        public async Task<IActionResult> Update(int id, ProductRequestDto productDto)
        {
            try
            {
                await _productService.UpdateProductAsync(id, productDto); // Calls service to update product
                return NoContent(); // Returns 204 No Content response on success
            }
            catch (KeyNotFoundException)
            {
                return NotFound(); // Returns 404 Not Found if product does not exist
            }
        }

        // Handles HTTP DELETE request to delete a product by ID
        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            try
            {
                await _productService.DeleteProductAsync(id); // Calls service to delete product
                return NoContent(); // Returns 204 No Content response on success
            }
            catch (KeyNotFoundException)
            {
                return NotFound(); // Returns 404 Not Found if product does not exist
            }
        }
    }
}

Here’s a table that explains each method inside the ProductController:

Method	HTTP Verb	Route	Description
`GetAll`	`GET`	`/api/product`	Fetches all products and returns them as a JSON array.
`GetById`	`GET`	`/api/product/{id}`	Fetches a single product by ID. Returns `404 Not Found` if the product does not exist.
`Add`	`POST`	`/api/product`	Accepts a `ProductRequestDto` to create a new product. Returns `201 Created` with the location of the new product.
`Update`	`PUT`	`/api/product/{id}`	Updates an existing product using the provided ID and DTO. Returns `404 Not Found` if the product does not exist.
`Delete`	`DELETE`	`/api/product/{id}`	Deletes a product by ID. Returns `204 No Content` on success or `404 Not Found` if the product does not exist.

9. Setting Up Dependency Injection

Finally, to connect everything, register the services and repositories in the Program.cs file for dependency injection.

File: Program.cs

using Microsoft.EntityFrameworkCore;
using PostgresCrud.Data;
using PostgresCrud.Repositories;
using PostgresCrud.Services;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();

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

// Register repositories and services
builder.Services.AddScoped<IProductRepository, ProductRepository>();
builder.Services.AddScoped<IProductService, ProductService>();

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

10. Running the Application

After implementing everything, we can now run the application.

Apply any database migrations (if required):

dotnet ef migrations add InitialCreate
dotnet ef database update

Start the application:

dotnet run

Access the API via Swagger UI at http://localhost:5052/swagger.

11. Conclusion

By following this structure, we have:

Set up an ASP.NET Core Web API.
Used PostgreSQL and Entity Framework Core to handle database operations.
Followed Clean Architecture principles by separating concerns into repositories, services, and controllers.
Implemented a CRUD API with structured error handling and appropriate HTTP status codes.

This structure makes it easier to manage, test, and scale our application as it grows.

This content originally appeared on DEV Community and was authored by M. Oly Mahmud

Print Share Comment Cite Upload Translate Updates

APA

M. Oly Mahmud | Sciencx (2025-02-15T13:49:16+00:00) Building a CRUD API with ASP.NET Core Web API and PostgreSQL. Retrieved from https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/

MLA

" » Building a CRUD API with ASP.NET Core Web API and PostgreSQL." M. Oly Mahmud | Sciencx - Saturday February 15, 2025, https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/

HARVARD

M. Oly Mahmud | Sciencx Saturday February 15, 2025 » Building a CRUD API with ASP.NET Core Web API and PostgreSQL., viewed ,<https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/>

VANCOUVER

M. Oly Mahmud | Sciencx - » Building a CRUD API with ASP.NET Core Web API and PostgreSQL. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/

CHICAGO

" » Building a CRUD API with ASP.NET Core Web API and PostgreSQL." M. Oly Mahmud | Sciencx - Accessed . https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/

IEEE

" » Building a CRUD API with ASP.NET Core Web API and PostgreSQL." M. Oly Mahmud | Sciencx [Online]. Available: https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/. [Accessed: ]

rf:citation

» Building a CRUD API with ASP.NET Core Web API and PostgreSQL | M. Oly Mahmud | Sciencx | https://www.scien.cx/2025/02/15/building-a-crud-api-with-asp-net-core-web-api-and-postgresql/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.