Connect a .NET app to an external PostgreSQL database

PostgreSQL databases tend to be robust and reliable. They also work well with .NET applications like Pedal, a sample bike e-commerce app.

As the Pedal app shifts from monolithic to a microservices-based application, connecting to an external database helps make managing data more efficient and reliable. The components are loosely coupled, making it more straightforward to organize, secure the data, and scale the database as needed.

This tutorial guides you through setting up the microservice to talk to PostgreSQL, handle data correctly, and maintain optimal performance. You’ll learn about networking, managing credentials securely, configuring data access, and ensuring that all components communicate smoothly.

Examining the .NET microservice

Pedal was originally a Java application. However, one of its microservices, bike-service, is now a .NET microservice. Each Bike entry has the following main code for the Bike model:

using System;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;


namespace RedHatPedalBike.Models

{

    [Table("bikes")]

    public class Bike

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public long Id { get; set; }


        [Required(ErrorMessage = "Bike's name cannot be empty.")]

        public string Name { get; set; } = "";


        [Required(ErrorMessage = "Bike's model cannot be empty.")]

        public string Model { get; set; } = "";


        [Column("date_created")]

        public DateTime DateCreated { get; set; }


        [Required(ErrorMessage = "Bike's price cannot be empty.")]

        public int Price { get; set; }


        public byte[] Image { get; set; }


        [Required(ErrorMessage = "Bike's warranty status cannot be empty.")]

        [Column("warranty_status")]

        public string WarrantyStatus { get; set; } = "";

    }

}

This code represents a bike in Pedal’s system, with properties like Name, Model, Price, and WarrantyStatus. It’s a classic .NET entity model designed to work seamlessly with a database.

This model forms the basis of the Web API in ASP.NET Core and the connected PostgreSQL web service. Clients can use its endpoints to access and manipulate database data, applying create, read, update, and delete (CRUD) operations

We deployed the microservice to Red Hat OpenShift using the Source-to-Image (S2I) method, reproducing a ready-to-run source code image. Figure 1 shows the running microservice.

The .NET microservice shown in the OpenShift console. — Figure 1: The .NET microservice.

Prerequisites

This tutorial requires the following:

A fundamental grasp of Microsoft .NET development
Familiarity with PostgreSQL basics
Free tier access to PostgreSQL as a service
A Developer Sandbox for Red Hat OpenShift trial
The OpenShift command-line interface (CLI)
Visual Studio or Visual Studio Code for development. These instructions use Visual Studio.
.NET 7 SDK
A GitHub account

Understanding the external PostgreSQL environment

When connecting your .NET microservice to an external database, it’s important to understand the database setup. This tutorial uses PostgreSQL as a service to avoid the hassle of installing and maintaining a database, including applying backups and updates. This approach lets you focus less on the infrastructure and more on coding

Key considerations when connecting .NET apps to a PostgreSQL as a service include:

Ensuring a secure connection through Secure Sockets Layer and Transport Layer Security (SSL/TLS)
Configuring firewalls and networks for accessibility
Considering the database server’s location for data transfer speed
Correctly setting up the connection string in the .NET app

The optimal setup facilitates a smooth, secure, and scalable database connection for .NET microservices.

Configuring network access in .NET on Red Hat OpenShift

Connecting your OpenShift-deployed .NET application to an external PostgreSQL database requires a few vital steps.

A .NET application runs in a containerized environment in OpenShift, adding a layer of networking considerations. OpenShift’s platform helps you manage these containers and their network interactions.

You can employ the Npgsql library to set up network access for your application to communicate with the PostgreSQL database. Use the NuGet package manager to integrate the Npgsql library into your .NET project.

Next, craft the connection string. It provides all the details to connect your .NET application to the PostgreSQL server, like the example below:

string connectionString = "Host=my_host;Username=my_user;Password=my_pw;Database=my_db";

Replace my_host, my_user, my_pw, and my_db with the actual details of your PostgreSQL server.

Running an application in a containerized environment requires using secure connections (like SSL/TLS) and managing resources efficiently. OpenShift’s built-in tools route and service configuration help establish secure network communication.

Establishing secure credential management in .NET

To ensure your application’s security, avoid hardcoding database credentials in the source code. Instead, use environment variables or configuration files not stored in version control.

You can use the Secret Manager tool to create secrets on your local machine. Execute the following command on your local terminal:

dotnet user-secrets set postgres-username replace-with-your-postgres-username
dotnet user-secrets set postgres-password replace-with-your-postgres-password

You can use the ConfigurationManager class in .NET or the IConfiguration interface in .NET Core to access these values, like in the following code:

var configuration = builder.Configuration;

var username = configuration["postgres-username"];

var password = configuration["postgres-password"];


// Replace placeholders in the connection string with actual environment variables

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection")

    .Replace("{USERNAME}", username)

    .Replace("{PASSWORD}", password);

The current approach stores sensitive information in a local machine folder, but you can use OpenShift’s secret management capability instead. Create a YAML file like the following:

apiVersion: "v1"

kind: "Secret"

metadata:

  name: "mysecret"

stringData:

  mysecretconfig: '{"postgres-username": "wllyumrq",

  "postgres-password": "your-postgres-password"}'

Then, use an oc command similar to the one below to create the secret:

oc create -f mysecret.yaml

Setting up .NET data access for PostgreSQL

Establishing a robust data access layer is fundamental when integrating a .NET application with an external PostgreSQL database. This method involves configuring connection strings and selecting appropriate data providers.

A connection string contains information required to establish a connection to the database. In .NET, you typically define it in a configuration file (like appsettings.json in .NET Core).

Below is an example of a PostgreSQL connection string:

"ConnectionStrings": { "DefaultConnection": "Host=pom.db.elephantsql.com;Port=5432;Database=wllyumrq;Username={USERNAME};Password={PASSWORD};Trust Server Certificate=true;" }

The Entity Framework (EF) Core object-relational mapping (ORM) tool enables .NET developers to use .NET objects to work with a database, eliminating most data-access code. Follow the steps below to set it up.

Create a model

Define your data models in C# classes, like the earlier Bike model.

Use DbContext

Create a context class inheriting from DbContext. Configure it to use Npgsql with PostgreSQL, like below:

public class BikedbContext : DbContext

    {

        public BikedbContext(DbContextOptions<BikedbContext> options)

            : base(options)

        {

        }

        public virtual DbSet<Bike> Bike { get; set; }

    }

Manage migrations

EF Core’s migrations help manage database schema changes. Use the following commands to migrate and update your database:

Add-Migration InitCommit

Update-Database

Implementing the database connection

To implement a database connection in a .NET 7 application, you’ll configure the connection string using EF Core or another ORM for data manipulation and add health checks to monitor the PostgreSQL database.

The following Bike model shows a DbContext class in .NET 7:

using Microsoft.EntityFrameworkCore;

using RedHatPedalBike.Models;


public class BikedbContext: DbContext

{

    public DbSet<Bike> Bikes { get; set; }


    public BikedbContext(DbContextOptions<BikedbContext> options)

        : base(options)

    {

    }


    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        modelBuilder.Entity<Bike>().ToTable("bikes");

    }

}

To integrate the ORM with your .NET microservice, register the DbContext in .NET Core’s dependency injection container. Typically, you do this in the Program.cs file using code like the following:

var builder = WebApplication.CreateBuilder(args);


// Add services to the container.

builder.Services.AddDbContext<PedalBikeContext>(options =>

    options.UseNpgsql(builder.Configuration.GetConnectionString("PedalBikeConnection")));


var app = builder.Build();


// The rest of the code, including the snippets already presented in this article


app.Run();

.NET 7 enables adding health checks to monitor the availability and performance of PostgreSQL databases. Add the following code to Program.cs for the health check service:

// Add services to the container.

builder.Services.AddHealthChecks()

    .AddNpgSql(connectionString);


app.UseEndpoints(endpoints =>

{

    // Map health checks endpoint

    endpoints.MapHealthChecks("/health");

});

This code creates a /health endpoint. When you access the endpoint, it runs health checks and returns the health statuses of the application and database connection.

Ensuring secure communication

You can secure PostgreSQL data transmission in .NET 7 by encrypting database connection strings and using SSL/TLS. For example, add the following code to the Program.cs file to set up the data protection services:

   var builder = WebApplication.CreateBuilder(args);

   builder.Services.AddDataProtection();

   // Other services configuration...

Use the following code to encrypt your stored connection string and decrypt it for use:

   var protector = serviceProvider.GetDataProtector("ConnectionStrings:Protect");

   var encryptedConnectionString = protector.Protect("your-connection-string");

   var decryptedConnectionString = protector.Unprotect(encryptedConnectionString);

You can store encryptedConnectionString in your configuration and use decryptedConnectionString to connect to the database.

SSL/TLS is vital to securely transfer data between a .NET application and a PostgreSQL database, necessitating SSL configuration on both sides. To enable SSL connections in PostgreSQL, on the server side, set the ssl option to on and provide a valid SSL certificate.

To enable SSL in the .NET connection string, modify the string to require SSL as follows:

"ConnectionStrings": {

       "PostgreSqlConnection": "Host=myhostname;Port=5432;Database=mydbname;Username=myusername;Password=mypassword;SSL Mode=Require;Trust Server Certificate=true"

Then, validate the server’s SSL certificate during the SSL handshake to ensure secure .NET-to-PostgreSQL connections.

Verifying the connectivity

Next, ensure functionality by verifying the .NET 7 application’s connection to the PostgreSQL database. Use the configured connection string to conduct a basic test:

using var context = new PedalBikeContext();

try

{

    context.Database.OpenConnection();

    Console.WriteLine("Connection successful.");

}

catch (Exception ex)

{

    Console.WriteLine($"Connection failed: {ex.Message}");

}

finally

{

    context.Database.CloseConnection();

}

After establishing basic connectivity, run a simple query like the following:

using var context = new PedalBikeContext();

try

{

    var bikeCount = context.Bikes.Count();

    Console.WriteLine($"Number of bikes in the database: {bikeCount}");

}

catch (Exception ex)

{

    Console.WriteLine($"Query failed: {ex.Message}");

}

The code snippet uses PedalBikeContext to establish a database connection, counts the number of bike entries in the Bikes table, and prints the count (or an error message if the query fails).

Deploying and building the application on OpenShift

When you deploy the .NET Pedal microservice to Red Hat OpenShift, use the S2I framework to streamline the build process. This handy build tool automatically converts the application’s source code into a deployable container image using the .NET 7 runtime. OpenShift uses the configuration in the source repository and the .NET 7 S2I builder image to efficiently handle the build and deployment, fetching the code and packaging it into a container image.

You can use a command to build the application as follows.

oc new-app dotnet:7.0~https://github.com/your_project -e DOTNET_STARTUP_PROJECT=your_project.csproj -e DOTNET_ASSEMBLY_NAME=your_project.csproj

Conclusion

You’ve now walked through the essential steps to connect a .NET 7 application to an external PostgreSQL database. You learned how to configure network access in OpenShift, emphasizing secure connections and correct network policies, and then established secure credential management.

Next, you configured .NET data access for PostgreSQL using Entity Framework, implemented the database connection by integrating an ORM tool with the .NET microservice, and set up health checks. You now know how to secure communication using encrypted connection strings and SSL/TLS and verify the connectivity, ensuring the application can communicate effectively with the database.

Security and efficient data access play a critical role in this microservices architecture, enhancing .NET application performance and reliability in a modern cloud-native environment.

You’re now prepared to adapt and extend these practices within your .NET applications. This ensures more robust, secure, and scalable solutions, aligning with the evolving demands of enterprise-level application development. Next, consider exploring other resources for working with .NET and Red Hat.

Connect a .NET app to an external PostgreSQL database

Share:

Examining the .NET microservice

Prerequisites

Understanding the external PostgreSQL environment

Configuring network access in .NET on Red Hat OpenShift

Establishing secure credential management in .NET

Setting up .NET data access for PostgreSQL

Create a model

Use DbContext

Manage migrations

Implementing the database connection

Ensuring secure communication

Verifying the connectivity

Deploying and building the application on OpenShift

Conclusion

Products

Build

Quicklinks

Communicate

RED HAT DEVELOPER

Red Hat legal and privacy links

Red Hat legal and privacy links

Report a website issue