Pedal is a simple e-commerce application to sell bikes, manage employees, and control inventory. The Java application uses the Quarkus microservice framework and Spring to serve a web front end and a representational state transfer (REST) service. It also connects to a PostgreSQL database and SQL Server database.
Hosting this Quarkus application’s PostgreSQL database externally provides several advantages, including automated backups, robust data persistence, extensive maintenance tools, and more straightforward scaling. Using external databases also reduces the operational burden on your application’s development team.
This guide helps you integrate Pedal’s external PostgreSQL database with one of its Quarkus microservices deployed on Red Hat OpenShift.
Overview of the external PostgreSQL setup
When connecting an OpenShift application to an external database, consider the following:
- Network latency: Your PostgreSQL database’s location could increase latency in your APIs. For example, if you run a service on OpenShift with the Azure cloud platform in West Central 2 and your on-premise PostgreSQL database is in Australia, your API latency will be significant.
- Network bandwidth: Like network latency, your connection’s bandwidth can vary depending on the database’s location. Low-bandwidth connections between your OpenShift service and the PostgreSQL database could impact how you structure database transactions within your code.
- Firewall rules: Connecting an OpenShift service to an on-premise PostgreSQL database requires adjusting your firewall. To keep the database connections as secure as possible, only allow port forwarding from your OpenShift cluster’s node IPs.
In this tutorial, assume that your on-premise PostgreSQL database is accessible through a public hostname like postgres.example.com.
Access the external PostgreSQL database from OpenShift
Before attempting to connect to the external PostgreSQL database, remember to check for any preexisting NetworkPolicy objects that could hinder communication between your OpenShift pods and the external service. Administrators may have set unmodifiable default network policies that might affect how your environment manages network traffic. After ensuring these policies do not obstruct your connection, you can enable communication using one of the two following methods.
Method 1: Service object and type
For one, you can use a Service object with the type ExternalName. OpenShift creates a CNAME record for a fully qualified domain name that the specification describes. This approach makes the external PostgreSQL database accessible through its host.
The following example service object for Pedal’s external PostgreSQL database is located in a service.yml file:
kind: "Service"
apiVersion: "v1"
metadata:
name: "pedal-postgres-service"
spec:
type: ExternalName
externalName: postgres.example.com
selector: {}Unlike most service object configurations, this one doesn’t require you to specify any port bindings. To create this service, run the following command:
oc create -f service.ymlMethod 2: Endpoints and service objects
Alternatively, you can create Endpoints and Service objects. OpenShift provides the Endpoints object to let services communicate with one or more IP addresses.
Specifying multiple addresses makes your database connections more robust. For example, if one IP address becomes unavailable due to a networking issue, the application can still use the others.
The Pedal project might have an Endpoints manifest like the following:
kind: "Endpoints"
apiVersion: "v1"
metadata:
name: "pedal-postgres-service"
subsets:
- addresses:
- ip: "172.45.3.2"
ports:
- name: "postgres"
port: 5432
- ip: "172.45.3.3"
ports:
- name: "postgres"
port: 5432
[Click and drag to move]
To communicate with these endpoints, the Pedal application requires a Service object corresponding to the Endpoints object’s name above. This object’s manifest might look like the following:
kind: "Service"
apiVersion: "v1"
metadata:
name: "pedal-postgres-service"
spec:
ports:
-
name: "postgres"
protocol: "TCP"
port: 5432
targetPort: 5432
nodePort: 0
selector: {}Since there are no pods exposed with this service, the selector object doesn’t require expansion. Note that the Service and Endpoints objects synchronize via their name, postgres.
Run the following command to create these objects:
oc apply -f endpoints.yml,service.ymlTest communication
For either networking setup, to verify that your pods can communicate with the external service hosted at pedal-postgres-service.<namespace>.svc.cluster.local, ping it from a running container in your cluster.
Configure database credentials securely
Safeguarding your PostgreSQL credentials is vital for maintaining your database’s overall security. The OpenShift Secret object type provides a straightforward means of credential management.
Additionally, if you use external secret management systems like HashiCorp Vault, OpenShift supports the ExternalSecret object, letting you integrate and expose secrets from external sources directly into your OpenShift environment. This integration is particularly beneficial for Vault users, as it provides a seamless way to manage and use externally stored secrets. For more information on using External Secrets with systems like Vault, refer to Red Hat’s guide on external secrets.
To create a Secret object to securely hold Pedal’s database credentials, create a secret.yml file and add the following code:
apiVersion: v1
kind: Secret
metadata:
name: pedal-postgres-credentials
data:
POSTGRES_PASSWORD: QVZOU19mSkpTVDVPS2NlSWEwS29rdzBzCg==
POSTGRES_USER: cG9zdGdyZXMtdXNlcgo=All the data object’s values must be Base64-encoded. For example, to quickly encode your database username, run the following command:
$ echo "postgres-user" | base64
cG9zdGdyZXMtdXNlcgo=Run the following command to create the manifest described above:
$ oc apply -f secret.ymlThe Quarkus OpenShift extension provides configuration operations to access OpenShift Secrets in your Quarkus project. First, run the following command to install the OpenShift extension in your Quarkus project.
$ quarkus extension add openshiftAlternatively, you can add the quarkus-openshift component when you start a new project. From the Quarkus project setup website, simply search for “openshift” in the setup interface and select the corresponding checkbox to include the OpenShift extension. This way, you can easily incorporate OpenShift capabilities from the very beginning of your project development.
Add the following line to your project’s application.properties file to ingest the secret:
quarkus.openshift.env.secrets=pedal-postgres-credentialsOnce you build the Quarkus application, the command adds the following specifications to its OpenShift container definition:
envFrom:
- secretRef:
name: pedal-postgres-credentials
optional: falseThe POSTGRES_PASSWORD and POSTGRES_USER values are available to the application as environment variables at runtime.
Configure Quarkus DataSource for an external PostgreSQL database
Next, add the following lines to Pedal’s application.properties file to provide the correct external host, database user, and password:
quarkus.datasource.customers.jdbc.url=jdbc:postgresql://[postgresql address]
quarkus.datasource.customers.username=${POSTGRES_USER}
quarkus.datasource.customers.password=${POSTGRES_PASSWORD}Note that the ${} syntax specifies environment variables.
To use different data sources for the production and development Quarkus profiles, specify the property with a %dev or %prod prefix. For example, if your database host’s development profile is on localhost, adjust the application.properties file to look like the following:
%prod.quarkus.datasource.customers.jdbc.url=jdbc:postgresql://[postgresql prod address]
%dev.quarkus.datasource.customers.jdbc.url=jdbc:postgresql://[postgresql dev address]The MicroProfile Config API offers the flexibility to inject configuration values in your Java code from the application.properties file or programmatically access them. For example, add the following code to your Java class to inject the value of configuration.property defined in the application.properties file:
import org.eclipse.microprofile.config.inject.ConfigProperty;
…
@ConfigProperty(name = "configuration.property")
String value;Similarly, add the following code to your Java class to programmatically access the same configuration.property value:
import org.eclipse.microprofile.config.ConfigProvider;
…
String value = ConfigProvider.getConfig().getValue("configuration.property", String.class);Establish the database connection
Incorporate ORM
Quarkus supports Hibernate ORM, an object-relational mapping (ORM) library to seamlessly translate Java objects into relational database models. Run the following command to install the Hibernate ORM in your Quarkus project.
$ quarkus ext add hibernate-ormYou should receive a response like the following:
[SUCCESS] ✅ Extension io.quarkus:quarkus-hibernate-orm has been installedSimply use the @Entity annotation above the class name to define a class to map to a PostgreSQL model. For example, the following code uses the Hibernate ORM to define a Pedal customer:
import jakarta.persistence.*;
import java.util.Date;
import io.quarkus.hibernate.orm.panache.PanacheEntity;
@Entity
@Table(name="service_user")
public class Customer extends PanacheEntity {
public Customer(){}
private String fullname;
private String username;
private String email;
private Date date_created;
private String password;
private String user_role;
}The code uses Hibernate’s Panache library to simplify entity definitions. You don’t have to explicitly define the database model’s column names for classes that extend the PanacheEntity abstract class. Hibernate also helps initialize your PostgreSQL database when the application boots.
To generate the tables that your Hibernate entities describe, set the following property in your application.properties file:
quarkus.hibernate-orm.database.generation=createNote that for sequential application boots, this operation fails since the tables already exist.
The generation property also supports the none, create, drop-and-create, drop, update, and validate values.
When the application boots, Hibernate runs arbitrary SQL commands defined in src/main/resources/import.sql. This approach lets you quickly initialize your local development PostgreSQL database.
Implement health checks
To ensure that the Pedal application runs well, it’s important to verify that the database connection is healthy at runtime. The SmallRye Health Quarkus extension creates an endpoint at /q/health that reports the status of the database connection. Execute the following command to add this endpoint to your Quarkus project:
$ quarkus extension add quarkus-smallrye-healthThe response should look like this:
[SUCCESS] ✅ Extension io.quarkus:quarkus-smallrye-health has been installedNow, you can call the API while running the development Quarkus application using the code below.
$ curl localhost:8080/q/health
{
"status": "UP",
"checks": [
{
"name": "Database connections health check",
"status": "UP",
"data": {
"<default>": "UP"
}
}
]
}Additionally, you can specify an alternative API path. Set the quarkus.smallrye-health.root-path property in the application.properties file.
Secure the connection
If your database’s SSL certification is signed by a trusted certificate authority (CA), you can quickly enable Secure Sockets Layer and Transport Layer Security (SSL/TLS) for your database connection. Append ?sslmode=require to the end of the quarkus.datasource.jdbc.url property, like below:
quarkus.datasource.customers.jdbc.url=jdbc:postgresql:[postgresql url]Test the connectivity
To rebuild and deploy your Quarkus application rapidly, execute the following command:
$ quarkus build -Dquarkus.kubernetes.deploy=trueQuarkus version 3 offers a new, streamlined command as a more Quarkus-centric means of handling deployments. It’s also a simpler process than using the JVM -D options. To use this new feature, execute:
$ quarkus deploy openshiftRegardless of which method you use, you can then run the following command to confirm that the Pedal application’s pods have started:
$ oc get podsYou should receive results similar to this:
pedal-32-dqn7r 1/1 Running 0 24m
pedal-34-deploy 0/1 Error 0 18mTo verify that the application works correctly with an external database, call the signup API with a new user’s data, like below:
$ curl -I --location 'localhost:8080/signup' \
--header 'Content-Type: application/json' \
--data-raw '{
"email": "[email protected]",
"username": "user",
"fullname": "user",
"user_role": "customer",
"password": "password"
}'
HTTP/1.1 200 OKSummary
When networking an external PostgreSQL database for an OpenShift service, you have two standard options: using a Service object with an ExternalName type or creating Endpoints and Service objects.
Then, use OpenShift secrets to secure your database credentials and protect your connections. Quarkus seamlessly integrates OpenShift secrets specified in the application.properties file.
The Hibernate ORM quickly sets up database models defined in Java classes and initializes your PostgreSQL database. Plus, the Quarkus SmallRye Health extension adds a simple health check endpoint to validate your Quarkus application’s connectivity with the PostgreSQL database. To fortify this connection further, configure your Quarkus data source to use SSL for database connections.
With these steps, you have a solid understanding of how to establish connectivity between your Quarkus application and an external PostgreSQL database. Next, learn more about developing with Quarkus in OpenShift.
