Distributed SQL Change Management Using Liquibase-Yugabyte 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
New to distributed SQL or YugabyteDB? Read on.
What is Distributed SQL?
Distributed SQL databases are becoming popular with organizations interested in moving data infrastructure to the cloud or cloud native environments. This is often motivated by the desire to reduce TCO or move away from the horizontal scaling limitations of monolithic RDBMS like Oracle, PostgreSQL, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:
- They must have a SQL API for querying and modeling data, with support for traditional RDBMS features like foreign keys, partial indexes, stored procedures, and triggers.
- Automatic distributed query execution so that no single node becomes a bottleneck.
- Should support automatic and transparent distributed data storage. This includes indexes, which should be sharded across multiple nodes of the cluster so that no single node becomes a bottleneck. Data distribution ensures high performance and high availability.
- Distributed SQL systems should also provide for strongly consistent replication and distributed ACID transactions.
“What is Distributed SQL?”
What is YugabyteDB?
YugabyteDB is an open source, high-performance distributed SQL database built on a scalable and fault-tolerant design inspired by Google Spanner. YugabyteDB is PostgreSQL wire compatible, cloud native, offers deep integration with GraphQL projects, plus supports advanced RDBMS features like stored procedures, triggers, and UDFs.
Got questions? Make sure to ask them in our YugabyteDB Slack channel. Ok, let’s dive in…
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
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
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
supportsTablespaces() return false for YugabyteDB implementation. Other changes were made to
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:
After your first update, you will see
test_table along with the
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
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.
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
dropUniqueConstraint. Altering and renaming of sequences are not supported as well.
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.
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.