SQLcl Projects is a new feature of SQLcl that aims to standardize and streamline the development lifecycle of Oracle Database and APEX applications. This post provides a high level overview of what the development process looks like.
Here are a couple of terms you’ll see in this article and when discussing SQLcl Projects in general:
Soft object - An object type that supports “create or replace” syntax. Examples: views, procedures, functions, packages, triggers, etc.
Hard object - An object that contains data or state and must be migrated with more care than “create or replace” provides. Examples: tables, indexes, sequences, materialized views, etc.
High-level Process Overview
For context, here’s the basic folder structure of a repo using SQLcl Projects:
.
├── .dbtools * SQLcl Projects config
├── .git * Git DB (it's required)
├── dist * A Liquibase-based installer
│ ├── install.sql
│ ├── releases
│ └── utils
└── src
└── database * An export of your DB objects
These are the generic steps that developers will use to complete a feature (or bug fix) using SQLcl Projects.
Make changes in DEV DB
Sync repo with changes in DEV DB (
srcdirectory)Create Liquibase changelogs/sets (
distdirectory)Test Liquibase changelogs/sets on TEST DB
Merge changes to repo
Let’s dive into the details for each step...
Step 1: Make changes in DEV DB
In this step, you get the work done. There are many decisions dev teams make that impact exactly how one makes changes to an application.
Individual vs. shared development environments
“Files first” vs. working with objects directly in the DB
Git branching strategy (Git Flow, GitHub flow, etc.)
Connecting to the DEV DB as Admin vs. app schema users
I’ll discuss the nuance of how those decisions impact app dev in future posts. For now, know that while such decisions will impact how you get work done, SQLcl Projects is pretty flexible and will generally work regardless.
Step 2: Sync repo with changes in DEV DB (src directory)
Next, you must synchronize the src directory of the repo with any changes made in the DEV DB as part of a feature/bug fix. Think of src as an export of your application’s database objects. Database objects are organized as files under src, grouped by schema and object type. The files under src are useful for:
Diffing for pull requests (PRs) or merge requests (MRs) and tracking changes over time - this is something that Git and its tooling ecosystem do very well.
Compiling soft objects into the database - if you need to change a soft object, simply find it in the repo, make the change, and compile it in via a tool like SQL Developer or the SQL Developer Extension for VS Code.
The project export command does most of the work managing the src directory, exporting anything from multiple schemas to individual objects. Before using project export, you’ll create a new branch to capture your changes.
Any hard objects that were changed in DEV must be exported via the project export command. This keeps the SXML representation of the object, found in a JSON comment at the bottom of the file, up-to-date. That SXML is used by the project stage command in the next step.
When it comes to soft objects, if the object was changed directly in the database (outside of the repo), then you should export the object to the repo. However, if you changed the file in the src directory and compiled the object into the DEV DB, then there’s no need to export the object as the content in the repo and the DEV DB are already in sync.
Step 3: Create Liquibase changelogs/sets (dist directory)
In Step 3, you’ll create an ordered, immutable set of SQL statements that will be used to migrate your application schemas on target databases. Think of dist as the order of operations to correctly arrive at the state of src. This means there’s a relationship between src and dist, and they must be kept in sync. The files under dist are advantageous because:
They leverage Liquibase to track what’s run on target databases. This help keep SQL nice and clean, without extra code to detect if the statement has already run.
They provide complete, granular control over order of operations (the order that SQL statements are executed). This is achieved via a three-level, hierarchical changelog system where the main changelog orders releases, release changelogs order changes (think tickets in a tracking system), and change changelogs order their SQL statements. Here’s what it looks like:
All objects are versioned, meaning custom SQL will not break if dependent objects are changed in the future. This allows installers to safely go from any version of an application to the latest one.
The project stage command automates much of the work related to managing the dist directory. When run, project stage compares the state of the src directory in your current branch to the state in another branch (typically main). Based on the result of the comparison, it then generates a Liquibase changelog and one or more changesets (Liquibase formatted SQL files) needed to go from the old state of src to the new one. These files are created in new directory under dist/releases/next/changes, where “next” represents the current sprint. The name of the new directory is based on your current branch name.
The generated changesets are ordered as best as possible, but developers can change the order by moving the related include line in the changelog up or down. Sometimes generated changesets will need to be modified or additional SQL will need to be added.
The project stage add-custom command can be used to easily add any custom SQL to the change. The command creates a placeholder file (with Liquibase related comments) in your change folder and adds a new include to the bottom of the changelog pointing to the new file. All you need to do is add your SQL and move the include to the correct order of operations.
Step 4: Test Liquibase changelogs/sets on TEST DB
This step is about guaranteeing quality going into the repo. There are many different types of testing one could consider at this time:
Installer testing - Because changes were exported from a DEV DB and used to generate the Liquibase changelogs/sets in
dist, that same database can’t be used for this testing (i.e., the statement that adds a column would fail if the column already exists). Developers should test on a DB that is at the latest state, just prior to the new change.Feature/bug fix testing - Just because the installer finishes doesn’t mean the feature or bug fix is complete. For example, the installer can complete successfully if a developer forgets to add a new table, but the application will fail when it tries to use it. These tests can be done manually or automated via integration and end-to-end tests.
Standards testing - A development team may have any number of standards around how developers should write code. Standards tools can scan code and report back on any deviations.
Unit testing - Unit tests will vary depending on where your business logic lives. If you’re like me and you put a lot of logic in PL/SQL code, then utPLSQL can be a great tool to help ensure the codebase is working as it should.
Security testing - There are various security related tools that can be used to scan source code for security issues, and you can use more than one. It’s always better to find vulnerabilities (e.g., SQL injection, XSS, etc.) before the code ships.
Load testing - The only way to find out if your app can stand up to a reasonable workload is to test it under load.
When and how tests are run will vary by project. One timing point to consider is pre-PR, which helps weed out issues that would take more time to resolve if caught further downstream. At a minimum, developers should test the installer and ensure the feature/bug fix works before creating a PR.
Teams should implement automated pipelines for testing when possible, potentially triggered when a new PR is created, a merge is completed, or both. Ideally, PRs would be blocked from merging if any serious tests fail.
Step 5: Merge changes to repo
When testing has passed you are ready to create a PR (if that wasn’t done in the previous step). A senior developer should review the PR, potentially repeating some of the testing done by the developer previously, and eventually merge the code into the repo. If anything needs to be corrected before merging, the PR can be rejected and the developer can repeat the steps above until the code is ready to be merged.
Process Variations
While the described process is SQLcl Projects' intended use, teams may adapt it to fit their needs. For example, some teams may delay running project stage until the end of a sprint. While this can simplify development, it can lead to issues such as forgetting to sequence custom DML correctly. I recommend starting with the standard process to gain experience before experimenting with variations.
Wrapping up
SQLcl Projects offers a robust framework for managing the development lifecycle of Oracle Database applications, from making changes in the development database to testing and merging updates into the repository. By following a structured process—synchronizing source files, generating Liquibase changelogs, and rigorously testing changes—teams can ensure their database updates are efficient, reliable, and maintainable. This approach not only simplifies collaboration but also promotes consistency and quality across the application lifecycle. As you dive deeper into SQLcl Projects, these foundational steps will help you harness its full potential for streamlined and standardized database development.
Ready to learn more? Watch this presentation and then work through the Quick Start guide. Don’t forget to ask your questions in the SQLcl forum.