How to Structure a Complex Changelog

Overview

We've assembled some best practices and examples for managing changesets and changelogs based on some of the most common use cases.

• Option 1: Utilizing 'includeAll' with Formatted SQL Changesets with an XML Master Changelog

• Option 2: Raw SQL Changesets with an XML Master Changelog

• Option 3: Formatted SQL Changesets with an XML Master Changelog

General Best Practices

The best practices listed here apply to any option selected and can have a significant impact on your team and project if not followed.

• Each changeset is recommended to include only one change.

  • Even if two changes are related, they should be separated into two changesets but can still be grouped together in one file.

Option 1: Utilizing 'includeAll' with Formatted SQL Changesets with an XML Master Changelog

This method is highly recommended and reduces the amount of manual effort by your DevOps/DBA teams to manually manage changesets in a master changelog by utilizing a clearly defined process and folder structure. This provides your developers with a clearer understanding of where to store their SQL code while relying on adherence to the process, such as using labels and/or contexts.

Benefits

• Clean and clearly defined folder structure

• Reduces, and can even eliminate, the need to manually update the master changelog

• Utilizes folder structure for managing changesets

• Easier visibility

• Ability to define rollbacks inside the same file as the changeset

Downside

• Requires users to follow a defined process, utilizing labels and/or contexts to manage deployed changesets

Example Folder Structures

Organize by Feature

This strategy works well when each feature/component is independent and managed by a separate team.

Organize by Release and Feature

You may want to set up a hierarchical directory structure using a separate directory for each release and a changelog for each feature with changes specific to that release.

For example, the "accounts.sql" file under the "1.0.0" directory creates a new table, and the "accounts.sql" file in the "1.0.1" directory alters the same table.

Example Master Changelog

mainChangelog.sql

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
     xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
     xmlns:pro="http://www.liquibase.org/xml/ns/pro"
     xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
     http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
     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">

         <includeAll path="releases" />

</databaseChangeLog>

-- liquibase formatted sql

-- changeset liquibase:1 labels:user_test_data
create table Person (
    id int primary key,
    namevarchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);
-- rollback drop table Person

-- changeset liquibase:2 runOnChange:true labels:user_test_data
INSERT INTO Person (id, name, address1, address2, city) VALUES ('1', 'liquibase', 'a', 'b', 'c');
-- rollback DELETE FROM Person WHERE id = 1

It can be challenging to implement significant changes to processes, especially when those changes impact large numbers of users. That's where this option comes in. You can limit changes to your development team by allowing them to continue writing and storing scripts as they do now. However, this puts most of the changelog and changeset management on the shoulders of your DevOps/DBA teams.

Benefits

• Developers can continue to write raw SQL scripts

• Folder structure doesn't impact the process of managing the changelog

Downside

• Requires managing changelogs through the master changelog

• Each changeset will need its own file and rollback file

Example Master Changelog

mainChangelog.sql

<?xml version="1.0" encoding="UTF-8"?><databaseChangeLog     xmlns="http://www.liquibase.org/xml/ns/dbchangelog"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"     xmlns:pro="http://www.liquibase.org/xml/ns/pro"     xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog     http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd     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">    <changeSet  author="Liquibase User" id="1" labels="1.0">      <sqlFile path="changeset1.sql"/>      <rollback>           <sqlFile path="changeset1-rollback.sql"/>      </rollback>  </changeSet>  <changeSet  author="Liquibase User" id="2" labels="1.0">    <sqlFile path="changeset2.sql"/>    <rollback>       <sqlFile path="changeset2-rollback.sql"/>    </rollback>  </changeSet></databaseChangeLog>

Example Changesets

Note: Multiple changset files are required for this process.

changeset1.sql

create table Person (     id int primary key,     namevarchar(50) not null,     address1 varchar(50),     address2 varchar(50),     city varchar(30));

changeset1-rollback.sql

drop table Person

changeset2.sql

INSERT INTO Person (id, name, address1, address2, city) VALUES ('1', 'liquibase', 'a', 'b', 'c');

changeset2-rollback.sql

DELETE FROM Person WHERE id = 1

Option 3: Formatted SQL Changesets with an XML Master Changelog

This method offers a compromise and middle ground by combining the benefits of the formatted SQL changeset from option 1 with the changelog management style from option 2. There's less emphasis on folder structure and process for your developers to follow, but your DevOps/DBA Teams will still shoulder the changelog and changeset management. The largest advantage of this method over option 2 is consolidating your scripts and rollbacks.

Benefits

• Folder structure doesn't impact the process of managing the changelog

• Ability to define rollbacks inside the same file as the changeset

Downside

• Requires managing changelogs through the master changelog

Example Master Changelog

mainChangelog.sql

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
     xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
     xmlns:pro="http://www.liquibase.org/xml/ns/pro"
     xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
     http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
     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">

    <include path="/changeset1.sql" />
    <include path="/folder/changeset2.sql" />
    <include path="/changeset3.sql" />

</databaseChangeLog>

-- liquibase formatted sql

-- changeset liquibase:1 labels:user_test_data
create table Person (
    id int primary key,
    namevarchar(50) not null,
    address1 varchar(50),
    address2 varchar(50),
    city varchar(30)
);
-- rollback drop table Person

-- changeset liquibase:2 runOnChange:true labels:user_test_data
INSERT INTO Person (id, name, address1, address2, city) VALUES ('1', 'liquibase', 'a', 'b', 'c');
-- rollback DELETE FROM Person WHERE id = 1

Additional Resources

Liquibase documentation is an excellent resource for getting more information and details on the various concepts and features mentioned below.

Additionally, Liquibase University offers free training resources that go into the concepts outlined here in more detail, along with tutorials. The concepts covered here are covered in the Liquibase University 203 course, "Managing Liquibase Changelogs". We highly recommend completing the training offered at Liquibase University.