What are migrations and why you need them
Database or Schema Migration is the management of reversible, incremental changes to Relational databases e.g. MySQL or Postgres. Think of it as a version control for your database structure. For example, if you add a column to a table, the migration file will describe the column type and how to add it (upgrade the DB to include it) and also how to remove it (a DB downgrade).
Much like commits in git, migrations are labeled, usually with timestamps, and migration tools provide commands to manipulate the DB to modify structure according to the existing migration files. This saves you the hassle of having to remember when certain changes occurred and having to write and run SQL to modify your DB in the future.
In this tutorial, we will be using FlywayDB to demonstrate how database migrations work. FlywayDB is language agnostic and can be dropped into any stack of your choice.
Setting up our project along with FlywayDB
First, create a project folder called notes. We will extract FlywayDB into this folder.
To get FlywayDB, visit this link to get it set up on your platform. For this tutorial, I will be using FlywayDB version 5.2.4 and Postgres version 10. You can set up Postgres and psql for your platform by reading the instructions here.
Once done, our notes folder should have the following structure:
notes
├── flyway-5.2.4
│ ├── README.txt
│ ├── conf
│ ├── drivers
│ ├── jars
│ ├── jre
│ ├── lib
│ ├── licenses
│ ├── sql
│ ├── flyway
│ ├── flyway.cmd
Our database will also be called notes. We can create it on the command line like so:
createdb notes
Next thing we need to do before writing our migrations is to tell FlywayDB where to find our database. We can do that by editing the flyway.conf file inside the conf folder in our flywaydb-5.2.4 folder.
Note that this conf file contains settings which you can configure to make FlywayDB behave however you want for your project.
Find the line which says flyway.url and add your DB URL:
flyway.url=jdbc:postgresql:///notes
Also look for flyway.user and flyway.password. Remove the # at the beginning of the lines to make them active and fill in the relevant details for your setup. I am going to leave my password field empty since I don't have a password for this tutorial.
flyway.user=postgres
flyway.passsword=
Once you have successfully edited and saved the conf file, you will be ready to create your first migration.
Defining our migrations
Migrations can be defined in the sql folder inside the FlywayDB folder.
Make your way over there and create a file called V1__initial_migration.sql drop in the following SQL code:
CREATE TABLE notes (
id INT NOT NULL PRIMARY KEY,
text VARCHAR NOT NULL
);
INSERT INTO notes (id, text) VALUES (1, 'Test Note');
Note that we are versioning this migration as 1 which is why we added the prefix V1 at the beginning.
Creating the initial migration
cd into the FlywayDB folder and run flyway migrate. This command will apply our migration to the database. If you are prompted for a password, enter your database user password.
If all goes well, your database will have two tables, our notes table with one note and a second called flyway_schema_history which FlywayDB uses to track migration history.
You can explore both these tables and see what data is in them before moving to the next step.
Updating a database table structure
You can add more migrations to update your database. Let's add one now in a file named V2__add_author_field which will contain the following code:
ALTER TABLE notes ADD author VARCHAR;
Run flyway migrate and check your notes table. Apart from id and text, you should see a new column called author.
Rolling back changes
To go back to the previous migration, you can rollback the changes made in the latest migration using the following command.
flyway undo <target>
If a target is applied, all changes will be rolled back to that point in history.
Note that you can't roll back changes in the free edition.
Miscellaneous commands
Some more commands you can use include:
flyway info
This command shows you more information about the migrations you have run on your database in a table with the following columns:
+----------+---------+-------------+------+--------------+-------+
| Category | Version | Description | Type | Installed On | State |
+----------+---------+-------------+------+--------------+-------+
flyway clean
This will basically drop everything in your database and leave it in a blank slate.
Conclusion
In this tutorial, we covered how to use FlywayDB to create and manage migrations. Although we used Postgres, remember that the techniques here can be used with any relational database like Oracle, MariaDB, SQLite, MySQL e.t.c. Head over to the FlywayDB Documentation to dive into more complex concepts.
Please share this tutorial with people who might find it helpful and leave your appreciations and comments if you liked it.