Introduction

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

In this article, I will give you a general idea and test the basic functionalities of the Oracle SQLcl tool with Liquibase built-in.

I highly encourage you to read the basic test of standalone Liquibase first - it can be found here.

Read more about test prerequisites in my "Introduction" blog post here.

Oracle SQLcl Liqiubase vs. standalone Liquibase - is it the same?

Ok, some of you may be confused at this moment. You've heard or read about Liquibase, and now...Liquibase in SQLcl? What's it all about?

Some people say that Liquibase and SQLcl Liquibase are the same tools.

They are right - sort of, but not entirely.

Let me explain that quickly (a more detailed explanation can be found here on Zachary's blog):

• A few years ago, the Oracle SQLcl team "took" Liquibase code (Open-Source) and implemented it into an existing tool called SQLcl,

• They've added some extra features to make it more Oracle Database friendly, e.g.:

  • extra commands for Oracle APEX

  • support for REST

  • capturing the entire database schema

• SQLcl Liquibase can work only with Oracle Database, while standalone Liquibae can work with many other databases. You can read my standalone Liquibase basic test here.

• Official SQLcl documentation (version 24.1) can be found here.

Test

1. Installation, Configuration & Connection to the database

To use SQLcl Liquibase, you need to have Oracle SQLcl installed.

Go to the Oracle SQLcl website and download the latest version.

Unzip files, choose a folder to store them in and add SQLcl to your environment path.

Open your terminal and type "sql /nolog" to verify if SQLcl is installed

Connecting to Oracle Database is super easy:

/*On-premise*/
sql username/password@host:port/SID

/*OCI*/
sql username/password@TNS_NAME

Or, the coolest way to connect (read how to do it here):

sql -name MY_COOL_DATABASE

That's it. We're connected.

2. Tracking database schema changes using SQLcl Liquibase

As I mentioned in my introduction blog post, I will make some changes in my database schema in the DEV environment:

1. Insert a new row into the table COUNTRIES

2. Create a new table PARAMETERS

3. Add a new column to the existing table EMPLOYEES

4. Change existing procedure SECURE_DML

5. Insert new rows into the newly created table PARAMETERS

My main goal is to find a way to track it and, later, easily deploy it to the production environment.

Making changes

One of the greatest features added to SQLcl Liquibase is the availability to capture an entire schema.

In standalone Liquibase, you should define changelogs and changesets to track your changes. SQLcl Liquibase will do it for you.

Ok, so how will I make my changes?

Directly in SQL Developer (or other tool)

My changes have already been deployed to DEV.

No need to write changesets or changelogs, just direct changes in the database.

3. Deployment to another environment (PROD)

Okay, moving that to PROD would be easy for the above example—it's just a few changes and could be done manually.

But what if more than one developer made changes in the past few days or weeks? It won't be fast and easy to remember all the changes we should deploy.

Capture all objects from the DEV schema automatically

That's why the SQLcl team implemented an extra liquibase command adjusted for Oracle Database

liquibase generate-schema

So, I've created a folder called /sqlcl_liquibase/

Now, I will use SQLcl to connect to my DEV environment and capture all my database objects from the HR schema.

Command is:

liquibase generate-schema -split

This command will automatically retrieve all my HR (DEV) schema database objects, create changesets, and create a changelog file.

It can take from a few seconds to around 30 minutes (my biggest database has 14k objects)

As I promised, SQLcl Liquibase generated changelogs and changesets automatically:

• controller.xml, changelog with the order in which changes should be executed

• changesets in XML format for all database objects

Don't be scared with XML format. You don't need to change anything there. Actually, you don't even need to open those XML files :)

Deploy DEV objects to PROD

Okay, now that I have captured the actual state of my database objects at DEV, I want to move everything new or changed to PROD.

How will I know what to change at PROD? What "ALTER" or "CREATE" command should be executed, or what procedure should be replaced?

SQLcl will do it for you!

Let's connect to PROD:

And execute "update" command:

liquibase update -changelog-file controller.xml

My 3 changes to database objects were automatically executed at PROD.

What about DMLs?

SQLcl Liquibase "generate-schema" command will not capture your data.

So, to move those kinds of changes to PROD (inserts into COUNTRIES and PARAMETERS tables), you need to write appropriate changesets, just like I did in my basic review of standalone Liquibase.

4. Visualise (retrieve) a list of changes made to the database schema.

Visualizing changes is the same as with standalone Liquibase but enhanced with extra features.

To check what was deployed, you can simply query the DATABASECHANGELOG table:

Or, if you use APEX, you can create simple, fancy reports to show that table to users.

You can also use a terminal to retrieve a list of changes:

liquibase history

SQLcl Liquibase also creates an additional view, DATABASECHANGELOG_DETAILS (not available in Liquibase standalone), that shows the exact SQL script that was executed during deployment:

And If you have your database in OCI, there's an extra Liquibase menu there:

It's for preview purposes only. You can not execute Liquibase commands from this panel.

Summary

I hope that this blog post gave you a general idea of what working with SQLcl Liqibase is like. Its greatest feature is the ability to capture entire schemas and automatically make changes across other databases.

Developers don't need to write changelogs or changesets or think about the script execution order.

Minimum effort is required only if you want to track changes unrelated to database objects (DML or script execution).

I hope you like this tool.

Drop me a comment if you have any questions!

Enjoy!

Rafal

Interested in SQLcl Liquibase? Read my other, more detailed blog posts.

• [Mastering Oracle SQLcl Liquibase - Part 1/3] Move all database objects from DEV to the UAT environment

• [Mastering Oracle SQLcl Liquibase - Part 2/3] Track your DEV database changes and move it to UAT(or any other environment)

• [Mastering Oracle SQLcl Liquibase - Part 3/3] - Synchronize your existing environments

• [Oracle SQLcl Liquibase] Install it on Mac&Windows and use it with Oracle Database (on-premise & cloud)

Tested tools