Poll

First Steps: Command-line

This brief tutorial will teach how to get up and running with the Flyway Command-line tool. It will take you through the steps on how to configure it and how to write and execute your first few database migrations, using Spawn to provision a database instance without the need for installing any database engines onto your own machine. You can create MySQL, MSSQL and PostgreSQL databases with Spawn that will work with Flyway.

This tutorial should take you about 10 minutes to complete.

Prerequisites

Start by downloading the Flyway Command-line Tool for your platform and extract it.

Install Spawn by visiting the getting started documentation and following the installation steps.

Configuring Flyway

To configure Flyway, we first need a database we can connect to. We’ll use Spawn to create your own, isolated database environment from which you can run migrations against. This will create you your first data container, which is your database instance. Here we are specifying a PostgreSQL database but you can also specify mysql:flyway-getting-started or mssql:flyway-getting-started:

> spawnctl create data-container \
  --image postgres:flyway-getting-started \
  --name flyway-container \
  --lifetime 24h

This will return connection string details which is used to connect and query using your normal tools:

Data container 'flyway-container' created!
-> Host=instances.spawn.cc;Port=xxxxx;Username=xxxx;Database=foobardb;Password=xxxxxxxxx

You can retrieve these details at any time by running:

> spawnctl get data-container flyway-container -o yaml

Let’s now jump into our new directory created from downloading Flyway:

> cd flyway-7.11.3

Configure Flyway by editing /conf/flyway.conf with your Spawn data container connection details, like this:

flyway.url=jdbc:postgresql://instances.spawn.cc:<Port>/foobardb
flyway.user=<User>
flyway.password=<Password>

Creating the first migration

Now create your first migration in the /sql directory called V1__Create_person_table.sql:

create table PERSON (
    ID int not null,
    NAME varchar(100) not null
);

Migrating the database

It’s now time to execute Flyway to migrate your database:

flyway-7.11.3> flyway migrate

If all went well, you should see the following output:

Database: jdbc:postgresql://instances.spawn.cc:<Port>/foobardb (PostgreSQL 11.0)
Successfully validated 1 migration (execution time 00:00.008s)
Creating Schema History table: "PUBLIC"."flyway_schema_history"
Current version of schema "PUBLIC": << Empty Schema >>
Migrating schema "PUBLIC" to version 1 - Create person table
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.033s)

Adding a second migration

If you now add a second migration to the /sql directory called V2__Add_people.sql:

insert into PERSON (ID, NAME) values (1, 'Axel');
insert into PERSON (ID, NAME) values (2, 'Mr. Foo');
insert into PERSON (ID, NAME) values (3, 'Ms. Bar');

and execute it by issuing:

flyway-7.11.3> flyway migrate

You now get:

Database: jdbc:postgresql://instances.spawn.cc:<Port>/foobardb (PostgreSQL 11.0)
Successfully validated 2 migrations (execution time 00:00.018s)
Current version of schema "PUBLIC": 1
Migrating schema "PUBLIC" to version 2 - Add people
Successfully applied 1 migration to schema "PUBLIC" (execution time 00:00.016s)

Any time you want to reset your database back to its original state, in this case an empty instance, you can run a reset command on your Spawn data container:

> spawnctl reset data-container flyway-container

You can then re-run migrate on your database.

Continue to use Spawn

At the start of this tutorial you used Spawn to provision a database to learn how Flyway works.

Spawn is a cloud-hosted database provisioning service provided by Redgate. You can continue to use it for development or CI workflows. Read more about the features and use cases on the Spawn Website.

Summary

In this brief tutorial we saw how to:

install the Flyway Command-line tool and Spawn
configure it so it can talk to our Spawn database
write our first couple of migrations

These migrations were then successfully found and executed.

Read the documentation

Flyway Documentation