Part 5: SQLcl Project (It will forever change your Database & APEX deployments)
Table of contents
- Prerequisites
- 1.Start using SQLcl Project with existing or new databases using project init
- Create a SQLcl database project
- Initialize GIT repository.
- Create the base-release branch, export DB objects from DEV and commit.
- Use project stage to create a deployment script
- Verify project using project verify
- Run project release to finish release 1.0
- Generate an artifact representing the current stage of the project using gen artifact
- Deploy release (version 1.0) to PROD using project deploy
- GIT Merge “base-release”(DEV) branch into “main” (PROD)
- 2. Create new changes on DEV and deploy to PROD as version 1.1
- Make some changes directly in DEV environment (use VS Code or SQL Dev etc.)
- Create a new GIT branch called version-1.1
- Export changes made using project export
- Use project stage to create a deployment script
- Use project stage add-custom to deploy custom scripts, e.g DML
- Verify project using project verify
- Run project release to finish release 1.1
- GIT Commit version 1.1 and merge into the main branch
- Generate an artifact representing the current stage (version-1.1) of the project using gen artifact
- Deploy release (version-1.1) to PROD using project deploy
- Other tested Tools
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
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.
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"
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
Use
project stage
to create a deployment script
--DOCKER_HR_DEV
project stage
- Files necessary for deployment were automatically created:
Verify project using
project verify
project verify -verbose
All good to go.
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
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)
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
commandIt 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
commandThis 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
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
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;
Create a new GIT branch called version-1.1
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:
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
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 thatThat’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:
Verify project using
project verify
- I will verify my project again:
--DOCKER_HR_DEV
project verify
- And we are good to go
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
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
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
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
Tool review & test | Official documentation | |
Liquibase (standalone Open-Source edition) | Link | Doc |
Oracle SQLcl with Liquibase | Link | Doc |
Flyway Community Edition | Link | Doc |
D.O.M.E - Deployment Organisation Made Easy | Link | Doc, Videos |
Oracle’s SQLcl “Project” feature | Link | Doc |
ADT - APEX Deployment Tool | Not published yet | Doc |
dbFlow | Not published yet | Doc |