Schema Versioning in YugabyteDB Using Flyway

Introduction

As developers, many of us are aware of source versioning which helps us easily track and manage changes to the source code of our applications. Schema versioning is to databases what source versioning is to the applications. It helps track and manage the changes to the databases that our applications rely on.

In this blog post, we will talk about using Flyway – one of the most popular tools that simplify schema versioning for databases – with YugabyteDB – the open source, high-performance, distributed SQL database for global, transactional applications.

YugabyteDB

YugabyteDB is a high performance distributed SQL database for transactional (OLTP) applications. It is 100% open source, available under the Apache 2.0 license. It is feature compatible with Postgres, and combines enterprise-grade relational database capabilities with the horizontal scalability and resilience of cloud native architectures.

It is built using a unique combination of high-performance document store, per-shard distributed consensus replication and multi-shard ACID transactions (inspired by Google Spanner).

Also, its SQL API is wire compatible with PostgreSQL.

Flyway

Flyway offers a solution to easily manage schema changes (also called schema migration) to your database. It provides support for many databases including PostgreSQL. Since YugabyteDB is compatible with PostgreSQL, Flyway works with your YugabyteDB cluster out-of-the-box.

As a first step towards an even better integration with Flyway to manage changes to your database schema for YugabyteDB instances, we have recently added the YugabyteDB-specific implementation of the Flyway APIs to Flyway Community project.

We’ll now cover the steps to 1) set up the YugabyteDB cluster in a Docker container, 2) create a Maven project using Flyway community edition, 3) create schema migrations in SQLs as well as in Java and 4) run them on the YugabyteDB cluster.

Supported versions

The steps in this blog have been tried against YugabyteDB 2.7 but should work for YugabyteDB 2.4 onwards as well.

The YugabyteDB specific implementation is available in Flyway 7.11.2 release onwards.

Setting up YugabyteDB cluster

You can download and install YugabyteDB in an environment of your choice.

Alternatively, you can use Yugabyte Cloud, a fully managed database-as-a-service (DBaaS) for YugabyteDB, where you get a resilient, scalable, PostgreSQL-compatible database for your applications in just a few minutes. We take care of infrastructure and database operations, letting you focus on building applications.

In this blog post, we’ll be creating a YugabyteDB cluster inside a Docker container using the YugabyteDB’s Docker image.

You will need to have Docker runtime installed on your local system.

Once Docker is installed, you can launch the YugabyteDB cluster with the default configurations using the command below. This will also download the Docker image under the hood, if it’s not already found on your local machine.

$ docker run -d --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042\
yugabytedb/yugabyte:latest bin/yugabyted start\
--daemon=false

Check the container status:

$ docker ps

You can also connect to the ysql shell in the YugabyteDB container using the command below. Use Control+D keys to exit the ysql shell.

$ docker exec -it yugabyte ./bin/ysqlsh

Setting up Flyway

Flyway allows users to manage schema migrations in three different ways:

1. Command line

2. Maven plugin

3. Gradle plugin

We’ll showcase the SQL migrations and Java-based migrations through the Maven plugin.

Users can also integrate Flyway in their applications using the gradle plugin or Flyway command line.

Flyway Maven plugin

We’ll need Java 8, 9, 10 or 11 and Maven 3.x installed on your local machine.

Let us create a basic Maven project where we’ll write .sql files as well as a Java class for migrations.

Create Maven project

$ mvn archetype:generate -B \
-DarchetypeGroupId=org.apache.maven.archetypes \
-DarchetypeArtifactId=maven-archetype-quickstart \
-DarchetypeVersion=1.1 \
-DgroupId=foo \
-DartifactId=bar \
-Dversion=1.0-SNAPSHOT \
-Dpackage=foobar

Move to the directory bar and edit the pom.xml file to 1) add flyway-core as project dependency, 2) include the maven-compiler-plugin to specify Java version required for Java-based migrations and 3) add url to connect to YugabyteDB, locations and postgresql JDBC Driver as dependency under the flyway-maven-plugin.

These details are provided below.

<project xmlns="...">
...
<dependencies>
...
  <dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
    <version>7.11.3</version>
  </dependency>
</dependencies>

<build>
  <plugins>
    <plugin>
      <artifactId>maven-compiler-plugin</artifactId>
      <version>3.7.0</version>
      <configuration>
        <source>1.8</source>
        <target>1.8</target>
      </configuration>
    </plugin>
    <plugin>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-maven-plugin</artifactId>
      <version>7.11.3</version>
      <configuration>
        <url>jdbc:postgresql://localhost:5433/yugabyte</url>
        <user>yugabyte</user>
        <locations>
          <location>filesystem:src/main/resources/db/migration</location>
          <location>classpath:db/migration</location>
        </locations>
      </configuration>
      <dependencies>
        <dependency>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
          <version>42.2.14</version>
        </dependency>
      </dependencies>
    </plugin>
  </plugins>
</build>
</project>

Performing migrations

Now that both the setups are ready, we will write some schema migrations and execute them.

With Flyway, you can specify migrations in two ways: via SQLs or as Java classes.

Migrations as SQLs

In this case, the migrations are specified as SQL statements in .sql files.

Create the migration directory src/main/resources/db/migration for .sql files.

1. Create sample migration SQL scripts with the appropriate naming convention under the directory created above. Here we have created two .sql files for two different versioned migrations.

The file V1__Create_space_explorators_table.sql has below SQL.

CREATE TABLE space_explorators ( explorator_id SERIAL PRIMARY KEY, explorator_name VARCHAR(50) NOT NULL, company_name VARCHAR(50) NOT NULL );

And the file V2__Insert_into_space_explorators_table.sql looks like this:

INSERT INTO space_explorators (explorator_id, explorator_name, company_name) VALUES (default, 'Jeff B.', 'Blue Origin'),(default, 'Elon M.', 'SpaceX'),(default, 'Richard B.', 'Virgin Galactica');

2. Check the initial state of the cluster before running the migrations.

$ mvn flyway:info

3. Now, run the migrations and then check the status again.

$ mvn flyway:migrate $ mvn flyway:info

4. You can also check the table from ysql shell.

Migrations as Java classes

For Java-based migrations, we’ll need to write a Java class which extends the BaseJavaMigration class provided by Flyway. The Java class name also needs to follow the naming convention: <prefix><version>__<description>.

Write Java migration

Create the migration directory src/main/java/db/migration. And create the Java class file V3__AddColumn.java in this migration directory and paste the code given below in it. This code defines a migration to add another column ‘public’ to the table we created earlier.

As you can see we are extending from BaseJavaMigration class to override the migrate() method.

package db.migration;
import org.flywaydb.core.api.migration.BaseJavaMigration;
import org.flywaydb.core.api.migration.Context;

import java.sql.Statement;

public class V3__AddColumn extends BaseJavaMigration {
  public void migrate (Context context) throws Exception {
    try (Statement alter = context.getConnection().createStatement()) {
      alter.execute("ALTER TABLE space_explorators ADD COLUMN public_company BOOLEAN DEFAULT 'f';");
    }

    try (Statement update = context.getConnection().createStatement()) {
      update.execute("UPDATE space_explorators SET public_company=TRUE where company_name='Virgin Galactica';");
    }
  }
}

Compile the Java class

$ mvn compile

Check the state of the database and run the migration. Note type of the third migration.

$ mvn flyway:info $ mvn flyway:migrate

Caveat

By default, Flyway runs each migration inside a transaction. In case of failures, it rolls back the transaction altogether. See the Flyway documentation page to better understand how Flyway handles migrations.

YugabyteDB does not yet support DDLs inside a user-initiated transaction. It executes each DDL inside its own implicit transaction even if it is part of another user-defined transaction.

So users may need to manually revert the DDL changes in the migration in case of a failure.

With the newly added support for YugabyteDB in Flyway 7.11.2, Flyway detects that, in a failed migration, DDL changes may not be rolled back automatically and suggests manual cleanup to the user.

Additionally, this newly added support would be helpful in highly concurrent environments.

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 Flyway. See Flyway documentation for more details. And get started with YugabyteDB by downloading and installing the software, or creating a free Yugabyte Cloud account in a few minutes.