Example: Use Liquibase with Spring Boot in Kubernetes

As a follow-up to using Using SQLcl + Liquibase in Kubernetes initContainers for Schema Change Management… You can also use Liquibase if your Microservice is a Spring Boot project with the Oracle Database as its data store.

Spring Boot Project - Maven

Here’s how it’s done when using Apache Maven to build the image:

POM.XML

Include in your pom.xml the following dependency:

  <dependency>
      <groupId>org.liquibase</groupId>
      <artifactId>liquibase-core</artifactId>
  </dependency>

As you are using the Oracle Database, you probably already have these dependencies:

  <dependency>
      <groupId>com.oracle.database.jdbc</groupId>
      <artifactId>ojdbc8</artifactId>
  </dependency>

  <dependency>
      <groupId>com.oracle.database.jdbc</groupId>
      <artifactId>ucp</artifactId>
  </dependency>

However, if you are using the Oracle Autonomous Database with an Oracle Wallet, you will also need these dependencies:

if you are using the Oracle Autonomous Database… you will also need…

  <!-- For Oracle Wallet (ADB-S); oraclepki, osdt_core, and osdt_cert artifacts -->
  <dependency>
      <groupId>com.oracle.database.security</groupId>
      <artifactId>oraclepki</artifactId>
  </dependency>

  <dependency>
      <groupId>com.oracle.database.security</groupId>
      <artifactId>osdt_core</artifactId>
  </dependency>

  <dependency>
      <groupId>com.oracle.database.security</groupId>
      <artifactId>osdt_cert</artifactId>
  </dependency>

Spring Starters for Oracle Database

Simplify your pom.xml and reduce those five dependencies by taking advantage of two Spring Starters as found here: com.oracle.database.spring.

The above five dependencies can be replaced with:

<dependency>
    <groupId>com.oracle.database.spring</groupId>
    <artifactId>oracle-spring-boot-starter-ucp</artifactId>
    <type>pom</type>
</dependency>

<dependency>
    <groupId>com.oracle.database.spring</groupId>
    <artifactId>oracle-spring-boot-starter-wallet</artifactId>
    <type>pom</type>
</dependency>

Application Properties

In the Spring Boot application.properties file, include the following:

#Liquibase for Schema Bootstrap
spring.liquibase.change-log=classpath:db/changelog/controller.yaml
spring.liquibase.url=${CONNECT_STRING}
spring.liquibase.user=${DB_USERNAME}
spring.liquibase.password=${DB_PASSWORD}
spring.liquibase.enabled=${LIQUIBASE_ENABLED:false}

We will set the variables in the Kubernetes deployment YAML using Secrets; but lets take a closer look at the spring.liquibase.change-log file.

Liquibase ChangeLog

In this example, I’m using a ChangeLog “controller”, essentially a Liquibase ChangeLog with includes. This allows me to break up the ChangeSets into individual files as a matter of preference, rather than necessity. The “controller” file, along with all the ChangeSet files, will be placed in the Maven projects src/main/resources/db/changelog directory.

---
databaseChangeLog:
  - include:
      file: classpath:db/changelog/dbuser.sql
  - include:
      file: classpath:db/changelog/rest.sql

When the Spring Boot application starts, it will process the “controller” and instruct Liquibase to run the ChangeSet files in the order specified.

Liquibase ChangeSets

As for the contents of the ChangeSet files, the first file (dbuser.sql) will:

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

-- 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 CONNECT TO "DEMO"';
  execute immediate 'GRANT RESOURCE TO "DEMO"';
  execute immediate 'ALTER USER "DEMO" DEFAULT ROLE CONNECT,RESOURCE';
  execute immediate 'GRANT INHERIT PRIVILEGES ON USER '||L_CONN_USER||' TO ORDS_METADATA';
  execute immediate 'GRANT INHERIT PRIVILEGES ON USER "DEMO" TO ORDS_METADATA';
END;
/

--rollback drop user "DEMO" cascade;

The second ChangeSet file (rest.sql), will enable Oracle REST Data Services to access the DEMO schema.

-- liquibase formatted sql

-- changeset gotsysdba:Initial endDelimiter:/
BEGIN
  ORDS.ENABLE_SCHEMA(
    p_enabled => TRUE,
    p_schema => 'DEMO',
    p_url_mapping_type => 'BASE_PATH',
    p_url_mapping_pattern => 'demo',
    p_auto_rest_auth=> TRUE
  );
  COMMIT;
END;
/

--rollback exec ORDS.DISABLE_SCHEMA(p_enabled => FALSE, p_schema => 'DEMO');

Kubernetes

With the Maven Spring Boot project ready, lets see what we need on the Kubernetes side.

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.

Remember those additional pom.xml dependencies for handling wallets (alternatively the oracle-spring-boot-starter-wallet Starter)? This is where they become important. 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>

Deployment

Finally, the deployment… we are mostly interested in the containers env section.

Using the db-secrets Secret, the application.properties variables spring.liquibase.user and spring.liquibase.password will be substituted while the tns-admin secret will provide the value for spring.liquibase.url.

There was one other variable in the application.properties, spring.liquibase.enabled which will allow you to turn off and on running Liquibase on deployment. It will default to false, per the application.properties, if not set in your deployment manifest. I use it for testing purposes.

Modify your Kubernetes Deployment Manifest to include the additional environment variables and volumes/volumeMount for the tns-admin Secret:

apiVersion: apps/v1
kind: Deployment
.
.
.
    spec:
      containers:
        - env:
            - name: DB_USERNAME
              valueFrom:
                secretKeyRef:
                  name: "db-secrets"
                  key: db.username
            - name: DB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: "db-secrets"
                  key: db.password
            - name: DB_SERVICE
              valueFrom:
                secretKeyRef:
                  name: "db-secrets"
                  key: db.service
            - name: CONNECT_STRING
              value: jdbc:oracle:thin:@$(DB_SERVICE)?TNS_ADMIN=/oracle/tnsadmin
            - name: LIQUIBASE_ENABLED
              value: "true"
          volumeMounts:
            - name: tns-admin
              mountPath: /oracle/tnsadmin
      volumes:
        - name: tns-admin
          secret:
            secretName: "tns-admin"

Next time you deploy your Spring Boot project, you should see something similar to:

liquibase.lockservice                    : Successfully acquired change log lock
liquibase.changelog                      : Creating database history table with name: DATABASECHANGELOG
liquibase.changelog                      : Reading from DATABASECHANGELOG
Running Changeset: classpath:db/changelog/dbuser.sql::inherit_system::gotsysdba
liquibase.changelog                      : Custom SQL executed
liquibase.changelog                      : ChangeSet classpath:db/changelog/dbuser.sql::inherit_system::gotsysdba ran successfully in 840ms
Running Changeset: classpath:db/changelog/rest.sql::Initial::gotsysdba
liquibase.changelog                      : Custom SQL executed
liquibase.changelog                      : ChangeSet classpath:db/changelog/rest.sql::Initial::gotsysdba ran successfully in 802ms
liquibase.lockservice                    : Successfully released change log lock

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 your ChangeSet files (remember to change the changeset), rebuild your Spring Boot project with Maven and deploy away!