Liquibase: Web Framework Independent Database Migration Tool

If you’re familiar with web frameworks such as Rails and Django, you probably know that these frameworks come with ORM and database migration. This is a great feature to have it enables evolutionary database design in your application. The problem with using built-in migration tools such as this is that you’re locked to your web framework. Moreover, independent migration tools such as Liquibase or Flyway may have some features that are not available off-the-self in your framework. In this article, I’m going to talk about Liquibase and it’s features that can be attractive to you based on your use case.

One of the most attractive features of Liquibase is the rollback meaning you can rollback your migration to bring your database schema back to its original state. At the time of this writing, this is not possible in Flyway or Django’s built-in migration manager. Additionally, Liquibase supports defining changes to your database in various formats such as SQL, XML, JSON and YAML. While this can be attractive, it also creates confusion as to which format is the best. According to this 2019 survey, majority of developers prefer to use the SQL approach and this conforms with my own experience of defining database changes in Liquibase. Defining database changes in SQL seems lot more intuitive than that in XML, JSON or YAML. However, there are two ways you can define changes in SQL in Liquibase - 1. With sqlFile and 2. With Liquibase Formatted SQL

sqlFile Approach

Here is an example of how you can use the sqlFile tag in your changeset in your xml changelog -

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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.5.xsd">

    <changeSet author="john-doe" id="migration-00000">
        <sqlFile path="00000_insert_rows_to_main_table.sql"
                 splitStatements="false"
                 relativeToChangelogFile="true"
                 stripComments="true" />
        <rollback>  
             <sqlFile path="00000_insert_rows_to_main_table_rb.sql"
                 splitStatements="false"
                 relativeToChangelogFile="true"
                 stripComments="true" />
        </rollback>
    </changeSet>

The problem with this approach is that you’ll have to define your rollback change in a separate sql file making your changelog bulky and also having to define a separate rollback sql for every sql change is not fun either. There is a better way to do the same with the help of Liquibase Formatted SQL

Liquibase Formatted SQL

Here is an example of a Liquibase Formatted SQL -

--liquibase formatted sql
--changeset john-doe:migration-00000
INSERT INTO public.main_table (col1, col2, col3, col4)
VALUES
 ('val1', 'val2', 'val3', val4);

 --rollback delete from public.main_table where col1 = 'val1;

As you can see, each file starts with text –liquibase formatted sql. The second line defines the changset attributes. In this example, we’re defining the author and the changeset id. Then you can define one or more sql statements of changes you want in your database. After you have defined all of your migration changes, you can then define your rollback changes each in a separate line that starts with –rollback. This is a much better approach as you define both your migration changes and its corrsponding rollback changes in the same file. To include Liquibase Formatted SQL in your xml changelog, you just need to use the include tag as the following (assuming your Liquibase Formatted SQL is based as 00000_insert_rows_to_main_table.sql-

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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.5.xsd">

    <include file="00000_insert_rows_to_main_table.sql" relativeToChangelogFile="true" />

</databaseChangeLog>

• data engineering