Guarding Liquibase Migrations with Preconditions to Prevent Duplicate Schema Changes

Problem: Migrations that run twice cause errors or duplicate data

In a CI/CD pipeline, the same Liquibase changelog can be applied more than once—due to retries, parallel stages, or a manual rerun after a failure. If a ChangeSet creates a table, inserts reference data, or adds a column, a second execution can throw errors such as table already exists or silently duplicate rows, corrupting reference data sets.

Thesis: Preconditions let you declaratively guard each ChangeSet so it runs only when the database is in the expected state

Liquibase evaluates the <preConditions> element before executing a ChangeSet. If the precondition fails, you decide whether to skip the change, mark it as ran, or halt the entire update. This makes migrations idempotent without writing procedural checks in SQL.

How Preconditions Work

• The <preConditions> block can contain multiple checks combined with implicit AND or explicit <and>/<or> elements.
• Supported checks include <tableExists>, <columnExists>, <indexExists>, custom SQL that returns a result set, and DBMS‑specific guards via the dbms attribute.
• The onFail attribute controls the behavior: MARK_RAN (skip but consider executed), WARN (log warning and continue), or HALT (stop the update).

Worked Example: Preventing Duplicate Reference‑Data Insertion

Suppose you have a reference table country that should be populated once with a static list. The following ChangeSet inserts a row only when the table exists but the row for 'ZZ' is missing.

<databaseChangeLog
  xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.8.xsd">

  <changeSet id="insert-country-zz" author="engineer">
    <preConditions onFail="MARK_RAN">
      <and>
        <tableExists tableName="country"/>
        <not>
          <sqlCheck expectedResult="0">
            SELECT COUNT(*) FROM country WHERE country_code = 'ZZ';
          </sqlCheck>
        </not>
      </and>
    </preConditions>

    <insert tableName="country">
      <column name="country_code" value="ZZ"/>
      <column name="name" value="Test Land"/>
    </insert>
  </changeSet>
</databaseChangeLog>

When Liquibase runs this ChangeSet on a database where the country table already contains the 'ZZ' row, the precondition fails. Because onFail="MARK_RAN" is set, Liquibase records the ChangeSet as executed and skips the insert, preventing duplicate data.

Trade‑Offs and Limitations

• Maintenance overhead: Overusing preconditions can clutter the changelog and make it harder to read. Keep them focused on critical protection points.
• Database platform differences: Some precondition types (e.g., <sqlCheck>) may not be supported on all DBMSs. Test on each target platform to avoid silent failures.
• Critical failures: A precondition that always fails can stop a deployment if onFail is set to HALT. Choose a policy that matches your risk tolerance.

Actionable Takeaway: Verify and Document Preconditions

To validate a precondition:

1. Create a local test database.
2. Run liquibase update with the ChangeSet that contains the precondition.
3. Check that the ChangeSet is skipped when the precondition fails and applied when it passes.
4. Review the DATABASECHANGELOGLOCK table to ensure no stale locks remain after a precondition failure.

Document each precondition’s intent in the changelog comment. This helps future maintainers understand why a guard exists and prevents accidental removal.

By strategically placing preconditions, you make your Liquibase migrations resilient, idempotent, and easier to reason about—especially in automated pipelines where repeat runs are common.