Introduction
Managing database schema versions can be a challenging task, especially when the application grows in complexity. One way to handle this complexity is by using a database migration tool, and Flyway is one such tool that can help manage database schema versions effectively. In this blog post, we will explore the various CLI commands offered by Flyway and their significance.
Info:
This command provides detailed information about the current status of the Flyway schema history table. It includes details about the latest applied migration, the checksum value for each migration, and the current schema version. The Info command is useful when you want to get a quick overview of the current state of the database schema.
docker run --platform linux/amd64 --rm redgate/flyway:9.16.1 -licenseKey= -url=jdbc:postgresql://localhost:5433/yugabyte -schemas=yugabyte -user=yugabyte -password= -connectRetries=60 info
Migrate:
The Migrate command is used to apply the database schema changes. When you execute the Migrate command, Flyway automatically detects the new migration files and applies them in the correct order. Flyway ensures that each migration is applied only once, and the database schema version is updated accordingly.
$ docker run --platform linux/amd64 -v $(pwd)/sql:/flyway/sql --rm redgate/flyway:9.16.1 -licenseKey= -url=jdbc:postgresql://localhost:5433/yugabyte -schemas=yugabyte -user=yugabyte -password= -connectRetries=60 migrate
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
WARNING: You are using a limited Flyway trial license, valid until Wed Apr 19 00:00:00 UTC 2023. In 19 days you must either upgrade to a full Flyway Teams Edition license or downgrade to Flyway Community Edition.
Flyway Teams Edition 9.16.1 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5433/yugabyte (PostgreSQL 11.2)
Successfully validated 5 migrations (execution time 00:00.546s)
Current version of schema "yugabyte": null
Successfully applied 4 migrations to schema "yugabyte", now at version v1.0.0.4 (execution time 00:08.147s)
Clean:
The Clean command is used to drop all the database objects created by Flyway. This command is useful when you want to clean up the database completely and start fresh. However, it’s important to note that the Clean command removes all the database objects created by Flyway, so use it with caution.
$ docker run --platform linux/amd64 -v $(pwd)/sql:/flyway/sql --rm redgate/flyway:9.16.1 -licenseKey= -url=jdbc:postgresql://localhost:5433/yugabyte -schemas=yugabyte -user=yugabyte -password= -connectRetries=60 clean -cleanDisabled="false"
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
WARNING: You are using a limited Flyway trial license, valid until Wed Apr 19 00:00:00 UTC 2023. In 19 days you must either upgrade to a full Flyway Teams Edition license or downgrade to Flyway Community Edition.
Flyway Teams Edition 9.16.1 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5433/yugabyte (PostgreSQL 11.2)
Successfully dropped pre-schema database level objects (execution time 00:00.015s)
Successfully cleaned schema "yugabyte" (execution time 00:02.734s)
Successfully cleaned schema "yugabyte" (execution time 00:00.313s)
Successfully dropped post-schema database level objects (execution time 00:00.021s)
Successfully dropped schema "yugabyte" (execution time 00:00.061s)
Validate:
The Validate command is used to check the integrity of the applied migrations. Flyway compares the checksum of the migration scripts with the checksum value stored in the Flyway schema history table. If there is any discrepancy, the migration will fail. The Validate command is useful when you want to ensure that the applied migrations are consistent with the migration scripts.
docker run --platform linux/amd64 -v $(pwd)/sql:/flyway/sql --rm redgate/flyway:9.16.1 -licenseKey= -url=jdbc:postgresql://localhost:5433/yugabyte -schemas=yugabyte -user=yugabyte -password= -connectRetries=60 validate
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
WARNING: You are using a limited Flyway trial license, valid until Wed Apr 19 00:00:00 UTC 2023. In 19 days you must either upgrade to a full Flyway Teams Edition license or downgrade to Flyway Community Edition.
Flyway Teams Edition 9.16.1 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5433/yugabyte (PostgreSQL 11.2)
Successfully validated 5 migrations (execution time 00:00.665s)
Automate migration testing for Database CI with Flyway Hub. Visit https://flywaydb.org/get-started-with-hub
Baseline:
The Baseline command is used to set the baseline version for the database. The baseline version is used to determine which migrations to apply. This command is useful when you’re introducing Flyway to an existing database that already has schema objects.
docker run --platform linux/amd64 -v $(pwd)/sql:/flyway/sql --rm redgate/flyway:9.16.1 -licenseKey= -url=jdbc:postgresql://localhost:5433/yugabyte -schemas=yugabyte -user=yugabyte -password= -connectRetries=60 baseline -outputType=json
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
{
"successfullyBaselined": true,
"baselineVersion": "1",
"flywayVersion": "9.16.1",
"database": "yugabyte",
"warnings": [],
"operation": "baseline"
}
yugabyte@20:yugabyte> select * from yugabyte.flyway_schema_history;
+----------------+---------+-------------+------+--------+----------+--------------+--------------+----------------+---------+
| installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success |
|----------------+---------+-------------+------+--------+----------+--------------+--------------+----------------+---------|
+----------------+---------+-------------+------+--------+----------+--------------+--------------+----------------+---------+
SELECT 0
Time: 0.240s
yugabyte@20:yugabyte>
Dry-run:
The Dry-run command allows you to simulate the migration process without actually applying the changes to the database. This command is useful when you want to test the migration process without making any changes to the database.
docker run --platform linux/amd64 \
-v $(pwd)/sql/dml:/flyway/sql/dml \
-v $(pwd)/sql/ddl:/flyway/sql/ddl:rw \
-v $(pwd)/output:/flyway/output \
--rm redgate/flyway:9.16.1 \
-licenseKey= \
-url=jdbc:postgresql://localhost:5433/yugabyte \
-schemas=yugabyte \
-user=yugabyte \
-password= \
-connectRetries=60 \
-dryRunOutput=/flyway/output/dryrun.sql \
migrate
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
WARNING: You are using a limited Flyway trial license, valid until Wed Apr 19 00:00:00 UTC 2023. In 11 days you must either upgrade to a full Flyway Teams Edition license or downgrade to Flyway Community Edition.
Flyway Teams Edition 9.16.1 by Redgate
See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://localhost:5433/yugabyte (PostgreSQL 11.2)
=================================================================================================================================
Dry Run: Database will NOT be modified. The following messages only represent what would happen in a normal Flyway migration run.
=================================================================================================================================
Successfully validated 6 migrations (execution time 00:00.334s)
Current version of schema "yugabyte": 1.0.0.6
Schema "yugabyte" is up to date. No migration necessary.
Above command will store the dry-run results in /flyway/output/dryrun.sql file.
Summary
In conclusion, managing database schema versions is a complex task that can be made much easier with the use of migration tools such as Flyway. By seamlessly integrating Flyway with the distributed SQL database, Yugabyte, you can manage schema changes more efficiently and with greater accuracy. This comprehensive guide has explored the various CLI commands offered by Flyway and their significance, providing you with a solid foundation for migrating database schema changes effectively. With Yugabyte and Flyway, you can ensure that your database schema is always up-to-date and in sync with the application code, making it easier to scale and evolve your application over time.
References:
https://flywaydb.org/documentation/usage/commandline/info
https://flywaydb.org/documentation/usage/commandline/migrate
https://flywaydb.org/documentation/usage/commandline/clean
https://flywaydb.org/documentation/usage/commandline/validate
https://flywaydb.org/documentation/usage/commandline/baseline
https://flywaydb.org/documentation/configuration/parameters/dryRunOutput