<Tech>Brunch


Generate SQL Script from Liquibase Changesets in the codebase




The previous post on using Liquibase to manage database states explains how to setup Liquibase in the existing application.
But sometimes, we might not want to run scripts in QA or PROD regions via Liquibase changesets trigger. One of the reason can be, to have our DBA review our SQL related changes before they can be run in higher regions.

Liquibase has always supported an “updateSQL” mode which does not update the database but instead outputs what would be run. This allows developers and DBAs to know exactly what will be ran and even make modifications as needed before actually executing the script.

Before version 3.2, however, Liquibase required an active database connection for updateSQL. It used that connection to determine the SQL dialect to use and to query the DATABASECHANGELOG table to learn what changeSets have already been executed.

I prefer to keep master.xml file, which includes all the changesets that need to be run. I will make use of updateSQL command to get the SQL queries generated for me. The generated queries from changesets will get syso-ed and a ‘databasechangelog.csv’ file will be generated which will give information on databasechangelog.

1
liquibase --changeLogFile="liquibase/master.xml" --url=offline:mssql?  updateSQL

The above statement doesn’t need to have an active DB connection. I have used version 3.2 of Liquibase for this. –url is offline to ensure no DB connection is established, and mssql? is to tell Liquibase to kindly generate the script for MS SQL Server only.
Liquibase supports almost all the databases. We can use “oracle?” for Oracle DB, “mysql?” for MySQL DB, and similarly we have different variants.