Let's try to understand the DB migration/versioning tools which support databases like Clickouse, PostgeSQL, MongoDB and Cassandra. The focus here is on exploring the Flyway and Liquibase and try to migrate in to PostgreSQL DB using both the tools.

Overview

Flyway Overview: Flyway is a lightweight and developer-friendly database migration tool. It follows a simple principle of versioning your database changes through SQL scripts. Flyway tracks the current version of your database and applies migrations in a deterministic order to bring it up to the desired state. Key features of Flyway include:

• Simple migration management using SQL scripts.

• Seamless integration with popular build tools and CI/CD pipelines.

• Support for version control systems like Git.

• Extensive support for various databases including PostgreSQL, ClickHouse, MongoDB, and Cassandra.

• Repeatable migrations for non-SQL changes such as Java-based migrations.

Liquibase Overview: Liquibase is a powerful and flexible database schema change management tool. Unlike Flyway, which primarily relies on SQL scripts, Liquibase allows you to define database changes using XML, YAML, or JSON formats. This declarative approach offers greater flexibility and abstraction, especially for complex database changes. Key features of Liquibase include:

• Declarative change management using XML, YAML, or JSON formats.

• Support for a wide range of databases including PostgreSQL, ClickHouse, MongoDB, and Cassandra.

• Rollback support for reverting changes in case of errors.

• Integration with version control systems and CI/CD pipelines.

• Ability to generate database change documentation automatically.

Let us look at each in detail.

Flyway

Flyway is an open-source database migration tool. Migrations can be written in SQL using database specific language/syntax.

Official documentation - Quickstart - How Flyway Works - Flyway - Product Documentation

DB migration

It is available with the below listed options -

1. Command Line client

2. Flyway Autopilot

3. Flyway Desktop with community edition

4. Docker

5. API - Java

6. Maven

7. Gradle

Plugins

Spring Boot, Dropwizard, Grails, Play, SBT, Ant, Griffon, Grunt, Ninja etc.

Commands

Flyway is based on 7 basic commands -

1 : Migrate - migrates the schema to the latest version. Flyway will create the schema history table(flyway_schema_history) automatically if it doesn’t exist.

2 : Clean - Drops all the objects in the configured schema.

3 : Info - Prints the details and status info about all the migrations.

4 : Validate - This mechanism checks if there is already any migration exists with similar checksum.

5 : Undo - Undoes the most recent applied versioned migrations.

6 : Baseline - Baselines an existing database, excluding all migrations up to and including baselineVersion.

7 : Repair - Repairs schema history table(flyway-schema_history). Removes failed migration entries. Marks all missing migrations as deleted.

Migration

With Flyway all changes to the database are called migrations. Migration types and file name conventions followed are

1 : Versioned migration - Versioned migrations have a version, a description and a checksum. The version must be unique. Ex : V1_Create_Person_Table.sql, V2_Add_Records.sql

2 : Baseline migration -In existing deployments they have no effect as your database is already where it needs to be. In new environments, the baseline migration with the latest version is applied first in order to bring your database up to speed before applying later migrations. Any migrations with a version older than the latest baseline migration's version are not applied and are treated as being ignored.

Ex : B3_create_table.sql

3 : Repeatable migration - Instead of being run just once, they are (re-)applied every time their checksum changes. Ex : R_People_view.sql

4 : Undo migrations -An undo migration is responsible for undoing the effects of the versioned migration with the same version. Ex : U2_Add_people.sql

Configuration Files

Flyway supports loading configuration via config files. By default Flyway will load configuration files from the following locations:

• installDir/conf/flyway.conf

• userhome/flyway.conf

• workingDir/flyway.conf

Sample conf file available - https://documentation.red-gate.com/fd/configuration-files-184127472.html

Environment Variables

Flyway supports reading the configuration using environment variables. Credentials can be set in FLYWAY_USER and FLYWAY_PASSWORD

Secrets Management

Flyway comes with support for the following secrets management solutions :

• AWS Secrets Manager

• Dapr Secret Store

• Google Cloud Secret Manager

• Harshicorp Vault

Supported databases

Supported Databases - Flyway - Product Documentation

Lets try to create sample Java project and use Flyway APIs to migrate data in to PostgreSQL DB

1. Create a Maven project and add below Maven dependencies to use Flyway and PostgresDB.

<dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-core</artifactId>
      <version>6.0.8</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.6.0</version>
</dependency>

2. Create db.migration folder inside /src/main/resources folder and add below SQL scripts.

V2_Create_person_table.sql

create table STUDENT ( ID int not null, NAME varchar(100) not null );

V3_Add_data.sql

insert into STUDENT (ID, NAME) values (1, 'Parth');

**Note : The Version migration numbers should be properly ordered. Here the version file numbers started from V2 because we have to create this table in to existing database and for that the baseline of the existing Db is required. This baseline will be considered as first migration. If we set the V2 as V1 then according to the convention the migration having version less than or equals to the baseline migration will be ignored.

3. Create a main method like this in Java class.

public static void main(String[] args) {
        Flyway flyway = Flyway.configure().dataSource("jdbc:postgresql://localhost:5432/your_db", "username", "").load();
        flyway.baseline();
        flyway.migrate();
    }

Run the program, and the output will be like this -

Sep 12, 2023 12:41:09 PM org.flywaydb.core.internal.license.VersionPrinter printVersionOnly
INFO: Flyway Community Edition 6.0.8 by Redgate
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.database.DatabaseFactory createDatabase
INFO: Database: jdbc:postgresql://localhost:5432/flywaydemodb(PostgreSQL 15.4)
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory create
INFO: Creating Schema History table "public"."flyway_schema_history" with baseline ...
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.command.DbBaseline baseline
INFO: Successfully baselined schema with version: 1
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 3 migrations (execution time 00:00.029s)
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "public": 1
Sep 12, 2023 12:41:10 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "public" to version 2 - Create student table
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.command.DbMigrate doMigrateGroup
INFO: Migrating schema "public" to version 3 - Add student
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Successfully applied 2 migrations to schema "public" (execution time 00:00.066s)
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory removeFailedMigrations
INFO: Repair of failed migration in Schema History table "public"."flyway_schema_history" not necessary. No failed migration detected.
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.command.DbRepair repair
INFO: Successfully repaired schema history table "public"."flyway_schema_history" (execution time 00:00.035s).
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:41:11 PM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 3 migrations (execution time 00:00.011s)

If we try to run again the output will be -

Sep 12, 2023 12:58:16 PM org.flywaydb.core.internal.license.VersionPrinter printVersionOnly
INFO: Flyway Community Edition 6.0.8 by Redgate
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.DatabaseFactory createDatabase
INFO: Database: jdbc:postgresql://localhost:5432/flywaydemodb(PostgreSQL 15.4)
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbBaseline baseline
INFO: Schema history table "public"."flyway_schema_history" already initialized with (1,<< Flyway Baseline >>). Skipping.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbBaseline baseline
INFO: Successfully baselined schema with version: 1
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 3 migrations (execution time 00:00.043s)
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbMigrate migrateGroup
INFO: Current version of schema "public": 3
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbMigrate logSummary
INFO: Schema "public" is up to date. No migration necessary.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory removeFailedMigrations
INFO: Repair of failed migration in Schema History table "public"."flyway_schema_history" not necessary. No failed migration detected.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbRepair repair
INFO: Successfully repaired schema history table "public"."flyway_schema_history" (execution time 00:00.031s).
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.database.base.Database recommendFlywayUpgrade
WARNING: Flyway upgrade recommended: PostgreSQL 15.4 is newer than this version of Flyway and support has not been tested. The latest supported version of PostgreSQL is 12.
Sep 12, 2023 12:58:18 PM org.flywaydb.core.internal.command.DbValidate validate
INFO: Successfully validated 3 migrations (execution time 00:00.021s)

Using Flyway Java APIs we have successfully created a Table in to existing DB and inserted a few records in to the table.

Liquibase

Liquibase is a database schema change management solution that enables you to revise and release database changes faster and safer from development to production.

Official documentation - Introduction to Liquibase

DB migration :

1. Command Line interface

2. Liquibase Java API

3. Integrating the Liquibase processes using Maven, Spring Boot, Ant, Jenkins, GitHub Actions or other CI/CD tools.

4. Docker

Plugins

Maven

Commands :

Liquibase runs 6 basic commands

1. update - Updates database to current version.

2. rollback - Rolls back the database to the state it was in when the tag was applied.

3. snapshot - Gathers the current database schema and displays that information to STDOUT. With options, can save the schema in JSON format, and that JSON snapshot can serve as a comparison database.

4. diff - Writes description of differences between two databases to standard out.

5. status - Outputs the count (or list, if --verbose) of changesets that have not been deployed.

6. utility commands

About Liquibase Commands

Liquibase creates 2 tables in DB if not already exists,

• DATABASECHANGELOGLOCK table - Prevents multiple instances of Liquibase from updating the DB at same time.

• DATABASECHANGELOG table - Keeps track of the changes deployed to the DB.

Configuration Files :

liquibase.properties file can be use to store information that Liquibase needs to connect to a particular database, along with other properties that rarely change.

Create and Configure a liquibase.properties File

Environment Variables :

Liquibase also provides environment variables that pass sensitive data securely during automated processes. LIQUIBASE_COMMAND_USERNAME , LIQUIBASE_COMMAND_PASSWORD ,LIQUIBASE_COMMAND_CHANGELOG_FILE

LIQUIBASE_COMMAND_URL

Secrets Management :

Harshicorp Vault

Supported databases :

Supported Databases | Liquibase.com

Lets try to create sample Java project and use Flyway APIs to migrate data in to PostgreSQL DB

1. Create a Maven project and add below Maven dependencies to use Liquibase and PostgresDB.

    <dependency>
          <groupId>org.liquibase</groupId>
          <artifactId>liquibase-core</artifactId>
          <version>4.23.1</version>
    </dependency>
    <dependency>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
          <version>42.6.0</version>
    </dependency>
   

2. Create db.migration folder inside /src/main/resources folder and add below SQL script.

changelog.sql

-- liquibase formatted sql

-- changeset liquibase:1
CREATE TABLE employee (emp_id INT, name VARCHAR, PRIMARY KEY (emp_id));

-- changeset liquibase:2
insert into employee (emp_id, name) values (1, 'Parth');

**Note : changelog is a text based file to sequentially list all changes made to DB. An individual unit of change in changelog is called changeset. When we want to modify DB, simply add a new changeset and specify its operation as a changetype. When update command is called Liquibase deploys the changes from changelog to DB.

3. Create a method like this in Java class.

    public static void liquibaseUpdate() {
            try {
                Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/your_db", "username", "");
                Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(connection));
                Liquibase liquibase = new liquibase.Liquibase("db/migration/changelog.sql", new ClassLoaderResourceAccessor(), database);
                liquibase.update();
            } catch (LiquibaseException e){
                System.out.println("Error :: "+e.getDetails());
            } catch (SQLException e) {
                System.out.println("Error :: "+e.getStackTrace());
            }
        }
   

   4. Run the program. Output will be like this -

Sep 12, 2023 12:45:37 PM liquibase.database
INFO: Set default schema name to public
Sep 12, 2023 12:45:37 PM liquibase.changelog
INFO: Creating database history table with name: public.databasechangelog
Sep 12, 2023 12:45:37 PM liquibase.changelog
INFO: Reading from public.databasechangelog
Sep 12, 2023 12:45:38 PM liquibase.lockservice
INFO: Successfully acquired change log lock
Sep 12, 2023 12:45:38 PM liquibase.command
INFO: Using deploymentId: 4502938113
Sep 12, 2023 12:45:38 PM liquibase.changelog
INFO: Reading from public.databasechangelog
Running Changeset: db/migration/changelog.sql::1::liquibase
Sep 12, 2023 12:45:38 PM liquibase.changelog
INFO: Custom SQL executed
Sep 12, 2023 12:45:38 PM liquibase.changelog
INFO: ChangeSet db/migration/changelog.sql::1::liquibase ran successfully in 68ms
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: UPDATE SUMMARY
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: Run: 1
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: Previously run: 0
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: Filtered out: 0
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: -------------------------------
Sep 12, 2023 12:45:38 PM liquibase.util
INFO: Total change sets: 1

Sep 12, 2023 12:45:38 PM liquibase.util
INFO: Update summary generated
Sep 12, 2023 12:45:38 PM liquibase.command
INFO: Update command completed successfully.
Sep 12, 2023 12:45:38 PM liquibase.lockservice
INFO: Successfully released change log lock
Sep 12, 2023 12:45:38 PM liquibase.command
INFO: Command execution complete

UPDATE SUMMARY

Run: 1
Previously run: 0
Filtered out: 0

Total change sets: 1

Liquibase: Update has been successful. Rows affected: 1

Process finished with exit code 0

The employee, databasechangelog and databasechangeloglock tables looks like this -

4. 

Flyway Vs Liquibase

Alternatives

1. Python library - clickhouse-migrations

2. Golang library - golang-migrate

3. Bytebase - is an open-source database DevOps tool, it's the GitLab for managing databases throughout the application development lifecycle. It offers a web-based workspace for DBAs and Developers to collaborate and manage the database change safely and efficiently.
   Why Bytebase