Drawback: we have to implement modifications to our database schemas in an orderly and managed method, identical to we’d for our software code.
Resolution: utilizing Oracle’s free SQLcl Mission function to routinely keep your database supply code, generate deployable artifacts, and handle the deployment of these artifacts within the database.
>>🤔 Already utilizing SQLcl Liquibase and surprise how that is completely different?
This submit will go over Oracle’s prescribed methodology for managing your Oracle Database software schema – your tables, views, saved procedures, REST APIs, and APEX apps – and delivering set up and upgrades.
Necessities
- Oracle SQLcl 24.4 or later
- Git
- A minimum of one Oracle Database (2 schemas)
If you wish to have a play with this function and borrow OUR atmosphere, I invite you to strive our absolutely hosted Oracle LiveLabs atmosphere.
What this submit will exhibit
We’re going to take an present Oracle Database schema, and put it beneath management utilizing the venture command in SQLcl.
I’ll present all of the SQLcl venture instructions and the related Git workflows. For those who’ve used Git earlier than, this needs to be routine! We’re simply making the era of the Oracle Database object and PL/SQL program scripts, and sustaining these for releases, straightforward.
In the present day’s steps:
- migrating an present software to SQLcl venture
- making modifications to our schema (DEV_USER)
- export mentioned modifications
- stage the modifications
- launch modifications
- generate artifact (the installer)
- deploy to manufacturing (PROD_USER)
Migrating an present software
As a substitute of ranging from nothing, we’ll begin from an present schema. This schema maybe is getting used to energy an software. For those who use our LiveLabs tutorial and atmosphere, the tip person app would love this –
This app is powered by only one easy desk and it’s related REST APIs.
Earlier than we will begin deploying updates to our database, we have to get the database reconciled with our venture because it’s going to be outlined in our Git repo, and still have the Liquibase metadata setup within the schema so updates might be managed and saved accountable.
So let’s get began by organising our native improvement atmosphere. We’re going to fake we’re a single developer, simply engaged on our personal, however this know-how equally applies to giant improvement groups as properly.
Step 1: Initializing Git
We’ll be operating our command from SQLcl, and we’re going to be doing that as the applying schema proprietor. In case your venture has multiple schema, simply use an account with privs to cowl all of these schemas.
We’re going to hook up with the db as our person (DEV_USER), and run the git command, through the host (!) choice in SQLcl.
SQL> !git init --initial-branch primary
Initialized empty Git repository in /dwelling/property/property/workshops/sqlcl-projects-react-app/.git/
Subsequent we’ll do a commit, ensuring we’re within the root of our software listing. This may put our React app and it’s recordsdata in addition to our database objects into our Git repo.
SQL> !git commit -m "Preliminary commit"
[main (root-commit) 6bee390] Preliminary commit
57 recordsdata modified, 11189 insertions(+)
create mode 100755 .env.instance
create mode 100755 .eslintrc.cjs
create mode 100755 .gitignore
create mode 100755 CONTRIBUTING.md
create mode 100755 README.md
create mode 100755 index.html
create mode 100755 package-lock.json
create mode 100755 bundle.json
create mode 100755 public/property/ORCL.svg
create mode 100755 public/property/Q.svg
create mode 100755 public/property/RMIL_Buildings_Medium-Business_Air_RGB.png
create mode 100755 public/property/RMIL_Personas_Student-Demographics-M_Air_RGB.png
create mode 100755 public/property/RMIL_Personas_Workforce-2_Air_RGB.png
create mode 100755 public/property/RMIL_Technology_Database_Air_RGB.png
create mode 100755 public/property/RMIL_Technology_Oracle-Storage-Expertise-M_Air_RGB.png
create mode 100755 public/property/logo-blue.png
create mode 100755 public/property/oracle-icon (2).svg
create mode 100755 public/property/react.svg
create mode 100755 public/vite.svg
create mode 100755 scripts/attendance_table.sql
create mode 100755 scripts/departments_table.sql
create mode 100755 scripts/employees_table.sql
create mode 100755 scripts/performancereviews_table.sql
create mode 100755 server/index.cjs
create mode 100755 server/routes/connection.cjs
create mode 100755 server/utils/db/config.cjs
create mode 100755 server/utils/db/index.cjs
create mode 100755 server/utils/db/pockets/file.txt
create mode 100755 server/utils/rest-services/connection.cjs
create mode 100755 src/App.css
create mode 100755 src/App.jsx
create mode 100755 src/README.md
create mode 100755 src/componenets/charts/AttendanceChart.jsx
create mode 100755 src/componenets/charts/EmployeesPerDepartmentChart.jsx
create mode 100755 src/componenets/charts/PerformanceReview.jsx
create mode 100755 src/componenets/widespread/CostmButton.tsx
create mode 100755 src/componenets/widespread/DepartementCards.tsx
create mode 100755 src/componenets/widespread/DescriptionInfoAlert.tsx
create mode 100755 src/componenets/widespread/ErrorPopup.tsx
create mode 100755 src/componenets/widespread/PageContainerBasic.tsx
create mode 100755 src/componenets/widespread/PlaceHolder.tsx
create mode 100755 src/componenets/widespread/styleGrid.tsx
create mode 100755 src/componenets/options/DeleteForm.tsx
create mode 100755 src/componenets/options/EmployeeForm.tsx
create mode 100755 src/componenets/options/UpdateForm.tsx
create mode 100755 src/componenets/navigation/NavigationConfig.tsx
create mode 100755 src/componenets/pages/AnalyticsPage.tsx
create mode 100755 src/componenets/pages/DepartmentPage.tsx
create mode 100755 src/componenets/pages/EmployeeDisplayerPage.tsx
create mode 100755 src/componenets/pages/ErrorPage.tsx
create mode 100755 src/componenets/pages/HRPageContentSwitcher.tsx
create mode 100755 src/componenets/pages/HrDashboardLayout.tsx
create mode 100755 src/hooks/useFetchData.js
create mode 100755 src/index.css
create mode 100755 src/primary.jsx
create mode 100755 src/utils/formatData.jsx
create mode 100755 vite.config.jsSQL>
Now we’re able to create our SQLcl venture, and we’re going to name it HrManager.
SQL> venture init -name HrManager -schemas DEV_USER -verbose
Cleansing and validating venture title as wanted
The ultimate venture title will likely be "HrManager"
Created dir: sqlcl-projects-react-app/.dbtools
Created dir: sqlcl-projects-react-app/.dbtools/filters
Created dir: sqlcl-projects-react-app/dist
Created dir: sqlcl-projects-react-app/src/database
Created dir: sqlcl-projects-react-app/src/database/dev_user
Created file: sqlcl-projects-react-app/.dbtools/venture.sqlformat.xml
Created file: sqlcl-projects-react-app/.dbtools/filters/venture.filters
Created file: sqlcl-projects-react-app/dist/set up.sql------------------------
PROJECT DETAILS
------------------------
Mission title: HrManager
Schema(s): DEV_USER
Listing: /dwelling/property/property/workshops/sqlcl-projects-react-app
Connection title:
Mission root: sqlcl-projects-react-app
Your venture has been efficiently created
SQL>
The venture command has many sub-commands, together with ‘init.’ This units up the configuration of our venture, with crucial being the title of the schema(s) we’re going to be managing with Git.
It additionally units up our supply (src) listing construction, and creates our distributable (dist) set up.sql file.
That appears like this –
──.dbtools
│ ├── filters
│ │ └── venture.filters
│ ├── venture.config.json
│ └── venture.sqlformat.xml
├── dist
│ └── set up.sql
└── src
└── database
Now let’s add and commit what we’ve executed to date.
Let’s go create our primary department!
SQL> !git department
* primarySQL> !git standing
On department primary
Modifications not staged for commit:
(use "git add..." to replace what will likely be dedicated)
(use "git restore..." to discard modifications in working listing)
modified: .gitignore
modified: README.mdUntracked recordsdata:
(use "git add..." to incorporate in what will likely be dedicated)
.dbtools/
dist/no modifications added to commit (use "git add" and/or "git commit -a")
SQL> !git add --all
SQL> !git standing
On department primary
Modifications to be dedicated:
(use "git restore --staged..." to unstage)
new file: .dbtools/filters/venture.filters
new file: .dbtools/venture.config.json
new file: .dbtools/venture.sqlformat.xml
modified: .gitignore
modified: README.md
new file: dist/set up.sqlSQL> !git commit -m "Add venture recordsdata"
[main 0099913] Add venture recordsdata
6 recordsdata modified, 204 insertions(+)
create mode 100644 .dbtools/filters/venture.filters
create mode 100644 .dbtools/venture.config.json
create mode 100644 .dbtools/venture.sqlformat.xml
create mode 100644 dist/set up.sqlSQL>
Now we have to create a department/check-out. We’ll name it SQLcl-Tasks-Migration.
SQL> !git checkout -b SQLcl-Tasks-Migration
Switched to a brand new department 'SQLcl-Tasks-Migration'SQL> !git department
* SQLcl-Tasks-Migration
primarySQL> !git standing
On department SQLcl-Tasks-Migration
nothing to commit, working tree clearSQL>
Tip: use SQLcl’s statusbar to see your lively venture and Git department always
Merely run:
SET statusbar ON
SET statusbar ADD editmode
SET statusbar ADD txn
SET statusbar ADD timing
SET statusbar ADD venture
SET statusbar ADD gitExporting our database objects to our venture
Earlier than we create the .sql recordsdata for our desk(s), we have to inform SQLcl we don’t wish to embody the schema names within the supply. This enables us to have dev and prod environments the place they don’t match.
We’re constructing in DEV_USER and manufacturing lives in PROD_USER, so it wouldn’t do a lot good to run scripts within the PROD_USER account that seek advice from DEV_USER.
SQLcl tasks have choices you’ll be able to set, and one in all these contains the flexibility to disable the schema bits.
SQL> venture config set -name export.setTransform.emitSchema -value false -verbose
Setting worth up to date: The worth of the setting "export.setTransform.emitSchema" has been efficiently up to date
Course of accomplished efficiently
SQL> venture config -list -name export.setTransform.emitSchema
+========================================+
| SETTING NAME | VALUE |
+========================================+
| export.setTransform.emitSchema | false |
+----------------------------------------+
SQL>
We’ll see precisely how this works in only a second.
However earlier than we try this, we additionally want to inform SQLcl that we wish to exclude objects from our venture. For instance, SQL Developer creates tables in your schema for issues like SQL Historical past, after which now we have Liquibase tables. We don’t need these managed by our CI/CD course of or Git.
Content material of our .dbtools/filters/venture.filters file:
not (object_type="VIEW" and object_name="DATABASECHANGELOG_DETAILS"),
not (object_type="TRIGGER" and object_name="DATABASECHANGELOG_ACTIONS_TRG"),
not (object_name like 'DBTOOLS$%'),
NOW we will do the export 🙂
SQL> venture export -verbose
The present connection (description=(retry_count=20)(retry_delay=3)(tackle=(protocol=tcps)(port=1521)(host=adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=ajs6esm7pafcr84_atp144783_low.adb.oraclecloud.com))(safety=(ssl_server_dn_match=sure))) DEV_USER will likely be used for all operations
*** TABLES ***
DEV_USER.EMPLOYEES
-------------------------------
TABLE 1
-------------------------------
Exported 1 objects
Elapsed 19 sec
SQL>
SQLcl is reminding us what database we’re utilizing to do the export, after which it exhibits us what was lined. We discover that solely the EMPLOYEES desk is included, and that DBTOOLS$EXECUTION_HISTORY has been neglected.
Let’s go take a look at the ensuing file, or not less than the primary few bytes…
Now it’s time to stage
Earlier than we stage to incorporate our recordsdata, let’s see what’s excellent through one other git standing –
SQL> !git standing
On department SQLcl-Tasks-Migration
Modifications not staged for commit:
(use "git add..." to replace what will likely be dedicated)
(use "git restore..." to discard modifications in working listing)
modified: .dbtools/filters/venture.filters
modified: .dbtools/venture.config.jsonUntracked recordsdata:
(use "git add..." to incorporate in what will likely be dedicated)
src/database/no modifications added to commit (use "git add" and/or "git commit -a")
SQL>
Now we’ll git add and commit.
SQL> !git add --allSQL> !git commit -m "Export dev_user schema"
[SQLcl-Projects-Migration 957bf55] Export dev_user schema
3 recordsdata modified, 28 insertions(+), 4 deletions(-)
create mode 100644 src/database/dev_user/tables/workers.sqlSQL>
Now we will stage!
We’re going to go from having simply src recordsdata, to additionally having our dist setup.
SQL> venture stage -verboseBeginning execution of stage command utilizing the present department
Stage is Evaluating:
Previous Department refs/heads/primary
New Department refs/heads/SQLcl-Tasks-MigrationCreated dir: dist/releases
Created dir: dist/utils
Created dir: dist/releases/subsequent
Created dir: dist/releases/subsequent/modifications
Created dir: dist/releases/subsequent/modifications/SQLcl-Tasks-Migration
Created dir: dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/_custom
Created change:dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
Created change:dist/releases/primary.changelog.xml
Created change:dist/releases/subsequent/launch.changelog.xml
Created file: dist/utils/recompile.sql
Created file: dist/utils/prechecks.sql
Created file: dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql
Up to date change:dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
Up to date change:dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
Up to date change:dist/releases/subsequent/launch.changelog.xml
Up to date change:dist/releases/subsequent/launch.changelog.xmlAccomplished executing stage command on department: SQLcl-Tasks-Migration
Stage processesing accomplished, please evaluation and commit your modifications to repository
Untracked recordsdata:
dist/releases
dist/utils
src/property
How did this occur? Effectively through the use of Liquibase. Our src has plain SQL up high, nevertheless it has a Liquibase XML formatted changeSet on the backside.
First an outline of what we simply did:
This command generates Liquibase changelogs for all supply (src) and customized SQL recordsdata by evaluating the present department (SQLcl-Tasks-Migration) to the bottom department (primary). It creates a structured folder (dist/releases/subsequent/) for staged recordsdata and permits including customized changesets with SQL and SQLcl instructions.
If we return and take a look at our desk SQL file in SRC, and scroll to the underside…
SQLcl is sustaining state with a hash, after which is in a position to determine what modifications to our database object should be included in a CREATE or ALTER desk when it goes to generate the set up or improve script for dist.
Add and commit our recordsdata
It’s not use to have our recordsdata, if Git doesn’t find out about them, proper?
SQL> !git standing
On department SQLcl-Tasks-Migration
Modifications to be dedicated:
(use "git restore --staged..." to unstage)
new file: dist/releases/primary.changelog.xml
new file: dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql
new file: dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
new file: dist/releases/subsequent/launch.changelog.xml
new file: dist/utils/prechecks.sql
new file: dist/utils/recompile.sqlSQL> !git commit -m "Add stage recordsdata"
[SQLcl-Projects-Migration 6627067] Add stage recordsdata
6 recordsdata modified, 354 insertions(+)
create mode 100644 dist/releases/primary.changelog.xml
create mode 100644 dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql
create mode 100644 dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
create mode 100644 dist/releases/subsequent/launch.changelog.xml
create mode 100644 dist/utils/prechecks.sql
create mode 100644 dist/utils/recompile.sqlSQL>
Our ‘subsequent’ launch can have the workers desk. And we began from scratch, so it’ll simply be the entire CREATE TABLE code for the workers desk.
Merging to primary
It’s simply me and my native repo, so I’m going to merge straight into the principle department. In the actual world you’d have a merge/pull request, and also you’d wish to embody the Jira tickets, unit exams, and so forth to make it straightforward for somebody to see not solely what your code is doing, however that you just’ve accounted for all of the downstream results.
SQL> !git checkout primary
Switched to department 'primary'SQL> !git merge SQLcl-Tasks-Migration
Updating 0099913..6627067
Quick-forward
.dbtools/filters/venture.filters | 5 +-
.dbtools/venture.config.json | 2 +-
dist/releases/primary.changelog.xml | 7 +
.../SQLcl-Tasks-Migration/dev_user/tables/workers.sql | 26 ++++
.../subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml | 7 +
dist/releases/subsequent/launch.changelog.xml | 8 +
dist/utils/prechecks.sql | 81 ++++++++++
dist/utils/recompile.sql | 225 +++++++++++++++++++++++++++
src/database/dev_user/tables/workers.sql | 25 +++
9 recordsdata modified, 382 insertions(+), 4 deletions(-)
create mode 100644 dist/releases/primary.changelog.xml
create mode 100644 dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql
create mode 100644 dist/releases/subsequent/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
create mode 100644 dist/releases/subsequent/launch.changelog.xml
create mode 100644 dist/utils/prechecks.sql
create mode 100644 dist/utils/recompile.sql
create mode 100644 src/database/dev_user/tables/workers.sqlSQL>
Let’s evaluate the supply vs distributable for our desk SQL file(s)
Our supply has human readable SQL for diffs, and on the backside is the liquibase XML formatted changeSet that SQLcl makes use of to do it’s ‘magic’ for creating what you see on the correct.
In our dist now we have a SQL formatted Liquibase changeSet. The XML doesn’t play in dist, solely src.
It’s lastly time to do our 1.0.0 launch!
SQL> venture launch -version 1.0.0 -verbose
Making a launch model 1.0.0 for the present physique of laborCreated dir: dist/releases/subsequent/modifications/primary
Created dir: dist/releases/subsequent/modifications/primary/_custom
Created change:dist/releases/subsequent/modifications/primary/stage.changelog.xml
Up to date change:dist/releases/primary.changelog.xml
Moved folder "dist/releases/subsequent" to "dist/releases/1.0.0"
Created file: dist/releases/subsequent
Created change:dist/releases/subsequent/launch.changelog.xml
Created change:dist/releases/subsequent/launch.changelog.xml
Course of accomplished efficiently
Okay, we’re not doing ‘subsequent,’ now we’re doing 1.0.0, however we are going to at all times have a ‘subsequent,’ so we create a brand new, ‘subsequent.’
Now let’s add and commit.
SQL> !git add .SQL> !git commit -m "Launch 1.0.0"
[main f036426] Launch 1.0.0
7 recordsdata modified, 16 insertions(+), 2 deletions(-)
rename dist/releases/{subsequent => 1.0.0}/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql (100%)
rename dist/releases/{subsequent => 1.0.0}/modifications/SQLcl-Tasks-Migration/stage.changelog.xml (100%)
create mode 100644 dist/releases/1.0.0/modifications/primary/stage.changelog.xml
create mode 100644 dist/releases/1.0.0/launch.changelog.xmlSQL>
We’re able to construct our artifact (installer!)
Effectively, ALMOST.
We have to make a small tweak to our set up.sql file.
Our ‘set up’ script needs to do a ‘liquibase replace’ – that’s, it needs to run our installer. However bear in mind, we’re organising an present software for SQLcl tasks, and this desk already exists in PROD! We’re simply attempting to determine the ‘baseline’ for Liquibase to start out monitoring what’s already there.
To do this, we’d like Liquibase to setup it’s native atmosphere within the database to it will probably management and monitor modifications coming in.
So we’re going to do a one-time change right here, to do a ‘liquibase changelog-sync’
And naturally meaning we have to git add and commit, once more.
SQL> !git add .SQL> !git commit -m "up to date installer to do a changelog-sync"
[main 9f6beeb] up to date installer to do a changelog-sync
1 file modified, 1 insertion(+), 1 deletion(-)SQL>
NOW we will generate the artifact.
SQL> venture gen-artifact -verbose
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/set up.sql
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/primary.changelog.xml
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/1.0.0/modifications/SQLcl-Tasks-Migration/dev_user/tables/workers.sql
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/1.0.0/modifications/SQLcl-Tasks-Migration/stage.changelog.xml
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/1.0.0/modifications/primary/stage.changelog.xml
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/1.0.0/launch.changelog.xml
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/releases/subsequent/launch.changelog.xml
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/utils/prechecks.sql
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/utils/recompile.sql
file : /dwelling/property/property/workshops/sqlcl-projects-react-app/dist/.ipynb_checkpoints/install-checkpoint.sql
Your artifact has been generated HrManager-1.0.0.zip
SQL>
Hmm, what’s in that Zip?
The set up script will name the assorted items from the suitable launch.
And now we will run this sucker in prod!
So let’s hook up with the prod atmosphere, and let’s see what now we have within the schema.
SQL> join prod_user/5izf5eB67NxYvbNp
Related.
SQL> tablesTABLES
____________
EMPLOYEESSQL>
And let’s deploy!
SQL> venture deploy -file artifact/HrManager-1.0.0.zip -verbose
Test database connection...
Extract the file title: HrManager-1.0.0
Artifact decompression in progress...
Artifact decompressed: /tmp/a9320c53-1a63-4ed2-9f31-7b7a87bb375e1434212536776481432
Beginning the migration...
Putting in/updating schemas
--Beginning Liquibase at 2025-05-01T22:02:58.143119496 (model 4.25.0 #4.25.1 constructed at 2025-01-21 15:25+0000)Produced logfile: sqlcl-lb-1746136978014.log
Operation accomplished efficiently.
Migration has been accomplished
Eradicating the decompressed artifact: /tmp/a9320c53-1a63-4ed2-9f31-7b7a87bb375e1434212536776481432...
SQL>
And let’s see what now we have in our schema now –
SQL> tablesTABLES
________________________
EMPLOYEES
DATABASECHANGELOGLOCK
DATABASECHANGELOGSQL> choose * from databasechangelog;
ID AUTHOR FILENAME DATEEXECUTED ORDEREXECUTED EXECTYPE MD5SUM DESCRIPTION COMMENTS TAG LIQUIBASE CONTEXTS LABELS DEPLOYMENT_ID
________________ ___________ _________________________________________________________ __________________________________ ________________ ___________ _____________________________________ ______________ ___________ ______ ____________ ___________ _________ ________________
1746134327826 DEV_USER SQLcl-Tasks-Migration/dev_user/tables/workers.sql 01-MAY-25 10.02.59.670499000 PM 1 EXECUTED 9:5311e6d469370386b72d4e70ded654aa sql 4.25.0 6136979631SQL>
Our database is now at an anticipated state, or launch, 1.0.0
We’re now prepared to start out doing a little improvement work! Including options, creating new tables, altering columns, deploying REST APIs, and so forth. The subsequent submit will exhibit how to do that, after which deploy that launch.
