Liquibase basics






About Liquibase

In release 7 we added Liquibase support to the Ibis Adapter Framework. Liquibase is an opensource source control system for databases. It is integrated in the IAF but Liquibase can also be integrated with Maven or Git. This makes it a great tool for your devops team supporting your CI/CD processes

 

The use and Power of Liquibase

We use Liquibase to control changes to our database structure, creating new tables; adding columns or even changing datatypes of existing columns. Liquibase supports simple changes like adding a record as well as the big ones like refactoring your database. In practice the value is in doing the large more complex changes. Small operational changes that do not need to be reversable or tracked can easily be done using the IAF console database management screens.

Changes in the database can be bundled in a “changeSets”. A changeset is a collection of changes that need to be processed in one go. In our case we group all database changes that belong with a feature deployment in one changeset. If the deployment of the feature is not successful the whole change can be reverted without any problem. Liquibase generates roll back script for every change and handles all complexity. We create our changesets in xml but there is support JSON, YAML and SQL.

<changeSet id="USERS" author="Init DB" failOnError="false">
    <createTable tableName="USERS">
        <column name="UID" type="int" autoIncrement="true">
            <constraints primaryKey="true" unique="true" nullable="false"></constraints>
        </column>
        <column name="NAME"            type="varchar(255)"></column>
        <column name="LASTNAME"        type="varchar(255)"></column>
        <column name="BIRTHDATE"    type="date"></column>
        <column name="EMAIL"        type="varchar(255)"></column>
        <column name="PASSWORD"        type="varchar(255)"></column>
        <column name="ACTIVE"        type="boolean" defaultValueBoolean="true">
            <constraints nullable="false"/>
        </column>
    </createTable>

    <insert tableName="USERS">
        <column name="UID"             value="1"></column>
        <column name="NAME"         value="Test"></column>
        <column name="LASTNAME"     value="User"></column>
        <column name="EMAIL"         value="test@user.com"></column>
        <column name="PASSWORD"     value="40000:15c7d95b125affd110ba5e4a1c344csd3542fcdrew36764216cee91f2c4d1f50:b16c471fecabc744b317c662f36866d4697c06acbaa7f5fc92aaf51b3fa2f725"></column>
    </insert>
</changeSet>

Image 1 – ChangeSet

Using Liquibase has many advantages from a developer as well as service management perspective.

Because it is fully integrated with the IAF, Liquibase will be automatically initialized and started with the IAF application. Liquibase will check if any changes need to be implemented, so no more problems with forgotten database changes leading to unexpected test results. It guarantees the database changes belonging to the specific delivery are performed through the (D)TAP environments. Do not forget to make sure the database user has sufficient access rights on the database!

Even when the database type differs in the environments there is no need to worry, Liquibase supports many database types: MySQL, PostgreSQL, Oracle, H2, etc. The complete list can be found here (http://www.liquibase.org/databases.html) and even when your database is not in the list you can always use SQL to do your changes.

<changeSet author="Ibissource" id="sql-example">
    <sql>INSERT INTO USERS (UID, NAME, LASTNAME, EMAIL, PASSWORD) VALUES (1, 'John', 'Doe', 'JohnDoe@gmail.com', '40000:15c7d95b125affd110ba5e4a1c3448b8516b25b24010e6a216cee91f2c4d1f50:b16c471fecabc744b317c662f36866d4697c06acbaa7f5fc92aaf51b3fa2f725')
        <comment>John wants to join Ibissource!</comment>
    </sql>
</changeSet>

Image 2 – SQL in Liquibase

So what are the drawbacks of Liquibase?

We are now using the tool extensively and we did not find any real problem. The only thing we did find was: On the first time implementation on an existing database, the tool needs to be configured to make it backwards compatible.

Do you want more information about Liquibase? In our next blog we take a deep dive into Liquibase, change logs, changesets and tips and tricks.