Cloud Native OCI

Connecting Oracle ATP

Created with ❤ by Oracle A-Team

Lab Overview

Introducing microservices using ATP
Oracle Autonomous Transaction Processing (ATP) setup
Connecting to ATP
Build & validation

MuShop Services with ATP

The MuShop runtime includes the services below, which connect to Oracle Autonomous Transaction Processing (ATP) databases. Each uses its own schema and connection, while providing REST services over HTTP.

Catalogue: src/catalogue

Services for MuShop products, categories, and search

Users: src/user

eCommerce customer data with profile and authentication services

Carts: src/carts

Customer shopping cart with SODA (Simple Oracle Document Access) table schemas

Orders: src/orders

Customer order processing services

Connecting to ATP

Provision ATP
Download Wallet
Create Secrets
Configure Chart
Deploy
Under the Hood

Create your Oracle ATP Database instance ℹ️ Documentation
Using the Cloud Shell:
```
export compartment_id="<COMPARTMENT_ID>"
                            
```
```
oci db autonomous-database create \
--cpu-core-count=1 \
--data-storage-size-in-tbs=1 \
--db-name=OCIWorkshop \
--display-name=OCIWorkshop \
--compartment-id=${compartment_id} \
--admin-password=<PASSWORD>
                            
```
📝 Be sure to make note of the ADMIN Password used when provisioning the ATP Instance

To connect applications to a real database, we can use ATP.
While its possible to run databases in containers, a managed service like ATP makes owning and operating the DB much more easier
There are many patterns for using databases with micro services, like database per service, shared and SAGA pattern.
Here we use a shared database, for simplicity, although in the real world this has the potential to be an SPOF.
Solutions like RAC can avoid these problems. To create the database you can use the console or the CLI. Ensure that you export the compartment_id of the compartment you want to use for the database and change the other parameters as required. In a shared environment, a single database will suffice. Individual attendees installing MuShop using the helm chart in their own namespaces will get their own set of schemas in the shared database, prefixed with the k8s namespace.
Download the database connection (Wallet): ℹ️ Documentation
DB Connection (Wallet) folder contents:
```
oci db autonomous-database list \
--query "data[*].{name:\"db-name\", OCID:id, State:\"lifecycle-state\"}" \
--output table \
--compartment-id=${compartment_id}

            
```
```
oci db autonomous-database generate-wallet --file=wallet.zip --autonomous-database-id=<ATP_OCID> --password=<PASSWORD>
            
```
```
unzip wallet.zip -d wallet
ls -ltr wallet
cat wallet/tnsnames.ora
            
```
📝 Be sure to make note of the Wallet Password used and connection TNS Name when downloading the Wallet

Connections to the autonomous-database and encrypted, the wallet provides the keys required in a wallet file. The wallet itself is protected by a password. Client applications use the connection information included in the download package, like tnsnames.ora, and security information included in the keystore files or the wallet files to establish a secure connection to the DB.
Configure ATP connection details in the form of Kubernetes secrets
1. Create oadb-admin secret containing the database administrator password:
```
kubectl create secret generic oadb-admin \
  --from-literal=oadb_admin_pw='<DB_ADMIN_PASSWORD>'
```
  ℹ️ The admin credentials are used once to initialize schemas in the application
2. Create oadb-wallet secret with the Wallet contents using the downloaded Wallet_*.zip The extracted directory is specified as the secret file path:
```
kubectl create secret generic oadb-wallet \
  --from-file='<PATH_TO_EXTRACTED_WALLET_FOLDER>'
```
  ℹ️ Each file will become a key name in the secret data.
3. Create oadb-connection secret with the Wallet password and the service TNS name to use for connections:
```
kubectl create secret generic oadb-connection \
  --from-literal=oadb_wallet_pw='<DB_WALLET_PASSWORD>' \
  --from-literal=oadb_service='<DB_TNS_NAME>'
```
  ℹ️ NOTE: Here each service will connect using the same conneciton for simplicity. It is expected for each service to support an independent instance and schema.

Next create (or edit) a myvalues.yaml file to specifiy connection information for the ATP-backed microservices:

Traverse into the helm chart deployment directory:
```
cd deploy/complete/helm-chart
```
If not done already, download or copy an example values file into your workspace:
(from download)
```
mv ~/Downloads/myvalues.yaml ./myvalues.yaml
```
- OR -
(copy from sample)
```
cp mushop/values-dev.yaml ./myvalues.yaml
```

Enter helm chart configuation for each service:

# Global service configurations
global:
  #...
  # ATP Secrets
  oadbAdminSecret: oadb-admin           # Name of DB Admin secret created separately
  oadbWalletSecret: oadb-wallet         # Name of wallet secret created separately
  oadbConnectionSecret: oadb-connection # Name of connection secret created separately
  
#...

ℹ️ NOTE: Secrets for the remaining services may be omitted

With the values configured, the application is ready to deploy
Remove a previous deployment (if applicable):
```
helm del mushop
```
Install from the deploy/complete/helm-cart directory:
```
helm install mushop mushop \
  --set tags.streaming=false \
  -f myvalues.yaml
```
ℹ️ If streaming has also been configured, omit the --set tags.streaming=false flag to deploy with all services:
```
helm install mushop mushop \
  -f myvalues.yaml
```
```
kubectl get pod --watch
```
👀 You will notice several pods with an -init- component in their name. These are pods corresponding to Kubernetes Job definitions, where each is performing DB schema initializations.
View the logs of an init pod like so (name will vary):
```
kubectl logs mushop-carts-init-1-d475m
```
⏲️ It may take a few moments for all services to become ready

In this exercise, the use of helm presents a degree of opaque automation that we can de-mystify by reviewing the mechanics under the hood. Using the src/user service as an example:

For each service connecting to ATP, a ConfigMap with SQL schema instructions along with an init Job are created:

kubectl describe configmap mushop-user-init

kubectl get job mushop-user-init-1 -o yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: mushop-user-init-1
spec:
  # ...
  template:
    metadata:
      creationTimestamp: null
      labels:
        job-name: mushop-user-init-1
    spec:
      containers:
      - image: iad.ocir.io/oracle/ateam/mushop-user:1.1.0
        imagePullPolicy: IfNotPresent
        name: user
        # TypeORM table schema initializations
        command: ["npm", "run", "schema:sync"]
        env:
        # Schema user credentials
        - name: OADB_USER
          valueFrom:
            secretKeyRef:
              key: oadb_user
              name: user-oadb-credentials
        - name: OADB_PW
          valueFrom:
            secretKeyRef:
              key: oadb_pw
              name: user-oadb-credentials
        - name: OADB_SERVICE
          valueFrom:
            secretKeyRef:
              key: oadb_service
              name: oadb-connection
        volumeMounts:
        - mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin/
          name: wallet
          readOnly: true
      # ...
      # Schema initialization with ADMIN user
      initContainers:
      - image: iad.ocir.io/oracle/ateam/mushop-dbtools:1.0.1
        imagePullPolicy: IfNotPresent
        name: init
        args:
        - sqlplus ADMIN/\"${OADB_ADMIN_PW}\"@${OADB_SERVICE} @service.sql ${OADB_USER^^}
          ${OADB_PW}
        command: ["/bin/sh", "-c"]
        env:
        # ... 
        # ADMIN user connection
        - name: OADB_ADMIN_PW
          valueFrom:
            secretKeyRef:
              key: oadb_admin_pw
              name: oadb-admin
        volumeMounts:
        # mounted 'wallet' volume to contain the wallet secret contents
        - mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin/
          name: wallet
          readOnly: true
      # ...
      volumes:
      # define the 'wallet' volume from the wallet secret files
      - name: wallet
        secret:
          defaultMode: 256
          secretName: oadb-wallet
      # mounted configmap as SQL file
      - configMap:
          defaultMode: 420
          items:
          - key: atp.init.sql
            path: service.sql
          name: mushop-user-init
        name: initdb

Example Deployment of user service:

kubectl get deployment mushop-user

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mushop-user
  labels:
    name: mushop-user
spec:
  template:
    metadata:
      labels:
        name: mushop-user
    spec:
      containers:
      - name: mushop-user
        image: iad.ocir.io/oracle/ateam/mushop-user:latest
        ports:
        - name: http
          containerPort: 3000
        # ...
        env:
        # Unique schema credentials
        - name: OADB_USER
          valueFrom:
            secretKeyRef:
              name: user-oadb-credentials
              key: oadb_user
        - name: OADB_PW
          valueFrom:
            secretKeyRef:
              name: user-oadb-credentials
              key: oadb_pw
        # use specified connection secret
        - name: OADB_SERVICE
          valueFrom:
            secretKeyRef:
              name: oadb-connection
              key: oadb_service
        # ...
        volumeMounts:
        # mount information for the the 'wallet' volume definition
        - name: wallet
          mountPath: /usr/lib/oracle/19.3/client64/lib/network/admin
          readOnly: true
      volumes:
      # define the 'wallet' volume from the wallet secret files
      - name: wallet
        secret:
          secretName: oadb-wallet
          defaultMode: 256

Note that the service runtime is connecting with the oadb-wallet and oadb-connection secrets configured separatley.

ATP Schemas

Each service manages its own schema in the database, with specific grants by the ADMIN

SELECT
  table_name, owner
FROM
  all_tables
WHERE
  owner IN (
    'CARTS_USER',
    'CATALOGUE_USER',
    'ORDERS_USER',
    'USER_USER')
ORDER BY
  owner, table_name

+---------------------+----------------+
| TABLE_NAME          | OWNER          |
+---------------------+----------------+
  CART                  CARTS_USER
  CATEGORIES            CATALOGUE_USER
  PRODUCTS              CATALOGUE_USER
  PRODUCT_CATEGORY      CATALOGUE_USER
  ADDRESS               ORDERS_USER
  CARD                  ORDERS_USER
  CART                  ORDERS_USER
  CART_ITEMS            ORDERS_USER
  CUSTOMER              ORDERS_USER
  CUSTOMER_ADDRESSES    ORDERS_USER
  CUSTOMER_CARDS        ORDERS_USER
  CUSTOMER_ORDER        ORDERS_USER
  CUSTOMER_ORDER_ITEMS  ORDERS_USER
  ITEM                  ORDERS_USER
  SHIPMENT              ORDERS_USER
  user                  USER_USER
  user_address          USER_USER
  user_card             USER_USER
+---------------------+----------------+