Redgate Flyway

Tutorial - Flyway Desktop migrations workflow with Flyway CLI

Tutorial: Flyway Desktop migrations workflow with Flyway CLI

Introduction

The V11 release of Flyway saw several new Flyway CLI verbs removed from preview and officially released. In this document we will explore how to use these new verbs to mirror the Flyway Desktop migrations workflow using Flyway CLI.

Prerequisites

The examples presented here assumed you are using at least Flyway CLI version 11.0.0 and have a teams or enterprise license.

Initialize a project

Firstly, we will create a new SQL Server project using the init command as shown below:

$ mkdir SqlServerProject
$ cd SqlServerProject
$ flyway init "-init.projectName=SqlServerProject" "-init.databaseType=sqlserver"

The init command must have the following options passed in:

  • -init.projectName - The name of the project
  • -init.databaseType - The type of database used for the project.

Once run this will leave us with a project folder that looks as follows:

PS C:\Users\Flyway\FlywayProjects\SqlServerProject> ls


    Directory: C:\Users\Flyway\FlywayProjects\SqlServerProject


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----        05/12/2024     14:48                migrations
d-----        05/12/2024     14:48                schema-model
-a----        05/12/2024     14:48             69 .gitignore
-a----        05/12/2024     14:48           4711 Filter.scpf
-a----        05/12/2024     14:48           3711 flyway.toml
-a----        05/12/2024     14:48              0 flyway.user.toml

Defining environments

The next step is to define development, shadow and prod environments that will be used as arguments to flyway CLI commands when comparing databases and generating scripts. Environments can be specified by adding environment definitions to the flyway.user.toml file, as the example below shows:

[environments.development]
url = "jdbc:sqlserver://localhost;authentication=sqlPassword;databaseName=Dev;encrypt=true;trustServerCertificate=true"
user = "sa"
password = "..."

[environments.shadow]
url = "jdbc:sqlserver://localhost;authentication=sqlPassword;databaseName=Shadow;encrypt=true;trustServerCertificate=true"
user = "sa"
password = "..."
provisioner = "clean"

[environments.prod]
url = "jdbc:sqlserver://localhost;authentication=sqlPassword;databaseName=Prod;encrypt=true;trustServerCertificate=true"
user = "sa"
password = "..."

These environments allow us to follow the usual development -> shadow -> prod workflow, where the shadow environment is used to validate development changes before they are applied to the prod environment.

Updating the schema model

The first step in the Flyway Desktop migrations workflow is to update the schema model with objects present in the development database, as this allows us to get our database into version control. In order to do this, we must first generate a diff between the development database and the schema model. This can be done using the diff command as shown below:

$ flyway diff "-diff.source=development" "-diff.target=schemaModel"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+
| Id                          | Change | Object Type           | Schema         | Name                                  |
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+
| YoZgVMdZR3p7FZEygVaRX9MoF2w | Add    | DDL trigger           |                | ddlDatabaseTriggerLog                 |
| ZKZljmz2_Vvl5wLmV.mczvanHzM | Add    | Extended property     |                | MS_Description                        |
| VYj3ZC0OtkZR4CbJ_JHm9BMkg_c | Add    | Full text catalog     |                | AW2016FullTextCatalog                 |
| qlJstpTbyOQ7nRXTfUvY4lnUDIA | Add    | Function              | dbo            | ufnGetAccountingEndDate               |
...
| YreyZ8E1z3onEQFgWSGvDqaDUeY | Add    | XML schema collection | Production     | ProductDescriptionSchemaCollection    |
| UJr0Z.pCcr8O5ntP1w6H9teL8kQ | Add    | XML schema collection | Sales          | StoreSurveySchemaCollection           |
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+

The diff command creates an artifact containing all the differences between the development database and the schema model. This artifact can then be used with other commands, such as the model command, which applies the differences in the artifact to the schema model.

Running the model command updates the schema model folder:

$ flyway model

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Saved to schema model
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Database Triggers\ddlDatabaseTriggerLog.sql
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Extended Properties\MS_Description.sql
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Functions\dbo.ufnGetAccountingEndDate.sql
 ...
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Views\Sales.vStoreWithContacts.sql
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Views\Sales.vStoreWithDemographics.sql

Generating a baseline script

Once the database is in version control, the next step is to generate a baseline script. This can be done using the generate command. Before generate can be run, the prod environment must be diffed against the shadow environment in order to determine the changes required for the baseline script.

The diff command to do this is shown below:

$ flyway diff "-diff.source=prod" "-diff.target=migrations" "-diff.buildEnvironment=shadow"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Successfully validated 0 migrations (execution time 00:00.009s)
WARNING: No migrations found. Are your locations set up correctly?
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+
| Id                          | Change | Object Type           | Schema         | Name                                  |
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+
| YoZgVMdZR3p7FZEygVaRX9MoF2w | Add    | DDL trigger           |                | ddlDatabaseTriggerLog                 |
| ZKZljmz2_Vvl5wLmV.mczvanHzM | Add    | Extended property     |                | MS_Description                        |
| VYj3ZC0OtkZR4CbJ_JHm9BMkg_c | Add    | Full text catalog     |                | AW2016FullTextCatalog                 |
| qlJstpTbyOQ7nRXTfUvY4lnUDIA | Add    | Function              | dbo            | ufnGetAccountingEndDate               |
...
| YreyZ8E1z3onEQFgWSGvDqaDUeY | Add    | XML schema collection | Production     | ProductDescriptionSchemaCollection    |
| UJr0Z.pCcr8O5ntP1w6H9teL8kQ | Add    | XML schema collection | Sales          | StoreSurveySchemaCollection           |
+-----------------------------+--------+-----------------------+----------------+---------------------------------------+

Note that, here the diff.target is set to migrations and not shadow, whilst the diff.buildEnvironment is set to shadow. The migrations target will cause the diff command to provision the shadow environment. That is, the shadow environment will be migrated to the latest version before being diffed. Although at the moment we have no migrations, this is useful further down the line when a project will have migrations.

The artifact created by the diff command can now be used to generate a baseline script using the generate command, as shown below:

$ flyway generate "-generate.types=baseline" "-generate.description=Baseline"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Using diff artifact: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
Generating baseline migration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\B001_20241209164727__Baseline.sql
Generated: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\B001_20241209164727__Baseline.sql
 Generated configuration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\B001_20241209164727__Baseline.sql.conf

Performing a diff between the prod environment and shadow environment will no longer show any differences, as the shadow environment will be provisioned with the baseline script that was just created:

$ flyway diff "-diff.source=prod" "-diff.target=migrations" "-diff.buildEnvironment=shadow"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
...
Successfully applied 1 migration to schema [dbo], now at version v001.20241209164727 (execution time 00:02.648s)
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+------+
| Id                          | Change | Object Type | Schema | Name |
+-----------------------------+--------+-------------+--------+------+
| No differences found                                               |
+-----------------------------+--------+-------------+--------+------+

Generating versioned and undo migrations scripts

Generating versioned and undo scripts uses the diff, model and generate commands seen above. Let's assume the following table is added to the development database:

CREATE TABLE Dev.dbo.NewTable
(
    Id   INT PRIMARY KEY,
    Name NVARCHAR(255) NOT NULL
);

We now want to get this change into a migration script that can be applied against production. The first step is to get this change into the schema model. If adding the table is the only change in the development database, then we can update the schema model with a single command by combining the diff and model commands:

$ flyway diff model "-diff.source=development" "-diff.target=schemaModel"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+----------+
| Id                          | Change | Object Type | Schema | Name     |
+-----------------------------+--------+-------------+--------+----------+
| J7JsJA_jmgaSrCD.hAdkbUqxxQs | Add    | Table       | dbo    | NewTable |
+-----------------------------+--------+-------------+--------+----------+

Saved to schema model
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Tables\dbo.NewTable.sql

i.e. The diff command is run first followed by the model command. This is equivalent to running the following commands sequentially:

$ flyway diff "-diff.source=development" "-diff.target=schemaModel"
$ flyway model

Versioned and undo scripts can now be generated using a diff and generate command combination, where the differences between the schema model and the shadow environment are converted into a migration script that can be executed.

$ flyway diff generate "-diff.source=schemaModel" "-diff.target=migrations" "-diff.buildEnvironment=shadow" "-generate.types=versioned,undo" "-generate.description=NewTableAdded"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Successfully applied 1 migration to schema [dbo], now at version v001.20241209164727 (execution time 00:02.820s)
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+----------+
| Id                          | Change | Object Type | Schema | Name     |
+-----------------------------+--------+-------------+--------+----------+
| J7JsJA_jmgaSrCD.hAdkbUqxxQs | Add    | Table       | dbo    | NewTable |
+-----------------------------+--------+-------------+--------+----------+

Using diff artifact: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
Generating versioned migration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\V002_20241210110201__NewTableAdded.sql
Generating undo migration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\U002_20241210110201__NewTableAdded.sql
Generated: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\U002_20241210110201__NewTableAdded.sql
Generated: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\V002_20241210110201__NewTableAdded.sql

We can validate the newly generated migration script by running the diff command and checking there are no differences between the schema model and the shadow database after applying the migration (remember that the diff command will provision the shadow environment with the latest migration scripts when -diff.target=migrations):

$ flyway diff "-diff.source=schemaModel" "-diff.target=migrations" "-diff.buildEnvironment=shadow"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Successfully validated 3 migrations (execution time 00:00.016s)
Current version of schema [dbo]: 001.20241209164727
Migrating schema [dbo] to version "002.20241210110201 - NewTableAdded"
Successfully applied 1 migration to schema [dbo], now at version v002.20241210110201 (execution time 00:00.028s)
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+------+
| Id                          | Change | Object Type | Schema | Name |
+-----------------------------+--------+-------------+--------+------+
| No differences found                                               |
+-----------------------------+--------+-------------+--------+------+

Applying specific changes

The generate and model command also accept a changes argument, which allows a command separated list of change IDs to be passed in as an argument or over stdin.

The example below shows how changes applied to the schema model can be limited to a specific set passed in as a CLI argument. The diff command shows there are 3 tables in the development database which are not present in the schema model. The model command below only applies the changes for the first two tables by specifying their IDs using the -model.changes argument.

$ flyway diff "-diff.source=development" "-diff.target=schemaModel"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+------------+
| Id                          | Change | Object Type | Schema | Name       |
+-----------------------------+--------+-------------+--------+------------+
| rlWm41EHCIp8krJRqZ.PTVIx2fs | Add    | Table       | dbo    | TestTable1 |
| QvJIORGzbAAz3irDJbEKBcQr8QY | Add    | Table       | dbo    | TestTable2 |
| n6OaP76s3hsLYT8jwdpnl5.WaJ8 | Add    | Table       | dbo    | TestTable3 |
+-----------------------------+--------+-------------+--------+------------+

$ flyway model "-model.changes=rlWm41EHCIp8krJRqZ.PTVIx2fs,QvJIORGzbAAz3irDJbEKBcQr8QY"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Saved to schema model
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Tables\dbo.TestTable1.sql
 File updated: C:\Users\Flyway\FlywayProjects\SqlServerProject\schema-model\Tables\dbo.TestTable2.sql

Change IDs can also be passed in over stdin by passing - as the argument to the changes argument. The example below shows how this is done for the generate command:

$ flyway diff "-diff.source=schemaModel" "-diff.target=migrations" "-diff.buildEnvironment=shadow"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)
Flyway permit on disk is outdated and can't be refreshed automatically because there is no refresh token on disk. Please rerun auth

See release notes here: https://rd.gt/416ObMi
Successfully validated 3 migrations (execution time 00:00.022s)
diff artifact generated: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+------------+
| Id                          | Change | Object Type | Schema | Name       |
+-----------------------------+--------+-------------+--------+------------+
| rlWm41EHCIp8krJRqZ.PTVIx2fs | Add    | Table       | dbo    | TestTable1 |
| QvJIORGzbAAz3irDJbEKBcQr8QY | Add    | Table       | dbo    | TestTable2 |
+-----------------------------+--------+-------------+--------+------------+

$ echo "rlWm41EHCIp8krJRqZ.PTVIx2fs" | flyway generate "-generate.changes=-" "-generate.types=versioned,undo" "-generate.description=TestTable1"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
Using diff artifact: C:\Users\Flyway\AppData\Local\Temp\flyway.artifact.diff
Generating undo migration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\U003_20241210113916__TestTable1.sql
Generating versioned migration: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\V003_20241210113916__TestTable1.sql
Generated: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\V003_20241210113916__TestTable1.sql
Generated: C:\Users\Flyway\FlywayProjects\SqlServerProject\migrations\U003_20241210113916__TestTable1.sql

The diff for each change ID can be viewed using the diffText command. For example, to view the diff for TestTable3 we can run the following command, which uses the change ID for TestTable3:

$ flyway diffText "-diffText.changes=n6OaP76s3hsLYT8jwdpnl5.WaJ8"

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi
--- none
+++ Table/dbo.TestTable3
CREATE TABLE [dbo].[TestTable3]
(
[Id] [int] NOT NULL,
[Name] [nvarchar] (255) NOT NULL
)
GO
ALTER TABLE [dbo].[TestTable3] ADD CONSTRAINT [PK__TestTabl__3214EC07250C0CE5] PRIMARY KEY CLUSTERED ([Id])
GO

Running migration scripts on prod

Finally, we can run the migrations scripts generated on the prod environment using the migrate command. Note that -baselineOnMigrate=true is passed to baseline the prod database as the schema history table does not exist yet.

flyway migrate -baselineOnMigrate=true -environment=prod

Flyway Enterprise Edition 11.3.2 by Redgate
Licensed to red-gate.com (license ID 1174ed6b-b10e-41bd-9a1b-285ddc3239c7)

See release notes here: https://rd.gt/416ObMi

Flyway Pipelines are not active for this project. Learn more here: https://flyway.red-gate.com
Schema history table [Prod2].[dbo].[flyway_schema_history] does not exist yet
Successfully validated 5 migrations (execution time 00:00.040s)
Creating Schema History table [Prod2].[dbo].[flyway_schema_history] with baseline ...
Successfully baselined schema with version: 1
Current version of schema [dbo]: 1
Migrating schema [dbo] to version "002.20241210110201 - NewTableAdded"
Migrating schema [dbo] to version "003.20241210113916 - TestTable1"
Successfully applied 2 migrations to schema [dbo], now at version v003.20241210113916 (execution time 00:00.023s)

Didn't find what you were looking for?