Generate SQL to Update Database Schemas

Liquibase allows the writing of database-agnostic DDL and DML queries.

There may be instances where it's necessary to determine which queries will run during an update. However, sometimes it's necessary to know which query will run while updating. In such cases, Liquibase provides update-sql feature.
The update-sql allows you to save all the queries into a file for verification and approval purposes. there are two primary reasons why update-sql is required

  1. The DBA exactly knows what DDL and DMLstatements will execute during the update.
  2. Some company policies may not support or allowLiquibase in certain environments.

update-sql will only create SQL for ChangeLog which will execute while updation, assuming Some Changesets are already executed and present in the Database, so those will not be executed again hence Liqubase will not create SQL for those ChangeSets.

The update-sql command will evaluate all the ChangeSets in the ChangeLog file and generate a corresponding SQL.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="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 http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692446190573-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
		<rollback>
			<dropTable tableName="apple" />
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Fuji'
				</where>
			</delete>
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Gala'
				</where>
			</delete>
		</rollback>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot

Run command liquibase update-sql, the SQL commands will be printed on the command prompt.

SaveGenerate SQL to File

The update-sql feature allows you to save generated SQL into a file.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="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 http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692446190573-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
		<rollback>
			<dropTable tableName="apple" />
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Fuji'
				</where>
			</delete>
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Gala'
				</where>
			</delete>
		</rollback>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot
outputFile=update-sql.txt

Run command liquibase update-sql, an update-sql.txt file will be created with all the corresponding DDL and DML SQL statements.

Generate SQL for Labeled ChangeSet

update-sql will generate SQL scripts for all pending ChangeSets, but sometimes SQL script is required for only certain Changesets, those ChangeSets can be marked by labels and update-sql will only generate corresponding SQL for those ChangeSets.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="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 http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692446190573-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
		<rollback>
			<dropTable tableName="apple" />
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Fuji'
				</where>
			</delete>
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Gala'
				</where>
			</delete>
		</rollback>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot
outputFile=update-sql.txt

Run command liquibase update-sql --labelFilter=create , or liquibase update-sql --labelFilter=update

Generate Future-rollback-sql

The update-sql command will only generate the SQL scripts for insertion and updation of the Database, even if the ChangeSet contains the rollback tags. Liquibase provides future-rollback-sql command that will generate the rollback SQL script from ChangeSets.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="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 http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1692446190573-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
		<rollback>
			<dropTable tableName="apple" />
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Fuji'
				</where>
			</delete>
		</rollback>
	</changeSet>
	<changeSet author="wesome" id="1692701200879-3">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
		<rollback>
			<delete tableName="apple">
				<where>
					apple_name = 'Gala'
				</where>
			</delete>
		</rollback>
	</changeSet>
</databaseChangeLog>

liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot
outputFile=rollback-sql.txt

Run command liquibase future-rollback-sql, a rollback-sql.txt file will be created with all the corresponding DDL and DML SQL rollback statements.

follow us on

YouTube IconLinkedIn Icon