Oracle SQLcl Project:  The only CI/CD tool for APEX you need?

In short words, the project command, along with its sub-commands, will help you manage database changes with ease. In this step-by-step tutorial, I will describe how to use this helpful CI/CD tool in new or existing projects.

Prerequisites

• I have two environments (DEV and PROD) in my Oracle OCI and I will use connection names HR_DEV_OCI and HR_PROD_OCI to connect to them (read about connection aliases here)
• DEV and PROD databases have the same number of objects created manually in past deployments (I used SQL Developer & Oracle’s sample HR schema)
• I have APEX applications with id 111 and  id 222 installed on DEV and PROD
• All commands you will see in this blog will be executed using SQLcl 24.3.2 (read how to install it here)

Start using SQLcl Project with existing or new databases

1. Create an SQLcl database project

I have an empty folder SQLCL_PROJECT_DB_APEX in my VS Code.

Now, I connect to my HR schema on DEV.

sql -name HR_DEV_OCI

I name my project “PRETIUS”, and I want to initialize it with the schema “HR”.

project init -name PRETIUS -schemas HR

The folders and files visible below are auto-created by SQLcl.

2. Initialize GIT repository

Now, I have to initialize a new GIT repository for created files and commit it.

!git init --initial-branch=main
!git add .
!git commit -m "initializing repository with default project PRETIUS files"

3. Create the base-release branch, export DB objects from DEV and commit

Now, I create a branch “base-release”, connect to HR_DEV, and export database objects and APEX apps into files. The steps are the same for new projects. You export your files once some objects are created in the database.

--this command creates new branch and switches to it
!git checkout -b base-release

Now, I export all database objects and APEX apps from DEV.

--connected to HR_DEV_OCI, execute this
project export

My database objects and APEX applications are exported.

Now, I commit my base-release to GIT.

!git add .
!git commit -m "Base release"

4. Use project stage to stage changes

In the next step, I use the command project stage to stage my changes – the command compares the main branch with base-release.

--DOCKER_HR_DEV
project stage

Files necessary for deployment were automatically created at location /dist/releases/next.

5. Verify project

Now, I use the project verify command to check the project for any possible errors and warnings.

project verify -verbose

Everything is good to go.

5. Run project release to finish release 1.1

I call my base release “version 1.0”.

--HR_DEV_OCI
project release -version 1.0 -verbose

SQLcl terminal says clearly what happened:

My release moves to /releases/1.0, and my next release is empty.

7. Generate an artifact representing the current stage of the project

Using the command gen-artifact I generate an artifact representing my project’s current status.

--HR_DEV_OCI
project gen-artifact -version 1.0-base-release

A new ZIP file PRETIUS-1.0-base-release.zip is generated (notice that artifacts are not versioned in GIT).

The deployment script is ready so I can commit it.

!git add .
!git commit -m "Version 1.0"

8. Deploy release (version 1.0) to PROD

For a new project

• If it were a new project, I would’ve deployed all my code to PROD using the project deploy command.
• It would look like that (connected to PROD or any other environment)

--HR_PROD_OCI
project deploy -file artifact/PRETIUS-1.0-base-release.zip -verbose

For existing projects

• In my case (I already have objects in PROD), I have to manually mark version 1.0 as released to PROD by running the liquibase changelog-sync command
• This command won’t change any of your DB objects – it will only create necessary Liquibase tables and make some inserts into the databasechangelog table

--HR_PROD_OCI
liquibase changelog-sync -chf dist/releases/main.changelog.xml

9. GIT merge “base-release” (DEV) branch into “main” (PROD)

At this moment, my DEV (base-release) is equal to PROD (main). Therefore, I have to merge my base-release branch into the main branch.

Create new changes on DEV and deploy to PROD as version 1.1

1. Make some changes directly in the DEV environment 

First, I need to change some stuff directly in the DEV environment (using VS Code, SQL Dev, etc.).I make some changes in APEX apps in DEV.

INSERT INTO HR.COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('PL', 'Poland', '10');

CREATE TABLE HR.PARAMETERS (NAME VARCHAR2(50),
                           VALUE VARCHAR2(50));

ALTER TABLE HR.EMPLOYEES 
ADD (ADDRESS VARCHAR2(100) );

 CREATE OR REPLACE PROCEDURE HR.SECURE_DML
 IS
 BEGIN
    IF TO_CHAR(SYSDATE, 'HH24:MI') not between '08:00' and '18:00'
       or TO_CHAR(SYSDATE, 'DY') in ('SAT', 'SUN')
    THEN
       RAISE_APPLICATION_ERROR(-20205,
                               'You may only make changes during normal office hours (this bracket is a change)');
    END IF;
END SECURE_DML;
/

INSERT INTO HR.PARAMETERS (name, value) VALUES ('MY_BLOG_URL','rafal.hashnode.dev');
COMMIT;

2. Create a new GIT branch

Now, I create a new GIT branch called “1.1“.

3. Export changes made in database objects and the APEX app

To export the changes, I use the project export command:

--on branch 1.1
--connected to HR_DEV_OCI
project export

This command exports my actual state of DEV database objects and APEX apps. 

Once exported, it’s time to commit changes on branch 1.1.

!git add .
!git commit -m "Files for version 1.1"

4. Create a deployment script

First, I use the project stage command:

--HR_DEV_OCI
project stage

This command creates some new files for my release 1.1.

5. Use project stage add-custom to deploy custom scripts

As you may remember, on my DEV, in addition to changes in the EMPLOYEES and PARAMETERS tables and the procedure SECURE_DML, I’ve also executed two inserts into the COUNTRIES and PARAMETERS tables. This is something that SQLcl won’t track automatically, and we need to use the add-custom command for that

That’s why I’ve created two files for DMLs in both tables:

--HR_DEV_OCI
project stage add-custom -file-name dml_parameters
project stage add-custom -file-name dml_countries

And SQLcl automatically created those files:

There’s no need to change anything in these files. I only put my insert scripts there:

6. Verify the project

Once again, I use the project verify command to check if everything’s alright.

--HR_DEV_OCI
project verify

We are good to go.

7. Run project release to finish release 1.1

I use the following command:

--HR_DEV_OCI
project release -version 1.1 -verbose

SQLcl terminal says clearly what happened:

My release moves to /releases/1.1, and my next release is empty.

8. GIT commit version 1.1 and merge into the main branch

I like to do this so that everything’s transparent. Since my release is ready for PROD, I should deploy it from the main (PROD) branch. That’s why I do the following:

• Git commit version 1.1 on branch version 1.1
• Merge branch 1.1 into MAIN

9. Generate an artifact representing the current stage (version 1.1) of the project

Now, I create an artifact for my version 1.1 using the gen-artifact command:

--HR_PROD_OCI
--on GIT branch MAIN(prod)
project gen-artifact -version 1.1

10. Deploy release (version 1.1) to PROD

I’m ready to deploy my changes to PROD with the project deploy command:

--HR_PROD_OCI
project deploy -file artifact/PRETIUS-1.1.zip -verbose

The SQLcl terminal clearly states what happened, but most importantly, all my changes, including APEX, were deployed to PROD:

Conlcusion

And that’s about it. I hope you liked this article. I advise you to experiment with the project command and see whether it fits your particular CI/CD needs. Personally, I’m pretty impressed with its capabilities and it’s possible that it’ll really be the only CI/CD tool that I will use in many APEX projects. 

If you have any questions, reach out to me at rgrzegorczyk@pretius.com. And, of course, check out some of my other articles on this blog:

1. Oracle APEX: Dynamic highlights for required fields
2. APEX 24.1 Working Copies – An in-depth look at one of the platform’s coolest features 
3. Liquibase tutorial 2024: What is this tool and how to start using it effectively?