Using Liquibase Preconditions to Make Schema Changes Idempotent and Environment‑Safe

Problem: Drift‑Prone Migrations in Shared Environments

In a shared development or staging database, manual edits, incomplete rollouts, or restores from snapshots often leave the live schema out of sync with the DATABASECHANGELOG table. When Liquibase runs a changeset that tries to create a table, column, or index that already exists, the update aborts with a “duplicate object” error and the migration pipeline stalls.

Thesis: Use Preconditions to Turn Fragile Changesets into Self‑Verifying Steps

Liquibase’s preConditions block lets a changeset inspect the target database before any DDL runs. By guarding against existing objects, a changeset becomes idempotent—it can be applied multiple times without side effects—while still recording its execution in DATABASECHANGELOG.

Understanding Preconditions

A precondition can be expressed in structured changelogs (XML, YAML, JSON) or as a header in formatted SQL. The most common guard types are:

• tableExists – true if a table is present
• columnExists – true if a column exists on a table
• indexExists – true if an index is defined
• Negated forms via not (e.g., not:columnExists)

The onFail attribute controls the outcome when the guard evaluates to true:

onFail Value	Behavior
FAIL	Abort the changeset and report an error
WARN	Log a warning and continue with the changeset
MARK_RAN	Record the changeset as executed without running DDL
CONTINUE	Skip the rest of the preconditions and continue with the changeset

In open‑source Liquibase 3.x and 4.x the syntax is identical; only the default onFail value (FAIL) remains constant. The onError matrix works similarly for runtime errors.

Worked Example: Adding an Email Column Safely

Below is a YAML changeset that adds an email column to the person table. The precondition ensures the column is added only if it does not already exist, and marks the changeset as executed if the column is present.

- changeSet:
    id: add-email-column
    author: devops
    preConditions:
      onFail: MARK_RAN
      onError: FAIL
      tableExists:
        tableName: person
      not:
        columnExists:
          tableName: person
          columnName: email
    changes:
      - addColumn:
          tableName: person
          columns:
            - column:
                name: email
                type: varchar(255)
                constraints:
                  nullable: true

In formatted SQL the same logic is expressed as a header line:

--preconditions onFail:MARK_RAN tableExists:person not:columnExists tableName:person columnName:email
ALTER TABLE person ADD COLUMN email VARCHAR(255);

When the changeset runs:

• If person.email does not exist, Liquibase adds the column and records the changeset as EXECUTED.
• If the column already exists, Liquibase skips the ALTER TABLE, marks the changeset as EXECUTED, and proceeds without error.

Trade‑Offs & Limitations

• MARK_RAN hides real drift: If a precondition is too broad, Liquibase may record a changeset as executed even though the required DDL never ran. Subsequent rollbacks will assume the change exists, potentially breaking the database.
• Preconditions are not a substitute for a clean migration history. Use them only to recover from known, documented drift.
• DDL is not transactional on many RDBMSs; a precondition that passes but fails later in the block can leave the database in a partially updated state.
• Formatted SQL precondition parsing is whitespace‑sensitive. A stray comment or line break can change the guard’s meaning.
• Version upgrades may rename or deprecate precondition types. Verify the grammar for your Liquibase version before promoting changes.

Practical Checklist for Reliable Migrations

1. Validate the changelog: liquibase validate ensures syntax and checksum consistency.
2. Check pending changes: liquibase status lists changesets that have not yet run.
3. Run update on a clean database: Confirm that all changesets apply without errors.
4. Simulate drift: Manually create the guarded object, then run liquibase update to verify the precondition path (MARK_RAN or WARN).
5. Verify DATABASECHANGELOG: Inspect the EXECUTED entries to ensure they match the expected state.
6. Automate CI checks: Include validate and status in your CI pipeline, and run update against a fresh clone of the target database.

Conclusion

Preconditions give Liquibase the ability to guard against duplicate objects and make migrations resilient to schema drift. By choosing MARK_RAN carefully, keeping guards narrow, and verifying the path with CI, teams can keep migrations idempotent and safe across shared environments without sacrificing traceability.