Comments
2 comments
-
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
-
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 NerellaSr. Database Administrator
Redgate Community Ambassador
Add comment
Please sign in to leave a comment.
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