SQLcl Projects - Dynamic Property File Location Definition

Building on the previous topic of leveraging Liquibase properties to modify APEX installations, we will delve into a companion technique. This one enables users to define as many property files as they wish in the artifact to set it when deploying. This technique comes in handy when dealing with APEX details such as workspace, app_id, and alias which differ between schemas/environments. If you are restricted to only able to work on one database where you seperate instances by schema, this will help you!

If you are storing private information such as credentials and such, I will provide another method later once supported where you can define a location outside of the artifact. This can include some cloud storage, or just anywhere you define inside a container perhaps.

Step 1: Define Multiple Property Files

As a first step, under the utils folder, you can freely create as many property files as you wish. I made a new folder utils/properties. Each file will incorporate a different configuration and can be directly tied to specific environments such as staging or production. This automatically eliminates the hassle of having to modify property details manually and regenerate a seperate artifact whenever a deployment between environments is done.

Example of utils/properties/stage.properties

apex.200.workspace:NEIFERNA
apex.200.appId:400

Step 2: Modify install.sql

Next, make modifications on your install.sql script. To ensure that the defaults parameter reads from the appropriate property file, add -defaults-file &DEFAULTS_FILE into the lb update line. We are going to define this later.

prompt "Installing/updating schemas"
lb update -log -changelog-file releases/main.changelog.xml -search-path "." -defaults-file &DEFAULTS_FILE

Step 3: Generate Artifact

Call project gen-artifact. This scenario only allows you to interact with property files that live inside of your artifact. Personally I am not storing sensitive data in here. Just app IDs and workspace names.

For reference, my current APEX app changeset values without modification are as follows:

App ID: 200 and Workspace: DEMO are my default values.

Step 4: Connect to Target

Following successful artifact generation, connect to your target environment.

Step 5: Define Default Property File location

Next step is to specify your defaults file. This can be achieved by calling DEFINE DEFAULTS_FILE=utils/properties/stage.properties within SQLcl.

Step 6: Deploy Your Project

Finally, deploy your project by calling project deploy. The deployment process will use the artifacts generated earlier, applying the configurations you defined in your default property file to your APEX installation.

Full runthrough

Here is an example of the full set of commands after modifying install.sql and adding your property files.

SQL> project gen-artifact -name demo
Your artifact has been generated demo-1.0.0.zip
SQL> conn neiferna/apex@//localhost:2307/ga_release_242
Connected.
SQL> DEFINE DEFAULTS_FILE=utils/properties/stage.properties
SQL> project deploy -file artifact/demo-1.0.0.zip 
Starting the migration...
Running Changeset: releases/apex/f200/f200.xml::INSTALL_200::SQLCL-Generated
PL/SQL procedure successfully completed.

Future

This is not ideal for storing sensitive information such as credentials, secrets, etc. Once supported I will show how to define property files that live outside of the artifact. This can be useful for pulling from a vault, somewhere in a container, etc. Hope this helps till then. Cheers!