SQLcl 26.1 DIFF Command: Schema Compare & Patch for CI/CD

It has already been some time since I started using SQLcl project, and in my opinion it is a very good tool for CI/CD. I like that I can just export, stage, and deploy in a few simple steps.

The new SQLcl 26.1 introduces a new feature called DIFF. Is this command a competitor to the SQLcl project or just an additional tool that can be used for your CI/CD? And how do they compare? Let's take a look.

DIFF Command

The command will compare 2 schemas and generate a patch. I can then deploy the changes and synchronize the schemas (apply all missing changes).

It is very simple to use, I don't have to be in a specific branch in my repository, and I don't have to use project export because the command is comparing the database schemas directly in the database. Below you can see an example DIFF command.

DIFF -source TEST_SCHEMA -target DEV_SCHEMA

Hands-on example

To demonstrate how the DIFF command works and how it compares to SQLcl project, here is an example. I have these two schemas, DEV_SCHEMA and TEST_SCHEMA, and I already set up the connections in my VS Code.

In the DEV_SCHEMA I created these three objects:

table PRODUCTS
package PRODUCTS_PKG
view PRODUCTS_V

Now, to get the objects from DEV to TEST I will use the DIFF command. First I open the terminal in my test-project folder. Then I start SQLcl with /nolog. I have multiple versions, so I use it with the path below.

/Users/tomaskucharzyk/test-project/sqlcl-26.1.2/bin/sql /nolog

The result is this message:

After that I run the DIFF command.

DIFF -source TEST_SCHEMA -target DEV_SCHEMA

The result is Success 🎉 and I can see a new zip file in my project.

When I unzip it, I can see the structure that is the same as the SQLcl project. I have releases with next, then schemas and the database object folders, where I can see my table, package, and view.

There is also a change log that puts my objects in the correct order.

To deploy the changes, I use the project deploy -file Path command. It is no coincidence that the structure of the DIFF output is the same as the project because DIFF is using project stage to create the updates (visible in the output of the DIFF command).

[diff - info] Generating diffs via project stage

I connect to my TEST_SCHEMA by running this command.

conn -name TEST_SCHEMA

Then I can deploy the changes using the project deploy command below.

project deploy -file diff_TEST_SCHEMA_vs_DEV_SCHEMA-1779221995810.zip

The objects were created (I have an error in the package because I used 2 schemas instead of 2 databases) and the changes were deployed to TEST.

Pros/Cons and comparison to SQLcl Project

In my opinion, DIFF is a very useful command that enables me to use SQLcl Project stage and deploy without project setup and a git repository.

I can see that DIFF will be a very good option when I need to move changes from DEV to TEST (or any other schemas) and I don't necessarily need to export everything into the git repository. In the DIFF case, there is no need for a repository, branches, or project setup. It is a very straightforward process.

I also think that there may be cases where DIFF is more precise because SQLcl project is dependent on the code that I have in the git branches. If I use project stage with a branch that is missing some merges, I will not generate the correct change log for the environment. The DIFF command will always be right, because it is comparing the database schemas directly.

Where I potentially see a problem is when the deploy fails or if I run the deploy twice. DIFF command does not generate a snapshot with a hash like the project does, and the database will not know which scripts are already applied.

Example of the hash below:

-- sqlcl_snapshot {"hash":"9ba38a2d0b8700f7aaae77739e51db658ac9a...

So how good is DIFF?

I think that it is an amazing tool that will be welcomed by many developers, and it may be useful in many situations.

It can also work with the SQLcl project because I can always do a fast DIFF when needed and then sync the project by using project export. So in my opinion, in the long term, SQLcl project is the way to go, but DIFF will be there to help when needed.

Thank you for reading the article! And please let me know what you think about the new DIFF command.