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!
Subscribe to my newsletter
Read articles from Neil Fernandez directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
