8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Liquibase and Source Control : Changes to Scripts Over Time

After a brief Twitter interaction with Martin D'Souza, we hopped on a Zoom call to discuss the way Liquibase handles changes to scripts over time, and I thought it would make interesting reading.

The Scenario
The Problem
How do we prevent this?
Conclusion

The Scenario

If you come from the Oracle world, you probably put everything into SQL scripts. All your database code is in scripts in source control (I hope), along with all your DDL and DML changes.

During the lifespan of a piece of code in Git, the code may change several times, but ultimately it will always be the same file in Git. If we are using these scripts from Liquibase, we'll see is a similar change set repeated numerous times in the master change log over time.

Here's a silly example. Imagine we have a function called AMEND_PAY_FUNCTION stored in a file called "amend_pay_function.sql".

In January we create the function to add 10% on to pay for all employees, check the file into Git and create a change log called "amend_pay_function_2020_01_01.xml" with the following contents.

<?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"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <changeSet author="tim" id="amend_pay_function_v1" runOnChange="true" failOnError="false">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/amend_pay_function.sql"
               relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

In April there is a 10% pay cut for all staff, so we amend the AMEND_PAY_FUNCTION function in the "amend_pay_function.sql" file, check it into Git and create a second change log called "amend_pay_function_2020_04_01.xml". It's similar to the previous one, but the change set has a different change set ID, so it is not mistaken for the previous change set.

<?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"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
   <changeSet author="tim" id="amend_pay_function_v2" runOnChange="true" failOnError="false">
      <sqlFile dbms="oracle"
               endDelimiter=";"
               path="../scripts/amend_pay_function.sql"
               relativeToChangelogFile="true"
               splitStatements="false"
               stripComments="false"/>
    </changeSet>
</databaseChangeLog>

The master change log may look like this.

<?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"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">

  ... Lots of other changes ...

  <include file="./amend_pay_function_2020_01_01.xml" relativeToChangelogFile="true"/> 
  
  ... Lots of other changes ...
  
  <include file="./amend_pay_function_2020_04_01.xml" relativeToChangelogFile="true"/> 
  
  ... Lots of other changes ...
  
</databaseChangeLog>

If we are applying the change log to a customer who has not had any changes applied since before January what happens?

We pull the current version of the code from Git.
We use Liquibase to apply the master change log.
The current version of the "amend_pay_function.sql" script is applied twice. Once by the "/amend_pay_function_2020_01_01.xml" change log and again by the "amend_pay_function_2020_04_01.xml" change log. Remember, it's a reference to the *same* script on the file system.

We end up with the correct version of the function definition at the end of the process, but depending on what other changes happened along the way, this could present a problem.

The Problem

Imagine there was a change log that called a script that did the following DML after each change to the function definition.

UPDATE employees
SET    pay = amend_pay_function(pay);
COMMIT;

At location 1, who apply changes on a monthly basis, they see this.

Version 1 of AMEND_PAY_FUNCTION is loaded.
Pay is increased by 10%.
Version 2 of AMEND_PAY_FUNCTION is loaded.
Pay is decreased by 10%.

At location 2, who apply changes every 6 months, they see this.

Version 2 of AMEND_PAY_FUNCTION is loaded.
Pay is decreased by 10%.
Version 2 of AMEND_PAY_FUNCTION is loaded.
Pay is decreased by 10%.

If locations aren't rapidly moving changes to production, the two locations run the same change log but get very different results.

How do we prevent this?

Unfortunately, the way people like to work with scripts and the way Liquibase attempts to apply them can be at odds in situations like this. As I see it, there are two approaches to this problem.

Use a different source file per version of the code...

We can use a different file for each version of the code, so the change log is definitely loading the correct version of the source. This could be a separate script with a version included in the file name, or the code could be included inline as part of the change log itself. You are definitely going to get the correct sequence of events, but this is a source control disaster. You loose all the power of source control and tracking change over time in files.

Pull and apply one release at a time...

Rather than pulling the latest version of the code with the latest change log from Git, you pull a series of intermediate releases (version/steps) and apply them one at a time in order. These releases could be based on release branches, tags or just a list of known commits. Provided you do it in a sufficiently granular manner, you will get the result you need, whilst retaining the power of source control. The important point here is *you* must guarantee you've not allowed such an event described above to happen within an individual release step.

Conclusion

This problem is not unique to Liquibase. You can encounter similar issues in a manual deployment process. If you are only tracking schema changes, life is easy. Once you include data into the mix, which you have to, life becomes more complicated.

Both methods proposed here have their pros and cons, but this is the reality of working on complex systems. Liquibase isn't magic. You would have to plan to avoid this type of situation in a manual operation, just the same way you have to with Liquibase. It all comes down to culture change and discipline.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.