Postgres: Flyway DB Project, Azure DevOps CICD Pipeline (CMD) - leganderson-dev/Flyway_PoC_Onboarding GitHub Wiki

Welcome to the Flyway_PoC_Onboarding wiki!

If you are following this steps and you are new to Flyway and Azure DevOps, I recommend using the same db, variable and property names as in this doco. If you are naming those differently, please keep an eye on the scripts and variables, as they will need to be modified.

Step by Step video following this Wiki

NOTE: At the time of the recording of this video, the schema model capability for postgres was in Beta. To enable it, on the developer's machine:

open 'environment variables'

add a 'system variable'

variable name: FLYWAY_DESKTOP_RG_COMPARE | variable value: true

1. Prerequisites

Install a Git client (Git, GitHub Desktop, Sourcetree)

Determine what CICD system (Azure DevOps, Jenkins, GitLab...) your organization uses.

I'll be using AzureDevOps (ADO) in this Demo, but the processes and steps used here would work in any setup 😉.

If your organization dont have a CICD system, ADO offers a free tier

2. Preparing the PoC Environment

Step 2.1 Access the URL with latest Redgate products

Step 2.2 Download and Install 'Flyway Desktop', from the 'Version Control and Deployment Automation' option

Step 2.3 Create the databases needed for the PoC

Shadow (empty schema) | dvdrental_shadow

Build (empty schema) | dvdrental_build

Dev | DVDRental_Dev

QA | DVDRental_QA

Prod | DVDRental

Download DVD Rental Sample Database

Step 2.5

Create a new project in your CICD system - Postgres_DVDRental

Clone (copy) your remote repository into your local development workspace (i.e. your laptop)

3. Create your Flyway database Project

Step 3.1 Open Flyway Desktop

Step 3.2 Create a New Project

Project location: file location that you cloned your repository to

Project name: any meaningful name, usually the name of your database | DVDRental

Database engine: PostgreSQL

Step 3.3 Link Development database to the project

This is the database that we (as developers) will use to make and validate our changes, before those get committed to source control

We will use the DVDRental_Dev database

Step 3.4 Create your 'Schema Model'

When we create a new DB project, the first comparison will prompt you to save all existing database objects into your schema model, thus creating a file representation of your database

Select all objects and click 'save to project'

Future comparisons will only show the objects that are different from your model

Step 3.5 Set up the shadow database

Click on 'Generate Migrations' and then in 'Set up Shadow Database'

We will link the dvdrental_shadow database

Learn more about Shadow Databases

Step 3.6 Create the Baseline

Click on 'Create Baseline' and then on 'Connect to Database'

We will use Widget_Staging DB to create the baseline script

Save the baseline file

The baseline is a file that represents your production database. It contains the 'create statement' for all its objects

No changes are made to the database when creating the baseline, so when onboarding an actual project, it is OK to point it to production or a copy of it that is the same schema version.

Step 3.7 Commit and Push changes to Repository

To enable the auto generation of rollback scripts, click on the 'cog'/'settings' icon on the top right and check the 'generate undo scripts' option

Click on the 'Commit changes' button, or alternatively go to the 'Version Control' tab in order to do it.

Write a relevant comment and press 'Commit'

'Push' changed to the remote repository and explore/browse it

4. Validate your project

4.1. In your preferred IDE, Make a change to the dev database

4.2. In Flyway, go the 'Schema Model' tab and 'refresh' your project

4.3. Save your changes to the schema model

4.4. 'Generate migrations' for the changes you have made

4.5. 'Commit' and 'Push' changes to the remote repo

4.6. Go to your repo and check both the 'schema model' and 'migrations' folders to make sure your changes are as expected

5. Create and Configure a CI pipeline (using Classic Editor)

Step 5.1 Create a new pipeline

Click on 'Pipelines' and 'New Pipeline'

Click on 'Use the Classic Editor'

'Continue'

'Empty Job'

Step 5.2 Select / Configure an 'Agent Pool'

If your organization already uses ADO, select an 'Agent Pool' that will have access to the servers/databases that we want to deploy to

If your organization does not have an agent, we will need to create/configure one

More on Azure Pipeline Agents

Creating a Self-Hosted Agent

Wiki to Step-by-Step

Step 5.3 Create a build validation task

Click on '+' to add a new task to your job

On the search bar, type 'Command line', click and add the 'command line' task

Click on '+' to add a new task to your job

On the search bar, type 'Publish build Artifacts', click and add the 'Publish build Artifacts' task

Step 5.4 Edit the 'Command Line' Task

Re-name your task to something relevant, i.e. 'Build Validation Process'

Paste the following into the script section:

flyway clean migrate info $(var_schemas) -url=jdbc:postgresql://$(var_servername)/$(var_buildDB) -locations=filesystem:$(Build.Repository.LocalPath)/*/migrations -user=$(var_username) -password=$(var_password)

Step 5.5 Edit the 'Publish Artifact' task

Paste the following into the 'Path to Publish'

$(Build.Repository.LocalPath)/

Rename the 'Artifact Name'

BuildArtifact

Step 5.6 Create Pipeline Variables

Add the following pipeline variables

name / value

var_buildDB | dvdrental_build # Name of your build database

var_schemas | -schemas=public # Name of the schema

var_password | your password

var_servername | localhost:5432 # server_name:port

var_username | postgres

Step 5.7 Cross your fingers

Step 5.8 Save and Queue

6. Create and Configure a Release Pipeline

Step 6.1 Click on 'Releases' and 'New Pipeline'

Step 6.2 Click on 'Empty Job'

Step 6.3 Click on the 'Artifacts' box and 'Add' your CI pipeline from the dropdown

Step 6.4 Click on the 'Stage 1' box and rename it to 'QA' (or anything more relevant to you)

Step 6.5 Create the Staging tasks

Click on the '1 job, 0 task' link

On the 'Agent Job' section, select the appropriate 'Agent Pool'

Add ('+') a 'Command Line' task

Step 6.6 Configure a QA deployment task

Re-name the first task to something relevant, i.e. Deploy changes to QA database

Paste the following code into the 'script' box flyway info migrate info $(var_schemas) -url=jdbc:postgresql://$(var_servername)/$(var_QADB) -locations=filesystem:"$(System.DefaultWorkingDirectory)\_$(var_buildpipelinename)\BuildArtifact\*\migrations" -user=$(var_username) -password=$(var_password) $(var_baselineonmigrate)

Step 6.7 Configure a Prod jobs and tasks

Add a new 'Stage', with an 'Empty job'

Rename it to Production

Edit the job

Add (+) a new 'Command Line' task

Paste the following code into the 'script' box

flyway migrate $(var_schemas) -url=jdbc:postgresql://$(var_servername)/$(var_prodDB) -locations=filesystem:"$(System.DefaultWorkingDirectory)\_$(var_buildpipelinename)\BuildArtifact\*\migrations" -dryRunOutput="$(var_Reportpath)\$(Release.ReleaseName)_dryrun.sql" -user=$(var_username) -password=$(var_password) $(var_baselineonmigrate)

Step 6.9 Add a 'Manual Intervantion' step

'Add an agentless job'

Add (+) a 'Manual intervention' task

Write a message i.e. Please review deployment script $(var_Reportpath)

Step 6.10 Create Production deployment task

'Add an Agent Job'

Ajust the 'Agent Pool' accordingly

Add (+) a new 'Command Line' task

Rename it to Release changes to Production database

Paste the following code into the 'script' box flyway info migrate info $(var_schemas) -url=jdbc:postgresql://$(var_servername)/$(var_prodDB) -locations=filesystem:"$(System.DefaultWorkingDirectory)\_$(var_buildpipelinename)\BuildArtifact\*\migrations" -user=$(var_username) -password=$(var_password) $(var_baselineonmigrate)

Step 6.11 Create Pipeline Variables

Add the following pipeline variables

name / value

var_baselineonmigrate | -baselineOnMigrate=true # Creates the Flyway_history table when deploying to an existing db for the first time

var_buildpipelinename | Postgres_DVDRental-CI # Name of the CI pipeline

var_password | your password

var_servername | localhost:5432 # server_name:port

var_username | postgres

var_prodDB | DVDRental # name of the prod DB

var_QADB | DVDRental_QA # name of the QA DB

var_Reportpath | # File directory to where the pre-prod SQL script will be created

var_schemas | -schemas=public # Name of the schema

Step 6.9 Cross your fingers

Step 6.10 'Save' and 'Release'

7. Validate de process end-to-end

7.1. In your preferred IDE, Make changes to the dev database

7.2. In Flyway, go the 'Schema Model' tab and 'refresh' your project

7.3. Save your changes to the schema model

7.4. 'Generate migrations' for the changes you have made

7.5. 'Commit' and 'Push' changes to the remote repo

7.6. Go to your repo and check both the 'schema model' and 'migrations' folders to make sure your changes are as expected

7.7 Click into your CI pipeline and 'Run Pipeline'

7.8 Click into your Release Pipeline and 'Create Release'

7.9 Validate that the changes you have made were successfully deployed to the QA DB

7.10 Review your migrations sctipt

7.11 Approve the release

7.12 Validate that the changes you have made were successfully deployed to the Production DB