Using Liquibase diffChangeLog to Bring Legacy Schemas Under Version Control

Problem: Legacy databases live outside version control

Many teams inherit databases that were created long before any migration tool was adopted. Schema objects exist only in the running instance, making it hard to reproduce environments, track changes, or enforce consistency across environments.

Thesis: The diffChangeLog command can reverse‑engineer a changelog from an existing schema

Liquibase’s diffChangeLog compares a target database (or a snapshot) with an empty model and emits a changelog that contains the necessary createTable, addColumn, createIndex, etc., changesets to rebuild that schema from scratch. Once generated, the changelog can be committed to source control and used like any other Liquibase change set.

How the command works

When invoked, Liquibase introspects the target database via JDBC metadata, collects all tables, columns, constraints, indexes, and sequences, then orders them as reported by the database. For each object it creates a changeset that would create that object in an empty database. The output includes contexts and labels that you can later use to filter which changesets run in a particular environment.

Running diffChangeLog in practice

The command can be executed from the CLI, Maven, or Gradle. Below is a CLI example that assumes you have read‑only access to the source schema.

# CLI – replace placeholders with your actual values
liquibase \
  --url=jdbc:postgresql://db.example.com:5432/legacy_schema \
  --username=liquibase \
  --password=***** \
  --changeLogFile=src/main/resources/db/changelog/initial.xml \
  diffChangeLog

Required permissions: the JDBC user needs at least SELECT on information_schema (or the vendor‑specific equivalent) and SELECT on the tables you wish to capture. No write access is needed because the command only reads metadata.

After the command finishes, inspect initial.xml. You should see a series of changesets like:

<changeSet id="1" author="generated">
  <createTable tableName="customer">
    <column name="id" type="BIGINT">
      <constraints primaryKey="true" nullable="false"/>
    </column>
    <column name="email" type="VARCHAR(255)"/>
  </createTable>
</changeSet>

Verifying the generated changelog

To confirm that the changelog reproduces the source schema:

1. Create a fresh, empty database (same vendor, version‑compatible).
2. Run liquibase --url=jdbc:... --changeLogFile=initial.xml update against the empty database.
3. Use a schema‑diff tool (e.g., pg_dump --schema-only for PostgreSQL or mysqldump --no-data for MySQL) to export both the original and the newly created schema.
4. Compare the two dumps; they should be identical aside from auto‑generated values like sequences.

If the diff shows differences, examine the generated changelog for missing objects (e.g., stored procedures, triggers) or data types that require a Liquibase extension.

Trade‑offs and limitations

• Extent of capture: diffChangeLog captures tables, columns, constraints, indexes, and sequences by default. Objects such as stored procedures, functions, triggers, or custom types need the appropriate Liquibase extension (liquibase-hibernate, liquibase‑sql, etc.) to be included.
• Verbosity: Large schemas can produce dozens or hundreds of changesets, many of which are similar (e.g., many addColumn statements). Teams often manually consolidate repetitive changesets or group them into logical blocks to keep the changelog maintainable.
• Idempotency: Running the command again against the same source database yields an empty changelog, which is useful for drift detection in CI pipelines but means you must commit the initial output before re‑running.

Actionable closing

Start by generating a baseline changelog for your legacy schema using the CLI example above. Verify the output against an empty database, then commit the changelog to your repository. Add a CI step that runs liquibase diffChangeLog and fails the build if the output is non‑empty — this will alert you to any drift from the version‑controlled schema. Over time, refactor the generated changelog into meaningful, labeled changesets and adopt the usual Liquibase workflow for future changes.