File Naming Conventions
JonBrune
Posts: 14 Bronze 1
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?
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?
Best Answer
-
Peter_Laws Posts: 259 Silver 2Hi 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
Answers