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.
