Start your APEX DevOps adventure with Liquibase

In this short blog we are going to take a look at how you can utilize DevOps with APEX running on an Autonomous Database using Liquibase.

This is meant as an introductory article if you are interested in beginning your adventure with APEX & DevOps on OCI!

Initial Setup

Note: Most of the below actions will be performed within the OCI Code Editor/Shell available in every OCI Tenancy.

Download wallet for the Autonomous Database, to learn how click HERE

Copy wallet zip to cloud shell, this can be done with a simple drag & drop

Create a folder ADB_APEX and unzip the wallet inside that folder

Change the directory location in sqlnet.ora to your wallet location

Use the command “export TNS_ADMIN=/home/your_home/ADB_APEX” to update your system path

Connect using sqlplus user@yourdb_high

If you were able to successfully connect with your database, you can continue to the next step!

Installing SQLcl

SQLcl contains Liquibase which will allow you to interact with the database and create the necessary artifacts to maintain version control of your database objects and the application itself

Download the latest version of SQLcl from HERE (V23.1 was used here)

Note: You can do it using wget to avoid transferring the file

Unzip the folder and cd into it

Run sql and connect using sql user@yourdb_high

Note: The sql executable can be found in sqlcl/bin/sql

Running Liquibase

Now let’s take a look how you can use Liquibase to generate all the necessary artifacts for your CI/CD pipeline

Generating a table controller — The below command will produce an XML file that contains the object metadata:

lb generate-object -object-type object -object-name name

SQL> lb generate-object -type table -name FATA

Note: The generate-object command is used instead of genobject as that is deprecated syntax, refer to the below:

Old Syntax: lb genobject -type table -name name

New Syntax: lb generate-object -object-type table -object-name name

Console output for Liquibase controller generation.

Updating the second environment

Then a change was made to the object name (FATA) -> (FATA_2), this change was done within the controller XML itself for demonstration purposes

Now simply passing the controller file back you are able to generate the table with the name FATA_2

SQL> lb update -changelog-file fata_table.xml

Now within the Liquibase tab in Database Actions you should be able to see the SQL executed by the deployed changeset and the object’s previous state if one exists

OCI Database Actions — Liquibase changes view.

Export APEX Application

Now you can export the APEX application itself very easily using Liquibase by performing the following command

lb genobject -type apex -application_id app_id -split — Export specific application, optional flag (-split divides objects into separate files)

In this example application with ID 134 is being exported:

SQL> apex export -applicationid 134

Console Output:
Exporting Application 134
File f134.sql created

Export remaining objects for the underlying application

If the application requires more database objects/schemas, you can include those within your controller

Generating schema controllers with liquibase is quite straightforward to do and will include all of the schema objects such as sequences or triggers. Simply use the command: lb generate-schema my_schema

Liquibase will take care of the rest for you, if you want to know more supported SQLcl commands click HERE

Creating a DevOps repository in OCI

Now that you are able to export the application and create the necessary controller files that will track the versioning of your database objects let’s look at a place where these objects can be stored, to stay within a single environment and ensure utmost security we are going to leverage OCI DevOps services for this task

Now you can navigate to Developer Services -> DevOps within your tenancy and proceed to Create a Project and a Code Repository

List repositories in OCI DevOps

Get your compartment OCID to see the list of repositories, HERE is how you can find the OCID of your compartment. Then execute the following command in the console:

oci devops repository list - compartment-id

If no repositories are listed you can create one from the cloud console or by running the create command

OR

oci devops repository create - name - project-id - repository-type

Configure ~/.ssh/config file

Host devops.scmservice.*.oci.oraclecloud.com
User oracleidentitycloudservice/your-oci-user@tenancy
IdentityFile ~/.ssh/id_rsa

If the file doesn’t exist, create it using your favorite text editor

Clone the repository

git clone ssh://devops.scmservice.eu-frankfurt-1.oci.oraclecloud.com/namespaces/your_namespace/projects/Demo_Devops/repositories/APEX_Demo

Configure the git

git config - global user.email "you@example.com"
git config - global user.name "Your Name"

Upload Files to OCI DevOps Repository

Copy the files to the repository “git add .”

Check the sync status “git status”

Commit the files to the repository “git commit -m ‘Your commit’ ”

Push the changes “git push”

Console output when pushing changes to the repository.

OCI DevOps repository view.

We are now able to generate the required database objects for our application as well as export the application itself and upload it to a central OCI DevOps repository. This gives us all the core components required to create a basic CI/CD pipeline and demonstrates how easily we can begin our DevOps adventure with APEX!

Thank you for tuning in to this article, if you would like to know more about lifecycle management within APEX please refer to the APEX Technical Paper which covers the subject in great detail

For more information you can find me on LinkedIn, and as always don’t forget to visit the Oracle APEX page for all your latest news