can we add custom validation with flyway community edition?

Question

can we add custom validation with the flyway community edition? basically, we wanted to add some (file checks through extensions like ".SQL" to ignore and ".sql" to accept)?

Answer 1

(Initial thoughts)

This can be done in a beforeMigrate SQL Script which is supported by community edition. The flyway schema history table in the database has the list of migrations, and you can read this in SQL. In your example, you can get from this the filename. You can check through these filenames to, for example, ensure that extensions aren't named ".SQL" but ".sql", and that they have good descriptions. All callbacks are provided with placeholder substitution, which means that you can locate the Flyway_schema_history table easily ( the ${flyway:defaultSchema} and ${flyway:table} placeholder).

The drawback is that you can only get the filenames after they have been used for a migration, but not when they are pending.

Unfortunately, you also cannot check the contents of each file.

With Flyway teams, it is possible to do all this, but to get a list of all the pending files is quite complicated. I use PowerShell to do it in Flyway Teamworks.

(later)

There is a much better way of doing this in Flyway Community by using Flyway Info with the -outputType=json parameter.

I'd forgotten that this gives you an array of migration files with all the extra information that you need. It is a bit slow to run, as you know, but the json document it passes back is gold-dust.

The most important point is that it gives you the paths to not only the executed files but all the pending files too. This will allow you to do a full code analysis of the pending files as well as check their filenames. There is a separate path to the file and the undo file.

A typical migration looks like this...

 {
      "category": "Versioned",
      "version": "1.1.10",
      "description": "Add Addresses Phones Etc",
      "type": "SQL",
      "installedOnUTC": "",
      "state": "Pending",
      "undoable": "Yes",
      "filepath": "<path to >\\Pubs\\Branches\\Develop\\Variants\\Rollback\\.\\migrations\\V1.1.10__A
ddAddressesPhonesEtc.sql",
      "undoFilepath": "<path to >\\Pubs\\Branches\\Develop\\Variants\\Rollback\\.\\migrations\\U1.1.1
0__AddAddressesPhonesEtc.sql",
      "installedBy": "",
      "executionTime": 0
    },

(I've overwritten the real path with <path to >) The only drawback with Flyway Community is that you can't do this in a callback. (You can't use it in an afterEach... callback even in Flyway Teams). It would require a separate process.

Here is how one might test all your migrations with sqlFluff, using PowerShell

$Migrations=Flyway info -outputType=json|convertfrom-json
$migrations.migrations|where {![string]::IsNullOrEmpty($_.filepath)}|foreach{
write-verbose "Checking $($_.version)-$($_.description) ($($_.state))"   
sqlfluff.exe lint --dialect tsql "$($_.filepath)"
}

Thanks for coming up with such an interesting problem!

Answer 2

After a little more research, I've discovered something I didn't know about Flyway. In the Teams Edition (yes, paid), there is the additional functionality of a Callback (documented here). You could address exactly this scenario by using a BeforeMigrate callback, using a script (Bash, Powershell, what have you), to address exactly this type of naming/formatting issue, within the code, within Flyway.