File Naming Conventions

JonBrune

Our company is moving away from SQL Server, meaning we can no longer use our beloved SSDT/DacPac deployments. I'm thinking of moving to Flyway but I don't understand the file naming.  I'm assuming we'd have a shared folder in our git repository that all our devs will share.  If dev #1 creates file v1234_MyChanges.sql and dev #2 creates file v1234_DifferentChanges.sql, will both get deployed? 

What if someone accidentally creates v1233_AbcChanges after that.  Will Flyway ever find out that v1233_AbcChanges never got deployed?  Can it be configured to throw an exception if there are files in the folder that are not getting, or haven't been, deployed?

Last question, if some sneaky dev applies a change directly to the database without going through the deployment process, how do I find that out?

Peter_Laws

Hi JonBrune,

Thanks for thinking of us, I'll add the obligatory documentation at the end, but first I'll talk you through it because docs can be a little dry.

Firstly version numbers have to be unique and the description isn't factored in, it's just the value preceding the double underscore. If you attempt it, you'll see this kind of exception:

C:\Users\Peter.Laws\Desktop\Flyway\flyway-commandline-9.18.0-windows-x64\flyway-9.18.0>flyway info
Licensed to red-gate.com

Database: jdbc:postgresql:postgres (PostgreSQL 14.2)
ERROR: Found more than one migration with version 1
Offenders:
-> C:\Users\Peter.Laws\Desktop\Flyway\flyway-commandline-9.18.0-windows-x64\flyway-9.18.0\sql\V1__first - Copy (2).sql (SQL)
-> C:\Users\Peter.Laws\Desktop\Flyway\flyway-commandline-9.18.0-windows-x64\flyway-9.18.0\sql\V1__first - Copy.sql (SQL)

By extension, this approach makes the ordering much more intuitive, alphanumeric ordering is typically slower to groc at a glance.

For the other points Flyway validate has your back here and will handle each of these scenarios, better yet, you don't actually have to do anything because it's always checked prior to performing a migration.

Looking at the schema history table that records the migrations, you'll see a checksum is taken at runtime/

So should a migration be altered (ill intentioned or otherwise) when the scrips are parsed at time of migration, flyway will itnerupt itself and inform you if any script checksums have changed, even if they wouldn't been run (due to having already been deployed) to make sure they aren't unintentionally implemented against another target introducing unknown variables.

Regarding order, if a new, earlier version is introduced, flyway will warn you that there are now new, out of order migrations that could be applied, so you can choose how to proceed.

Nearly everything I've listed here can be configured, this is just the default behaviour.
Thanks for contributing to the forum!

Documentation
Migrations naming conventions
Validate
OutOfOrder and CherryPick

JonBrune

Regarding order, if a new, earlier version is introduced, flyway will warn you that there are now new, out of order migrations that could be applied, so you can choose how to proceed.

Our deployments are run via Octopus.  Does the installer have hooks that would allow automated responses?

Peter_Laws

Looking at https://octopus.com/blog/flyway-deployments-with-execution-containers I don't think an hooks are explicitly exposed, but since flyway had dedicated error codes, you could programmatically identify the outcome and respond accordingly if desired.