Skip to main content
HashnodeSpendolini BlogSpendolini Blog

Command Palette

Search for a command to run...

Using SQLcl Projects

Updated
•35 min read
Using SQLcl Projects
S
Scott Spendolini

One of the more difficult parts of the software development lifecycle is deployment. While it seems simple on the surface - just write some scripts and run them - it gets pretty complicated pretty quickly as the level of sophistication of your systems increase.

Assume that you’re developing an employee management application. At the core of this system is a table called EMP:

SQL> desc emp;

Name        Null?       Type             
___________ ___________ ________________ 
EMPNO       NOT NULL    NUMBER(4)        
ENAME                   VARCHAR2(10)     
JOB                     VARCHAR2(9)      
MGR                     NUMBER(4)        
HIREDATE                DATE             
SAL                     NUMBER(7,2)      
COMM                    NUMBER(7,2)      
DEPTNO                  NUMBER(2)

To deploy your application, you can create a script called emp.sql and simply run it in production. And that’s exactly what you do.

Before you know it, your table has a bunch of data about the employees of the organization and people love using it. The next week, you get an enhancement request to add a column to track employee’s email addresses. Sounds simple enough, right?

Just run the following script:

SQL> alter table emp add (email varchar2(100));

Simple, right?

What happens if you scale up your development? Now, instead of one table and one developer, you have hundreds of tables, packages and views and ten or more developers.

And what happens if you also add multiple target systems to the mix? Or want to ensure that changes are properly tested? Or want to incorporate a CI/CD pipeline for automation?

Things will get really complicated really quickly if you’re going to rely on manual processes. It’s a matter of time before the wrong script is run on the wrong environment.

The good news is that there are modern tools & methodologies available that were designed to help alleviate this complexity. This blog will dive into the details of one of them - SQLcl Projects & Liquibase.

About Liquibase

Before we dive into SQLcl Projects, let’s talk Liquibase for a minute. Liquibase is an open source utility that helps promote database schema-level changes. SQLcl ships with a fork of Liquibase embedded in it, so if you have SQLcl, you have Liquibase and there’s no need to download anything else.

At a high level, Liquibase creates a “ledger” of sorts to track all DDL called changelogs. These changelogs can be pointed at any schema and “replayed” to install a set of database objects. The changelogs also prevent Liquibase from running the same script twice, ensuring database object integrity.

Explain it Like I’m 5 Years Old

Sure! Let’s walk through a simple example and how Liquibase helps manage your development lifecycle.

Let’s stick with our simple example that contains a single table called EMP. You can easily script out the DDL for these tables and even the INSERT statements to populate them. Traditionally, when you’re ready to deploy, you would hand those scripts to a DBA who would then run them in the next tier - QA, TEST, PROD, etc.

With Liquibase, things are similar - but different. Think of Liquibase as a ledger. This ledger - or in Liquibase terms, changelog - will keep track of all scripts that are run for a specific target. This way, it will only ever execute each script once as well as keep an running audit of which was run.

In our example, our script to create the EMP table will be deployed by Liquibase to the target. Given that we have a brand new schema with nothing in it, Liquibase runs the script, creates the table and adds an entry to it’s changelog. So far, so good.

Let’s say that we need to add a new column to EMP. Clearly, we can’t re-create the entire table, as we would lose all of our data. Instead, we create and deploy a script that only adds the new column. Liquibase will then run the single script, which adds the column to the table and adds another entry to the changelog.

So far, this is not unlike how you would do things without Liquibase.

So, what’s the difference?

It’s easy to mentally manage two changes to a single system. It’s not as easy to manage hundreds of changes to many systems, especially across many developers and many target systems.

If you have multiple target databases or have the need to frequently generate test databases automatically to confirm your changes as you develop, you’ll wonder how you lived without Liquibase. Using Liquibase, each target gets its own changelog of which releases have been applied. Releases are also inclusive, meaning that you can start with any release you like and not have to manually apply previous ones.

Or, if you’re building a packaged solution that multiple users will download and use, Liquibase makes it easy for them to keep up with changes. They can choose how often to upgrade and be assured that with no additional effort, they will always be able to patch to the latest version.

Liquibase is also completely scriptable and can easily fit into any CI/CD process. This enables developers to automate the deployment of their code to any number of targets across development and deployment pipelines.

And finally - and perhaps most importantly - the Liquibase changelog serves as an audit table as to who made what change when. In many organizations, auditing these type of actions are not just important, it’s the law.

SQLcl Projects

Last year, SQLcl introduced a new feature called Projects. From the SQlcl Projects documentation:

The project command in Oracle SQLcl is a powerful tool designed to standardize database software versioning and create releasable artifacts, including APEX elements. This command supports a consistent model of development and operations, enabling repeatable builds that can be applied in a specific order.

While SQLcl has had Liquibase support for some time now - this new Projects feature makes using Liquibase even easier and more intuitive than before. Think of it as a set of commands that act as a wrapper to Liquibase that make managing development easier. SQLcl Projects syntax is simple and easy to learn, and the tool itself automates a number of the requirements found when working with Liquibase.

SQlcl Projects Workflow

SQLcl Projects has a prescribed workflow when it comes to building deployable releases:

Here’s a breakdown of each phase.

Build

Build is where you as a developer build your application. This involves creating database objects of all sorts, as well as APEX applications. In many cases, you will “over-create” and end up with some objects that just don’t need to get deployed.

Stage

As you complete your work, you “stage” it. Stage is where your release will be built from. SQLcl Projects can automatically generate and stage DDL scripts based on what you create in the database, or you can manually do this on your own. Thus, you can simply issue DDL commands from any tool and either stage the scripts or let SQLcl do it for you.

Release

Once you have staged all of your scripts, it’s time to cut a release. A release is a collection of your scripts blended in with the required files for SQLcl to be able to properly deploy it. Again, this process is as simple as issuing a command; SQLcl again does the heavy lifting or you.

Deploy

Once you have a release artifact, deploying it is also as simple as a single-line command. Release artifacts - which are really just ZIP files - can be deployed manually or as part of a CI/CD release process.

My First Project

Let’s get to the fun part and build our first SQLcl Project. To keep things simple, we’re going to stick with the use case I just described. We’ll build an “application” that consists of just EMP & DEPT and deploy it with SQLcl Projects.

Prerequisites

In order to follow along, you’ll need to have access to the following resources:

  • SQLcl version 25.2+ installed on your machine

  • VS Code

  • SQL Developer for VS Code

  • An instance of Git; github.com will work just fine and is used in this example

  • Access to two Oracle database instances or two PDBs that are the same or at least similar versions

    • You will need to create a new schema called DEMO in each database, so DBA-level access is required

    • In reality, these should be the exact same version; for our use case, something close should be fine

💡
A quick and free way to get a pair of Oracle Databases is to create a free Oracle Cloud account and then create them there.

Create & Seed the Repository

Let's start by creating a new repository and then using SQLcl Projects to seed it with your schema objects.

Create a Repository

First things first, create a new, clean repository. This can be anywhere; we'll use GitHub for this example.

  1. Navigate to github.com and sign in.

  2. Click New.

  3. Choose an Owner and enter a Repository name. We’ll use sqlcl-projects-demo for this demonstration.

  4. Click Create repository.

You now have a new, blank repository that we can use with SQLcl Projects.

Clone the Repository

Next, clone the repository to your local machine and change to the new directory. Be sure to update the username of the repo with yours!

git clone git@github.com:[your-github-user]/sqlcl-projects-demo.git
cd sqlcl-projects-demo

Create the main Branch

We'll need to create the main branch in the repository. These steps will create a basic "README" file and then commit and push it to a new branch called main.

echo "# sqlcl-projects-demo" >> README.md
git init
git add README.md
git commit -m "first commit"
git branch -M main
git push -u origin main

Create the Schema & Objects

Next, we need to create our development schema and seed it with a couple tables.

Create the DEMO Schema on the Source Database

For this example, we’ll create & deploy our objects from and to a schema called DEMO. Thus, we will need to create this schema on both databases that we’re going to use.

  1. Connect to the source database as a DBA-level user, such as SYSTEM or ADMIN (ADB).

  2. Run the following commands, ensuring to adjust the password and tablespace name, if needed.

create user demo identified by "StrongPassword1$";
alter user demo quota unlimited on users;
grant connect, resource, create view to demo;
  1. Next, create a connection in SQL Developer for VS Code for this schema. Name that connection demo. If using ADB, please be sure to select the _LOW connection; using others can cause issues with Liquibase.
💡
Need help installing or creating connections in SQL Developer VS Code? Have a look at the documentation.

Connect as the DEMO Schema

Next, we’ll connect to the owner of the source code. We can re-used a SQL Developer for VS Code connection string here, making it a lot easier. In our case, we’ve pre-configured a connection called demo to point to the database schema where we have our source code and database objects.

Let’s connect to it with SQLcl:

sql -name demo

Create the EMP & DEPT Tables

For this example, let’s keep things simple and use the standard EMP & DEPT tables.

As the demo user, run the following:

create table dept(
  deptno number(2,0),
  dname  varchar2(14),
  loc    varchar2(13),
  constraint pk_dept primary key (deptno)
);

create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references demo.dept (deptno)
);

Populate the EMP & DEPT Tables

Now that we have a pair of tables, let’s throw some data in them.

begin
insert into demo.dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into demo.dept values(20, 'RESEARCH', 'DALLAS');
insert into demo.dept values(30, 'SALES', 'CHICAGO');
insert into demo.dept values(40, 'OPERATIONS', 'BOSTON');
insert into demo.emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'),5000, null, 10);
insert into demo.emp values(7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'),2850, null, 30);
insert into demo.emp values(7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'),2450, null, 10);
insert into demo.emp values(7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'),2975, null, 20);
insert into demo.emp values(7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85,3000, null, 20);
insert into demo.emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'),3000, null, 20);
insert into demo.emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'),800, null, 20);
insert into demo.emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'),1600, 300, 30);
insert into demo.emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'),1250, 500, 30);
insert into demo.emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'),1250, 1400, 30);
insert into demo.emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'),1500, 0, 30);
insert into demo.emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51,1100, null, 20);
insert into demo.emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'),950, null, 30);
insert into demo.emp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'),1300, null, 10);
commit;
end;
/

SQLcl Projects

Let’s turn our attention to configuring our first project with SQLcl. A project in SQLcl consists of a number of files:

  • Liquibase changelogs

  • Schema objects

  • Deployment scripts

  • Configuration files

These files act in concert to assist with the development and deployment of your code. Using SQLcl Project commands, developers can more easily manage their code without having to manually edit configuration files.

Initialize Project

While still logged into the DEMO schema, let’s create a new project called DEMO. Run the following command from SQLcl:

project init -name demo -schemas demo

You should see the following, or something similar:

—-------------------
PROJECT DETAILS
—-------------------
Project name: demo 
Schema(s): DEMO 
Directory: /Users/scspend/Github/demo 
Connection name: faegans_demo 
Project root: demo 
Your project has been successfully created

You should also see some files & folders appear in your local working copy directory:

Let’s take a second to run through the purpose of these files.

.dbtools/filters/project.filtersThis file controls which types of database objects are included when you run an export command from SQLcl.
.dbtools/project.config.jsonThis is the project configuration file. You can make changes to the values here, should you need to change any of them. One option that you may want to change is expSavedReports; setting this to true will include any saved APEX reports that you may have.
.dbtools/project.sqlformat.xmlThis file controls how the generated SQL will be formatted.
dist/install.sqlThis is the main installation file that will be called when deploying a project. It will call Liquibase, which will handle the bulk of the deployment.
.gitignoreThis file will tell Git which types of files to ignore and not include in the repository.

Create the Initial Release Branch

Next, we need to create our initial release, which we will call 1.0. This represents the initial set of objects that we will include in our application.

💡
Some commands will need to be run from SQLcl, while others need to be run from the command line. Be mindful of which commands are run where.

From the command line, run the following:

git checkout -b release-1.0

We can validate that we’ve successfully switched to that branch by running the following command:

git branch --show-current

You should see release-1.0 if successful.

Export, Commit & Stage Schema Objects

Next, we’ll use SQLcl Project’s export command to create scripts for our database objects.

Export Schema Objects

The export command is one of the coolest part of SQLcl Projects. With a single command, I can generate all of the scripts I need for my schema objects. I no longer need to maintain a separate of SQL scripts for my database objects, as I can simply generate them. I can also run export more discretely and export a single, specific object.

Of course, if you want to maintain scripts or already have them that’s also supported.

Let’s use the export command to generate ours from SQLcl:

project export

The results will look like this:

The current connection FAEGANS_TP DEMO will be used for all operations
*** TABLES ***
*** REF_CONSTRAINTS ***
-------------------------------
REF_CONSTRAINT                1
TABLE                         2
-------------------------------
Exported 3 objects
Elapsed 11 sec

Let’s take another look at the local working copy files now:

Notice that there’s three new files in src/database/demo. These are the three schema objects that were in our source schema. If we take a look at one - for instance, emp.sql - we see mostly what we’d expect to see:

create table demo.emp (
    empno    number(4, 0),
    ename    varchar2(10 byte),
    job      varchar2(9 byte),
    mgr      number(4, 0),
    hiredate date,
    sal      number(7, 2),
    comm     number(7, 2),
    deptno   number(2, 0)
);

alter table demo.emp
    add constraint pk_emp primary key ( empno )
        using index enable;


-- sqlcl_snapshot {"hash":"22c6c8ad205e23dd589af01b8cfdd5e06a2b6add","type":"TABLE","name":"EMP","schemaName":"DEMO","sxml":"\n  <TABLE xmlns=\"http://xmlns.oracle.com/ku\" version=\"1.0\">\n   <SCHEMA>DEMO</SCHEMA>\n   <NAME>EMP</NAME>\n   <RELATIONAL_TABLE>\n      <COL_LIST>\n         <COL_LIST_ITEM>\n            <NAME>EMPNO</NAME>\n            <DATATYPE>NUMBER</DATATYPE>\n            <PRECISION>4</PRECISION>\n            <SCALE>0</SCALE>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>ENAME</NAME>\n            <DATATYPE>VARCHAR2</DATATYPE>\n            <LENGTH>10</LENGTH>\n            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>JOB</NAME>\n            <DATATYPE>VARCHAR2</DATATYPE>\n            <LENGTH>9</LENGTH>\n            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>MGR</NAME>\n            <DATATYPE>NUMBER</DATATYPE>\n            <PRECISION>4</PRECISION>\n            <SCALE>0</SCALE>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>HIREDATE</NAME>\n            <DATATYPE>DATE</DATATYPE>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>SAL</NAME>\n            <DATATYPE>NUMBER</DATATYPE>\n            <PRECISION>7</PRECISION>\n            <SCALE>2</SCALE>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>COMM</NAME>\n            <DATATYPE>NUMBER</DATATYPE>\n            <PRECISION>7</PRECISION>\n            <SCALE>2</SCALE>\n            \n         </COL_LIST_ITEM>\n         <COL_LIST_ITEM>\n            <NAME>DEPTNO</NAME>\n            <DATATYPE>NUMBER</DATATYPE>\n            <PRECISION>2</PRECISION>\n            <SCALE>0</SCALE>\n            \n         </COL_LIST_ITEM>\n      </COL_LIST>\n      <PRIMARY_KEY_CONSTRAINT_LIST>\n         <PRIMARY_KEY_CONSTRAINT_LIST_ITEM>\n            <NAME>PK_EMP</NAME>\n            <COL_LIST>\n               <COL_LIST_ITEM>\n                  <NAME>EMPNO</NAME>\n               </COL_LIST_ITEM>\n            </COL_LIST>\n            <USING_INDEX></USING_INDEX>\n         </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>\n      </PRIMARY_KEY_CONSTRAINT_LIST>\n      <DEFAULT_COLLATION>USING_NLS_COMP</DEFAULT_COLLATION>\n      <PHYSICAL_PROPERTIES>\n         <HEAP_TABLE></HEAP_TABLE>\n      </PHYSICAL_PROPERTIES>\n      \n   </RELATIONAL_TABLE>\n</TABLE>"}

There is one notable difference - the last line that is commented out. This line was automatically added by SQLcl when we ran the export command. Essentially, this is a JSON document that contains metadata about the SQL script. If we format the XML portion of the JSON document, we get a much clearer picture of what it is:

<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <SCHEMA>DEMO</SCHEMA>
   <NAME>EMP</NAME>
   <RELATIONAL_TABLE>
      <COL_LIST>
         <COL_LIST_ITEM>
            <NAME>EMPNO</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>4</PRECISION>
            <SCALE>0</SCALE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>ENAME</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH>10</LENGTH>
            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>JOB</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH>9</LENGTH>
            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>MGR</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>4</PRECISION>
            <SCALE>0</SCALE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>HIREDATE</NAME>
            <DATATYPE>DATE</DATATYPE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>SAL</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>7</PRECISION>
            <SCALE>2</SCALE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>COMM</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>7</PRECISION>
            <SCALE>2</SCALE>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>DEPTNO</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <PRECISION>2</PRECISION>
            <SCALE>0</SCALE>
         </COL_LIST_ITEM>
      </COL_LIST>
      <PRIMARY_KEY_CONSTRAINT_LIST>
         <PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
            <NAME>PK_EMP</NAME>
            <COL_LIST>
               <COL_LIST_ITEM>
                  <NAME>EMPNO</NAME>
               </COL_LIST_ITEM>
            </COL_LIST>
            <USING_INDEX></USING_INDEX>
         </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
      </PRIMARY_KEY_CONSTRAINT_LIST>
      <DEFAULT_COLLATION>USING_NLS_COMP</DEFAULT_COLLATION>
      <PHYSICAL_PROPERTIES>
         <HEAP_TABLE></HEAP_TABLE>
      </PHYSICAL_PROPERTIES>
   </RELATIONAL_TABLE>
</TABLE>

It’s a lot more obvious that the XML portion of this defines our table and associated constraint.

The point of this JSON document is a “snapshot” or sorts that SQLcl will use when managing changelogs. It’s best to leave it alone, as changing it can likely cause more harm than good.

Commit to Git

Now that we have our initial scripts, let’s commit those to Git. This is necessary because in the next step - staging - it will only stage files that are committed.

Let’s add and commit the new files that were created as part of the project creation and export. Run the following commands from the command line:

git add -A
git commit -m "Initial commit"

After the commit, you will see something similar to this:

[release-1.0 0005da5] Initial commit
 9 files changed, 254 insertions(+)
 create mode 100644 .dbtools/filters/project.filters
 create mode 100644 .dbtools/project.config.json
 create mode 100644 .dbtools/project.sqlformat.xml
 create mode 100644 .gitignore
 create mode 100644 dist/install.sql
 create mode 100644 src/database/demo/ref_constraints/fk_deptno.sql
 create mode 100644 src/database/demo/tables/dept.sql
 create mode 100644 src/database/demo/tables/emp.sql

Staging the Project

Now that we’ve committed our changes, we can stage the project. Staging is a process where SQLcl will create a set of files that will eventually become a release. Think of staged files as a temporary place that we can add and remove files from during our development process before we create a release. Files that are staged will eventually become released.

To stage your project, enter the following from SQLcl:

project stage

If we look at the local working copy now, notice that there’s a whole new set of files & folders, highlighted in green:

Everything under the dist/releases/next folder is the staging area. It’s extremely similar to the src/database/demo folder, as it’s literally a copy of those files with one notable difference. Here’s the emp.sql file under dist/releases/next/changes/release-1.0/demo/tables:

-- liquibase formatted sql
-- changeset DEMO:1756306177836 stripComments:false  logicalFilePath:release-1.0/demo/tables/emp.sql runAlways:false runOnChange:false replaceIfExists:true failOnError:true
-- sqlcl_snapshot src/database/demo/tables/emp.sql:null:3fb1c8da8567805e174f2feea15ffb986a37c120:create

create table demo.emp (
    empno    number(4, 0),
    ename    varchar2(10 byte),
    job      varchar2(9 byte),
    mgr      number(4, 0),
    hiredate date,
    sal      number(7, 2),
    comm     number(7, 2),
    deptno   number(2, 0)
);

alter table demo.emp
    add constraint pk_emp primary key ( empno )
        using index enable;

Notice that we now have Liquibase-related comments added to the top of the file and the SQLcl-generated JSON document at the bottoms of the file is gone. This is the standard way of marking up a file so that Liquibase can process it.

Changelogs

There’s now three “changelog” files that were added to the local working copy. Let’s dig into each one of them.

main.changelog.xmlThis file is referenced by the install.sql script when Liquibase runs. it simply has an include directive that points to release.changelog.xml, the next file in our sequence of changelog files.
release.changelog.xmlThis file is the changelog for the specific release. If additional directives needed to be added, you can make the changes here.
stage.changelog.xmlThis is where the actual DLL changes are tracked. If you inspect this file, you will see references to the .sql files under the changes/release-1.0 directory. Liquibase will run these files in the order that they are listed during a deployment. It is not recommended to change this file manually, as it will be managed automatically when you run project stage.

Utilities

There’s also a pair of files in the utilities - or utils - directory:

utils/prechecks.sqlEnsures that the correct SQLcl version is used to deploy the changes
utils/recompile.sqlScript to recompile invalid objects in your schema

Add Custom Files

Before we can create our release, it would be nice to also automatically populate the EMP & DEPT tables with their respective datasets. SQLcl Projects allows us to also add custom files - those that are not automatically generated based on database objects to our project. Let’s do that.

In a SQLcl session, enter the following:

project stage add-custom -file-name seed.sql

Once this command is run, have a close look at your local working copy. A new file - seed.sql - has been added at releases/next/changes/release-1.0/_custom. If we take a look at that file, it’s blank aside from the three lines of Liquibase control information:

-- liquibase formatted sql
-- changeset  SqlCl:1756384780690 stripComments:false logicalFilePath:release-1.0/_custom/seed.sql
-- sqlcl_snapshot dist/releases/next/changes/release-1.0/_custom/seed.sql:null:null:custom

We will need to add our SQL script to this file, starting at line 4. The end result of seed.sql should look like this:

-- liquibase formatted sql
-- changeset  SqlCl:1756384780690 stripComments:false logicalFilePath:release-1.0/_custom/seed.sql
-- sqlcl_snapshot dist/releases/next/changes/release-1.0/_custom/seed.sql:null:null:custom
begin
insert into demo.dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into demo.dept values(20, 'RESEARCH', 'DALLAS');
insert into demo.dept values(30, 'SALES', 'CHICAGO');
insert into demo.dept values(40, 'OPERATIONS', 'BOSTON');
insert into demo.emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'),5000, null, 10);
insert into demo.emp values(7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'),2850, null, 30);
insert into demo.emp values(7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'),2450, null, 10);
insert into demo.emp values(7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'),2975, null, 20);
insert into demo.emp values(7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85,3000, null, 20);
insert into demo.emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'),3000, null, 20);
insert into demo.emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'),800, null, 20);
insert into demo.emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'),1600, 300, 30);
insert into demo.emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'),1250, 500, 30);
insert into demo.emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'),1250, 1400, 30);
insert into demo.emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'),1500, 0, 30);
insert into demo.emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51,1100, null, 20);
insert into demo.emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'),950, null, 30);
insert into demo.emp values(7934, 'MILLER', 'CLERK', 7782, to_date('23-1-1982','dd-mm-yyyy'),1300, null, 10);
commit;
end;
/

Make sure that you save seed.sql before continuing.

Before we generate a release, have a look at the stage.changelog.xml file one more time:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<include file="demo/tables/dept.sql" relativeToChangelogFile="true"/>
<include file="demo/tables/emp.sql" relativeToChangelogFile="true"/>
<include file="demo/ref_constraints/fk_deptno.sql" relativeToChangelogFile="true"/>
<include file="_custom/seed.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

Notice that there is a new reference for our seed.sql file. This was automatically added when we staged the file a moment ago.

Creating & Deploying a Release

Once we have finished a phase of development and are ready to ship our code, we need to create a release and deploy it. SQLcl Projects makes this as easy as running a couple of commands. Let’s get started.

Commit

Before creating a release, we need to be sure that we commit our code into Git. SQLcl will ignore any code that is not committed when building releases, so while this step is small, it’s critical.

As we have done in the past, from a terminal session, issue the following two commands:

git add -A
git commit -m "Release 1.0"

You should see something similar to the following:

[release-1.0 a78c91c] Release 1.0
 9 files changed, 402 insertions(+)
 create mode 100644 dist/releases/main.changelog.xml
 create mode 100644 dist/releases/next/changes/release-1.0/_custom/seed.sql
 create mode 100644 dist/releases/next/changes/release-1.0/demo/ref_constraints/fk_deptno.sql
 create mode 100644 dist/releases/next/changes/release-1.0/demo/tables/dept.sql
 create mode 100644 dist/releases/next/changes/release-1.0/demo/tables/emp.sql
 create mode 100644 dist/releases/next/changes/release-1.0/stage.changelog.xml
 create mode 100644 dist/releases/next/release.changelog.xml
 create mode 100644 dist/utils/prechecks.sql
 create mode 100644 dist/utils/recompile.sql

Verify the Project

It’s a good idea to verify that your project is ready to be released. The verify step looks at your changelog files, as well as other Liquibase-specific configurations to ensure that it can build a functional release. Adding the -verbose tag is not necessary, but when used, will highlight the specifics of any error or warning.

project verify -verbose

You should see something similar:

-------- Results Summary ----------
Errors:     0
Warnings:   0
Info:       6
---------------------------------

Level     Group Name     Test Name                     Message
-----     ----------     -----------                   -------------------------
INFO      settings       verifynonpublicsettings       No unsupported internal settings found
INFO      init           verifyprojectname             The final project name will be "demo"
INFO      examples       exampletest                   a message
INFO      stage          stagechangelogcomplete        Stage Changelog validation found no issues.
INFO      project        sqlclversion                  SQLcl Version check passed
INFO      snapshot       verifysnapshot                Snapshot validation found no issues.

Create the Release

Now that we have exported our objects as scripts and added a custom one to populate them with data, staged our changes and verified that they will run, it’s time to create out first release.

project release -version 1.0 -verbose

Creating a release does a few of things to our files & folders:

  • Moves the contents of the folder dist/releases/next to dist/releases/1.0

  • Creates a fresh dist/releases/next directory ready for the next release

  • Updates the main.changelog.xml file to include a reference to the new release

  • Updates the project.config.json file with the latest release number

Generate the Artifact

While it’s possible to manually take the source and run it against our target environment, that’s not the best approach at all. SQLcl Projects can automatically generate an artifact that consists of all necessary files to deploy this release.

This artifact will be inclusive. In other words, if you have release 1.0 and the artifact contains release 1.1, running it will only apply changes in 1.1. With a clean installation, the artifact will install 1.0 as well as 1.1. We’ll demonstrate an “upgrade” flow next, but before we do that, let’s generate the artifact.

In a SQLcl session, run the following command:

project gen-artifact -version 1.0

This command will generate an artifact in the form of a ZIP file. There should also now be a new folder in your local directory called artifact. Inside that folder will be a file called demo-1.0.zip - a combination of the project name and release.

Feel free to unzip the file and explore the contents. It will be very similar to what the dist/releases/1.0 directory in the working copy looks like.

One important thing to note - any file in the artifact directory will NOT be checked into your repository. This is due to the fact that that in the .gitignore file, there is a line that tells Git to ignore the artifact directory altogether.

You will need to manually move these artifacts to the system where they will be deployed.

Deploying a Release

Now that we have an artifact, we’re ready to deploy. Before we do, let’s review how Liquibase deploys artifacts. When deployed, SQLcl Projects will issue a command to run the install.sql file that is found in the ZIP. The install.sql file will, in turn run the contents of the main.changelog.xml file. That file will, in turn, run release.changelog.xml. That file will, in turn, execute all of the discrete scripts to create table, constraints and populate the tables with data.

All of the discrete scripts that SQLcl Projects creates will refer to database objects in the schema.object format. Because of this, there’s two ways we can deploy our application:

  • From the DEMO schema

  • From a more privileged schema

Which one you use will depend on your needs. If all of your database objects will be in a single schema, and that schema has the required privileges to run the installation scripts, then you can connect directly to that schema and deploy. However, if your application is spread across multiple schemas, you may want to connect as a more privileged schema and deploy from there.

Keep in mind that when SQLcl Projects deploys an application, it will need to create a couple of tables to track which release is installed. If you change your mind, it gets tricky reconciling the data in these tables, so when in doubt, err on the side of the more privileged schema to give yourself room to grow.

To keep things simple, we will use the DEMO schema on the target database for our deployment.

Create a Privileged Installation User

Should you need to create a privileged user for installations, take a look at how we do this for APEX-SERT. We use a schema called ACDC to run all of our Liquibase deployments. This is a much safer way to do things vs. using something like SYSTEM or ADMIN (ADB), as we only grant the privileges needed to get the job done.

Details about how to create the ACDC schema can be found here - specifically in section 2.2.1.

Create the DEMO Schema on the Source Database

Since we’re going to deploy with the DEMO user, let’s create that on the target database.

  1. Connect to the target database as a DBA-level user, such as SYSTEM or ADMIN (Autonomous).

  2. Run the following commands, ensuring to adjust the password and tablespace name, if needed.

create user demo identified by "StrongPassword1$";
alter user demo quota unlimited on users;
grant connect, resource, create view to demo;
  1. Next, create a connection in SQL Developer for VS Code for this schema. Name that connection demo_target. If using ADB, please be sure to select the _LOW connection; using others can cause issues with Liquibase.

Deploy the Artifact

Now that we have a new schema ready to go on the target database, let’s connect to it and kick off the deployment.

  1. Quit SQLcl and then re-connect to the target database as the DEMO schema.
sql -name demo_target

You should now be connected to a fresh schema with no objects. Let’s deploy our schema.

  1. Enter the following command:
project deploy -file artifact/demo-1.0.zip -verbose
  1. After a few seconds, you will see something like the following:
Check database connection...
Extract the file name: demo-1.0
Artifact decompression in progress...
Artifact decompressed: /var/folders/0_/nwtj13qx4bv2gzs667vs3h3w0000gn/T/23c6cc92-35ce-4f9f-9e2e-0ffb00f6e2d712784159437265393156
Starting the migration...
Running Changeset: release-1.0/demo/tables/dept.sql::1756346516693::DEMO
Table DEMO.DEPT created.


Table DEMO.DEPT altered.
Running Changeset: release-1.0/demo/tables/emp.sql::1756346516709::DEMO
Table DEMO.EMP created.


Table DEMO.EMP altered.
Running Changeset: release-1.0/demo/ref_constraints/fk_deptno.sql::1756346516684::DEMO
Table DEMO.EMP altered.
Running Changeset: release-1.0/_custom/seed.sql::1756384780690::SqlCl

Liquibase: Update has been successful. Rows affected: 4
Installing/updating schemas
--Starting Liquibase at 2025-08-29T06:41:26.162524 using Java 21.0.8 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Table DEMO.DEPT created.


Table DEMO.DEPT altered.
Table DEMO.EMP created.


Table DEMO.EMP altered.
Table DEMO.EMP altered.


UPDATE SUMMARY
Run:                          4
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            4


Produced logfile: sqlcl-lb-1756467685000.log

Operation completed successfully.
Migration has been completed
Removing the decompressed artifact: /var/folders/0_/nwtj13qx4bv2gzs667vs3h3w0000gn/T/23c6cc92-35ce-4f9f-9e2e-0ffb00f6e2d712784159437265393156...

We can easily verify that the release was deployed by querying the EMP table, like so:

SQL> select * from emp;

   EMPNO ENAME     JOB              MGR HIREDATE         SAL    COMM    DEPTNO 
________ _________ ____________ _______ ____________ _______ _______ _________ 
    7839 KING      PRESIDENT            17-NOV-81       5000                10 
    7698 BLAKE     MANAGER         7839 01-MAY-81       2850                30 
    7782 CLARK     MANAGER         7839 09-JUN-81       2450                10 
    7566 JONES     MANAGER         7839 02-APR-81       2975                20 
    7788 SCOTT     ANALYST         7566 19-APR-87       3000                20 
    7902 FORD      ANALYST         7566 03-DEC-81       3000                20 
    7369 SMITH     CLERK           7902 17-DEC-80        800                20 
    7499 ALLEN     SALESMAN        7698 20-FEB-81       1600     300        30 
    7521 WARD      SALESMAN        7698 22-FEB-81       1250     500        30 
    7654 MARTIN    SALESMAN        7698 28-SEP-81       1250    1400        30 
    7844 TURNER    SALESMAN        7698 08-SEP-81       1500       0        30 
    7876 ADAMS     CLERK           7788 23-MAY-87       1100                20 
    7900 JAMES     CLERK           7698 03-DEC-81        950                30 
    7934 MILLER    CLERK           7782 23-JAN-82       1300                10 

14 rows selected.

If you see 14 rows - congratulations - your release was successfully deployed!

Liquibase Tables

If you look closely at the DEMO schema, you will see an additional three tables were created. These are used by Liquibase to track deployments. Data in these tables should never be modified, as Liquibase will make changes as additional releases are deployed.

For the curious, here’s the names of them and their purpose:

DATABASECHANGELOGTracks which Liquibase changesets have been applied and ensures that the same changeset is not run twice.
DATABASECHANGELOGLOCKEnsures that only one Liquibase session is run at a time.
DATABASECHANGELOG_ACTIONSSQLcl’s version of which changelog has been run.

I always feel a little uneasy mingling Liquibase tables with my application tables. Hence, I typically will opt to manage my deployments with a separate schema, as mentioned earlier.

Merge Your Changes

Now is a good time to merge your changes to the repository. This way, the next branch that we pull will have the most up-to-date code. Let’s run through the steps.

Another Commit

First, let’s add & commit all of the files as we’ve done before. From a terminal, run the following:

git add -A
git commit -m "Release 1.0"

Push to Github

Next, we need to push our committed changes back to the repository.

git push -u origin release-1.0

You should see something similar to this:

[release-1.0 d13db64] Release 1.0
 10 files changed, 35 insertions(+), 8 deletions(-)
 create mode 100644 dist/releases/1.0/changes/release-1.0/_custom/seed.sql
 rename dist/releases/{next => 1.0}/changes/release-1.0/demo/ref_constraints/fk_deptno.sql (100%)
 rename dist/releases/{next => 1.0}/changes/release-1.0/demo/tables/dept.sql (100%)
 rename dist/releases/{next => 1.0}/changes/release-1.0/demo/tables/emp.sql (100%)
 rename dist/releases/{next => 1.0}/changes/release-1.0/stage.changelog.xml (100%)
 create mode 100644 dist/releases/1.0/release.changelog.xml
 delete mode 100644 dist/releases/next/changes/release-1.0/_custom/seed.sql
sspendol@Scotts-MacBook-Air sqlcl-projects-demo % git push -u origin release-1.0
Enumerating objects: 56, done.
Counting objects: 100% (56/56), done.
Delta compression using up to 10 threads
Compressing objects: 100% (43/43), done.
Writing objects: 100% (54/54), 11.20 KiB | 3.73 MiB/s, done.
Total 54 (delta 10), reused 0 (delta 0), pack-reused 0
remote: Resolving deltas: 100% (10/10), done.
remote: 
remote: Create a pull request for 'release-1.0' on GitHub by visiting:
remote:      https://github.com/sspendol/sqlcl-projects-demo/pull/new/release-1.0
remote: 
To github.com:sspendol/sqlcl-projects-demo.git
 * [new branch]      release-1.0 -> release-1.0
branch 'release-1.0' set up to track 'origin/release-1.0'.

Confirm & Merge Pull Request

Pushing the changes will trigger a pull request on Github. If we switch back to our browser, we should see that pull request:

Go ahead and click the Compare & pull request button. Enter any comments that you’d like to track and click Create pull request.

Github will check to ensure that there are no conflicts with your pull request, and if so, allow you to merge it into the main branch. Click Merge pull request to do so.

One more step - enter a commit message, select a user and then click Confirm merge to complete the merge process.

Once your changes are merged, you can safely delete the release-1.0 branch if you want to.

If you click the Code tab, you should see your committed source code from the local working copy now in the main branch.

Keep in mind that a merge will usually trigger a code review process, and developers don’t usually approve their own merges.

Changing the Project

If you’ve been a developer for more than 15 minutes, you’re well aware of the fact that end users sometimes don’t know what they want. For example, with our simple EMP & DEPT tables, how would we add an additional column to EMP - say something like EMAIL - and deploy that change on top of release 1.0? This is where SQLcl Projects & Liquibase shine.

Modifying EMP

We clearly can’t recreate EMP from scratch, as there is data in there that we don’t want to use. Thus, we will need to issue an ALTER TABLE command as part of our next release so that the new column is added without interfering with the data. Or do we…

Create a New Branch

Let’s start out by creating a new branch in our repository:

git checkout -b release-1.1

Alter the Table

One of the benefits of SQLcl Projects is that it doesn’t care how the DDL changes are made. While we could create a file that contains the ALTER TABLE statement, we can also just run the ALTER TABLE and let SQLcl Projects do all the work. Let’s opt for that route.

Connect to your source database as the DEMO user.

sql -name demo

Issue the ALTER TABLE command to add the email column. Don’t worry about saving this script anywhere, as Liquibase will automatically generate it later.

alter table emp add (email varchar2(100));

Export the Project

Next, we need to export the project again. Again, you can do this discretely for a single database object, or just run the command naked to get any objects that have changed.

project export

Once you’ve exported the project, notice that the file src/database/demo/tables/emp.sql has changed. It now includes our new EMAIL column.

“We can’t run this script!” you might be thinking. And you’re right and wrong at the same time.

You’re right in that we can’t run this script, as it would fail. But this is not the script we’re going to run. SQLcl Projects will take care of that when we stage our changes.

Add a Custom File

Let’s add a custom file that when run, will populate the new EMAIL column.

project stage add-custom -file-name seed_email.sql

Notice that the new file should be visible in the dist/releases/next/changes/release-1.1/_custom directory of our local working copy:

This concept of “next” release is important. As you start to stage files, they will be placed under the releases/next directory. They will remain here until a new release is created. At that point, they will be moved to a folder named after the release version, and a new releases/next directory will be created.

Edit the seed_email.sql file and add the following after the comments:

-- liquibase formatted sql
-- changeset  SqlCl:1756471732399 stripComments:false logicalFilePath:version-1.1/_custom/seed_email.sql
-- sqlcl_snapshot dist/releases/next/changes/version-1.1/_custom/seed_email.sql:null:null:custom
begin
update demo.emp set email = lower(ename) || '@example.com';
commit;
end;
/

Don’t forget to save your changes.

Add & Commit Your Files

Remember - before we can stage the project, we will need to add & commit our changes.

From a terminal, enter the following commands:

git add -A
git commit -m "Release 1.1"

Stage the Project

Now, from SQLcl, stage the project with the following command:

project stage

You should now see a new version of emp.sql file in your local working copy under dist/releases/next/changes/release-1.1:

Inspecting this file will reveal that SQLcl Projects was able to compare the baseline version of EMP with the current version of EMP and generate the following DDL to synchronize them:

-- liquibase formatted sql
-- changeset DEMO:1756472006274 stripComments:false  logicalFilePath:version-1.1/demo/tables/emp.sql runAlways:false runOnChange:false replaceIfExists:true failOnError:true
-- sqlcl_snapshot src/database/demo/tables/emp.sql:22c6c8ad205e23dd589af01b8cfdd5e06a2b6add:594f20e09b4dafdc75d54f0f6a617fda9fba7bdd:alter

alter table demo.emp add (
    email varchar2(100)
)
/

Here’s a curveball.

Have a look at the stage.changelog.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<include file="_custom/seed_email.sql" relativeToChangelogFile="true"/>
<include file="demo/tables/emp.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

See it? Our seed_email.sql script will run BEFORE the new EMAIL column is added to EMP. That’s because we staged the custom file before we staged the changes to EMP. Sometimes this happens with Liquibase, and we need to modify the order of the files in the changelog.

Fortunately, there’s a quick fix - just change the order of when the scripts are called:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<include file="demo/tables/emp.sql" relativeToChangelogFile="true"/>
<include file="_custom/seed_email.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

One thing that you’ll learn to appreciate when working with Liquibase is that order really matters, and it doesn’t always get it right. In this case, we had to swap two files. You will likely run into similar matters with your own projects.

Create a Release

Now that we have that sorted, let’s create and deploy the release. From SQLcl, run the following:

project release -version 1.1 -verbose

After running this command, have a look at your local working copy. You should see an additional folder called 1.1 under the dist/releases folder. If you expand change/version-1.1/demo/tables and inspect emp.sql, it should only contain the ALTER statement; not the full CREATE TABLE one.

This is the magic of Liquibase: if a user was applying 1.1 on top of 1.0, only the ALTER statement will be run. If a user was doing a clean installation of 1.1, it would run both the CREATE TABLE and ALTER TABLE statements sequentially.

Generate the Artifact

As we did before, let’s create a new artifact based on the new release we just created.

project gen-artifact -version 1.1

Deploy the Release

Let’s switch to our target database and deploy the release. Connect to SQLcl as the DEMO user on the target database:

sql -name demo_target

Once connected, all that’s left is to run the deployment.

project deploy -file artifact/demo-1.1.zip -verbose

When complete, we should see something like this:

Check database connection...
Extract the file name: demo-1.1
Artifact decompression in progress...
Artifact decompressed: /var/folders/0_/nwtj13qx4bv2gzs667vs3h3w0000gn/T/39ec72ef-8073-4483-81c4-1fbd34e9a10315832945422908414459
Starting the migration...
Running Changeset: version-1.1/demo/tables/emp.sql::1756472006274::DEMO
Table DEMO.EMP altered.
Running Changeset: version-1.1/_custom/seed_email.sql::1756471732399::SqlCl
PL/SQL procedure successfully completed.
Liquibase: Update has been successful. Rows affected: 2
Installing/updating schemas
--Starting Liquibase at 2025-08-29T08:31:46.608397 using Java 21.0.8 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Table DEMO.EMP altered.
PL/SQL procedure successfully completed.

UPDATE SUMMARY
Run:                          2
Previously run:               4
Filtered out:                 0
-------------------------------
Total change sets:            6


Produced logfile: sqlcl-lb-1756474305795.log

Operation completed successfully.
Migration has been completed
Removing the decompressed artifact: /var/folders/0_/nwtj13qx4bv2gzs667vs3h3w0000gn/T/39ec72ef-8073-4483-81c4-1fbd34e9a10315832945422908414459...

To prove that it did, in fact, work, run this SQL:

select * from emp;

   EMPNO ENAME     JOB              MGR HIREDATE         SAL    COMM    DEPTNO EMAIL                 
________ _________ ____________ _______ ____________ _______ _______ _________ _____________________ 
    7839 KING      PRESIDENT            17-NOV-81       5000                10 king@example.com      
    7698 BLAKE     MANAGER         7839 01-MAY-81       2850                30 blake@example.com     
    7782 CLARK     MANAGER         7839 09-JUN-81       2450                10 clark@example.com     
    7566 JONES     MANAGER         7839 02-APR-81       2975                20 jones@example.com     
    7788 SCOTT     ANALYST         7566 19-APR-87       3000                20 scott@example.com     
    7902 FORD      ANALYST         7566 03-DEC-81       3000                20 ford@example.com      
    7369 SMITH     CLERK           7902 17-DEC-80        800                20 smith@example.com     
    7499 ALLEN     SALESMAN        7698 20-FEB-81       1600     300        30 allen@example.com     
    7521 WARD      SALESMAN        7698 22-FEB-81       1250     500        30 ward@example.com      
    7654 MARTIN    SALESMAN        7698 28-SEP-81       1250    1400        30 martin@example.com    
    7844 TURNER    SALESMAN        7698 08-SEP-81       1500       0        30 turner@example.com    
    7876 ADAMS     CLERK           7788 23-MAY-87       1100                20 adams@example.com     
    7900 JAMES     CLERK           7698 03-DEC-81        950                30 james@example.com     
    7934 MILLER    CLERK           7782 23-JAN-82       1300                10 miller@example.com    

14 rows selected.

There’s our 14 rows, complete with a new, populated column for email address.

Add, Commit & Push to Github

As we did with the initial release, we want to be sure to push our changes back to Github and merge them into the main branch.

git add -A
git commit -m "Release 1.1"
git push -u origin release-1.1

Flip back over to your browser and complete the merge. Use the same sequence as you did to merge the release-1.0 in the previous section. Here’s the high-level steps:

  • Click the Compare & pull request button

  • Enter any comments that you’d like to track and click Create pull request

  • Click Merge pull request

  • Enter a commit message, select a user and then click Confirm merge

  • Delete the version-1.1 branch

  • Click the Code tab to confirm your changes were merged

Don’t forget to remove your working copy and/or switch back to the main branch, as the release-1.1 branch was deleted from the repository.

Conclusion

Managing database changes might seem straightforward when you’re working alone on a single table, but as soon as your application and team grow, the cracks start to show. Ad-hoc scripts quickly become messy, inconsistent, and risky to deploy. That’s where structured approaches like SQLcl Projects and Liquibase come in. They provide a repeatable, controlled way to evolve your schema with confidence, while still keeping the flexibility developers need.

By treating database changes like source code, you not only reduce errors but also improve collaboration, traceability, and long-term maintainability. SQLcl Projects helps developers of all experience levels easily adapt into the world of automated deployments. It’s a great first step to get into the world of CI/CD pipelines, as the barrier to entry is low and benefit is high.

And yes, APEX is supported with SQLcl Projects. Look for another blog about how to do that in the coming days.

Additional Reading

Still want to learn more about SQLcl Projects or want to hear about how others use it? Look no further than these articles - some of which I referred to while writing this post:

Title photo by Chris Ried on Unsplash

#oracle-apex#oracle#oracle-cloud#oracle-database#sqlcl#liquibase#ci-cd#devops
2.3K views

More from this blog

S

Spendolini Blog

30 posts

Long-time Oracle APEX developer currently managing a development team @ Oracle.

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.