mysql

According to Wikipedia, Software multitenancy is a software architecture in which a single instance of software runs on a server and serves multiple tenants. But for this sample we are trying to create a full multi-tenancy software, while isolating each user in a different namespace and a different database to optimize resource utilisation and make operation much easier.

The high level implementation would be like below image,

Based on above diagram, we are creating several Openshift Namespaces where we will install the same application, which is built with Quarkus, and a mysql database while having Flyway to maintain the database schema among different databases instances.

So lets start with a simple pom file,

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.edw.example</groupId>
    <artifactId>quarkus-flyway</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <compiler-plugin.version>3.11.0</compiler-plugin.version>
        <maven.compiler.release>11</maven.compiler.release>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <quarkus.platform.artifact-id>quarkus-bom</quarkus.platform.artifact-id>
        <quarkus.platform.group-id>io.quarkus.platform</quarkus.platform.group-id>
        <quarkus.platform.version>3.4.1</quarkus.platform.version>
        <skipITs>true</skipITs>
        <surefire-plugin.version>3.1.2</surefire-plugin.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>${quarkus.platform.group-id}</groupId>
                <artifactId>${quarkus.platform.artifact-id}</artifactId>
                <version>${quarkus.platform.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-arc</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-resteasy-jackson</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-resteasy</artifactId>
        </dependency>

        <!-- Hibernate ORM specific dependencies -->
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-hibernate-orm-panache</artifactId>
        </dependency>
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-hibernate-orm</artifactId>
        </dependency>

        <!-- Flyway specific dependencies -->
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-flyway</artifactId>
        </dependency>

        <!-- Flyway MariaDB/MySQL specific dependencies -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-mysql</artifactId>
        </dependency>

        <!-- JDBC driver dependencies -->
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-jdbc-mysql</artifactId>
        </dependency>

        <!-- monitoring-->
        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-smallrye-health</artifactId>
        </dependency>

        <dependency>
            <groupId>io.quarkus</groupId>
            <artifactId>quarkus-junit5</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>io.rest-assured</groupId>
            <artifactId>rest-assured</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>${quarkus.platform.group-id}</groupId>
                <artifactId>quarkus-maven-plugin</artifactId>
                <version>${quarkus.platform.version}</version>
                <extensions>true</extensions>
                <executions>
                    <execution>
                        <goals>
                            <goal>build</goal>
                            <goal>generate-code</goal>
                            <goal>generate-code-tests</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>${compiler-plugin.version}</version>
                <configuration>
                    <compilerArgs>
                        <arg>-parameters</arg>
                    </compilerArgs>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>${surefire-plugin.version}</version>
                <configuration>
                    <systemPropertyVariables>
                        <java.util.logging.manager>org.jboss.logmanager.LogManager</java.util.logging.manager>
                        <maven.home>${maven.home}</maven.home>
                    </systemPropertyVariables>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-failsafe-plugin</artifactId>
                <version>${surefire-plugin.version}</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>integration-test</goal>
                            <goal>verify</goal>
                        </goals>
                        <configuration>
                            <systemPropertyVariables>
                                <native.image.path>${project.build.directory}/${project.build.finalName}-runner</native.image.path>
                                <java.util.logging.manager>org.jboss.logmanager.LogManager</java.util.logging.manager>
                                <maven.home>${maven.home}</maven.home>
                            </systemPropertyVariables>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
    <profiles>
        <profile>
            <id>native</id>
            <activation>
                <property>
                    <name>native</name>
                </property>
            </activation>
            <properties>
                <skipITs>false</skipITs>
                <quarkus.package.type>native</quarkus.package.type>
            </properties>
        </profile>
    </profiles>

</project>

and properties file, in here we set database connection as parameterized to accomodate a multiple database integration for each tenant.

# default
quarkus.http.port=8080
quarkus.log.level=INFO
quarkus.log.category."com.edw".level=DEBUG

# disable sending anonymous statistics
quarkus.analytics.disabled=true

# datasource
quarkus.datasource.db-kind = mysql
quarkus.datasource.jdbc.max-size=20
quarkus.datasource.jdbc.min-size=2
quarkus.datasource.jdbc.initial-size=5
quarkus.datasource.jdbc.background-validation-interval=15S
quarkus.datasource.jdbc.validation-query-sql=select 1;

quarkus.datasource.username = ${DB_USER}
quarkus.datasource.password = ${DB_PASSWORD}
quarkus.datasource.jdbc.url = ${DB_URL}

# Run Flyway migrations automatically
quarkus.flyway.migrate-at-start=true
quarkus.flyway.baseline-on-migrate=true
quarkus.flyway.validate-on-migrate=true
quarkus.flyway.default-schema=db_test

after that, lets add Entity, Service, and Controller class

package com.edw.example.entity;

import io.quarkus.hibernate.orm.panache.PanacheEntityBase;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity(name = "T_STUDENT")
public class Student extends PanacheEntityBase {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String nama;
    private Integer age;

    public Student() {
    }

    public Student(Long id, String nama, Integer age) {
        this.id = id;
        this.nama = nama;
        this.age = age;
    }

    public Long getId() {
        return id;
    }

    // other setter and getter
}

package com.edw.example.service;

import com.edw.example.entity.Student;
import io.quarkus.panache.common.Page;
import io.quarkus.panache.common.Sort;
import jakarta.enterprise.context.ApplicationScoped;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

@ApplicationScoped
public class StudentService {

    private Logger logger = LoggerFactory.getLogger(this.getClass().getName());

    public List<Student> getAll() {
        logger.debug(String.format("getting all table result"));
        return Student.findAll(Sort.by("id", Sort.Direction.Descending))
                .page(Page.ofSize(5))
                .list();
    }
}

package com.edw.example.controller;

import com.edw.example.service.StudentService;
import jakarta.inject.Inject;
import jakarta.ws.rs.Consumes;
import jakarta.ws.rs.GET;
import jakarta.ws.rs.Path;
import jakarta.ws.rs.Produces;
import jakarta.ws.rs.core.MediaType;
import jakarta.ws.rs.core.Response;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.HashMap;

@Path("/student")
public class StudentController {

    private Logger logger = LoggerFactory.getLogger(this.getClass().getName());

    @Inject
    StudentService studentService;

    @GET
    @Path("/get-all")
    @Produces(MediaType.APPLICATION_JSON)
    @Consumes(MediaType.APPLICATION_JSON)
    public Response getAll() {
        return Response
                .status(200)
                .entity(studentService.getAll())
                .build();
    }

}

Now lets go to the interesting part, and that is the Flyway configuration. First we need to create a file with the name of “V1.0.0__initial_table_t_student.sql” and put it under the “resources/db/migration” folder. This is needed to initialize database schema the first time during application initialization.

create table t_student
(
    id     bigint  auto_increment
        primary key,
    nama varchar(100)      null,
    age int null
);

And last is, a simple Dockerfile since we are going to containerized

FROM registry.access.redhat.com/ubi8/openjdk-11-runtime:latest

ENV LANG='en_US.UTF-8' LANGUAGE='en_US:en'
ENV JAVA_OPTIONS="-Dquarkus.http.host=0.0.0.0 -Djava.util.logging.manager=org.jboss.logmanager.LogManager"

COPY target/quarkus-app/lib/ /deployments/lib/
COPY target/quarkus-app/*.jar /deployments/
COPY target/quarkus-app/app/ /deployments/app/
COPY target/quarkus-app/quarkus/ /deployments/quarkus/

EXPOSE 8080
USER 185

ENTRYPOINT [ "java", "-jar", "/deployments/quarkus-run.jar" ]

Build our application by using mvn and containerized it

$ mvn clean package

$ docker build -t quarkus-flyway:latest .

Push this image to an Image Registry, for this example im using Openshift Internal Image Registry, and deploy it to Openshift using below YAML file using “oc apply” command.

kind: Deployment
apiVersion: apps/v1
metadata:
  name: quarkus-flyway
  namespace: company-01-ns
  labels:
    app: quarkus-flyway
spec:
  replicas: 1
  selector:
    matchLabels:
      app: quarkus-flyway
    spec:
      containers:
        - resources: {}
          readinessProbe:
            httpGet:
              path: /q/health
              port: 8080
              scheme: HTTP
            initialDelaySeconds: 10
            timeoutSeconds: 1
            periodSeconds: 10
            successThreshold: 1
            failureThreshold: 3
          terminationMessagePath: /dev/termination-log
          name: quarkus-flyway
          livenessProbe:
            httpGet:
              path: /q/health
              port: 8080
              scheme: HTTP
            initialDelaySeconds: 30
            timeoutSeconds: 2
            periodSeconds: 5
            successThreshold: 1
            failureThreshold: 3
          env:
            - name: DB_USER
              value: admin
            - name: DB_PASSWORD
              value: password
            - name: DB_URL
              value: 'jdbc:mysql://mysql:3306/db_test'
          ports:
            - containerPort: 8080
              protocol: TCP
            - containerPort: 8443
              protocol: TCP
          imagePullPolicy: IfNotPresent
          terminationMessagePolicy: File
          image: >-
            image-registry.openshift-image-registry.svc:5000/company-01-ns/quarkus-flyway:latest
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst
      securityContext: {}
      schedulerName: default-scheduler
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxUnavailable: 25%
      maxSurge: 25%
  revisionHistoryLimit: 10
  progressDeadlineSeconds: 600

This will resulted in each Namespace having their own application, and MySql database.

Code for this project can be seen on below Github repo,

https://github.com/edwin/quarkus-flyway