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="https://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog https://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="https://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://www.liquibase.org/xml/ns/dbchangelog https://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.