Managing Data Changes to your PostgreSQL Database with Liquibase

Managing data changes to your PostgreSQL Database with Liquibase

Speaking for myself, rarely do I spend time thinking about the effort involved in tracking, logging, managing, and recovering from unintended data changes. Not referring to backups nor restores here, talking specifically about changes to tuples in tables that occur many times a day. 

The thought had not even crossed my mind until recently when a client asked: “Is managing, tracking, and versioning database changes in a PostgreSQL database possible?” The answer: “Yes, you can. It is achievable with Liquibase.”

What is Liquibase?

Liquibase lets you track and version database schema changes, and deploy database code with the velocity to match your application code. Liquibase is available in multiple editions: community (free), pro, business, and enterprise.

An analogy for Liquibase would be that it is the equivalent of a source control management system for data.

Features and capabilities of Liquibase

Features and capabilities vary by edition, the ones listed below are available across all editions:

• Rollback
• Change preview/dry run
• Database state comparison
• Command line & properties file
• Community support
• Training
• More...

Creating a Project in Liquibase Hub

The first step for working with Liquibase is to create a project, such a project will be used to attach all data changes with the registerChangeLog subcommand. 

A Liquibase project is created by performing the steps below:

• Navigate, and Login to Liquibase Hub - an registered account should be created
• Click on Projects Link
• Click on Create Project Link
• Type in Project
  • Name
  • Description
• Click Create Project Button

Downloading your Liquibase jar file

Navigate to Liquibase download location, review the requirements, assess your requirements and download the file that matches your environment.

Creating your liquibase.properties file

A liquibase.properties file contains the information required in order to authenticate and connect via the JDBC driver. Before proceeding to fill in the details for this file, it is recommended to have the values readily available prior to commencing this step.

The classpath location should indicate the path for its location if the Liquibase jar file is not located alongside the liquibase.properties file.

changeLogFile: dbchangelog.xml
url: jdbc:postgresql://<db_url>:<port>/<database>
username: <username>
password: <password>
classpath: postgresql-42.3.0.jar
liquibaseProLicenseKey:
<licensekey>

Generating Changelog

The subcommand to generate the change log file is:

liquibase generateChangeLog

The generateChangeLog subcommand creates a script file containing all of the objects within the database represented as a changeset (unit of change that can be grouped and tagged) at the moment of generateChangeLog execution. The content of this file can help perform a full rollback should the database suffer a catastrophic failure.

A successful generateChangelog will create a file with the name indicated in the changeLogFile parameter of ‘liquibase.properties’. If successful the resulting output of the command line will display:

Liquibase command ‘generateChangelog’ was executed successfully.

Creating the changeset file

Create a template file containing all the data changes to be pushed towards the database. Keep in mind that Liquibase Pro Edition allows for rollbacks per changeset, which is the format provided as an example. Each data  change should follow the convention below:

<changeSet author="<user> (generated)" id="<id>-<numberofchange>">
   <insert catalogName="<databasename>"
    dbms="postgresql"
    schemaName="public"
    tableName="departments">
      <column name="deptno" value="40"/>
      <column name="dname" value="Sales"/>
      <column name="location" value="USA"/>
   </insert>
   <rollback>
   DELETE FROM public.departments WHERE deptno=40;
   </rollback>   
</changeSet>

ChangeLog files are supported in multiple formats: SQL, XML, JSON, YAML, and other formats.

Update dbchangelog.xml file

Before proceeding to execute the registerChangeLog subcommand, the steps below must be completed:

• Create a copy of the generated dbchangelog.xml file
• Remove all the of the ‘<changeSet>’ tags except the first one
• Copy all of the data changes to be applied after the first ‘<changeSet>’ tag
• Copy ID from generated ID in generated dbchangelog.xml
• Apply ID along with dash and number of data change: 

<id>-<numberofchange>

• Remove the first ‘<changeSet>’ tag

Registering the Changelog

The liquibase subcommand to register the changeLog is:

liquibase registerChangeLog

A prompt will appear listing the Liquibase projects available as choices from which you should:

• Select the desired project to be attached towards

A successful registerChangelog will be shown

Liquibase command 'registerChangelog' was executed successfully.

Pushing changes to the DB

At this point, we are ready to push our data changes to the database with the liquibase subcommand:

liquibase update

A successful update will look like to this

Liquibase command 'update' was executed successfully.

Rollback changes to the DB

Rolling back specific changesets is only available in the Liquibase Pro version. The changeset number is what becomes useful when targeting to rollback a specific changeset as illustrated next:

liquibase rollbackCount <count>

A successful rollbackCount will be displayed

Liquibase command 'rollbackCount' was executed successfully.

Conclusion

In this blog we: 

• Explained what Liquibase is
• Glanced at the features and capabilities of Liquibase
• Learned how to 
  • Download Liquibase
  • Generate a changelog
  • Register a changelog
• Push updates to the DB
• Perform rollbacks to db changes

Liquibase is an extremely useful tool as we have shown in this post. Should you have questions feel free to reach out and contact us.

See you in the next blog post!