Generate

This concept page assumes you understand the following area of Flyway:

Diff

Why is this useful ?

The generate command allows migration scripts to be generated based on the differences found by running the diff command. That is, users can leverage Redgate's comparison technology to generate migration scripts rather than hand craft them.

How is this used ?

To use generate a diff must first be performed, which will store the differences between the specified source and target in a flyway.artifact.diff file. The differences in this file can then be used to generate a migration script which contains the queries required to apply the changes to the target environment.

For example, the following commands generate a diff between a schema model folder and a prod environment. The diff command shows that a NewTable has been added to the schema model which is not present in the prod environment.

$ flyway diff -diff.source=schemaModel -diff.target=prod

diff artifact generated: C:\Users\FlywayUser\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+--------+----------+
| Id                          | Change | Object Type | Schema | Name     |
+-----------------------------+--------+-------------+--------+----------+
| Qq6V7onaXK6D5iF2GOSd_H5buis | Add    | Table       | sakila | NewTable |
+-----------------------------+--------+-------------+--------+----------+

The generate command can then be used to generate a migration script which will create NewTable, alongside an undo migration to undo the creation of NewTable.

Consider that the migrations folder used has the following contents before running the generate command:

B001__baseline.sql
V001__Add_feature_ABC.sql

We can let flyway determine the next correct migration version to use and simply specify the description to use for our migration scripts. Running the following command generates a migration script, and corresponding undo script, for the NewTable change:

$ generate -generate.types=versioned,undo -generate.description=Add_NewTable

Database: jdbc:mysql://clone-internal.red-gate.com:32781/mysql (MySQL 8.0)
Database: jdbc:mysql://clone-internal.red-gate.com:32781/mysql (MySQL 8.0)
Generating versioned migration: C:\Users\FlywayUser\Project\migrations\V002__Add_NewTable.sql
Generating undo migration: C:\Users\FlywayUser\Project\migrations\U002__Add_NewTable.sql
Generated: C:\Users\FlywayUser\Project\migrations\U002__Add_NewTable.sql
 Warning: Line 5: [TableDrop] This deployment drops the table sakila.NewTable. Any data in the table will be lost.
Generated: C:\Users\FlywayUser\Project\migrations\V002__Add_NewTable.sql

The migrations directory now contains the following migration scripts:

B001__baseline.sql
V001__Add_feature_ABC.sql
U002__Add_NewTable.sql
V002__Add_NewTable.sql

Where the new migration scripts contain the expected contents:

$ cat V002__Add_NewTable.sql
-- Script generated by Redgate Compare v1.21.3.23091


-- deployment: Creating sakila.NewTable...
CREATE TABLE sakila.NewTable (
    id int NOT NULL,
    name varchar(45) NOT NULL
);
ALTER TABLE sakila.NewTable ADD PRIMARY KEY (id);

$ cat U002__Add_NewTable.sql
-- Script generated by Redgate Compare v1.21.3.23091


-- deployment: Dropping sakila.NewTable...
DROP TABLE sakila.NewTable;

Baseline migrations

It's also possible to use the generate command to create a baseline migration script. In the example below we diff the schema model and the shadow environment. We note from the errors in the output below that the migrations folder does not exist yet, which is expected for a new project.

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

ERROR: Skipping filesystem location: .\migrations (not found)
Database: jdbc:mysql://clone-internal.red-gate.com:32881/mysql (MySQL 8.0)
Schema history table `sakila_shadow`.`flyway_schema_history` does not exist yet
ERROR: Skipping filesystem location: .\migrations (not found)
Database: jdbc:mysql://clone-internal.red-gate.com:32881/mysql (MySQL 8.0)
diff artifact generated: C:\Users\FlywayUser\AppData\Local\Temp\flyway.artifact.diff
+-----------------------------+--------+-------------+---------------+-----------------------------------------+
| Id                          | Change | Object Type | Schema        | Name                                    |
+-----------------------------+--------+-------------+---------------+-----------------------------------------+
| GTUNyJbY9j5j.hntdn0fEKCnbXw | Add    | Database    |               | sakila_shadow                           |
| LHPQbjN7DZ.n6KB2Nizi4BxInds | Add    | Foreign key | sakila_shadow | address.fk_address_city                 |
| EcdJ_YlXJ_MIpToiOhssrvLBkZY | Add    | Foreign key | sakila_shadow | city.fk_city_country                    |
...
| vofH2dqiRsKaH5Ddu.lDqSADDJA | Add    | View        | sakila_shadow | staff_list                              |
+-----------------------------+--------+-------------+---------------+-----------------------------------------+

Using the generate command with the -generate.types=baseline argument allows a baseline migration script to be generated from this diff:

$ flyway generate -generate.types=baseline -generate.description=initial_import -generate.addTimestamp=true

Using diff artifact: C:\Users\FlywayUser\AppData\Local\Temp\flyway.artifact.diff
ERROR: Skipping filesystem location: .\migrations (not found)
Generating baseline migration: C:\Users\FlywayUser\Project\migrations\B001_20241014144007__initial_import.sql
Generated: C:\Users\FlywayUser\Project\migrations\B001_20241014144007__initial_import.sql

Note that the migrations folder is created (if it doesn't exist) when the baseline migration script is generated.

Redgate Flyway

Generate concept

Generate

Why is this useful ?

How is this used ?

Baseline migrations

Further Reading