Welcome to part 5 of "A whirlwind tour of Database Schema Changes Tracking Tools"

In this article, I will provide a general overview and test the basic functionalities of the Oracle SQLcl “Project” command.

I will cover only CI/CD for database changes. The APEX part is covered in a separate blog post here. However, I encourage you to read a current article first.

If you’re interested in more details, read the official documentation here.

Prerequisites

• I have two environments (DEV and PROD) on my Docker for MacOS (read here how I’ve set it).

• I will use connection names DOCKER_HR_DEV and DOCKER_HR_PROD to connect to it. (read about connection aliases here)

• DEV and PROD databases have the same number of objects created manually in past deployments.

• All commands you will see in this blog will be executed using SQLcl 24.3.1 (read how to install it here)

1.Start using SQLcl Project with existing or new databases using project init

1. Create a SQLcl database project

• I have an empty folder SQLCL_PROJECT in my VS Code.

• Now, I will connect to my HR schema on DEV.

sql -name DOCKER_HR_DEV

• Initialize new project with project init command.

• My project will be called “BOMBSHELL”, and I want to initialize it with the schema “HR”.

--DOCKER_HR_DEV
project init -name BOMBSHELL -schemas HR

• Folders and files visible below were auto-created by SQLcl.

2. Initialize GIT repository.

• Now, I have to initialize a new GIT repository for created files and commit it.

!git init --initial-branch=main
!git add .
!git commit -m "chore: initializing repository with default project BOMBSHELL files"

3. Create the base-release branch, export DB objects from DEV and commit.

• Now, I will create a branch “base-release”, connect to DOCKER_HR_DEV, and export database objects into files.

• The steps are the same for new projects. You will export your files once some objects are created in the database.

    # this command creates new branch and switches to it
    # or use GUI tool to create it
    !git checkout -b base-release
  

• Now, I’ll export all DB files from DEV.

--DOCKER_HR_DEV
project export

• My database objects were exported into SQL files - see examples below.

• I have committed my base-release into GIT

  

4. Use project stage to create a deployment script

--DOCKER_HR_DEV
project stage

• Files necessary for deployment were automatically created:

5. Verify project using project verify

project verify -verbose

All good to go.

6. Run project release to finish release 1.0

• I will call my base release version “1.0”.

--DOCKER_HR_DEV
project release -version 1.0 -verbose

• SQLcl terminal says clearly what happened:

• My release was moved to /releases/1.0, and my next release is empty

7. Generate an artifact representing the current stage of the project using gen artifact

--DOCKER_HR_DEV
project gen-artifact -version 1.0-base-release

• Zip file BOMBSHELL-1.0-base-release.zip was generated (notice that artifacts are not versioned in GIT)

8. Deploy release (version 1.0) to PROD using project deploy

For new project

• If it were a new project, I would’ve deployed all my code to PROD using project deploy command

• It would look like that (connected to PROD or any other environments)

--DOCKER_HR_PROD
project deploy -file artifact/BOMBSHELL-1.0-base-release.zip -verbose

For existing projects

• In my case (I already have objects in PROD), I have to manually mark version 1.0 as released to PROD by running liquibase changelog-sync command

• This command won’t change any of your DB objects - it will only create necessary liquibase tables and make some inserts into databasechangelog table

--DOCKER_HR_PROD
sql -name DOCKER_HR_PROD
liquibase changelog-sync -chf dist/releases/main.changelog.xml

9. GIT Merge “base-release”(DEV) branch into “main” (PROD)

• I have to merge my base-release branch into main because, at this moment,t my DEV (base-release) is equal to PROD(main)

2. Create new changes on DEV and deploy to PROD as version 1.1

1. Make some changes directly in DEV environment (use VS Code or SQL Dev etc.)

INSERT INTO HR.COUNTRIES (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES ('PL', 'Poland', '10');

CREATE TABLE HR.PARAMETERS (NAME VARCHAR2(50),
                           VALUE VARCHAR2(50));

ALTER TABLE HR.EMPLOYEES 
ADD (ADDRESS VARCHAR2(100) );

 CREATE OR REPLACE PROCEDURE HR.SECURE_DML
 IS
 BEGIN
    IF TO_CHAR(SYSDATE, 'HH24:MI') not between '08:00' and '18:00'
       or TO_CHAR(SYSDATE, 'DY') in ('SAT', 'SUN')
    THEN
       RAISE_APPLICATION_ERROR(-20205,
                               'You may only make changes during normal office hours (this bracket is a change)');
    END IF;
END SECURE_DML;
/

INSERT INTO HR.PARAMETERS (name, value) VALUES ('MY_BLOG_URL','rafal.hashnode.dev');
COMMIT;

2. Create a new GIT branch called version-1.1

3. Export changes made using project export

--on branch version-1.1
--connected to DOCKER_HR_DEV
project export

• This command exported my actual state of DEV database objects. As we can see, there are three new/changed files:

4. Use project stage to create a deployment script

--DOCKER_HR_DEV
project stage

• This command created some new files for my release 1.1

• As you can notice, SQLcl automatically prepared required scripts

5. Use project stage add-custom to deploy custom scripts, e.g DML

• As you may remember, on my DEV, in addition to changes in the EMPLOYEES and PARAMETERS tables and the procedure SECURE_DML, I’ve also executed two inserts into the COUNTRIES and PARAMETERS tables.

• This is something that SQLcl won’t track automatically, and we need to use add-custom command for that

• That’s why I’ve created two files for DMLs in both tables:

--DOCKER_HR_DEV
project stage add-custom -file-name dml_employees
project stage add-custom -file-name dml_countries

• And SQLcl automatically created those files:

• There’s no need to change anything in these files. I will only put my insert scripts there:

6. Verify project using project verify

• I will verify my project again:

--DOCKER_HR_DEV
project verify

• And we are good to go

7. Run project release to finish release 1.1

--DOCKER_HR_DEV
project release -version 1.1 -verbose

• SQLcl terminal says clearly what happened:

• My release was moved to /releases/1.1, and my next release is empty

8. GIT Commit version 1.1 and merge into the main branch

• I like to do this to make it clear. Since my release is ready to deploy to PROD, I should deploy it from the main (PROD) branch.

• Git commit version 1.1 on branch version 1.1

• Merge branch version-1.1 into MAIN

9. Generate an artifact representing the current stage (version-1.1) of the project using gen artifact

• Now, I will create an artifact for my version-1.1

--DOCKER_HR_PROD
--on GIT branch MAIN(prod)
project gen-artifact -version version-1.1

10. Deploy release (version-1.1) to PROD using project deploy

• I’m ready to deploy my changes to PROD.

--DOCKER_HR_PROD
project deploy -file artifact/BOMBSHELL-version-1.1.zip -verbose

• The SQLcl terminal clearly states what happened, but the most important thing is that all my five changes were deployed to PROD.

I hope you like this article.

Drop me a comment if you have any questions or thoughts.

Bests,

Rafal

PS SQLcl Projects also supports APEX deployment, which I will cover in a separate article.

PPS: Wondering about the picture? It’s right before the sunrise in Kielce (Poland)

Other tested Tools