Redgate Flyway

Pre-requisites

Create a project
Configure an environment. This tutorial refers to an empty SQlite environment called test (JDBC URL e.g. `jdbc:sqlite:test.db`)

Steps to run

Create some migrations and undo migrations, e.g.
- Command-line
- Flyway Desktop
1. create your first migration in the /migrations directory called V1__Create_person_table.sql:
```
create table PERSON ( ID int not null, NAME varchar(100) not null );
```
2. add a second migration to the /migrations 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');
```
3. add an undo migration to the /migrations directory called U1__Create_person_table.sql
```
DROP TABLE PERSON;
```
4. add a second undo migration to the /migrations directory called U2__Add_people.sql
```
DELETE FROM PERSON;
```
5. Migrate your test environment
```
flyway migrate -environment=test
```
  You should get confirmation in the console output that this operation has run successfully.
1. Navigate to the migration scripts page and select your test environment as your target database, configuring it if needed by pasting in the JDBC URL.
2. Click the Add migration button, set the Description to be "Create_person_table", set the following script content, and click Save
```
create table PERSON ( ID int not null, NAME varchar(100) not null );
```
3. Click the Add migration button a second time, select, the Undo radio button, set the following script content, and click Save
```
DROP TABLE PERSON;
```
4. Click the Add migration button a third time, set the Description to be "Add_people", set the following script content, and click Save
```
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');
```
5. Click the Add migration button a fourth and final time, select, the Undo radio button, set the following script content, and click Save
```
DELETE FROM PERSON;
```
6. Click Run migrate, and then Close following operation success

Verify the status of your migrations

Run the info command

flyway info -environment=test

You should get some table output which looks similar to the following:

+-----------+---------+---------------------+------+---------------------+---------+----------+
| Category  | Version | Description         | Type | Installed On        | State   | Undoable |
+-----------+---------+---------------------+------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL  | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL  | 2017-12-17 20:01:13 | Success | Yes      |
+-----------+---------+---------------------+------+---------------------+---------+----------+

Viewing your scripts on the migrations scripts page, you should see something like the following:

Undo the last migration

Run the undo command

flyway undo -environment=test

You should get confirmation in the console output that this operation has run successfully.

If you rerun `flyway info -environment=test`, you should now get output similar to the folowing:

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Category  | Version | Description         | Type     | Installed On        | State   | Undoable |
+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL      | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 20:01:13 | Undone  |          |
| Undo      | 2       | Add people          | UNDO_SQL | 2017-12-17 22:45:56 | Success |          |
| Versioned | 2       | Add people          | SQL      |                     | Pending | Yes      |
+-----------+---------+---------------------+----------+---------------------+---------+----------+

Our audit trail now clearly shows that version 2 was first applied, then undone and is now pending again.

Select the dropdown in the right hand pane which currently says Migrate, and select Undo
Click Run Undo, and then Close following operation success
You should now see version 2 as a pending migration
If you uncheck Only show pending migrations, you should see something like the following:

(Optionally) reapply the migration

Run the migrate command

flyway migrate -environment=test

You should get confirmation in the console output that this operation has run successfully.

If you rerun `flyway info -environment=test`, you should now get output similar to the folowing:

+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Category  | Version | Description         | Type     | Installed On        | State   | Undoable |
+-----------+---------+---------------------+----------+---------------------+---------+----------+
| Versioned | 1       | Create person table | SQL      | 2017-12-17 19:57:28 | Success | Yes      |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 20:01:13 | Undone  |          |
| Undo      | 2       | Add people          | UNDO_SQL | 2017-12-17 22:45:56 | Success |          |
| Versioned | 2       | Add people          | SQL      | 2017-12-17 22:50:49 | Success | Yes      |
+-----------+---------+---------------------+----------+---------------------+---------+----------+

Ensure migrate is selected from the command dropdown, click Run migrate, and then Close following operation success
There should now be no pending migrations
If you uncheck Only show pending migrations, you should see something like the following:

Redgate Flyway

Tutorial - Roll back using undo migrations

Pre-requisites

Steps to run

Related command reference