What are the challenges you face when working across database platforms? Take the survey

File Naming Conventions

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?

Best Answer

  • Options
    Peter_LawsPeter_Laws Posts: 243 Silver 2
    Answer ✓
    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
    -> 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!

    Migrations naming conventions
    OutOfOrder and CherryPick
    Kind regards
    Peter Laws | Redgate Software
    Have you visited our Help Center?


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file