.NET 6 with PostgreSQL and Redis Cache

To exploring how to utilise PostgreSQL with?.NET 6. The steps we need to take to setup, configure and utilise PostgreSQL with?.NET 6 WebAPI.

GitSource : https://github.com/jayamoorthi/BookApiPostgresql

Preparing the database

First, you’ll want to install Postgres locally. If you’re using a Mac, this step is very easy. Simply install?Postgres.app?and you’ll be ready to go.

If you’re using Windows, check out the?Windows Installers page?on the Postgres website to download the latest installer.

Local Docker Setup:

Pull image and create image for application running in locally.

Docker-Compose.yml

version: '3.4'

services:
  books.api:
    image: ${DOCKER_REGISTRY-}bookapipostgresql
    container_name: books.api
    build:
      context: .
      dockerfile: BookApiPostgresql\Dockerfile
    ports:
      - 5000:5000
      - 5001:5001
  
 
  books.cache: 
    image: redis/redis-stack-server:latest
    container_name: books.cache
    restart: always 
    volumes:
      - ./.containers/redis_volume_data:/data
    ports:
      - 6389:6389

  books.db: 
    image: postgres:latest
    container_name: books.db
    hostname: localhost
    #dns: postgresdb    
    environment: 
       - POSTGRES_DB=books
       - POSTGRES_USER=postgres
       - POSTGRES_PASSWORD=postgres       
       - POSTGRES_HOST=localhost
    volumes:
       #- ./.containers/books-db:/path/in/container
        #- /path/on/the/host:books-db/lib/postgres:rw
       #- ./.containers/books-db:containers/books-db/lib/postgres/db/data 
       - ./.containers/books-db:/var/containers/books-db/lib/postgres/db/data
    ports: 
       - 5432:5432 
    #extra_hosts:
    #  - "host.docker.internal:host-gateway"

  pgadmin:
    image: dpage/pgadmin4
    restart: always
    ports:
      - "5050:80"
    # environment:
      # - pgadmin_default_email: [email protected]
      # - pgadmin_default_password: mypgadminpass
    # volumes:
       # - pgadmin_data:/var/lib/pgadmin
    depends_on:
       - books.db        

Docker-compose.override.yml

services:
   books.api:
    environment:
      - ASPNETCORE_ENVIRONMENT=Development
      #- ASPNETCORE_URLS=https://+:443;https://+:80
      - ASPNETCORE_HTTP_PORTS=5000
      - ASPNETCORE_HTTPS_PORTS=5001        

Running docker

docker compose command

docker-compose up -d        

To verify docker is running on docker Desktop and command windows

using Docker ps command

.NET 6 API project :

Let's create a new api project for Books crud operation perform apis using postgresql Database.

Add PostgreSQL database provider from NuGet

?

Run the following command from the project root folder to install the EF Core database provider for PostgreSQL from NuGet:

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

Redis Cache:

Microsoft.Extensions.Caching.StackExchangeRedis        

?

Healthy check:

Microsoft.AspNetCore.Diagnostics.HealthChecks
AspNetCore.HealthChecks.UI.Client
AspNetCore.HealthChecks.Redis
AspNetCore.HealthChecks.NpgSql        

Appsettings

"ConnectionStrings": {
    "PostgresDatabase": "Host=localhost;port=5432;Database=books;Username=postgres;Password=postgres;Include Error Detail=true",
    "RedisCache": "books.cache:6389"
  }        

Domain Entity :

Book.cs

 public sealed class Book
    {
        public int Id { get; set; }

        public string Title { get; set; }
        public string ISBN { get; set; }
        public string Description { get; set; }
        public string Author { get; set; }
        public DateTimeOffset CreatedAt { get; set; }
    }        

DB Migration :

Models first approach generate migration script for db and update script in the Database.

Add-Migration <MigrationName> Command

Update-Database Command changes updated in Local Db.

Books API Controller

Create a endpoint for crud operation to books.

public class BookController : ControllerBase
    {
        private readonly IBookService _bookService;
        private readonly IRedisCacheService _redisCacheService;

        public BookController(IBookService bookService, IRedisCacheService redisCacheService)
        {

            _bookService = bookService ?? throw new ArgumentNullException(nameof(bookService));
            _redisCacheService = redisCacheService;
        }

        [HttpPost("CreateBook")]
        public async Task<IActionResult> CreateBookAsync([FromBody] CreateBookRequest1 request)
        {
            var book = request.ToEntity1();
            var cancellationToken = new CancellationToken();
            var id = await _bookService.CreateBookAsync(book, cancellationToken);

            return Ok(CreatedAtRoute(
                nameof(GetBookById),
                new { id = id },
                book));
        }


        [HttpGet("GetBookById/{id}")]
        public async Task<IResult> GetBookById([FromQuery] int id)
        {
            var cacheKey = $"book_{id}";

            var cancellationToken = new CancellationToken();

            var response = await _redisCacheService.GetDataAsync<BookResponse>(
                cacheKey, cancellationToken);

            if (response is not null)
            {
                return Results.Ok(response);
            }

            var book = await _bookService.GetBookByIdAsync(id, cancellationToken);

            if (book is null)
            {
                return Results.NotFound();
            }

            response = book.ToResponseDto();

            await _redisCacheService.SetDataAsync<BookResponse>(
                cacheKey,
                response,
                cancellationToken);

            return Results.Ok(response);
        }
    }        

Programs.cs

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services
    .AddPostgreSqlConfig(builder.Configuration)
    .AddRedisConfig(builder.Configuration)
    .AddHealthChecks()
    .AddPostgreSqlHealth(builder.Configuration)
    .AddRedisHealth(builder.Configuration);


//builder.Services.AddScoped<BookService>((serviceProvider) => {
//    var bookservice = new BookService(serviceProvider.GetRequiredService<ApplicationDbContext>());
//    return bookservice;
//    });


//builder.Services.AddScoped<IBookService>(serviceProvider => new BookService(serviceProvider.GetRequiredService<ApplicationDbContext>()));

builder.Services.AddDistributedMemoryCache();



//builder.Services.AddScoped<IBookService, new BookService>();
builder.Services.AddScoped(config => new BookService(config.GetRequiredService<ApplicationDbContext>()));
builder.Services.AddScoped(redis => new RedisCacheService(redis.GetRequiredService<IDistributedCache>()));

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



var app = builder.Build();


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

app.MapHealthChecks("health", new HealthCheckOptions
{
    ResponseWriter = UIResponseWriter.WriteHealthCheckUIResponse
});

app.UseHttpsRedirection();

app.UseAuthorization();

//app.MapBookEndpoints();

app.MapControllers();




app.Run();        

Application running :

Conclusion:

Implements API for CRUD operation using postgresql DB and docker.