You won't have direct access to ANY Oracle database in some projects. That's a fact.

You will probably prepare SQL scripts using exported objects from the GIT repository. After that, you or someone else will execute those scripts "by hand".

What if you could still track what was changed and deployed on those databases?

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

Sounds unbelievable? Read further.

1.Prerequisites.

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.

If you are new to Liquibase, please read my tutorial for beginners first.

2.Configuration of Liquibase properties file.

I configured 3 settings:

changelogFile - my previously created changelog location,
outputFile - default file name of generated SQL files,
liquibase.command.url - normally, there would be my connection string, but in this case, I'm saying: "Behave as you would connect to the Oracle database but without access to it"

3.Capture 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 will change in those files".

I need to run 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 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 will 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 changes.

4.Change your database code and track it.

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

I changed the alias of my column in regions_v and added liquibase syntax:

I will run update-sql command to check what files were changed and what would executed SQL 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'll save this file as v1.sql.

Now, just execute it into your database.

And let's check my databasechangelog.csv:

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

You don't have to check your changes in not quite comfortable CSV file.

You can do it using SQL. Just run:

liquibase --defaultsfile=test1.properties history

And the output is:

Summary

I hope this blog inspired you somehow to track your database changes even if you don't have direct access.

More information about the offline usage of Liquibase can be found here and here.

Keep It Simple. Developer's tips.