Skip to content

Flyway Database Migrations

Jump to bottom
afwilcox edited this page Sep 19, 2024 · 4 revisions

Automated database migrations are handled by Flyway. Here's how Flyway is configured for the C&E project:

  1. Configuration: Flyway is configured to check the backend/db/migrations folder for sql scripts of the format V{#.#.#}__{description}.

    • The first number is the major version of the application (at the time of writing this article we are still working on MVP aka version 0)
    • The second number is the number of the current sprint
    • The third number is an incremental number for builds within the sprint

  2. Version Control: Flyway maintains a table named "flyway_schema_history" to keep track of the applied migrations.

  3. Migration Workflow: When the application starts or when triggered manually, Flyway compares the current state of the database with the available migration scripts.

    • Flyway checks the schema history table to determine the last applied version.
    • It scans the specified migration scripts location (typically a directory) for new scripts that have not been applied yet.
    • It orders the new scripts based on their version numbers and applies them sequentially.

  4. Migration Execution: For each migration script, Flyway executes the SQL statements within the script against the target database. This can involve creating or modifying database tables, views, indexes, procedures, or inserting/updating data.

  5. Schema History Update: After successfully applying a migration script, Flyway updates the schema history table, recording the script's version and checksum as applied.

  6. Repeatable Migrations: Flyway also supports "repeatable" migrations, which are applied every time the database changes are synchronized. These migrations are typically used for managing data changes that cannot be expressed purely in SQL, such as stored procedures or triggers. Repeatable scripts are in the format of R__{description}.

  7. Best Practices / Conventions:

  • DDL Operations such as Creating Tables, Altering Tables, etc. MUST go in a new versioned script (V{#.#.#}__{description}) as these operations often cannot be re-run.
  • DML Operations that insert or modify code tables that are stored in redux MUST go in the repeatable script R__Create-Test-Data.sql as this script will increment the code table version value that will prompt a redux refresh.
  • All other DML Operations SHOULD go into a repeatable script following the R__{description} naming convention as this allows for the incremental development and maintenance of data without causing script-bloat where multiple scripts are making successive updates to the same rows of data.
  • Before making a new repeatable script check that a script that doesn't modify this data doesn't already exist as the execution order of the repeatable scripts is not guaranteed.

This process has been built into docker-compose as well as the C&E deployment pipeline.

Clone this wiki locally