Distributed SQL Change Management with Liquibase and YugabyteDB on GKE
Tags: yugabyte, postgres, kubernetes, distributedsystems
Published: 2020-08-13 00:30:20

[Liquibase](https://www.liquibase.org/) is an open source and extensible change management project that supports [a variety of databases](https://www.liquibase.org/get-started/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](https://docs.liquibase.com/home.html).

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

* Install a 3 node YugabyteDB cluster on Google Kubernetes Engine
* Build the sample Northwind database
* 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**.

For a deeper discussion about what Distributed SQL is, check out, [“What is Distributed SQL?”](https://blog.yugabyte.com/what-is-distributed-sql/)

# What is YugabyteDB?
[YugabyteDB](https://github.com/yugabyte/yugabyte-db) 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](https://www.yugabyte.com/slack). Ok, let’s dive in…

# Step 1: Install YugabyteDB on a GKE Cluster using Helm 3
In this section we are going to install YugabyteDB on the cluster. The complete steps are documented [here](https://docs.yugabyte.com/latest/deploy/kubernetes/oss/helm-chart/). We’ll assume you already have a GKE cluster up and running as a starting point.

The first thing to do is to add the charts repository.

```bash
$ helm repo add yugabytedb https://charts.yugabyte.com
```

Now, fetch the updates.

```bash
$ helm repo update
```

Create a namespace. In this case we’ll call it `yb-demo`.

```bash
$ kubectl create namespace yb-demo
```

Expected output:

```bash
namespace/yb-demo created
```

We are now ready to install YugabyteDB. In the command below we’ll be specifying values for a resource constrained environment.

```bash
$ helm install yb-demo yugabytedb/yugabyte \
--set resource.master.requests.cpu=1,resource.master.requests.memory=1Gi,\
resource.tserver.requests.cpu=1,resource.tserver.requests.memory=1Gi,\
enableLoadBalancer=True --namespace yb-demo --wait
```

To check the status of the cluster, execute the below command:

```bash
$ kubectl get services --namespace yb-demo
```
![check the status of the YugabyteDB cluster Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/2-check-the-status-of-the-cluster.png)

Note the external-IP for `yb-tserver-service` which we are going to use to establish a connection between YugabyteDB and Liquibase. From the screenshot above we can see that the IP is `34.72.XX.XX` and the YSQL port is `5433`.

# Step 2: Creating the Northwind sample database
The next step is to download a sample schema and data. You can find a variety of sample databases that are compatible with YugabyteDB [in our Docs](https://docs.yugabyte.com/latest/sample-data/). For the purposes of this tutorial we are going to use the [Northwind](https://docs.yugabyte.com/latest/sample-data/northwind/) sample database. The Northwind database contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods from around the world. The Northwind database is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting.

Connect to the `yb-tserver-pod` by running the following command:

```bash
$ kubectl exec -n yb-demo -it yb-tserver-0 /bin/bash
```

To download the schema and data files, run the following commands:

```bash
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_ddl.sql
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_data.sql
```

**Note:** If the Google Cloud Shell tells you that the wget command does not exist, you can execute:

```bash
$ yum install wget -y
```

To connect to the YSQL service exit out of the pod shell and run the following command:

```bash
$ exit 
$ kubectl exec -n yb-demo -it yb-tserver-0 -- ysqlsh -h yb-tserver-0.yb-tservers.yb-demo
```

Create a database and connect to it using the following commands:

```sql
yugabyte=# CREATE DATABASE northwind;
northwind=# \c northwind;
```

We can now create the database objects and load them with data using the files we downloaded to `yb-tserver-pod` using the following commands:

```bash
northwind=# \i 'northwind_ddl.sql';
northwind=# \i 'northwind_data.sql';
```

Verify that the tables are created by running the following command:

```sql
northwind-# \d

                 List of relations
 Schema |          Name          | Type  |  Owner
--------+------------------------+-------+----------
 public | categories             | table | yugabyte
 public | customer_customer_demo | table | yugabyte
 public | customer_demographics  | table | yugabyte
 public | customers              | table | yugabyte
 public | employee_territories   | table | yugabyte
 public | employees              | table | yugabyte
 public | order_details          | table | yugabyte
 public | orders                 | table | yugabyte
 public | products               | table | yugabyte
 public | region                 | table | yugabyte
 public | shippers               | table | yugabyte
 public | suppliers              | table | yugabyte
 public | territories            | table | yugabyte
 public | us_states              | table | yugabyte
(14 rows)
```

Verify we have data by issuing a simple `SELECT`:

```sql
northwind=# SELECT count(*) FROM products;

 count
-------
    77
(1 row)
```

By default, a YugabyteDB installation doesn’t have a password setup for the default `yugabyte` user. Specifying one is done the same way you’d do it in PostgreSQL.

```sql
northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';
```

# Step 3: Install and configure Liquibase
Locate the appropriate version of Liquibase for your platform from their downloads page.

[https://www.liquibase.org/download](https://www.liquibase.org/download)

For the purposes of this demo, we’ll be installing Liquibase locally on a Mac.

## Create a Liquibase project
On my Mac I created a new folder called `LiquibaseYugabyteDB`.

## Download the appropriate PostgreSQL driver
Visit [https://jdbc.postgresql.org/download.html](https://jdbc.postgresql.org/download.html) and download the appropriate JDBC driver for your environment. I downloaded JDBC 4.2 (42.2.14) and placed it in the `LiquidbaseYugabyteDB` folder.

## Create a changelog file
Next, in this same folder I created a file called `dbchangelog.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
<?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.

```bash
changeLogFile:  ../LiquibaseYugabyteDB/dbchangelog.xml  
    url:  jdbc:postgresql://34.72.XX.XX:5433/northwind
    username:  yugabyte  
    password:  password
    driver:  org.postgresql.Driver  
    classpath:  ../LiquibaseYugabyteDB/postgresql-42.2.14.jar
```

# Step 4: Create a changeset and verify the results
## Create a changeset
To create a `changeset`, return to the `dbchangelog.xml` file and add the following copy. In the `changeset` we are going to create a `promotions` table in the `northwind` database with columns for the name of the promotion and the discounted amount.

```xml
<?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="jguerrero">  
        <createTable  tableName="promotions">  
            <column  name="id"  type="serial">  
                <constraints  primaryKey="true"  nullable="false"/>  
            </column>  
            <column  name="name"  type="varchar(50)">  
                <constraints  nullable="false"/>  
            </column>  
            <column  name="discount"  type="numeric(3,2)"/>                  
        </createTable>  
   </changeSet>
  </databaseChangeLog>
```

The SQL equivalent of the `changeset` above would be:

```sql
CREATE  TABLE  promotions  
  (id serial PRIMARY KEY,  
   name  VARCHAR(50) UNIQUE NULL,  
   discount  NUMERIC (3,2) 
  );
```

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

```bash
$ liquibase update
Liquibase Community 4.0.0 by Datical
Starting Liquibase at 11:00:28 (version 4.0.0 #19 built at 2020-07-13 19:45+0000)
Liquibase: Update has been successful.
```

## Verify the results
To verify that the `promotions` table was created we can open up a YSQL shell and insert two records into the table and select the data out.

```sql
INSERT INTO promotions 
(id, name, discount)
VALUES 
(DEFAULT, 'Memorial Day Promotion', 3.50),
(DEFAULT, 'International Coffee Day Promotion', 5.35);

SELECT * FROM promotions;
```
![verify that the promotions table was created YugabyteDB Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/3-verify-that-the-promotions-table-was-created.png)

# Step 5: Managing changes
Along with the `promotions` table, you should now see two additional tables that Liquibase has created in the `northwind` database.

![Northwind database YugabyteDB Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/4-create-the-northwind-database-liquibase-yugabytedb-gke-tutorial.png)

## The databasechangelog table
This table keeps a record of all the `changesets` that were deployed. This means that the next time you deploy again, the `changesets` in the `changelog` will be compared with the `databasechangelog` tracking table and only the new `changesets` that were not found in the `databasechangelog` will be deployed. You will notice that a new row was created in that table with the `changeset` information we have just deployed. For this example:

![new row was created in that table with the changeset information YugabyteDB Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/5-changeset-information-row-was-created-1024x54.png)

## The databasechangelock table
This table is used internally by Liquibase to manage access to the `changelog` table during deployment. So, nothing to see here!

# Step 6: Issue a second changeset
##Create a second changeset
Let’s create an additional `changeset`. In this case let’s add two more columns to the `promotions` table. A `start_date` column and a `stop_date` column so we know when a promotion starts and ends. Instead of XML, let’s use SQL to initiate the changes. To do this, create a file in your Liquibase project directory called `changelog.sql`.

Add the following commands to this file:

```bash
--liquibase formatted sql  
--changeset jimmy:1
ALTER TABLE promotions
ADD COLUMN start_date TIMESTAMP,
ADD COLUMN stop_date TIMESTAMP;
```

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

```bash
$ liquibase --changeLogFile=changelog.sql update
```

## Verify the change
We should now be able to see a second record in the `databasechangelog` tracking table.

![see a second record in the databasechangelog tracking table YugabyteDB Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/6-see-a-second-record-in-the-databasechangelog-tracking-table.png)

We should also be able to see the new `start_time` and `stop_date` columns in the `promotions` table.

![see the new start_time and stop_date columns in the promotions table YugabyteDB Liquibase GKE tutorial](https://3lr6t13cowm230cj0q42yphj-wpengine.netdna-ssl.com/wp-content/uploads/2020/07/7-see-the-new-start_time-and-stop_date-columns-in-the-promotions-table.png)

# Conclusion
That’s it! You now have a PostgreSQL-compatible, 3 node YugabyteDB cluster running on GKE whose changes are now being managed and tracked by Liquibase. To learn more about supported third-party PostgreSQL tools that work with YugabyteDB, check out the [YugabyteDB Documentation](https://docs.yugabyte.com/latest/tools/) or join our community [Slack](https://www.yugabyte.com/slack) and hit us up there.