Phil Factor

16 April 2024

• Flyway
• Database migrations

The Flyway Info 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:

The Flyway info command gives you full insight into the state of your database schema and migrations, for Flyway-managed databases in every environment. When we run the Flyway info command, Flyway presents basic details of every valid migration file it can locate for that project, including files that have already been applied to the database, and any that could be. It also extracts status information about the migration files that have already been applied to a database from a special metadata table in the database called the Flyway Schema History table. This allows it to present the current schema version of any copy of the database.

With the info command, you can inspect all this information on-screen or save it as a JSON file, for a script to use.

Version and migration history for a Flyway-managed database

When we issue any command to Flyway, one of the first things it usually does is scan all configured locations for valid migration files. This means it can collect basic details about each file, like the category of migration (Versioned, Undo or Repeatable), its version number, description, type (SQL or Java), filename and path, whether its undoable (Yes, if it finds an undo migration file with the same version number).

It can also determine which files have been applied to any copy of the database and which haven’t. How does it do this? Each time Flyway successfully applies a migration file to a target database, either by running the migrate command or an undo command, it records and then tracks the details for that file in a special metadata table in the database’s default schema, called the Flyway Schema History table. It records in the table the basic file details discussed above, plus when the file was installed, who installed it, and how long it took to execute.

For each file, Flyway also calculates a checksum and stores that in the table. On each subsequent migration, it recalculates the checksum for each file that has already been applied to the database and will raise an error if it has been changed subsequently. In this way it helps to maintain “version integrity” (different copies of the same version of the database should be identical).

By preserving information about all applied migrations in the schema history table, Flyway can always determine the current schema version of every copy of the database, and the history of the migrations used to create it. This gives a lot more predictability to migrations.

Getting version and file information from the info command

Flyway users execute the info command to extract all this information, either for manual review or for use in automation scripts (e.g. PowerShell or Bash).

The on-screen output includes details of the current connection (not shown below), followed by the current schema version, and then an output table collating the status of migrations files already applied to the connected database, taken from the schema history table, plus the details Flyway collected for other migration files that it located but have not yet been applied. When we run the info command, all files already successfully applied are listed with a state of Success and any migrations with a version number higher than the current schema version are listed as Pending. These pending migrations are in the queue to be applied to the database, the next time we run Flyway migrate.

The current schema version

The current schema version will be the version in the filename of the most recent versioned (V) migration file that is currently installed on the database. This won’t necessarily be the last or highest version listed in the table because a database migration can be reversed with a versioned undo (U) file. In the example output below, the current schema version is V1.1.8, because V1.1.10 was applied but then successfully undone, and V1.1.9 is ignored and hasn’t been applied. You need to account for this if you want to find the current schema version using SQL.

Seeing the full JSON details of each migration

If you run the info command but select JSON as the output type, you’ll see the additional details recorded for each installed migration file, including when it was installed, who installed it, and how long it took to execute. You also see other file details like its full path.

flyway info -outputType=json 
…
  {
      "category": "Versioned",
      "version": "1.1.1",
      "rawVersion": "1.1.1",
      "description": "Initial Build",
      "type": "SQL",
      "installedOnUTC": "2024-03-28T15:51:32.193Z",
      "state": "Success",
      "undoable": "No",
      "filepath": "<MyPathTo>\\migrations\\V1.1.1__Initial_Build.sql",
      "undoFilepath": "",
      "installedBy": "SQLTony",
      "shouldExecuteExpression": null,
      "executionTime": 1479
    }, 
…

We can use some of this information to provide an ‘audit trail’ of schema changes. For example, if each user, when running a migration, sets the installedBy configuration item, in Flyway’s user-level configuration file, with their name or Source Control ID, then Flyway stores this value in the installed_by column of the schema history table. This allows each migration to be associated with the correct user. When committing changes to source control, once a migration run completes, an automated process can read the history table and then commit each change using the ID of the person responsible for it. This history builds into a narrative of changes, telling us who did what and when.

For further information on JSON output, see Using Flyway JSON Output in PowerShell for Reporting.