Use SQLcl Liquibase to move all database objects from DEV to the UAT environment

What do I want to achieve?

I want to move all my objects from DEV to UAT. I don’t want to think about what should be moved to UAT and in which order – I just want SQLcl to do it for me. Moreover, I won’t create any changesets or changelogs you normally should create when using Liquibase (more information here).

Demo prerequisites

1. SQLcl installed (version 23.1 at the time of this blog) on the local OS (Liquibase is already built-in). Read more here and here.
2. To simplify things, I will use two separate schemas, DEV and UAT, created at one OCI Database (my TNS name is rgatp28_low).
3. The DEV schema has some objects, and the UAT schema is empty.

Step-by-step guide

1. Capture the current state of the DEV environment

First, create a folder “database” in your local OS and go into it. Connect to your DEV schema using SQLcl.

Now, capture your current state of the DEV schema using the first command:

liquibase generate-schema --split

The generate-schema command captures all of your schema objects. The –split parameter is responsible for moving all of theminto subdirectories (depending on the object type).

However, remember that, by default, SQLcl doesn’t export grants and synonyms.

If you want to include grants and synonyms, add the following parameter (or parameters) to your command:

liquibase generate-schema --split --grants --synonyms

Now, let’s take a look at what files and folders were generated automatically:

In subdirectories like comment, procedure, table etc., there are changelogs containing database objects in XML format.

Of course, we all love SQL, so you don’t even need to open these XML files. There is also another XML file generated automatically – controller.xml.

SQLcl Liquibase treats it as a ledger, a root of all changes. That file holds information in which order objects should be executed.

However, as I’ve mentioned, you don’t actually need to open it or change anything. Liquibase will handle all execution orders for you. Isn’t it great?

2. Move all objects from DEV to UAT schema

Start by connecting to UAT with SQLcl (there are no objects on UAT yet).

Run the Liquibase update command:

liquibase update --changelog-file controller.xml

This command will execute all objects on the UAT schema in the order specified in the controller.xml file.

Conclusion

And that’s pretty much it. All objects were moved from DEV to UAT. Additionally, Liquibase created a few objects to store information about which changes were executed to the database. For example, you can check the databasechangelog_details view to see what was deployed and when. Moreover, you also have the exact SQL that was executed. 

If you’re using Oracle OCI, you can also preview your deployments using Oracle Database Actions (remember to REST enable your user first!). Also, if you don’t want to capture the entire schema, read this. 

That’s it for today. Stay tuned for my future articles – for example, I plan to follow this up with a blog post about making DDL changes directly in the DEV database schema and moving only changed/new objects to UAT. If you’d like me to cover a specific feature of SQLcl Liquibase, drop me a line at rgrzegorczyk@pretius.com. Also, be sure to check out my other articles on the Pretius blog:

1. What is Liquibase and how to start using it? Automate your database scripts deployment with this Liquibase tutorial
2. Liquibase for teams: GIT collaboration and easy deployment
3. Boost the management of your Oracle Database version control changes with Liquibase
4. Oracle APEX charts: How to make them look better using linear gradients
5. Oracle data masking: hide information from users with an easy-to-use VPD
6. Use Liquibase to track DB changes without direct access – A quick guide