APEX: Quick Tip when using SQLcl 24.3 Projects for Database Application CI/CD

If you’re reading this, you were just as excited as I was to see SQLcl release 24.3 and its Database Application CI/CD tooling from Oracle.

After the announcements, the posts on getting started by Dan McGhan [An Overview of the Oracle SQLcl Projects Development Process] or the complete step-by-step by Rafał Grzegorczyk post [Oracle SQLcl Project: The only CI/CD tool for APEX you need?], the fantastic demo on YouTube, you might start to believe it is the only tool you need.

It is a great leap forward for sure and I am looking forward to having a proper one-stop shop for DB artifact migration, as Liquibase was a start, SQLcl extension an improvement, this is just better.

About this Post

I have not completed my end-to-end testing and looking to discover more as I experiment with the latest version.

The first ask I had of the team was, “How do I filter to only include my projects objects?” and this post shares that small tip.

Database Project Scaffolding

The introduction section of the guide covers the Database Project Scaffolding, essentially the directories structure and files that are created when initializing SQLcl projects.

The Documentation describes the three folders

• .dbtools: This folder contains the following:

  • Project filters that are used by project export to filter out objects that will be exported.

  • Project format rules that are used to format the code when it is exported.

  • Project configuration settings.

• src: This folder is where the exported objects from the database get placed. This is broken down by schema and objects types.

• dist: The release artifacts are created in this folder. This folder gets populated by the project stage command and the project release command compresses its contents to create a release artifact.

The .dbtools folder stores the important project configuration details, what is exported, how are statements formatted, and for APEX the additional export options recommended in the technical paper: Understanding the Oracle APEX Application Development Lifecycle

Project Filtering - project.filters file

Under .dbtools/filters exists the project.filters file which has some self-description documentation as of this article the documentation is still playing catch up.

The file uses a comma separated list of predicates and provides some examples and default filter criteria.

For example it filters out Liquibase Change Tables and system generated tables and views

-- Liquibase Tables
object_type != 'TABLE' or object_name not in ('DATABASECHANGELOG',
                                              'DATABASECHANGELOGLOCK',
                                              'DATABASECHANGELOG_ACTIONS'
                                              ),
not (object_type = 'VIEW'    and object_name ='DATABASECHANGELOG_DETAILS'),
not (object_type = 'TRIGGER' and object_name ='DATABASECHANGELOG_ACTIONS_TRG'),


-- DM generated tables
not (object_type = 'TABLE' and object_name like 'DM$%' ),
not (object_type = 'VIEW' and object_name like 'DM$V%' ),

I have not experimented with enabling the ORDS Schema export, disabled by default but it is on my list.

The file is simple to understand and standard clauses are supported, seen by the provided defaults, 'like', 'not', 'and', '=' etc.

Only My Project Tables

To restrict the DB export to only my project tables, simple add a predicate for the objects

-- Project Tables
object_name like 'AICD%' OR
object_name in ('QTAB','ZTAB'),

Only My Project App as well, please!

Following the SQLcl option for APEX I added an application ID

-- Project application only
application_id = 162,

What about ORDS?

Well, just comment out the file line and your ORDS Schema with its modules, roles and privileges will be included in the project files.

-- ORDS export is disabled by default
--export_type not in ('ORDS_SCHEMA', 'CREATE_USER'),

Conclusion

The team putting out this updates has thought about development project structues, and provided a blueprint that we can start from.

I’m not a hardcore DB Developer, so I can’t comment for or against the format.

I like the simplicity to add customer scripts and since it is all text based, I can re-order the staged and distribution change logs to improve dependencies and prepare the targets as needed for objects or any custom steps required for my environment.

Including multiple schemas is supported by the project initialisation and updating the configuration and filter is also straight forward and human readable.

Explore the .dbtools directory and file set to get to know this feature as it will aide project management teams immensely.

As always, let me know what you think in the comments section.