Software project: Creating a real-life transactional microservices application on Kubernetes

Part 2: Setting up database access

In the Part 1 we did the local development environment setup for my Helidon 4 Gymapp microservices project. Now it is the time to continue the project by setting up the database access for it that is an essential part of any microservice typically.

Access for Oracle database from Helidon microservices can be set up in several ways. I’m presenting a couple of options that I’ve used successfully. In your own project you can choose either one or maybe use some other type of configuration that fits you better.

The first option is to run an Oracle Base database service from OCI. It does not require a database wallet and the Helidon setup is easy to do. Just create the Oracle Base Database DB System on a VCN public subnet, open the VCN subnet network traffic for the port 1521 and then setup the database connection in the Helidon configuration using the public IP address assigned by OCI for the DB System created.

For example, to a DB System named “Gymapp” and database name “gymappdb” the Helidon config for “gymusers” datasource would be as follows where IP address is the public IP (141.144.16.22 in below) of the DB System Node and sub09121022341.publicvcn.oraclevcn.com is the Host domain name of the DB System:

javax.sql.DataSource.gyumsers.dataSourceClassName=oracle.jdbc.pool.OracleDataSource
javax.sql.DataSource.gyumsers.dataSource.url=jdbc:oracle:thin:@141.144.16.22:1521/gymappdb_pdb1.sub09121022341.publicvcn.oraclevcn.com
javax.sql.DataSource.gyumsers.dataSource.user=system
javax.sql.DataSource.gyumsers.dataSource.password=DevIsCool456##

This configuration is saved in the Helidon starter generated src/main/resources/META-INF/microprofile-config.properties file for both of the microservices projects, Gymuser and Gyminstructor, of the Gymapp.

To add the database schema and some test data for example Oracle SQL*Plus can be used that is preinstalled on the Developer VM image to access the database from the VM shell:

sqlplus system@141.144.16.22:1521/gymappdb_pdb1.sub09121022341.publicvcn.oraclevcn.com

To make the database access secure, which is always a good idea, creating the database to a VCN private subnet (instead of public) and using port forwarding over ssh with a jump host or OCI Bastion to access the database can be used.

The second option is to run the Oracle database as a Docker container in my Helidon Dev. To do this the free XE database Docker container image can be pulled and run from Oracle container registry. Besides using this locally I’m using this option with Kubernetes and will cover this once we get to the Kubernetes part.

I’m doing development on Mac with Apple chip so I cannot run the XE container locally. Therefore I run it on an always-free OCI compute VM that I have provisioned from an OCI Linux 8 Developer Image that has many of the common developer tools preinstalled.

After logging into this developer VM (for this you can use plain ssh or OCI Bastion depending on how securely you want to set it up) I can run the XE image by:

docker run --name oracledb -d -p 1521:1521 -e ORACLE_PWD=DevIsCool456## container-registry.oracle.com/database/express:latest

The second option is to run the Oracle database as a Docker container Helidon Dev. To do this the free XE database Docker container image can be pulled and run from Oracle container registry.

For the Gymapp I’m not actually running the vanilla XE container image. Instead, made a custom image of it to have the Gymapp schema and some test data built-in to the image so that the databases are properly set when I run the dev environment. The alternative would be running database setups like the schema and data from the Helidon source code but I prefer this pre-built container image option for the Gymapp microservices.

To create a custom container image here’s the Docker file I’m using:

FROM container-registry.oracle.com/database/express:21.3.0-xe
EXPOSE 1521
COPY scripts/init_script.sql /opt/oracle/scripts/startup

The init SQL script contains the database schema and some test data (SQL insert statements) for both of the Gymapp microservices, Gymuser and Gyminstructor, and is run whenever the container is started up.

For the Gymapp I’m not actually running the vanilla XE container image. Instead, made a custom image of it to have the Gymapp schema and some test data built-in to the image so that the databases are properly set when I run the dev environment.

Here are the init_script.sql SQL scripts for the Gymuser and Gyminstructor DB container image:

create table gymuser (
  name varchar2(200),
  email varchar2(200)
);

create table myclass (
  id number(38) NOT NULL,
  useremail varchar2(200),
  instructoremail varchar2(200),
  name varchar2(200),
  info varchar2(2000),
  price number(5,2), 
  status number(2),
  lra varchar2(200),
  payment varchar2(3000),
  selfcancelled number(2),
  paymentrefund varchar2(3000)
);

create sequence myclass_id_seq
  increment BY 1
  start with 10
  minvalue 1
  maxvalue 10000000
  cycle
  cache 2;

create table instructorclass (
  id number(38),
  instructoremail varchar2(200),
  name varchar2(200),
  info varchar2(2000),
  price number(5,2),
  status number(2)
);

create sequence instructorclass_id_seq
  increment BY 1
  start with 10
  minvalue 1
  maxvalue 10000000
  cycle
  cache 2;

create table signup (
  id number(38),
  instructorclassid number(38),
  useremail varchar2(200),
  status number(2),
  lra varchar2(200)
);

create sequence signup_id_seq
  increment BY 1
  start with 10
  minvalue 1
  maxvalue 10000000
  cycle
  cache 2;

insert into instructorclass (id, instructoremail, name, info, status, price) values (1, 'mika.rinne@oracle.com', 'Body', '1.3.24 7pm Instructor led body pump 2e', 1, 2);
insert into instructorclass (id, instructoremail, name, info, status, price) values (2, 'mika.rinne@oracle.com', 'Yoga', '15.1.24 9pm Balance training 1e Only!', 1, 1);
insert into instructorclass (id, instructoremail, name, info, status, price) values (3, 'mika.rinne@oracle.com', 'Aerobic', '10.2.24 8am 80s hard core 5e', 1, 5);

After building these custom images for the Gymuser and Gyminstructor services using docker build command, I created OCI Registry repositories into my personal compartment in OCI, logged into it using docker login from the VM shell, tagged the containers using docker tag and then pushed them to their repositories for future use using docker push, although only for the local use we don’t need the OCI Registry just yet (this will become later with the Kubernetes setup).

The tagged names for the DB container images in my case (using OCIR in the Frankfurt region) are:

fra.ocir.io/<tenancy namespace>/gym-db-xe:latest
fra.ocir.io/<tenancy namespace>/gym-ins-db-xe:latest

After pushing images are visible in the OCI Registry:

Gymapp microservices custom XE database container images in OCIR

I run the following command on VM shell to start up the Gymuser DB container image (where the <tenancy namespace> is my OCI tenancy namespace):

docker run --name oracledb -d -p 1522:1521 -e ORACLE_PWD=DevIsCool456## fra.ocir.io/<tenancy namespace>/gym-db-xe:latest

To connect to the Docker XE database from microservices the following configuration is saved in the Helidon starter generated src/main/resources/META-INF/microprofile-config.properties file of the Gymapp projects, where IP address (130.66.229.222 in below) is the public IP of the OCI developer VM host running the container:

javax.sql.DataSource.gyumsers.dataSourceClassName=oracle.jdbc.pool.OracleDataSource
javax.sql.DataSource.gyumsers.dataSource.url=jdbc:oracle:thin:@130.66.229.222:1521:XE
javax.sql.DataSource.gyumsers.dataSource.user=sys as sysdba
javax.sql.DataSource.gyumsers.dataSource.password=DevIsCool456##

This will create “gymusers” datasource for the Gymuser microservice. Similarly we need add the datasource “gyminstructors” for the Gyminstructors microservice.

Just remember to allow outside traffic to the VM by opening up the subnet security rule and the VM firewall for the TCP port 1521.

Gymapp microservices use JPA Hibernate persistence and object relational mapping so I modified the Helidon starter generated for the Gymuser microservice src/main/resources/META-INF/persistence.xml as below by adding my 2 resource Java classes, Gymuser.java and Myclass.java, to it:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_1.xsd"
             version="3.1">

    <persistence-unit name="gymusers" transaction-type="JTA">
        <jta-data-source>gyumsers</jta-data-source>
        <class>com.oracle.microtx.sample.lra.gym.Gymuser</class>
        <class>com.oracle.microtx.sample.lra.gym.Myclass</class>
    </persistence-unit>
</persistence>

Similarly, the Gyminstructor microservice has the configuration below in src/main/resources/META-INF/persistence.xml file having added my 3 resource Java classes, Instructorclass, Gyminstructor.java and ClassAttendance.java, to it:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_1.xsd"
             version="3.1">

    <persistence-unit name="gyminstructors" transaction-type="JTA">
        <jta-data-source>gyminstructors</jta-data-source>
        <class>com.oracle.microtx.sample.lra.gym.Instructorclass</class>
        <class>com.oracle.microtx.sample.lra.gym.Gyminstructor</class>
        <class>com.oracle.microtx.sample.lra.gym.ClassAttendance</class>
    </persistence-unit>
</persistence>

We will cover the development of these resource classes in the Part 4 but first we cover setting up the LRA coordinator in Part 3.