Example: Use SQLcl + Liquibase in Kubernetes initContainers

If your Microservice application running on Kubernetes relies on the Oracle Database, you can take advantage of SQLcl + Liquibase with initContainers for effective Schema Change Management during your deployments.

Lets take a quick look at how this is done:

Secrets

First you’ll need two Secrets for connecting to your database, one for Database (Names) Resolution and another for Authentication. I’m using a Secret for a tnsnames.ora file to perform the resolution, but other resolution options are available such as creating an ldap.ora or a ConfigMap of the unencoded connect string.

Database Resolution

Take your database Connect String and Base64 encode it for the Secret:

For example, on macOS:

echo "MYDB = (DESCRIPTION... (SERVICE_NAME = MYDB)))" | base64

Create a file with the following content, replacing <conn_string_b64_output> with the output from above command:

---
apiVersion: v1
kind: Secret
type: Opaque
metadata:
  name: tns-admin
  namespace: default
data:
    tnsnames.ora: <conn_string_b64_output>

OraOperator and ADB

If you are using the Oracle Autonomous Database, save yourself a bit of aggro and let the OraOperator create the resolution secrets for you. Bind the OraOperator to the existing ADB and the wallet will be downloaded into a secret that you can export and import into any namespace.

Database Authentication

Create a Secret with the base64 encoded username, password, and service_name for your database. The user specified in the Secret should already exist and, for this example, be able to create other users; e.g. SYSTEM or ADMIN.

Append the below to the file you created for the tns-admin Secret after replacing the appropriate values with the base64 encoded output:

---
apiVersion: v1
kind: Secret
metadata:
  name: db-secrets
  namespace: default
type: Opaque
data:
  db.username: <b64_username>
  db.password: <b64_password>
  db.service_name: <b64_service_name>

ConfigMaps

A ConfigMap will be used contain the Liquibase Changelog (changelog.sql) and a small helper SQL script (liquibase.sql) to call it.

Append the below to the file you created for the Secrets:

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: liquibase-changelog
  namespace: default
data:
  liquibase.sql: "liquibase update -chf changelog.sql"
  changelog.sql: |-
    -- liquibase formatted sql

    -- changeset gotsysdba:initial endDelimiter:/
    DECLARE
      L_CONN_USER   VARCHAR2(255);
      L_USER        VARCHAR2(255);
      L_TBLSPACE    VARCHAR2(255);
    BEGIN
      BEGIN
        SELECT USER INTO L_CONN_USER FROM DUAL;
        SELECT USERNAME INTO L_USER FROM DBA_USERS WHERE USERNAME="DEMO";
      EXCEPTION WHEN NO_DATA_FOUND THEN
        execute immediate 'CREATE USER "DEMO" NO AUTHENTICATION';
      END;
      execute immediate 'ALTER USER "DEMO" GRANT CONNECT THROUGH '||L_CONN_USER;
      SELECT DEFAULT_TABLESPACE INTO L_TBLSPACE FROM DBA_USERS WHERE USERNAME='DEMO';
      execute immediate 'ALTER USER "DEMO" QUOTA UNLIMITED ON '||L_TBLSPACE;
      execute immediate 'GRANT CREATE SESSION TO "DEMO"';
      execute immediate 'GRANT SODA_APP TO "DEMO"';
      execute immediate 'GRANT CREATE TABLE TO "DEMO"';
    END;
    /

    --rollback drop user "DEMO" cascade;

The above Liquibase Changelog will:

• Create the DEMO user (if it doesn’t exist) in the database
• Allow the creating user (db.username from the db-secrets Secret) to proxy into it
• Grant roles and privileges to DEMO

Deployment

Finally, the deployment… we are mostly interested in the initContainers section. We will use the container-registry.oracle.com/database/sqlcl:latest image for our initContainer and send in the arguments: -L -nohistory <username>/<password>@<connect_string> @liquidbase.sql.

Using the db-secrets Secret, the <username>/<password>@<connect_string> will be populated with the correct values which will use the tns-admin Secret mounted at /opt/oracle/network/admin for resolution. The liquidbase.sql and changelog.sql files will be made available from the liquibase-changelog ConfigMap in the /opt/oracle/sql_scripts mount.

Append this to your exiting manifest file:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: liquibase-demo
  namespace: default
spec:
  replicas: 1
  template:
    spec:
      initContainers:
      - name: liquibase
        image: container-registry.oracle.com/database/sqlcl:latest
        args:
        - -L
        - -nohistory
        - $(LIQUIBASE_COMMAND_USERNAME)/$(LIQUIBASE_COMMAND_PASSWORD)@$(LIQUIBASE_COMMAND_URL)
        - '@liquibase.sql'
        env:
        - name: LIQUIBASE_COMMAND_URL
          value: jdbc:oracle:thin:@$(LIQUIBASE_COMMAND_SERVICE)?TNS_ADMIN=/opt/oracle/network/admin
        - name: LIQUIBASE_COMMAND_SERVICE
          valueFrom:
            secretKeyRef:
              key: db.service_name
              name: 'db-secrets'
        - name: LIQUIBASE_COMMAND_USERNAME
          valueFrom:
            secretKeyRef:
              key: db.username
              name: 'db-secrets'
        - name: LIQUIBASE_COMMAND_PASSWORD
          valueFrom:
            secretKeyRef:
              key: db.password
              name: 'db-secrets'
        volumeMounts:
        - name: tns-admin
          mountPath: /opt/oracle/network/admin
          readOnly: true
        - name: liquibase-changelog
          mountPath: /opt/oracle/sql_scripts 
          readOnly: true
      volumes:
      - name: liquibase-changelog
        configMap:
          name: liquibase-changelog
      - name: tns-admin
        secret:
          secretName: tns-admin

Apply your manifest: kubectl apply -f manifest.yaml and check the logs: kubectl logs pod/liquibase-demo-0 -c liquibase -n default

You should see something similar to:

--Starting Liquibase at 12:24:40 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)
-- Loaded 1 change(s)
Operation completed successfully.

Check your database… you will now have a new user called DEMO. When you’re ready for your next deployment, if it requires a change to the database, update the configmap definition (remember to change the changeset) and deploy away!