DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • Authorization: Get It Done Right, Get It Done Early
  • Writing a Vector Database in a Week in Rust
  • Replacing Apache Hive, Elasticsearch, and PostgreSQL With Apache Doris
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing

Trending

  • Authorization: Get It Done Right, Get It Done Early
  • Writing a Vector Database in a Week in Rust
  • Replacing Apache Hive, Elasticsearch, and PostgreSQL With Apache Doris
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing
  1. DZone
  2. Data Engineering
  3. Databases
  4. Version Control for Distributed SQL With Flyway’s Maven Plugin

Version Control for Distributed SQL With Flyway’s Maven Plugin

In this article, take a look at version control for distributed SQL with Flyway's Maven plugin.

Jimmy Guerrero user avatar by
Jimmy Guerrero
·
Oct. 13, 20 · Tutorial
Like (2)
Save
Tweet
Share
5.37K Views

Join the DZone community and get the full member experience.

Join For Free

Flyway is an open source database version control and migration tool that stresses simplicity and convention over configuration. Changes to the database can be written in SQL (and in some database-specific dialects like PL/SQL and T-SQL) or Java. You interact with Flyway using a command-line client, however there are a variety of plugins that can be leveraged, including Maven, Gradle, Spring Boot, and more.

Supported databases include Oracle, SQL Server, DB2, MySQL, PostgreSQL, and others. Because YugabyteDB is PostgreSQL compatible, most third-party tools and apps will work “out of the box.” Flyway is no exception here. This allows developers to deploy and rollback schema changes to YugabyteDB using Flyway by making use of the PostgreSQL JDBC driver.

Flyway relies on seven commands to manage database version control.

  • Migrate: Migrates the schema to the latest version. Flyway will create the schema history table automatically if it doesn’t exist.
  • Clean: Drops all objects in the configured schemas.
  • Info: Prints the details and status information about all the migrations.
  • Validate: Validates the applied migrations against the available ones.
  • Undo: Undoes the most recently applied versioned migration.
  • Baseline: Baselines an existing database, excluding all migrations up to and including baselineVersion.
  • Repair: Repairs the schema history table.
    In this blog post we’ll walk you though the following steps:
  • Install a 3 node YugabyteDB cluster on Google Kubernetes Platform
  • Install and configure Flyway locally to work with YugabyteDB
  • How to run migrations using the Maven plugin

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 to cloud native environments. This is often motivated by the desire to reduce TCO or move away from the scaling limitations of monolithic RDBMS like Oracle, MySQL, and SQL Server. The basic characteristics of Distributed SQL are:

  • A SQL API for querying and modeling data, with support for traditional RDBMS features like primary keys, foreign keys, indexes, stored procedures, and triggers.
  • Automatic distributed query execution so that no single node becomes a bottleneck.
  • A distributed SQL database should support automatically distributed data storage. This includes indexes which should be automatically distributed (aka sharded) across multiple nodes of the cluster so that no single node becomes a bottleneck for ensuring 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?”

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 with support for advanced RDBMS features like stored procedures, triggers, and UDFs.

Ok, on with the demo…

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. 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.

SQL
xxxxxxxxxx
1
 
1
$ helm repo add yugabytedb https://charts.yugabyte.com

Now, fetch the updates.

SQL
xxxxxxxxxx
1
 
1
$ helm repo update

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

SQL
xxxxxxxxxx
1
 
1
$ kubectl create namespace yb-demo

Expected output:

SQL
xxxxxxxxxx
1
 
1
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.

SQL
xxxxxxxxxx
1
 
1
$ helm install yb-demo yugabytedb/yugabyte \
2
--set resource.master.requests.cpu=1,resource.master.requests.memory=1Gi,\
3
resource.tserver.requests.cpu=1,resource.tserver.requests.memory=1Gi,\
4
enableLoadBalancer=True --namespace yb-demo --wait

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

SQL
xxxxxxxxxx
1
 
1
$ kubectl get services --namespace yb-demo

Note the external-IP for yb-tserver-service which we are going to use to establish a connection between YugabyteDB and Flyway. From the screenshot above we can see that the IP is 35.224.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. For the purposes of this tutorial we are going to use the 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:

SQL
xxxxxxxxxx
1
 
1
$ kubectl exec -n yb-demo -it yb-tserver-0 /bin/bash

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

SQL
xxxxxxxxxx
1
 
1
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_ddl.sql
2
3
$ wget https://raw.githubusercontent.com/yugabyte/yugabyte-db/master/sample/northwind_data.sql

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

SQL
xxxxxxxxxx
1
 
1
$ exit
2
$ 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
 




xxxxxxxxxx
1


 
1
yugabyte=# CREATE DATABASE northwind;
2
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:

SQL
 




xxxxxxxxxx
1


 
1
northwind=# \i 'northwind_ddl.sql';
2
northwind=# \i 'northwind_data.sql';


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

SQL
 




xxxxxxxxxx
1
20


 
1
northwind-# \d
2

          
3
                 List of relations
4
 Schema |          Name          | Type  |  Owner
5
--------+------------------------+-------+----------
6
 public | categories             | table | yugabyte
7
 public | customer_customer_demo | table | yugabyte
8
 public | customer_demographics  | table | yugabyte
9
 public | customers              | table | yugabyte
10
 public | employee_territories   | table | yugabyte
11
 public | employees              | table | yugabyte
12
 public | order_details          | table | yugabyte
13
 public | orders                 | table | yugabyte
14
 public | products               | table | yugabyte
15
 public | region                 | table | yugabyte
16
 public | shippers               | table | yugabyte
17
 public | suppliers              | table | yugabyte
18
 public | territories            | table | yugabyte
19
 public | us_states              | table | yugabyte
20
(14 rows)


Verify we have data by issuing a simple SELECT:

SQL
 




xxxxxxxxxx
1


 
1
northwind=# SELECT count(*) FROM products;
2
 count
3
-------
4
    77
5
(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
 




xxxxxxxxxx
1


 
1
northwind=# ALTER ROLE yugabyte WITH PASSWORD 'password';


Step 3: Install and Configure Flyway

For the purposes of this demo we’ll be installing and configuring Flyway Community Edition. After downloading Flyway, navigate to the flyway-6.5.2 directory. We are going to want to make a few configuration changes to get Flyway connected to the YugabyteDB database we just deployed.

In the conf directory locate the flyway.conf file. Uncomment or modify the following lines:

SQL
 




xxxxxxxxxx
1


 
1
flyway.url=jdbc:postgresql://35.224.XX.XX:5433/northwind
2
flyway.user=yugabyte
3
flyway.password=password


At this point Flyway will be able to connect to YugabyteDB running on GKE.

Step 4: Create a Project

Create a basic project using the Maven Archetype Plugin by issuing the following command:

SQL
 




xxxxxxxxxx
1


 
1
$ mvn archetype:generate -B \
2
    -DarchetypeGroupId=org.apache.maven.archetypes \
3
    -DarchetypeArtifactId=maven-archetype-quickstart \
4
    -DarchetypeVersion=1.1 \
5
    -DgroupId=foo \
6
    -DartifactId=bar \
7
    -Dversion=1.0-SNAPSHOT \
8
    -Dpackage=foobar


You should now see a project structure similar to the one below in your IDE of choice.

Step 5: Configure the Flyway Integration

Edit the pom.xml and add entries for the Flyway plugin, YugabyteDB (PostgreSQL) connectivity, and PostgreSQL Driver.

XML
 




xxxxxxxxxx
1
19


 
1
    <plugins>
2
      <plugin>
3
        <groupId>org.flywaydb</groupId>
4
        <artifactId>flyway-maven-plugin</artifactId>
5
        <version>6.5.2</version>
6
        <configuration>
7
         <url>jdbc:postgresql://XX.XX.XX.XX:5433/northwind</url>
8
          <user>yugabyte</user>
9
          <password>password</password>
10
        </configuration>
11
        <dependencies>
12
          <dependency>
13
            <groupId>org.postgresql</groupId>
14
            <artifactId>postgresql</artifactId>
15
            <version>42.2.2</version>
16
          </dependency>
17
        </dependencies>
18
      </plugin>
19
    </plugins>



Note: There are few other configuration options when it comes to Flyway and Maven. You can find them detailed in the Flyway Documentation.

Step 6: Create Table Migration

Create the migration directory ~bar/src/main/resources/db/migration to hold our SQL scripts.

Create a SQL script in this directory with the following content named V1__create_promotions_table.sql.

SQL
 




xxxxxxxxxx
1


 
1
CREATE TABLE promotions (
2
    promotion_id serial PRIMARY KEY,
3
    supplier_id int2 NOT NULL,
4
    promotion_name VARCHAR ( 50 ) NOT NULL,
5
    company_name VARCHAR ( 40 ) NOT NULL,
6
    start_date DATE NOT NULL,
7
    stop_date DATE NOT NULL,
8
    created_on DATE NOT NULL DEFAULT CURRENT_DATE
9
);


We can now execute this migration at the command line.

SQL
 




xxxxxxxxxx
1


 
1
$ cd /bar
2
$ mvn flyway:migrate


We can verify that the "promotions" table was indeed built.

We should also be able to see this migration in the "flyway_schema_history" table.

Step 7: Insert Data Migration

Create the SQL script below, place it in the migration directory with the following and name it "V2__insert_promotions.sql".

SQL
 




xxxxxxxxxx
1


 
1
INSERT INTO promotions (supplier_id, promotion_name, company_name, start_date, stop_date, created_on)
2
VALUES
3
('28','Buy One Get One Free', 'Gai pâturage', '1996-08-15', '1996-08-30', CURRENT_DATE),
4
('19', 'Free Shipping', 'New England Seafood Cannery', '1996-10-15', '1996-10-30', CURRENT_DATE),
5
('16', 'Free T-Shirt With Every Purchase', 'Bigfoot Breweries', '1996-12-15', '1996-12-30', CURRENT_DATE),
6
('20','10% Off', 'Gula Malacca', '1996-11-15', '1996-11-30', CURRENT_DATE),
7
('8', '20% Off', 'Specialty Biscuits, Ltd.', '1996-09-15', '1996-09-30', CURRENT_DATE);


We can now execute this migration at the command line.

SQL
 




xxxxxxxxxx
1


 
1
$ mvn flyway:migrate


Let’s verify that the data was inserted into the "promotions" table.

We should also be able to see this migration in the "flyway_schema_history" table.

Conclusion

That’s it! You now have a 3 node YugabyteDB cluster on GKE, with versioning control managed by a local installation of Flyway. For more information about how to perform more advanced operations in Flyway using the Java API, Maven, or Gradle, check out their documentation.

MySQL Flyway (software) Database Version control Apache Maven YugabyteDB

Published at DZone with permission of Jimmy Guerrero. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Authorization: Get It Done Right, Get It Done Early
  • Writing a Vector Database in a Week in Rust
  • Replacing Apache Hive, Elasticsearch, and PostgreSQL With Apache Doris
  • Tomorrow’s Cloud Today: Unpacking the Future of Cloud Computing

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: