We’ve always had features to version our code, but it’s been harder to version the objects in our databases. With the increasing use of DevOps and continuous delivery, the pain of managing database schema versions is growing. Fortunately, the open source, cross-database tool Flyway (https://flywaydb.org/) can help you. Flyway is compatible with Oracle, SQL Server, DB2, MySQL, PostgreSQL and many others.
What is Flyway?
Flyway is an open source database versioning tool. Flyways talks of “migrations,” but it does not help whole database migration as in the “from 11c to 12g” sense, nor in the “Oracle to SQL Server” sense. Instead, Flyway migrates application schemas from one version to the next.
To keep track of these migrations, Flyway creates a metadata table in your schema that it uses to keep track of which scripts have been run in that schema.
There are many ways of using Flyway:
- As a command-line tool
- Integrated into a Java application
- In your build tool (Maven, Gradle, Ant, sbt)
- Through plugins (in Chef, Grunt, etc.)
How do you use it?
To use Flyway, you name your database build scripts in accordance with Flyway’s conventions and then always use Flyway to build the database. The benefit of Flyway goes out the window if you start running scripts outside Flyway, because this will cause Flyway to get out of sync with the database state.
Flyways calls every script a migration. Most of your migrations will be written in SQL, but Flyway also supports Java-based migrations to handle BLOB and CLOB changes. An SQL migration is simply a script, while a Java migration is a class that implements the org.flywaydb.core.api.migration.jdbc.JdbcMigration interface.
You tell Flyway where you keep your scripts (in the file system or on the classpath), and Flyway then automatically scans those locations for migrations.
Flyway Example
If I have an empty schema and a single script called V1.1__create_events.sql in the Flyway location in the file system, running Flyway on the command line can looks like this:
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+---------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+---------------+---------------------+---------+
| 1.1 | create events | | Pending |
+---------+---------------+---------------------+---------+
Tahoe:flyway-4.1.1 sten$ flyway migrate
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
Successfully validated 1 migration (execution time 00:00.004s)
Creating Metadata table: "DEV2"."schema_version"
Current version of schema "DEV2": << Empty Schema >>
Migrating schema "DEV2" to version 1.1 - create events
Successfully applied 1 migration to schema "DEV2" (execution time 00:00.100s).
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+---------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+---------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 16:01:50 | Success |
+---------+---------------+---------------------+---------+
Tahoe:flyway-4.1.1 sten$
In this example, I have configured my schema and database connection in the Flyway configuration file – I can also provide username, password and connect info on the command line.
The first info command shows that Flyway has found my script, identified it as version 1.1 based on the file name, and determined that it has not been run yet. When I execute the migrate command, Flyway automatically creates its metadata table and then runs my script. The second info command shows that my script was successfully run.
Flyway Setup
It is easiest to get started with Flyway using the command-line tool. The command-line tool simply needs to be unzipped and run.
In the installation directory, you find a /conf directory containing a flyway.conf file. In this file, you should set the flyway.url parameter to connect to the desired database and flyway.user to identify the schema you want to work with. To avoid having to enter your password every time, you can also set flyway.password.
The file contains examples of connect URLs for many databases. For example, to connect to my Oracle database, I use
flyway.url=jdbc:oracle:thin:@//192.168.56.101:1521/orcl
Flyway comes with a number of database drivers, but the Oracle JDBC driver is not included. You need to download the ojdbc7.jar file from OTN (http://www.oracle.com/technetwork/database/features/jdbc/default-2280470.html) and place it in the /drivers subdirectory under your Flyway installation.
You should also set the flyway.locations parameter to the directory or directories where you want Flyway to look for migrations.
Writing Migrations
Flyway works with two types of migrations:
- Versioned
- Repeatable
Versioned SQL Migrations
A versioned SQL migration is a SQL script named in accordance with the Flyway standard:
(illustration from Flyway documentation)
You can change the file naming conventions in the flyway.conf file.
Flyway will apply versioned migrations in sequential order, and will complain if you have used the same version number more than once.
Repeatable SQL Migrations
A repeatable SQL migration is an SQL script named with R instead of V, and without a number:
(illustration from Flyway documentation)
Repeatable migrations are typically everything that starts with CREATE OR REPLACE (views, procedures, functions, packages). Flyway will apply the repeatable migrations after all the versioned ones.
Flyway is rather clever about the repeatable migrations, because it calculates a hash of the file when running it. In this way, Flyway can tell if a file has been changed. For example, my database might look like this after a successful migrate command:
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+-----------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+-----------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 17:08:57 | Success |
| 1.2 | event seq | 2017-02-26 17:08:57 | Success |
| 2 | fix nullable | 2017-02-26 17:08:57 | Success |
| 2.1 | events data | 2017-02-26 17:08:57 | Success |
| | attendance pkg | 2017-02-26 17:08:57 | Success |
| | events bir trg | 2017-02-26 17:08:57 | Success |
| 3 | add max people | 2017-02-26 17:22:37 | Success |
| 3.1 | max people data | 2017-02-26 17:22:37 | Success |
+---------+-----------------+---------------------+---------+
Now if I change my R__attendance_pkg.sql script and run flyway info again, it will look like this:
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+-----------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+-----------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 17:08:57 | Success |
| 1.2 | event seq | 2017-02-26 17:08:57 | Success |
| 2 | fix nullable | 2017-02-26 17:08:57 | Success |
| 2.1 | events data | 2017-02-26 17:08:57 | Success |
| | attendance pkg | 2017-02-26 17:08:57 | Outdate |
| | events bir trg | 2017-02-26 17:08:57 | Success |
| 3 | add max people | 2017-02-26 17:22:37 | Success |
| 3.1 | max people data | 2017-02-26 17:22:37 | Success |
| | attendance pkg | | Pending |
+---------+-----------------+---------------------+---------+
You can see that Flyway has recognized that my attendance pkg script is outdated, and the new version is pending. Running flyway migrate will execute only the updated R__attendance_pkg.sql script:
Tahoe:flyway-4.1.1 sten$ flyway migrate
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
Successfully validated 9 migrations (execution time 00:00.011s)
Current version of schema "DEV2": 3.1
Migrating schema "DEV2" with repeatable migration attendance pkg
Successfully applied 1 migration to schema "DEV2" (execution time 00:00.028s).
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+-----------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+-----------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 17:08:57 | Success |
| 1.2 | event seq | 2017-02-26 17:08:57 | Success |
| 2 | fix nullable | 2017-02-26 17:08:57 | Success |
| 2.1 | events data | 2017-02-26 17:08:57 | Success |
| | attendance pkg | 2017-02-26 17:08:57 | Superse |
| | events bir trg | 2017-02-26 17:08:57 | Success |
| 3 | add max people | 2017-02-26 17:22:37 | Success |
| 3.1 | max people data | 2017-02-26 17:22:37 | Success |
| | attendance pkg | 2017-02-26 17:23:52 | Success |
+---------+-----------------+---------------------+---------+
Flyway keeps the record that my attendance pkg migration was run earlier, but it now has the status superseded (i.e. replaced by a later run), and the last execution has status success.
Clearing the Database
You can issue the flyway clean command to delete everything from your database, for example in order to start over.
Handling Failure
Some database support DDL transactions, i.e., can roll back DDL statements like create table. For these databases, it is not a problem if a Flyway migration fails. For other databases (like Oracle), a failed script will leave your Flyway database in state Failed.
Tahoe:flyway-4.1.1 sten$ flyway migrate
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
Successfully validated 8 migrations (execution time 00:00.011s)
Current version of schema "DEV2": 2.1
Migrating schema "DEV2" to version 3 - add max people
ERROR: Migration of schema "DEV2" to version 3 - add max people failed! Please restore backups and roll back database and code!
ERROR:
Migration V3__add_max_people.sql failed
---------------------------------------
SQL State : 42000
Error Code : 902
Message : ORA-00902: invalid datatype
Location : /Users/sten/event_app/sql/V3__add_max_people.sql (/Users/sten/event_app/sql/V3__add_max_people.sql)
Line : 1
Statement : ALTER TABLE "EVENTS" ADD ("MAX_ATTEND" NUMBR)
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+-----------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+-----------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 17:08:57 | Success |
| 1.2 | event seq | 2017-02-26 17:08:57 | Success |
| 2 | fix nullable | 2017-02-26 17:08:57 | Success |
| 2.1 | events data | 2017-02-26 17:08:57 | Success |
| | attendance pkg | 2017-02-26 17:08:57 | Success |
| | events bir trg | 2017-02-26 17:08:57 | Success |
| 3 | add max people | 2017-02-26 17:09:22 | Failed |
| 3.1 | max people data | | Pending |
+---------+-----------------+---------------------+---------+
When Flyway is in state Failed, it will refuse to run more migrations:
Tahoe:flyway-4.1.1 sten$ flyway migrate
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
ERROR: Detected failed migration to version 3 (add max people)
When you have fixed the error in your file, you need to run flyway repair to clean up the error state. Flyway will warn you about the potential need to fix things manually, but this will rarely be necessary.
Tahoe:flyway-4.1.1 sten$ flyway repair
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
Successfully repaired metadata table "DEV2"."schema_version" (execution time 00:00.019s).
Manual cleanup of the remaining effects the failed migration may still be required.
Tahoe:flyway-4.1.1 sten$ flyway info
Flyway 4.1.1 by Boxfuse
Database: jdbc:oracle:thin:@//192.168.56.101:1521/orcl (Oracle 12.1)
+---------+-----------------+---------------------+---------+
| Version | Description | Installed on | State |
+---------+-----------------+---------------------+---------+
| 1.1 | create events | 2017-02-26 17:08:57 | Success |
| 1.2 | event seq | 2017-02-26 17:08:57 | Success |
| 2 | fix nullable | 2017-02-26 17:08:57 | Success |
| 2.1 | events data | 2017-02-26 17:08:57 | Success |
| | attendance pkg | 2017-02-26 17:08:57 | Success |
| | events bir trg | 2017-02-26 17:08:57 | Success |
| 3 | add max people | | Pending |
| 3.1 | max people data | | Pending |
+---------+-----------------+---------------------+---------+
Once you have repaired, you can run flyway migrate again.
Advanced Flyway
If you want to change how Flyway works because of your special situation, you can hook into the Flyway lifecycle with callbacks as described in the documentation (https://flywaydb.org/documentation/callbacks).
You can write your own SQL scripts to be run in the following situations:
- Before Migrate runs
- Before every single migration during Migrate
- After every single migration during Migrate
- After Migrate runs
- Before Clean runs
- After Clean runs
- Before Info runs
- After Info runs
- Before Validate runs
- After Validate runs
- Before Baseline runs
- After Baseline runs
- Before Repair runs
- After Repair runs
Conclusion
Using Flyway to run all your database creation scripts makes it much easier to manage versions of schema objects and you’ll know exactly which database version is running on each environment. If you haven’t already written something like this yourself, check out Flyway.
Start the discussion at forums.toadworld.com