How can we help you today? How can we help you today?

Flyway migrations with Azure devops pipelines

i have task to run flyway must run in docker container and run flyway CLI to compare schema model  sql scripts with multiple target environments DB and generate migrations scripts , help me with template for the above requirement 

shreenivasayg
0

Comments

2 comments

  • shreenivasayg

    Azure DevOps Docker YML Microsoft Hosted Agent - Redgate Flyway - Product Documentation  this is comparing DBs but i need to compare schema model with target DB and generate migration scripts across multiple DBs

    shreenivasayg
    0
  • Ganesh Nerella

    If you're looking to run   Flyway CLI inside a Docker container   to compare a   schema model (SQL scripts)   with   multiple target environments   and auto-generate migration scripts, here’s a reusable setup template that should help:

    Folder Structure  

    ```
    flyway-project/
    ├── sql/                   # Folder with your schema model scripts
    │   ├── V1__init.sql
    │   └── ...
    ├── conf/
    │   ├── flyway-dev.conf
    │   ├── flyway-qa.conf
    │   └── flyway-prod.conf
    └── docker-compose.yml     # Optional if you want to orchestrate
    ```

    ---

    Docker Run Command (per environment)  

    ```bash
    docker run --rm \
     -v $(pwd)/sql:/flyway/sql \
     -v $(pwd)/conf:/flyway/conf \
     redgate/flyway \
     -configFiles=conf/flyway-dev.conf \
     info
    ```

    You can replace `info` with `migrate`, `validate`, or `repair` as needed.

    ---

    Sample `flyway-dev.conf` File  

    ```ini
    flyway.url=jdbc:sqlserver://your-dev-db-host:1433;databaseName=YourDB
    flyway.user=your_username
    flyway.password=your_password
    flyway.locations=filesystem:/flyway/sql
    flyway.baselineOnMigrate=true
    flyway.outOfOrder=true
    ```

    Create similar config files for `QA`, `Staging`, `Production`, etc.

    ---

    Generating Migration Scripts  

    Flyway doesn’t generate a migration  diff  script by default like Redgate SQL Compare, but here’s a workaround:

    If you have:

     a baseline script in `sql/`
     new changes in separate SQL files (e.g., `V2__add_table.sql`),

    Then, Flyway will:

    1. Validate target DB vs. your model scripts
    2. Apply new scripts in order (or just output if using `info` or `validate`)

    For scripting without applying:

    ```bash
    docker run --rm \
     -v $(pwd)/sql:/flyway/sql \
     -v $(pwd)/conf:/flyway/conf \
     redgate/flyway \
     -configFiles=conf/flyway-qa.conf \
     -dryRunOutput=/flyway/sql/dryrun_qa.sql \
     migrate
    ```

    ---

    Looping Through Multiple Environments (Optional Bash Loop)  

    ```bash
    for env in dev qa prod; do
     docker run --rm \
       -v $(pwd)/sql:/flyway/sql \
       -v $(pwd)/conf:/flyway/conf \
       redgate/flyway \
       -configFiles=conf/flyway-$env.conf \
       info
    done
    ```

    ---

    Tips

     1.) Keep your `sql/` folder under source control.
    2.)  Use semantic versioning in file names (`V3__feature_X.sql`) to ensure order.
     3.) For full schema diff and advanced modeling, you can pair Flyway with   Redgate SQL Compare CLI   as a pre-step.

     

    Regards,
    Ganesh Nerella 

    Sr. Database Administrator
    Redgate Community Ambassador
     

    Ganesh Nerella
    0

Add comment

Please sign in to leave a comment.