Distributed SQL Change Management Using Liquibase-YugabyteDB Extension

Liquibase is an open source and extensible change management project that supports a variety of databases including Snowflake, MySQL, and PostgreSQL via JDBC. Liquibase allows users to easily define changes in SQL, XML, JSON, and YAML. These changes are then managed in a version control system so the changes can be documented, ordered, and standardized. For more information on the features and benefits of Liquibase, check out their documentation site.

In this blog post, we’ll show you how to:

• Start a YugabyteDB cluster on your local Engine
• Install and configure Liquibase
• Create a simple changeset and verify the results
• Explore how changes are documented and managed in Liquibase

Starting a YugabyteDB cluster

Before starting the YugabyteDB cluster on your local workstation, you must have it installed. To install YugabyteDB on your system follow the steps mentioned on this page.

• To create a single-node local cluster with a replication factor (RF) of 1, run the following command.

$ ./bin/yugabyted start

Alternatively, you can also use the yb-ctl command to create the cluster like

$ ./bin/yb-ctl create

• Check the cluster status by running the following command:

$ ./bin/yugabyted status

Or in case of yb-ctl cluster,

$ ./bin/yb-ctl status

In this blog, we will be working with the default yugabyte database. In case you want to work with another database, create it by running the command in ysqlsh:

CREATE DATABASE <database_name>;
\c <database_name>;

Once the cluster is up and running, we can proceed to the next step of setting up Liquibase.

Install and configure Liquibase

Locate the appropriate version of Liquibase for your platform from their downloads page.

Once you have downloaded the appropriate version of Liquibase, extract the file. Next step is to add the Liquibase path as an environment variable in your machine.

$ echo "export PATH=$PATH:/<full-path>/liquibase-x.y.z" >> ~/.bash_profile

$ source ~/.bash_profile

Note: If your terminal does not run .bash_profile at start-up, you can alternatively append the Liquibase path to the PATH definition in .bashrc or .profile.

After doing the previous step, you can now create a folder on your system for your Liquibase Project, in my case it was Liquibase-Demo.

Download the appropriate drivers

Visit https://jdbc.postgresql.org/download.html and download the appropriate JDBC driver for your environment. I downloaded JDBC 4.2 (42.2.8) and placed it in the Liquibase-Demo folder.

Go to the liquibase-yugabytedb repository and download the latest released Liquibase extension liquibase-yugabytedb-<version>.jar file. Place the liquibase-yugabytedb-<version>.jar file in the Liquibase-Demo folder.

If you use Maven, you can alternatively download the PostgreSQL JDBC driver and put the driver in a location that your Maven build can access. Configure the Maven pom.xml file to use the local copy of the driver jar file. For example:

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.8</version>
</dependency>

Additionally, you need to specify the Liquibase YugabyteDB extension in your pom.xml file as explained in Configuring Liquibase Attributes in your Maven POM File. Make sure that the Liquibase plugin and the extension have the same version.

<dependency>
<groupId>org.liquibase.ext</groupId>
<artifactId>liquibase-yugabytedb</artifactId>
<version>4.4.1</version>
</dependency>

The liquibase-yugabytedb extension

The liquibase-yugabytedb extension overrides methods from PostgreSQL database implementation. It basically makes changes where PostgreSQL and YugabyteDB are different, for example, YugabyteDB does not yet support Initially Deferrable columns for unique constraints, DDLs in transactions. Also, YugabyteDB has a different definition and implementation of tablespaces from PostgreSQL, so the methods supportsInitiallyDeferrableColumns(), supportsDDLInTransaction() and supportsTablespaces() return false for YugabyteDB implementation. Other changes were made to getDefaultPort()and getShortName().

Create a changelog file

Next, in this same folder I created a file called master-changelog.xml file. This changelog contains the sequence of changesets, each one of which makes small changes to the structure of the database. Add the following boilerplate to this file to get started.

<?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.8.xsd">
</databaseChangeLog>

Create a properties file

Now in the same directory, create a file called liquibase.properties and add the following content to it that is reflective of your environment. For more information on how to configure liquibase.properties file, visit this page. Notice that classpath has the liquibase-yugabytedb-<version>.jar for enabling the YugabyteDB specific behaviour.

changeLogFile:master-changelog.xml
url: jdbc:postgresql://localhost:5433/yugabyte
username:  yugabyte
password:  yugabyte
classpath:  <relative-path-to-postgres-jar>/postgresql-42.2.8.jar:<relative-path-to-liquibase-yugabytedb-<version>-jar>/liquibase-yugabytedb-<version>.jar

When using the YugabyteDB on-premises and specifying the URL, enter your IP address or host name, and then the port followed by the database name. The example of the format is: jdbc:postgresql://<IP OR HOSTNAME>:<PORT>/<DATABASE>. When specifying the classpath for the postgresql driver make sure that the version matches the version of the driver that you have downloaded.

Create a changeset and verify the results

Create the changeset

To create a changeset, return to the master-changelog.xml file and add the following copy highlighted in red. In the changeset we are going to create a test table in the yugabyte database with test columns.

<?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.8.xsd">
<changeSet  id="1"  author="sfurtisarah">
<createTable tableName="test_table">
<column name="test_id" type="int">
<constraints primaryKey="true"/>
</column>
<column name="test_column" type="varchar"/>
</createTable>
</changeSet>
</databaseChangeLog>

Execute the changeset

Execute the update command locally in the directory you created to send the changeset to the database.

$ liquibase update

On successful update, you will see the following:

Liquibase: Update has been successful.

Verify the results

To verify that the test_table table was created we can open up a YSQL shell and run the following command:

$ \dt

After your first update, you will see test_table along with the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables added to the database:

DATABASECHANGELOG table. This table keeps a record of all the changesets that were deployed. When you deploy, the changesets in the changelog are compared with the DATABASECHANGELOG tracking table, and only the new changesets that were not found in the DATABASECHANGELOG will be deployed.

DATABASECHANGELOGLOCK table. This table is used internally by Liquibase to manage access to the DATABASECHANGELOG table during deployment and ensure only one instance of Liquibase is updating the database at a time, whether that is creating, updating or deleting changes.

Issue a second changeset

Create a second changeset

Let’s create an additional change set. In this case let’s add another table to the yugabyte database. Instead of XML, let’s use SQL to initiate the changes. To do this, create a file in your Liquibase project directory called new-changelog.sql.

Add the following commands to this file:

--liquibase formatted sql
--changeset liquibase:1
--Database: yugabytedb
CREATE TABLE test_table_2 (test_id INT, test_column VARCHAR, PRIMARY KEY (test_id))

Issue the following command to send the change to the database.

$ liquibase --changeLogFile=new-changelog.sql update

Verify the change in the similar manner as above.

Caveats

When running changelogs against YugabyteDB, there are certain changesets not supported by the database yet. For example dropPrimaryKey changeset which will give immediate failure when tried to run against YugabyteDB. This also means that addPrimaryKey changeset cannot be rolled back. Other changesets not supported by YugabyteDB are addUniqueConstraint and dropUniqueConstraint. Altering and renaming of sequences are not supported as well.

Supported versions

The steps in this blog have been tried against YugabyteDB 2.6 but should work for YugabyteDB 2.4 onwards as well. The Liquibase version tried was 4.3.5. So the steps should work for any versions above that.

Conclusion

In this blog post, we became familiar with the concept of Schema Versioning with an example of managing the schema changes to a YugabyteDB instance using Liquibase. To see a list of all the changesets YugabyteDB can support, head over to the Liquibase documentation page. To learn more about other tools supported by YugabyteDB and how to use them, check out the integrations page.