CQRS & PostgreSQL: DateTime Service Proof Of Concept

by Alex Johnson 53 views

In this article, we will walk through the creation of a Proof of Concept (POC) that integrates a Services layer with Command Query Responsibility Segregation (CQRS) to retrieve the current date and time from a PostgreSQL database. This POC will demonstrate how to connect various layers of an application, including Services, Data.Cqrs, Entity Framework (EF) Core, and PostgreSQL. This comprehensive guide provides a detailed walkthrough, ensuring a clear understanding for developers aiming to implement similar architectures. By focusing on practical implementation and best practices, this article serves as a valuable resource for building robust and scalable applications.

Goal

The primary goal of this POC is to establish a connection between all layers of the application, specifically:

  • Services Layer
  • Data.Cqrs Layer
  • EF Core
  • PostgreSQL Database

This integration will showcase the feasibility and efficiency of using CQRS with PostgreSQL in a .NET environment. The project aims to create a streamlined data flow, ensuring each component works cohesively to deliver the desired functionality. This setup is crucial for building scalable and maintainable applications, providing a clear separation of concerns and promoting code reusability.

Description

To achieve this goal, we will implement a simple DateTimeService that performs the following steps:

  1. Accept Request via IMediator: The service will receive a request through the IMediator interface, which acts as a central hub for handling commands and queries.
  2. Execute SELECT NOW() on PostgreSQL Database: Upon receiving the request, the service will execute a SELECT NOW() query on the PostgreSQL database to retrieve the current date and time.
  3. Return Current Date and Time: Finally, the service will return the current date and time obtained from the database.

This process highlights the core functionality of the service and demonstrates how CQRS facilitates a clean separation between command and query operations. By using IMediator, the service remains decoupled from the underlying data access logic, promoting flexibility and testability.

Diving Deeper into the Implementation

The DateTimeService is designed to be a straightforward yet illustrative example of CQRS in action. Let's break down the key components and how they interact:

  • IMediator: This interface is part of a mediation pattern implementation, acting as a central point for dispatching commands and queries. In our context, it allows the DateTimeService to receive requests without needing to know the specifics of how those requests are handled. This decoupling is a core benefit of CQRS, as it allows different parts of the system to evolve independently.
  • Request Handling: The service will define a specific request type (e.g., GetCurrentDateTimeRequest) that implements a marker interface (e.g., IRequest<DateTime>). This request object will be passed to the IMediator.
  • Query Execution: A corresponding handler (e.g., GetCurrentDateTimeHandler) will implement the logic to process the request. This handler is responsible for interacting with the database to fetch the current date and time.
  • Database Interaction: The handler will use EF Core to execute the SELECT NOW() query against the PostgreSQL database. EF Core provides an abstraction layer over the database, allowing us to interact with it using .NET objects rather than raw SQL.
  • Result Retrieval: The result of the query (the current date and time) is then returned to the service, which can further process it or return it directly to the client.

This step-by-step process ensures a clear separation of concerns, with each component having a specific responsibility. This not only makes the code easier to understand and maintain but also allows for greater flexibility in the future.

Implementation Details

Setting up the Environment

Before diving into the code, it's crucial to set up the development environment correctly. This involves installing the necessary tools and dependencies, configuring the database connection, and creating the project structure. Here’s a detailed breakdown of the setup process:

  1. Install .NET SDK: Ensure that the .NET SDK is installed on your machine. The SDK provides the necessary tools and libraries for building .NET applications. You can download the latest version from the official .NET website.
  2. Install PostgreSQL: If you don't have PostgreSQL installed, download and install it from the official PostgreSQL website. Follow the installation instructions for your operating system. After installation, make sure the PostgreSQL server is running and accessible.
  3. Create a PostgreSQL Database: Create a new database in PostgreSQL for the POC. You can use a tool like pgAdmin or the command-line interface (psql) to create the database. Note down the database name, username, and password, as you will need these to configure the connection string.
  4. Set up the Project: Create a new .NET solution using the dotnet new sln command. Then, add the necessary projects to the solution, such as a Services project, a Data.Cqrs project, and a Console or Web API project for the application entry point.

Project Structure

A well-organized project structure is essential for maintainability and scalability. Here’s a recommended structure for the POC:

  • Solution Root:
    • SolutionName.sln (Solution file)
    • src/ (Source code directory)
      • Services/ (Services Layer Project)
        • DateTimeService.cs
        • GetCurrentDateTime/ (Feature directory)
          • GetCurrentDateTimeRequest.cs
          • GetCurrentDateTimeHandler.cs
      • Data.Cqrs/ (CQRS Infrastructure Project)
        • Common/ (Common interfaces and classes)
          • IRequest.cs
          • IRequestHandler.cs
        • Commands/
        • Queries/
      • Data/ (Data Access Layer Project)
        • AppDbContext.cs (EF Core DbContext)
        • Entities/ (Database entities)
      • Api/ (API Project - Web API or Console App)
        • Controllers/ (API Controllers)
        • Program.cs (Application entry point)
    • tests/ (Tests directory)
      • Services.Tests/ (Services Layer Tests)
      • Data.Tests/ (Data Access Layer Tests)

This structure separates the concerns of each layer, making it easier to navigate and maintain the codebase.

Implementing the Data.Cqrs Layer

The Data.Cqrs layer is a crucial component of the POC, providing the infrastructure for Command Query Responsibility Segregation. This layer typically includes interfaces and classes that facilitate the dispatching of commands and queries to their respective handlers. Here’s how to implement the Data.Cqrs layer:

  1. Define Interfaces:
    • IRequest<TResult>: A marker interface for requests that return a result of type TResult.
    • IRequestHandler<TRequest, TResult>: An interface for request handlers that process requests of type TRequest and return a result of type TResult.
    • IMediator: An interface for the mediator pattern, which provides a central point for dispatching requests to their handlers.
  2. Implement Mediator:
    • Create a Mediator class that implements the IMediator interface. This class will be responsible for finding the appropriate handler for a given request and invoking it.
  3. Register Handlers:
    • Use a dependency injection container (e.g., Microsoft.Extensions.DependencyInjection) to register the request handlers. This allows the mediator to resolve the handlers at runtime.

Implementing the DateTimeService

The DateTimeService is the core component that retrieves the current date and time from the PostgreSQL database. This service will receive requests via the IMediator, execute the necessary database query, and return the result. Here’s how to implement the DateTimeService:

  1. Create Request and Handler:
    • GetCurrentDateTimeRequest: A class that implements IRequest<DateTime>. This class represents the request to get the current date and time.
    • GetCurrentDateTimeHandler: A class that implements IRequestHandler<GetCurrentDateTimeRequest, DateTime>. This class contains the logic to execute the database query and return the result.
  2. Implement the Handler:
    • In the GetCurrentDateTimeHandler, inject the AppDbContext (EF Core DbContext) and use it to execute the SELECT NOW() query.
  3. Register the Service:
    • Register the DateTimeService and the GetCurrentDateTimeHandler in the dependency injection container.

Setting up EF Core and PostgreSQL

Entity Framework Core (EF Core) provides an abstraction layer for interacting with the PostgreSQL database. Here’s how to set up EF Core and configure the connection to PostgreSQL:

  1. Install NuGet Packages:
    • Install the Npgsql.EntityFrameworkCore.PostgreSQL NuGet package to use PostgreSQL with EF Core.
    • Install the Microsoft.EntityFrameworkCore.Design and Microsoft.EntityFrameworkCore.Tools packages for database migrations.
  2. Create DbContext:
    • Create an AppDbContext class that inherits from DbContext. This class will represent the database context and provide access to the database tables.
    • Override the OnConfiguring method to configure the connection to the PostgreSQL database using the connection string.
  3. Configure Connection String:
    • Store the connection string in the appsettings.json file or an environment variable.
    • Use the connection string to configure the database context in the OnConfiguring method.
  4. Create Database Migration:
    • Use the Add-Migration command in the Package Manager Console to create an initial migration for the database schema.
    • Use the Update-Database command to apply the migration and create the database tables.

Implementing the API Endpoint

To expose the DateTimeService functionality, you can create an API endpoint using ASP.NET Core Web API or a simple console application. Here’s how to implement the API endpoint:

  1. Create API Controller:
    • Create an API controller (e.g., DateTimeController) that handles the request to get the current date and time.
    • Inject the IMediator into the controller.
  2. Implement the Action:
    • Create an action method (e.g., GetCurrentDateTime) that handles the request.
    • Use the IMediator to send the GetCurrentDateTimeRequest and retrieve the result.
    • Return the result as an API response.

Running the Application

After implementing all the components, you can run the application and test the API endpoint. Here are the steps to run the application:

  1. Build the Application:
    • Use the dotnet build command to build the application.
  2. Run the Application:
    • Use the dotnet run command to run the application.
  3. Test the API:
    • Use a tool like Postman or curl to send a request to the API endpoint and verify the response.

Notes

Several key aspects should be considered during the implementation of this POC:

  • Local Olbrasoft Packages: The POC utilizes local Olbrasoft packages (Data.Cqrs, Mediation) as ProjectReferences, which allows for easier debugging and modification of these packages during development.
  • External Packages: External packages such as Npgsql, xUnit, and Moq are used from NuGet for database connectivity, testing, and mocking, respectively.
  • .NET 10: The project targets .NET 10, ensuring compatibility with the latest features and performance improvements.
  • Duplicate Interfaces Folder: It's important to remove the duplicate Interfaces/ folder in the Data project and use the existing Data.Cqrs.Common to avoid redundancy and maintain consistency.

Conclusion

This POC successfully demonstrates the integration of a Services layer with CQRS to retrieve the current date and time from a PostgreSQL database. By following the steps outlined in this article, developers can gain a clear understanding of how to implement a similar architecture in their own applications. The use of CQRS provides a clean separation of concerns, making the codebase more maintainable and scalable. This approach ensures that each component of the system works cohesively, delivering the desired functionality efficiently and effectively.

The implementation details provided, including setting up the environment, structuring the project, implementing the Data.Cqrs layer, setting up EF Core with PostgreSQL, and creating an API endpoint, offer a comprehensive guide for building robust and scalable applications. By adhering to these best practices, developers can create systems that are not only functional but also maintainable and adaptable to future changes.

For further reading on CQRS and its applications, consider exploring resources like Microsoft's documentation on CQRS patterns. This will provide additional insights and best practices for implementing CQRS in various scenarios.