In this video, you’ll learn what Flyway is, see the step-by-step process on setting it up
on your database, and create a couple of SQL scripts to demonstrate how it works.
Let’s get into it.
Flyway, or Flyway DB, is a tool that lets you handle version control of your database.
You can maintain the state of your database objects, such as tables and functions,
to make it easier for you and your team to work with the database.
For this tutorial, the only thing you’ll need access to is a database.
In this tutorial, I’ll be demonstrating Flyway with a locally-installed MySQL database,
because it’s simple and popular.
However, Flyway works on many databases, including MySQL,
PostgreSQL, Oracle, SQL Server, MariaDB, and many more.
You can also use Flyway on different database configurations
such as a local database, docker, cloud-hosted, and more.
I’ll also be demonstrating this using MacOS. The process is very similar for Windows.
Let’s get started.
The first step is to download the Flyway Command Line tool.
This will let you run the Flway tool and update your database.
To do this, visit the Flyway DB website at flywaydb.org.
Click on Download and Pricing.
On the Download page, you’ll see two options that are offered. The Community option is a free
edition that includes all of the core features. The Teams edition is aimed at larger teams.
For the purposes of this demo, we’ll use the Community edition.
Click the download button for the Community edition.
You’ll then see a range of platforms you can download it for.
Click the button for your operating system. In this tutorial, I’m on a Mac,
so I’ll select that. If you’re running Windows, select the Windows button.
Both links actually take you to the same place.
You’ll see the documentation page with a download button.
Click on the file for your operating system. You’ll then see this Download window.
Enter your email if you like, or you can just click Skip to download.
The file will then be downloaded. It’s just over
100 megabytes so will take a bit of time depending on your connection.
Once it has downloaded, extract the file into a location you like.
For this tutorial, I’ve just extracted it into the Downloads folder on my Mac,
and I haven’t had a problem with it so far, when I’ve been using it before this video.
If you have a better place for you, you can move it to another folder.
You should now have a folder on your computer, whether it’s Mac or Windows or Linux,
with the name of flyway and the version number.
The next step is to configure Flyway to connect to your database.
To do this, open the flyway folder that you extracted earlier.
Inside this folder, open the conf folder.
Inside the conf folder is a single file called flyway.conf.
This contains the configuration settings for flyway.
Open this flyway.conf file in a text editor. I’ll use Atom, but you can use whatever you like.
Here’s what the file looks like.
There are a range of settings and comments for each setting.
We need to change three things: the database connection string, the username, and the password.
With the file open, set the flyway.url property to your database.
This is the connection string or the setting that Flyway uses to connect to your database.
This string is different depending on your database and where you’re hosting it.
In the config file there are a range of examples for many different databases.
This was very helpful for me in getting the right URL property.
I copied the MySQL example and changed the host,
port, and database. I’ve got MySQL running on my computer,
so the host is [localhost](http://localhost) and the port is 3306.
This is my url property here.
The final part of the string is “fwtest”. This is the test database that I’ll use for this tutorial.
We’ll create this database shortly, as it does not exist at the moment.
Next, we change the flyway.user property. A little further down in this file is the
flyway.user property. This should be set to the user for your database.
In this example, I’m using the root account on my local MySQL database.
You may want to use a different account, and if this is going into a real system,
you probably don’t want to use the root account.
For other database vendors, you’ll have a different user.
Next, we set the flyway.password property. This is the password for the user we just specified.
There are other methods for storing these properties more securely,
such as environment variables. But for this tutorial we’ll keep it simple.
Here’s what our file looks like. The url,
user, and password properties are all uncommented and set.
Next, we should start our database on our computer if it’s not running.
For this tutorial, I’ll run MySQL database which I’ve already got installed.
Once the database is running, we need to create the database to use for this tutorial.
We’re demonstrating using Flyway on a new database, so let’s create it.
In MySQL, this is done using the Create Database command. I’ll open MySQL Workbench, which is an
IDE for working with MySQL. I’ll connect to the database server. I’ve moved quickly with these
steps as I assume you’ve already got this set up, but if not, I’ve got separate videos on this.
Once you’re connected, run this command in a new query window:
Create Database fwtest. This fwtest is the database we mentioned earlier in the video.
If you’re on a different database vendor, such as Oracle or SQL Server, you’ll use a different IDE.
The aim is to create a new database,
so use whatever IDE and command you would normally use to do this.
Now we have the fwtest database created, we can proceed with setting up our data with Flyway.
The next step is to write a script to do something on the database,
then get Flyway to run the script. Any database change made by Flyway is called a migration.
We migrate the database from one state to another state.
Migrations can be written as SQL scripts or in other files.
In this tutorial, we’ll write our first migration or first change as an SQL script.
The change we’ll make is to create a table.
First, open a text editor or IDE to create an SQL file.
You can use whatever IDE or editor you want. I’ll use Atom for this tutorial.
With a new file open, write a simple Create Table script. We’ll create a new table called product.
Our script looks like this: create table product,
then two columns, one called id and one called product name.
Ensure the data types you are using are valid.
These types work on MySQL, but if you’re on another database you may need to use other types.
Once you’ve written that, save the file. The file needs to be saved inside the
flyway folder you extracted earlier in this video, then inside the sql folder.
This is where all of the SQL scripts go that Flyway will run on your database.
Now it needs to be given a specific file name. We’ll call it “V1__Create_product_table.sql”.
This includes several parts. It starts with “V” to indicate it’s a versioned migration.
This just tells Flyway to run it in a certain way.
Then we have a number 1, which is the version number. This is the first script we’re running,
so we start with a version of 1. It’s used to specify the order that scripts are run in.
Then we have two underscores.
Then we have a description of the script,
separated by underscores, which in this example is create product table.
Finally we give it a .sql extension.
The filename is written this way so that Flyway can interpret it and run it correctly.
That’s all we need to do for now! As long as this file is saved in this format in the sql folder,
Flyway can pick it up.
Here’s what your folders should look like. We’ve got a single SQL file in our SQL folder.
Now, let’s get Flyway to do something.
Now that we have our first SQL file created, we can get Flyway to run this script.
This is called migrating the database.
We start by opening a terminal window.
If you’re on Mac, open the Terminal. If you’re on Windows, open the Command Prompt.
We’ll run our Flyway commands from the command line.
Next, we change to the flyway directory we’re using.
Use the cd command to navigate to this flyway directory.
For me, I would run “cd downloads” then “cd flyway 8.4.3”.
Now we’re in the flyway folder. We want Flyway to run the scripts in the sql folder,
which is the script we just created.
To get Flyway to run these scripts, we run “flyway migrate”. Your terminal would look like this.
When you run the command, here’s what the output should look like.
You may get some issues when you run this command for the first time, specifically on a Mac. I got
an error of “bash flyway command not found” and several “developer cannot be verified” messages.
The solution to these is to add the flyway command to the PATH variable,
and to update your Security settings on Mac. I won’t go into that now, but I’ll link to my Flyway
tutorial post which has the step-by-step solutions on how to fix these in the description below.
Once the flyway migrate command is complete, we can see what our database looks like.
If we return to the IDE, we can see the table we created in the fwtest database,
which is the product table.
We can also see a table called flyway_schema_history.
This table is used by flyway to keep track of the changes.
It’s created automatically the first time you run flyway migrate. I’ll explain how
the table works in another video, but you can query it to see what’s inside it if you like.
We’ve successfully got an SQL script created and ran it using Flyway.
What if we want to make another change?
We can add another migration, or another script, to be run.
It’s a similar process to creating the file as before.
Open your IDE or text editor. I’ll return to Atom to do this.
You could do this in MySQL Workbench if you like, as well.
We’ll write some SQL to insert data.
Here are the statements we’ll use, which inserts four rows into the product table.
Now, save the file. We’ll save it with the name of “V2__Add_products.sql”.
This includes the letter “V” to indicate it’s a versioned migration just like the last file.
It includes the number 2, which means it runs after the V1 file we created earlier.
Then we have two underscores, then a description, such as add products.
Finally we save it with a .sql extension, and save it into the SQL folder.
Our SQL folder should look like this. We don’t need to delete the V1 file,
and in fact, we need the file there.
Next, we return to the terminal. We run the “flyway migrate” command again.
We don’t need to add any parameters or anything. It’s the same command.
The output should look like this. We can see some output
that says a migration has been done to version 2, and how it was successful.
You can then check the table contents in your IDE.
Return to MySQL Workbench, and select some data from the product table.
We can do this, and see the results here. These are the
four records we inserted in that V2 script.
And we are done with our tutorial! The data is in the table.
This was populated by our flyway tool by running flyway migrate.
If you want to make more changes to your database, such as adding more data
or running more commands, simply create more SQL files and run flyway migrate again.
If you want to reset your database to a point before all scripts were run,
you can run “flyway clean”. This will delete
the flyway_schema_history table and all tables that were created from your scripts.
And that brings us to the end of this tutorial video on Flyway.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the editing of the configuration file,
or the steps to create and save an SQL file to be run, or something else?
Thanks for watching.