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.
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.
