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.
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.
I configured 3 settings in the properties file:
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:
-- ********************************************************************* -- 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.
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:
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: