Tutorial - Generate a baseline script
Published 11 October 2021
Prerequisites
- You can only generate a baseline for a project with no migrations
- It is currently necessary to configure a shadow environment before baselining.
When working with an existing database, we recommend creating a baseline script that reflects the state of your objects and reference data that have already been deployed to production so that you can generate new migrations from this point. A baseline script is also necessary to spin up new environments for testing purposes or when deploying to new client sites.
- Open Flyway Desktop and open your project. Navigate to the Generate Migrations tab.
- You will see a banner prompting you to baseline. Click Create baseline
- A dialog will appear allowing you to fill in the connection details for the database you want to use as your baseline.
- Note - Do you need to filter out any additional objects from Production?
Users and roles are automatically filtered out, but if there are other objects that you don't want included in your baseline script, you can click Static data & comparisons to edit your comparison options or configure static data before creating the baseline script. Filters can also be applied separately. See filters and comparison options. If you've setup filters and comparison options for your schema model, then these will already be excluded since the same settings are used.
- Note - Do you need to filter out any additional objects from Production?
- Click the Plus button. You will be shown a connection dialog.
- Enter the credentials for the database you want to use as the baseline, then click Test and save. If the databases across your environments are in sync, then you can use your development database for this. You can optionally customize the version number and description in the filename. Once the details have been filled in click Create baseline to generate the baseline script.
- At the start of the baseline process, Flyway Desktop will check to see if your target contains any invalid objects, e.g., a procedure that refers to a table that no longer exists. If so, it will present you a table of invalid objects. If you believe any are incorrectly raised as invalid, you can deselect them to ensure they're included in the resulting baseline script.
- You will then be able to view and edit your baseline script once it has been generated. You may want to edit your baseline script if it contains Production only information like usernames or permissions that shouldn't be part of the baseline script. You may also want to include additional SQL statements to populate static data or create seed data.
- If your development database is empty, you can bring it in line with the baseline script by migrating the development database. Use the Migrations tab, click Add target database..., select the Development database, and click Close. Then choose Migrate from the drop down and click Run migrate. This will apply the baseline and any pending migration scripts to the selected database. It will also add the flyway_schema_history table to the database and add entries about these scripts being applied, so they are not re-applied on future migrations.
- Make sure you complete the one-off task to baseline your downstream environments in preparation for your first deployment with flyway.
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 -source=prod -target=migrations -buildEnvironment=shadow
Flyway Enterprise Edition 11.2.0 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 target is set to migrations and not shadow, whilst the 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 -types=baseline -description=Baseline
Flyway Enterprise Edition 11.2.0 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 -source=prod -target=migrations -buildEnvironment=shadow
Flyway Enterprise Edition 11.2.0 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 |
+-----------------------------+--------+-------------+--------+------+Next steps
First:
- Validate that your baseline migration deploys against the shadow database. Although the baseline script may have generated successfully, that does not guarantee that it can be deployed, and you may want to make some changes and try again.
Then optionally save your new project to Version Control:
Then:
- Make changes to your development environment, optionally save them to your schema model, and generate migrations
