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 Link to heading

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 Link to heading

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 Link to heading

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 Link to heading

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 Link to heading

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 Link to heading

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!