Using Liquibase preConditions to Validate Database State Before Applying Changesets

Desired outcome

Ensure that a changeset is applied only when the database meets specific state requirements (e.g., a table or column exists, a row count matches, or a custom SQL condition returns true). If the requirement is not met, Liquibase should either halt the update, mark the changeset as ran without applying it, or continue with a warning, according to the chosen onFail behavior.

Prerequisites

• Liquibase version 4.x or later installed and accessible via the command line.
• A target database (any supported vendor) with the Liquibase DATABASECHANGELOG and DATABASECHANGELOGLOCK tables already created.
• A Liquibase changelog file (XML, YAML, JSON, or SQL) where you can add the changeset to be guarded.
• Sufficient database privileges to create tables, read schema information, and execute the preCondition SQL checks.

Focused procedure

1. Define the preCondition inside the changeset. Choose the type that matches your validation need.
   • <tableExists> – checks for a table.
   • <columnExists> – checks for a column in a table.
   • <rowCount> – validates the number of rows in a table.
   • <sqlCheck> – runs a custom SQL statement; the condition passes if the query returns at least one row.
   • <dbms> – validates the database vendor or version.

   <changeset id="add‑user‑profile‑table" author="dev">
       <preConditions onFail="HALT">
           <tableExists tableName="user_profile" />
       </preConditions>
       <createTable tableName="user_profile">
           <column name="id" type="BIGINT" autoIncrement="true">
               <constraints primaryKey="true" nullable="false" />
           </column>
           <column name="email" type="VARCHAR(255)">
               <constraints nullable="false" />
           </column>
       </createTable>
   </changeset>
   

2. Set the flow control using the onFail and optionally onError attributes on the <preConditions> element:
   • HALT – stops the entire liquibase update run.
   • MARK_RAN – records the changeset in DATABASECHANGELOG as executed but skips the actual changes.
   • WARN – logs a warning and continues processing.
3. Run the update from the directory containing your changelog:

   liquibase --changeLogFile=db/changelog-master.xml update
   

4. Observe the outcome:
   • If the preCondition passes, the changeset runs normally.
   • If it fails and onFail=HALT, the command exits with an error and no further changesets are processed.
   • If it fails and onFail=MARK_RAN, the changeset appears in DATABASECHANGELOG but the table (or other change) is not created.

Expected checks

• Check the console output or log file for messages like "Precondition [tableExists] failed" or "Precondition passed".
• After a successful run, verify that the expected database object exists (e.g., SELECT * FROM user_profile;).
• Inspect the DATABASECHANGELOGLOCK table before and after the run to confirm that Liquibase acquired the lock only after evaluating preConditions.
• When using onFail=MARK_RAN, query DATABASECHANGELOG for the changeset ID and confirm that the DATEEXECUTED column is populated while the associated object is absent.

Recovery options

• Temporarily disable preConditions – set the onFail attribute to WARN or remove the <preConditions> block to allow the update to proceed.
• Use MARK_RAN for safe retries – if a preCondition failure is expected in certain environments, configure onFail=MARK_RAN so the changeset is considered applied and will not be re‑attempted on subsequent runs.
• Manual rollback – if a changeset was applied despite a failing preCondition (e.g., due to a mis‑configured onError), you can manually drop the created object or run a dedicated rollback changeset.