Liquibase is a tool to automate database upgrades starting from a script, through testing, till a deployment to production. However, sometimes we want to take care of deployment ourselves. In that case, the possibility to generate an SQL script is extremely useful.
Generate SQL script instead of automatic deployment
A common case, when generating an SQL script is better than deploying a change is this.
It is a reality in many companies that developers create database upgrade scripts when working on a new application release. They run them in a development environment, test them, and change them if needed. But when it is the time to deploy everything on production, they are not allowed to do that themselves. Instead, they are obligated to provide a complete SQL upgrade script to a database administrator. And then, the DBA runs the script on the production database.
We can easily call it a stupid procedures, a waste of time and money. But let's stay open-minded for a second. Big tech companies have been introducing segregation of duties for many years, justifying it by security and expertise. I am not saying that it is always the best solution, but guarding the production from employees that not always know what they are doing, kind of makes sense.
If we agree that only database administrators are allowed to change a production database structure, we have at least two options: give them a complete SQL upgrade script or teach them how to use Liquibase. Although the second choice may be beneficial in the long run, from my experience, the first one is the way to go in most cases.
So the procedure is as follows:
- We (developers) create Liquibase change sets as usual during development of a new application release.
- When the release is complete, we generate an SQL script from the change sets.
- DBAs run the script on the (production) database.
In this article, I will show you how to create such SQL script out of a Liquibase file. We will also take care of a rollback SQL script just in case.
Generate SQL upgrade script with updateSQL
As I mentioned in the previous chapter, an SQL upgrade script with all database changes that need to be applied on the database, is almost a must-have in many cases. Liquibase has a special command to generate one instead of directly applying the change sets. Assuming that the Liquibase file is this db-changelog.xml,
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
<changeSet id="Create table" author="DBA presents">
<createTable tableName="USERS">
<column name="US_ID" type="int">
<constraints nullable="false" />
</column>
<column name="US_NAME" type="varchar(100)" />
</createTable>
</changeSet>
<changeSet id="Add column" author="DBA presents">
<addColumn tableName="USERS">
<column name="US_STATUS" type="char(1)" />
</addColumn>
</changeSet>
</databaseChangeLog>
the database upgrade can be done by using the update Liquibase command like here. Driver, classpath and connection credentials are skipped in the command below for simplicity.
liquibase --changeLogFile=db-changelog.xml update
However, we don't want to directly upgrade the database, but to generate an upgrade SQL script, that can be executed later by a DBA. In that case, we can use the updateSQL command.
liquibase --changeLogFile=db-changelog.xml --output-file=generated-upgrade-script.sql updateSQL
Under the hood it starts with the very same logic. It reads change sets one by one from the change log file, and looks up the DATABASECHANGELOG table to identify change sets that have not been applied yet. Then, they are converted to SQL statements, according to the SQL dialect known from the database connection. The result is printed to the output file. Notice, that the create table change set is not converted to SQL, because it already exists in the DATABASECHANGELOG table.
-- ********************************************************************* -- Update Database Script -- ********************************************************************* -- Change Log: db-changelog.xml -- Ran at: 1/27/23, 6:47 PM -- Against: webuser@localhost@jdbc:mysql://localhost:3306/employees -- Liquibase version: 4.18.0 -- ********************************************************************* -- Lock Database UPDATE employees.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'mymachine (192.168.37.1)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0; -- Changeset db-changelog.xml::Add column::DBA presents ALTER TABLE employees.USERS ADD US_STATUS CHAR(1) NULL; INSERT INTO employees.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, `DESCRIPTION`, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID)
VALUES ('Add column', 'DBA presents', 'db-changelog.xml', NOW(), 55, '8:5a7433a07333ec240a46b634dbd74363', 'addColumn tableName=USERS', '', 'EXECUTED', NULL, NULL, '4.18.0', '4841621498'); -- Release Database Lock UPDATE employees.DATABASECHANGELOGLOCK SET `LOCKED` = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
The produced SQL file contains not only SQL statements, that upgrade the database structure, but also insert statements to the DATABASECHANGELOG table.
Thanks to them, running such a prepared SQL file gives the same result as applying the changes directly to the database using Liquibase - all executed change sets are properly marked as applied.
Generate SQL rollback script with futureRollbackSQL
It is often helpful to not only have an SQL script to upgrade the database, but also have a script to roll back those changes. Just in case. It is good to have one in real production situations. Imagine, that a DBA runs the upgrade SQL script, then someone deploys a new version of Java application. And a few minutes later, after smoke-testing the new deployment, a decision is made to roll everything back, because of some serious bug. Rolling back the Java application is easy - it is enough to deploy the previous version. Unfortunately, rolling back the database requires a special rollback script. This is why you may want to prepare one along with the upgrade script.
Liquibase made it easy for us. We can use the futureRollbackSQL command.
liquibase --changeLogFile=db-changelog.xml --output-file=generated-rollback-script.sql futureRollbackSQL
It reads the change log file and compares it to the DATABASECHANGELOG table. Then it knows which change sets should be included in the upgrade script. Liquibase reverses the order of them, generates the rollback SQL statements for each one, and prints them to the output file. It is like preparing a rollback script for changes that have not been executed yet.
-- ********************************************************************* -- SQL to roll back currently unexecuted changes -- ********************************************************************* -- Change Log: db-changelog.xml -- Ran at: 1/27/23, 6:54 PM -- Against: webuser@localhost@jdbc:mysql://localhost:3306/employees -- Liquibase version: 4.18.0 -- ********************************************************************* -- Lock Database UPDATE employees.DATABASECHANGELOGLOCK SET `LOCKED` = 1, LOCKEDBY = 'chojrak (192.168.37.1)', LOCKGRANTED = NOW() WHERE ID = 1 AND `LOCKED` = 0; -- Rolling Back ChangeSet: db-changelog.xml::Add column::DBA presents ALTER TABLE employees.USERS DROP COLUMN US_STATUS; DELETE FROM employees.DATABASECHANGELOG WHERE ID = 'Add column' AND AUTHOR = 'DBA presents' AND FILENAME = 'db-changelog.xml'; -- Release Database Lock UPDATE employees.DATABASECHANGELOGLOCK SET `LOCKED` = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;
As you can see, the file contains only those change sets, which will be applied during the upgrade, not all from the change log file. The rollback file contains the delete statements for the DATABASECHANGELOG table, which makes it I fully usable for rolling back the changes.
