#HelloWorld SAPUI5 meets Kubernetes – Integration of Database via separate Pod’s

All Blogs in the series

What we are making now?

In our previous blog we have been able to get our SAPUI5 hello world pod up and running. This was a single pod running our basic application. Now the plan is to integrate with a database which is also hosted as a separate pod.  So in this blog we will try to get our MySQL database pod running along with a separate PHP pod. The MySQL pod will not be exposed to the outside world, PHP pod will internally talk to MySQL pod and expose the data via API. So we will have 3 pod’s running with our  SAPUI5 app and PHP pod accessible to the outside world and database pod which can only communicate internally among pods and containers.

Getting our MySQL pod up

We already have MySQL docker image available in the hub, let use it to create our database pod. We can specify the database username, password etc. which we will use in our PHP script to connect to the database

apiVersion: apps/v1
kind: Deployment
metadata:
 name: ui5sql
spec:
 selector:
   matchLabels:
     app: ui5sql
 template:
   metadata:
     labels:
       app: ui5sql
   spec:
     containers:
     - image: mysql:latest
       name: mysql
       env:
       - name: MYSQL_ROOT_PASSWORD
         value: ui5root
       - name: MYSQL_DATABASE
         value: ui5db
       - name: MYSQL_USER
         value: ui5user
       - name: MYSQL_PASSWORD
         value: ui5pwd
       args: ["--default-authentication-plugin=mysql_native_password"]
       ports:
       - containerPort: 3306
         name: ui5sql

We have our MySQL pod running, let’s create a service which will be used to interact with the pod. This service is of type clustered ip meaning it can be accessed only internally inside the cluster.

apiVersion: v1
kind: Service
metadata:
 name: ui5sql-service
 labels:
   app: ui5sql
spec:
 ports:
 - port: 3306
   protocol: TCP
 selector:
   app: ui5sql

Now our database pod is up lets login and create a database tables saledocument with some entries.

kubectl exec  -ti ui5sql-86644bf45f-q7757 -- mysql --user=root --password=ui5root

CREATE TABLE salesdocument (
          SalesOrder int,
          Material char(30),
          amount float
      );
insert into salesdocument(SalesOrder,Material,amount) values (1,'mat 1',20.20);
insert into salesdocument(SalesOrder,Material,amount) values (2,'mat 2',32.20);

Our database pod is ready let’s get our PHP pod up

Getting our PHP pod up

Like in our first blog we have created an image for our SAPUI5 application, we will be creating the docker image this time via docker file. In the file below we have mentioned the php image version, then add mysqli plugin, copy our PHP code and expose pod 80. Rest of the steps are the same as we did in part 1.

FROM php:7.3.2-apache
RUN docker-php-ext-install mysqli
COPY src/ /var/www/html
EXPOSE 80

The PHP endpoints returns the data fetched from MySQL database.

<?php
$conn = new mysqli("ui5sql-service", "root", "ui5root", "ui5db");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$myArray = array();
$sql = "SELECT * FROM salesdocument";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
     	$myArray[] = $row;
    }
    echo json_encode($myArray);
} else {
    echo json_encode("0 results");
}
$conn->close();
?>

Our updated docker image pushed to the docker hub.  Next step is to create deployment and service for this PHP image.

apiVersion: apps/v1
kind: Deployment
metadata:
 name: php
 labels:
   app: php
spec:
 replicas: 1
 selector:
   matchLabels:
     app: php
 template:
   metadata:
     labels:
       app: php
   spec:
     containers:
     - name: php
       image: nabheetmadan/nabheetui5php:6.0
       ports:
       - containerPort: 80

PHP service

apiVersion: v1
kind: Service
metadata:
 name: web-service
 labels:
   run: web-service
spec:
 type: NodePort
 ports:
 - port: 80
   protocol: TCP
 selector:
   app: php

Now we have our PHP pod and MySQL pod up and running, but how will they interact, read on!

How will MySQL pod talks to PHP pod?

As of now the PHP pod is exposed as Nodeport IP so external world can access it but not MySQL. How will MySQL talk to PHP, if you look the PHP code for making the connection the host is mentioned as nothing but MySQL service name, so basically we access pods internally via service name.

$conn = new mysqli("ui5sql-service", "root", "ui5root", "ui5db");

Integrating with our SAPUI5 pod

If you remember our SAPUI5 app has only basic Hello World stuff, we have modified the app to add a list which will show the sales document data fetched from MySQL via PHP api.

Update App View.

<mvc:View
    controllerName="sap.ui.demo.walkthrough.controller.App"
    xmlns="sap.m"
   xmlns:l="sap.ui.layout"
    xmlns:mvc="sap.ui.core.mvc">
   <List
       headerText="Sales Order"
       items="{/}" >
       <StandardListItem
       title="{Material}"
       description="{SalesOrder} - {amount}"/>
   </List>
   <Button
        text="{i18n>showHelloButtonText}"
        press=".onShowHello"/>
    <Input
        value="{/recipient/name}"
        description="Hello {/recipient/name}"
        valueLiveUpdate="true"
        width="60%"/>
</mvc:View>

Updated App Controller.

  var oGetData = new JSONModel("http://192.168.99.113:30404/",
            oParameters,true,"get");
            this.getView().setModel(oGetData);

Now we have all three pod’s running with our SAPUI5 app consuming the PHP endpoint.

Output of the application

What is next?

Containers is a vast topic, i have also been trying to understand more and more. In future blogs we will again build on the same app to add more features of Kubernetes such as ReplicaSet, Config and Maps, Persistent Storage( the moment i delete the pod the database changes will be lost), RBAC etc.