Connect Azure Cosmos DB for PostgreSQL to your ASP.NET Core application.

Brian_Kemboi · Oct 17, 2024

You’re a software developer tasked with creating robust backend web applications for your team. You’re always on the lookout for tools that can enhance performance, scalability, and ease of use. Azure Cosmos DB for PostgreSQL is a powerful, globally distributed database service that seamlessly integrates with your SDKs. In this blog, we’ll explore how to connect Azure Cosmos DB for PostgreSQL to your ASP.NET Core application, unlocking new levels of efficiency and reliability for your projects.

Topics Covered

Creating an ASP.NET Core Web Application
Connecting to Azure Cosmos DB for PostgreSQL
Managing Migrations with Entity Framework Core
Performing CRUD Operations on data

The source code for the web API we will be developing is available at: CosmosDB-PostgresAPI

Prerequisites

To achieve this goal, ensure you have the following:

Understanding of Azure Cosmos DB For PostgreSQL: Familiarize yourself with what Azure Cosmos DB for PostgreSQL is, as covered in our previous blog.

Foundations of Azure Cosmos DB: Review the foundational concepts of Azure Cosmos DB, also discussed in our earlier blog.

Azure Account with Subscriptions: Make sure you have an active Azure account with the necessary subscriptions.

Development Environment: Use Visual Studio Code or Visual Studio as your Integrated Development Environment (IDE). I will be using Visual Studio Code.

.NET SDK: Install the .NET SDK to develop and run your ASP.NET Core applications.

Creating an ASP.NET Core Web Application

To check if you have successfully installed .NET SDK, run the following command in your terminal to check the version.

dotnet --version

I have dotnet 8 installed.

In your terminal, run the following commands to create ASP .NET core web Api and open it in visual studio code.

Code:

dotnet new webapi --use-controllers -o CosmosPostgresApi 
cd CosmosPostgresApi 
code .

We shall be using Microsoft Entity Framework, an Object-Relational Mapper (ORM) which simplifies data access by allowing developers to interact with databases using .NET objects instead of writing raw SQL queries. We need to install the necessary package from nuget.org in the integrated terminal. Microsoft.EntityFrameworkCore 8.0.8

dotnet add package Microsoft.EntityFrameworkCore

The package will be added to CosmosPostgresAPI.csproj

In your solution explorer, at the root of your project, create Models folder and add a class name Pharmacy.cs. Add the following code to your class.

Copy the code and paste it in Pharmacy.cs class

Code:

using System; 
namespace CosmosPostgresApi.Models; 
public class Pharmacy 

{ 
    public int PharmacyId { get; set; } 

    public required string PharmacyName { get; set; } 

    public required string City { get; set; } 

    public required string State { get; set; } 

    public int ZipCode { get; set; } 
}

The above code will help map data from the database to the object and vice versa. Entity Framework will use it to create a database table with columns, PharmacyId, PharmacyName, City, State, and ZipCode.

Create another file AppDbContext.cs in the Models folder and add the following code.

Code:

using System; 

using Microsoft.EntityFrameworkCore; 

namespace CosmosPostgresApi.Models; 

public class AppDbContext : DbContext 

{ 
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { } 

    public DbSet<Pharmacy> Pharmacies { get; set; } 

    protected override void OnModelCreating(ModelBuilder modelBuilder) 

    { 

        base.OnModelCreating(modelBuilder); 

        modelBuilder.Entity<Pharmacy>() 

        .ToTable("pharmacies") 

        .HasKey(p => p.PharmacyId); 
    } 

    public async Task DistributeTableAsync() 

    { 

        await Database.ExecuteSqlRawAsync("SELECT create_distributed_table('pharmacies', 'PharmacyId');"); 

    } 
}

This code snippet means that AppDbContext class which is a class that is inherited from the DbContext class.
The AppDbContext class is used to interact with the database and represents a session with the database.
It contains property Pharmacies of type DbSet<Pharmacy> which represents the collection of pharmacies in the database.
The OnModelCreating method is used to configure the entity mappings and relationships in the database. It creates a table named pharmacies and sets the primary key to the PharmacyId property of the Pharmacy class.

DistributeTableAsync method is used to distribute the pharmacies table across the distributed database. Learn more about distribution of Azure Cosmos DB for PostgreSQL

Connecting to Azure Cosmos DB for PostgreSQL

To be able to connect to Azure Cosmos DB for PostgreSQL into our web API, you will be required to create a cluster in Azure Portal. We covered this in our previous blog.
While creating the cluster, do not forget your database name, password and admin User.
Once a cluster is created, navigate to the resource you just created.
I have created a cluster called csharp-postgres-sdk

Let's go back to the Visual Studio code and connect to the created database.

Since we need the credentials to connect to the database we just created, we shall store them in appsettings.json file as a connection string.

Copy this code and paste it in appsettings.json. Replace <uniqueID>, <cluster>, <password> with the correct values.

Code:

"ConnectionStrings": { 
    "CosmosPostgres": "c-<cluster>.<uniqueID>.postgres.cosmos.azure.com; Database = citus; Port = 5432; User Id = citus; Password = <password>; Ssl Mode = Require; Pooling = true; Minimum Pool Size=0; Maximum Pool Size =50;" 
  },

Your connection string should be placed as follows:

We shall install some few packages like PostgreSQL provider and others which will help us generate code for CRUD operations and Migration using Entity Framework.

Use the terminal to install the following packages:

Code:

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL 

dotnet add package Microsoft.VisualStudio.Web.CodeGeneration.Design 

dotnet add package Microsoft.EntityFrameworkCore.Design 

dotnet add package Microsoft.EntityFrameworkCore.SqlServer 

dotnet add package Microsoft.EntityFrameworkCore.Tools 

dotnet tool install -g dotnet-aspnet-codegenerator

To confirm all the packages has been added check ItemGroup.

Before we run migrations lets register the AddDbContext class as a service in the dependency injection container and configure it to use a PostgreSQL database.

Now we can Create a controller, I will use the following code which helps quickly set up a controller with CRUD operations for the Pharmacy model, using asynchronous methods and integrating with the specified database context.

Run the code in your terminal:

dotnet aspnet-codegenerator controller -name PharmacyController -async -api -m Pharmacy -dc AppDbContext -outDir Controllers

You should be able to see the CRUD operations generated in the Controllers folder named PharmacyController.cs

Managing Migrations with Entity Framework Core

Entity Framework Core allows us to generate SQL code directly from our C# objects, providing the advantage of using an Object-Relational Mapper (ORM) to simplify database interactions.

In your terminal run the following command which creates a new migration named “InitialCreate” in your project. This migration will contain the necessary code to create the initial database schema based on your current data model.

dotnet ef migrations add InitialCreate

A new folder Migrations is generated with initial SQL code that will be used to create the table.

To apply the following changes to the database you need to run update command:

dotnet ef database update

Navigate to Azure portal, Quick start (Preview) under your created resource, launch PostgreSQL Shell a command line interface to interact with your database. Enter your password when prompted.

Run this command to see the tables:

\dt

This should be the results you find:

The table pharmacies have been created and we can now perform some CRUD operations.

In your visual studio code, press ctr + F5 to run your code.

The project will launch the swagger in the browser, and you can start testing the endpoints. I will be using Rest Client to test the API. For this, you should have Rest Client extension installed in visual studio code.

To POST a Pharmacy: (Create a Pharmacy)

To GET all a Pharmacies:

To GET single a Pharmacy:

To PUT a Pharmacy (Update)

To DELETE a Pharmacy:

In this blog, we’ve successfully demonstrated how to persist data in Azure Cosmos DB for PostgreSQL. I hope you found the steps clear and easy to follow. Thank you for reading, and happy coding!

Connect Azure Cosmos DB for PostgreSQL to your ASP.NET Core application.

Brian_Kemboi

​

Topics Covered​

Prerequisites​

Creating an ASP.NET Core Web Application​

Connecting to Azure Cosmos DB for PostgreSQL​

Managing Migrations with Entity Framework Core​

Read more​

Topics Covered

Prerequisites

Creating an ASP.NET Core Web Application

Connecting to Azure Cosmos DB for PostgreSQL

Managing Migrations with Entity Framework Core

Read more