Flyway migrations with Azure devops pipelines

• 

  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 July 08, 2025 19:24

  - 0 +
• 

  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 July 26, 2025 14:26

  - 0 +

Add comment

Please sign in to leave a comment.