New! – Try automated migration testing Learn more

Flyway Documentation

Poll

Tutorial: Dry Runs

Flyway Teams

This brief tutorial will teach how to do Dry Runs. It will take you through the steps on how to use them.

Setting up the database

This tutorial picks up from where the First Steps: Command-line tutorial left off.

To get started quickly without having to run through that tutorial first, we will create a new Spawn data container with the migrations from that tutorial already applied:

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

The flyway-getting-started-complete image is available for other database engines besides postgres. Use:

$ spawnctl get data-images --public | grep flyway-getting-started-complete

to find them.

Configure Flyway by editing ./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>

Create a ./sql directory and create the two migration scripts that have already been applied. The first file should be called V1__Create_person_table.sql:

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

and the second one 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');

The database is now ready to go.

Introduction

Dry Runs are a great fit for situations where you may want to:

  • preview the changes Flyway will make to the database
  • submit the SQL statements for review to a DBA before applying them
  • use Flyway to determine what needs updating, yet use a different tool to apply the actual database changes

When doing a Dry Run, Flyway sets up a read-only connection to the database. It assesses what migrations need to run and generates a single SQL file containing all statements it would have executed in case of a regular migration run. This SQL file can then be reviewed. If satisfactory, Flyway can then be instructed to migrate the database and all changes will be applied. Alternatively a separate tool of your choice can also be used to apply the dry run SQL file directly to the database without using Flyway. This SQL file also contains the necessary statements to create and update Flyway’s schema history table, ensuring that all schema changes are tracked the usual way.

Reviewing the status

After having completed the First Steps: Command-line, you can now execute

flyway-8.2.1> flyway info

This should give you the following status:

Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)

+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create person table | SQL  | 2017-12-22 15:26:39 | Success |
| Versioned | 2       | Add people          | SQL  | 2017-12-22 15:28:17 | Success |
+-----------+---------+---------------------+------+---------------------+---------+

Adding a new migration

Let’s add a new migration for which we’ll do a dry run at first.

In the ./sql directory, create a migration called V3__Couple.sql:

create table COUPLE (
    ID int not null,
    PERSON1 int not null references PERSON(ID), 
    PERSON2 int not null references PERSON(ID) 
);

Doing a dry run

Now let’s preview the database changes of this migration by doing a dry run:

flyway-8.2.1> flyway migrate -dryRunOutput=dryrun.sql

This will produce a file called dryrun.sql which contains all SQL statements that Flyway would have executed against the database in case of a regular migration. See for yourself:

---====================================
-- Flyway Dry Run (2018-01-25 17:19:17)
---====================================

SET SCHEMA "PUBLIC";

-- Executing: validate (with callbacks)
------------------------------------------------------------------------------------------
-- ...

-- Executing: migrate (with callbacks)
------------------------------------------------------------------------------------------
-- ...

-- Executing: migrate -> v3 (with callbacks)
------------------------------------------------------------------------------------------

-- Source: ./V3__Couple.sql
---------------------------
create table COUPLE (
    ID int not null,
    PERSON1 int not null references PERSON(ID), 
    PERSON2 int not null references PERSON(ID) 
);
INSERT INTO "PUBLIC"."flyway_schema_history" ("installed_rank","version","description","type","script","checksum","installed_by","execution_time","success") VALUES (2, '3', 'Couple', 'SQL', 'V3__Couple.sql', -722651034, 'SA', 0, 1);
-- ...

This file can now be manually inspected.

Applying the changes

Once the inspection has completed and it is deemed to be OK, the migration can then be applied using the usual migrate command:

flyway-8.2.1> flyway migrate

Alternatively, you can also apply the migration using your database’s built-in support for running SQL scripts. For example, using H2:

flyway-8.2.1> java -cp drivers/* org.h2.tools.RunScript -url jdbc:h2:file:./foobardb -script dryrun.sql

Either one of these approaches yields the same result as you can see using:

flyway-8.2.1> flyway info

This should give you the following status:

Database: jdbc:postgresql://instances.spawn.cc:31585/ (PostgreSQL 11.0)
Schema version: 3

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL  | 2017-12-22 15:26:39 | Success | No       |
| Versioned | 2       | Add people          | SQL  | 2017-12-22 15:28:17 | Success | No       |
| Versioned | 3       | Couple              | SQL  | 2018-01-25 17:57:13 | Success | No       |
+-----------+---------+---------------------+------+---------------------+---------+----------+

Summary

In this brief tutorial we saw how to

  • configure and execute Flyway to do a dry run
  • apply the changes after the dry run has been validated

Read the Dry Runs documentation