Liquibase Preconditions sqlCheck

Liquibase provides Preconditions for all basic checks, but it cannot cover all the scenarios, some Preconditions are complex queries that can only be expected in SQL. To solve this issue Liquibase provides sqlCheck as a Preconditions, it executes the SQL query and matches with the value provided. the query must return a single row with a single value.

<?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="1692599548266-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>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-3">
		<preConditions>
			<sqlCheck expectedResult="1">
				SELECT max(apple_id)
				FROM apple
			</sqlCheck>
		</preConditions>
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Macintosh" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-4">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</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

Precondition with custom message

sqlCheck fails the ChangeSet if Precondition is not matched, so onFailMessage can be used to provide a custom message.

<?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="1692599548266-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>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-2">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Fuji" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-3">
		<preConditions onFailMessage="expecting max of apple_id as 0">
			<sqlCheck expectedResult="0">
				SELECT max(apple_id)
				FROM apple
			</sqlCheck>
		</preConditions>
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Macintosh" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</changeSet>
	<changeSet author="wesome" id="1692599548266-4">
		<insert catalogName="appledb" tableName="apple">
			<column name="apple_id" />
			<column name="apple_name" value="Gala" />
			<column name="current_date_time" valueDate="now()" />
		</insert>
	</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