Use Liquibase to track DB changes without direct access– A quick guide

Contents

Sometimes you just need to track DB changes without direct access to the database. Thankfully, there’s an easy way to do that with Liquibase.

Sooner or later you’ll encounter projects during which you won’t have direct access to ANY Oracle database. That’s a fact you should be aware ofand get ready for. Typically, you simply prepare SQL scripts using exported objects from the GIT repository. After that, you – or someone else – execute those scripts manually.

But what if you could still track what was changed and deployed on those databases? Sounds unbelievable? It’s entirely possible thanks to Liquibase. I’ll show you how to do this.

Examples are based on the Oracle database, but you can do the same using other databases supported by Liquibase.

Prerequisites

First , let’s start with the prerequisites so that everything is easy to understand and follow. I have four objects in my TEST1 schema, exported into /offline_liquibase/database/ using SQL developer.

Now I want to make some changes and prepare SQL containing my work. As I mentioned, I don’t have access to my database, but I still want to track all my changes.

I prepare my controller.xml changelog – it’s a ledger of my changes. Now, Liquibase knows what files should be executed (and tracked) and in which order:

Files used in this blog post can be found in my GitHub repository. Also, if you are new to Liquibase, please read my tutorial for beginners first.

How to use Liquibase to track DB changes without direct access

Configuration of the Liquibase properties file

I configured 3 settings in the properties file:

changelogFile – my previously created changelog’s location
outputFile – default file name of generated SQL files
liquibase.command.url – normally, it would be my connection string, but in this case, I’m telling Liquibase something like: “Behave as if you connected to the Oracle database but without access to it”

Capture the actual state of your DB objects

I want to track my changes, so I must tell Liquibase: “Hey, this is what I already have. Please capture the actual state of objects and track if something changes in those files”.

To do this, I need to run the Liquibase changelogsyncSQL command:

liquibase --defaultsfile=test1.properties changelog-sync-sql

As an output, I have two new files:

databasechangelog.csv – this is a file that represents the structure of the DATABASECHANGELOG table.
test1_sql_preview.sql – my first SQL file that should be executed into the Oracle database

-- *********************************************************************
-- SQL to add all changesets to database history table
-- *********************************************************************
-- Change Log: controller.xml
-- Ran at: 23.02.2023, 13:03
-- Against: null@offline:oracle?outputLiquibaseSql=all
-- Liquibase version: 4.19.0
-- *********************************************************************

CREATE TABLE DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED INTEGER NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(255), LIQUIBASE VARCHAR2(20), CONTEXTS VARCHAR2(255), LABELS VARCHAR2(255), DEPLOYMENT_ID VARCHAR2(10));

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'database/tables/REGIONS.sql', SYSTIMESTAMP, 1, '8:d43c1f03ce2691631319b8ee86e15196', 'sql', '', 'EXECUTED', NULL, NULL, '4.19.0', '7153834099');

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'database/constraints/REGIONS.sql', SYSTIMESTAMP, 3, '8:ade6f0436672e8f3ad3bfc30dd202096', 'sql', '', 'EXECUTED', NULL, NULL, '4.19.0', '7153834099');

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'database/views/REGIONS_V.sql', SYSTIMESTAMP, 5, '8:6117db7f3b8a2e0a99fff98801ce7620', 'sql', '', 'EXECUTED', NULL, NULL, '4.19.0', '7153834099');

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('raw', 'includeAll', 'database/procedures/SECURE_DML.sql', SYSTIMESTAMP, 7, '8:0e6712d9cf6b237fb21d23da559cbe68', 'sql', '', 'EXECUTED', NULL, NULL, '4.19.0', '7153834099');

I rename test1_sql_preview.sql into synchronize.sql and save it. Now, look into my CSV:

Now, just execute synchronize.sql into your database, and you’re all set to track the changes.

Change your database code and track it

Okay, now I want to change my code, prepare the SQL, and have information on what was deployed in my offline Liquibase (databasechangelog.csv).

I change the alias of my column in regions_v and add the Liquibase syntax:

I then run the update-sql command to check which files were changed and what the executed SQL will look like:

liquibase --defaultsfile=test1.properties updateSQL

And my output test1_sql_preview.sql file is:

-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: controller.xml
-- Ran at: 23.02.2023, 13:20
-- Against: null@offline:oracle?outputLiquibaseSql=all
-- Liquibase version: 4.19.0
-- *********************************************************************

-- Changeset database/views/REGIONS_V.sql::REGIONS_V::rgrzegorczyk
-- Changed column alias.
CREATE OR REPLACE FORCE EDITIONABLE VIEW "REGIONS_V" ("REGION_ID", "REGION_NAME") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  select  r.region_id,
          r.region_name as name
     from regions r;

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('REGIONS_V', 'rgrzegorczyk', 'database/views/REGIONS_V.sql', SYSTIMESTAMP, 9, '8:1f5476854002c19c1ee9c71abdfffc49', 'sql', 'Changed column alias.', 'EXECUTED', NULL, NULL, '4.19.0', '7154857665');

I save this file as v1.sql, and then, simply execute it into my database. Now, let’s take a look at my databasechangelog.csv:

Notice the difference between the two rows with regions.sql file – the checksums are different because the file was changed.

Of course, I don’t have to track changes in the CSV file, which – to be honest – isn’t very comfortable and practical. I can simply do it via SQL, by running:

liquibase --defaultsfile=test1.properties history

The output looks like this:

Conclusion

With that, we’ve reached the end of this article. I hope it inspired you somehow to track your database changes even if you don’t have direct access to the DB in a given project. If you need more information about the offline usage of Liquibase, you can find it here and here. You can also check out my other articles on the Pretius blog: