Phil Factor

3 May 2024

• Flyway
• Database migrations

Flyway’s Baseline Command Explained Simply

The “Flyway Commands Explained Simply” series of articles provides simple, visual explanations of what each Flyway command does, how it works, how and how not to use it:

Unlike most databases in development, a ‘Flyway database’ will be at a known version, with a record of the migration files used to get to that version. However, what if you want to adopt Flyway versioning with an existing database that is already in use?

The Baseline command’s role is in converting existing production databases to Flyway use. You will only run this command once, on the database that you wish Flyway to “initialize”. When you execute the Baseline command, Flyway will:

1. Create the Flyway schema history table in the connected database – you cannot run a Baseline command on a database where this table already exists.
2. Register, or baseline, the database as being at a specified baselineVersion

The database is now under Flyway’s management. When we run the Flyway migrate command, subsequently, Flyway will ignore any migrations with a version number less than or equal to the baselineVersion.

Running the baseline command

To run the Baseline command, we should provide, as parameters, the following information:

• -baselineVersion – Flyway will create an entry in the Flyway schema history table with this version and a type of BASELINE
• -baselineDescription – a description of that baseline, written to the schema history table

If your database supports schemas, it is also wise to inform Flyway which one is the default schema and therefore where to create the schema history table. You can use the -schemas parameter or, more often, you specify the default schema in a Flyway configuration file.

In the following example, we use the Northwind sample database (source on GitHub). The link gives you the build script (no database or schema creation), which also inserts some sample data. Just run it on an empty database and we’ll pretend it’s our production database.

Assuming you’ve set up the credentials and URL (connection details) as an environment variable or Flyway configuration file, the following Baseline command will initialize Northwind as a Flyway database, at version 1.4.0:

When we execute this command, Flyway creates its schema history table in the dbo schema of the production database with a special entry that registers the ‘baseline’ with the version number and description we supplied, a Type of BASELINE and a State of Baseline, and when it was installed. The flyway info command shows the following output:

>+----------+---------+----------------+----------+---------------------+----------+----------+
| Category  | Version | Description    | Type     | Installed On        | State    | Undoable |
+---------- +---------+----------------+----------+---------------------+----------+----------+
|           | 1.4.0   | Base Migration | BASELINE | 2024-05-01 16:09:16 | Baseline | No       |
+-----------+---------+----------------+----------+---------------------+----------+----------+

If you are using Flyway with a scripting language such as DOS batch, PowerShell or Bash, Flyway can provide feedback to the calling script when this operation is complete, in the form of a JSON file that will report on the operation. You will need to add the -outputType=json parameter to the command. In this case, it will tell you that it succeeded and whether there were warnings:

{
  "successfullyBaselined": true,
  "baselineVersion": "1.4.0",
  "flywayVersion": "10.11.0",
  "database": "Northwind",
  "warnings": [],
  "operation": "baseline"
}

Any subsequent migrations can be added to the Migrations folder specified by flyway.locations for the baselined production database and applied using the migrate command. Once you’ve run the Baseline command on a database, Flyway will ignore any existing migration files with a version equal to or lower than the baseline version.

Although this is a Flyway database now, what we can’t yet do is use Flyway migrate to rebuild or recreate V1.4.0 of the database, because Flyway does not have a migration file to go from a clean database to version 1.4.0. While we won’t ever need rebuild the production database, we will need to create, and often rebuild, development and test copies of this database. So how do we do it?

Reproducing the baseline version in development

We now have a Flyway database in Production, which is V1.4.0, but to develop and test any subsequent migration files, we need a development copy of Northwind that’s the same version.

In our example, we already have a build script that takes a Northwind database from ‘clean’ to V.14.0. We place it in the Migrations folder specified by flyway.locations for the development database with the name V1.4.0__MyDescription.sql.

Assuming this is currently an empty (clean) database, simply run Flyway migrate. Flyway will create the schema history table and run the script to take the database to V.1.4.0:

flyway  migrate
<#
Schema history table [Northwind].[dbo].[flyway_schema_history] does not exist yet
Successfully validated 1 migration (execution time 00:00.522s)
Creating Schema History table [Northwind].[dbo].[flyway_schema_history] ...
Current version of schema [dbo]: << Empty Schema >>
Migrating schema [dbo] to version "1.4.0.0 - NorthWind 1994 to 2000"
Successfully applied 1 migration to schema [dbo], now at version v1.4.0.0 (execution time 00:08.976s)
#>
Flyway info
<#
+-----------+---------+------------------------+------+---------------------+---------+----------+
| Category  | Version | Description            | Type | Installed On        | State   | Undoable |
+-----------+---------+------------------------+------+---------------------+---------+----------+
| Versioned | 1.4.0.0 | NorthWind 1994 to 2000 | SQL  | 2024-04-15 15:43:23 | Success | No       |
+-----------+---------+------------------------+------+---------------------+---------+----------+
#>

Rather than being empty, the development database might be a complete copy of Northwind that is at the same version as production, and not currently managed by Flyway. However, armed with the build script, we can dispense with the Baseline command and instead rebuild this version of the database by running Flyway clean, followed by a Flyway migrate. Similarly, if any other development or test copy of the database comes seriously adrift, simply clean and recreate it.

If we have a large, historical set of scripts that will reproduce the required production version of the database, V1.4.0 in our case, then you could instead consider ‘consolidating’ them into a single Flyway baseline migration (B) file. Flyway Enterprise will generate a baseline migration script automatically.

Conclusions

The Baseline command instantly converts an ordinary production database into a Flyway database, so that we can then use the Flyway migrate command to apply all subsequent migrations. You just need to run it once on any existing copy of a database that you don’t need to rebuild and that started its life using another system of database development and release. For a new database, the Baseline command is not required because the version is determined by the migration files that are applied to it.