There should be a way to apply automated database migrations

[kmanning]

• It seems like it would make sense for migrations to live in the database pipeline (migrations = schema changes)
• If the above statement is true, it seems like there should be a way to include such a step in the TerraformEnvironmentStage
• I don't think we should dictate what tools are used to handle migration. Some options:
  • Flyway (java) - https://flywaydb.org/
  • ActiveRecord (ruby) - https://edgeguides.rubyonrails.org/active_record_migrations.html
  • Other?
• Just like everything else is intended to be pluggable, it seems like the migration stage should be pluggable with various implementations. And maybe we offer a few default implementations (java/ruby), while still allowing someone to specify their own implementation.

[kmanning]

Example code:

• Assumption, flyway commands are available on your jenkins node
• Network and security connectivity are already available between jenkins node and database.
• Pre-defined variables containing username/passwords that can run migrations (variable names can be overridden)
• Pre-defined location containing migration scripts (can be overridden)

@Library(['terraform-pipeline']) _
Jenkinsfile.init(this, Customizations)
FlywayMigrationPlugin.init()

def validate = new TerraformValidateStage()
// For each environment
//     During plan, flyway will run dryrun and display output
//     During apply, flyway will execute migration after successful apply
def deployQa = new TerraformEnvironmentStage('qa')
def deployUat = new TerraformEnvironmentStage('uat')
def deployProd = new TerraformEnvironmentStage('prod')

validate.then(deployQa)
        .then(deployUat)
        .then(deployProd)
        .build()

[kmanning]

Dryrun is only available for Teams edition (paid). Consider the ability to run dryrun or info, since info is available in Teams (paid) and Community (free) editions.

[kmanning]

Cut a number of separate issues to iterate on this workflow:

1. Optionally disable echo on the flyway commands (to prevent username/password from being displayed) - See Issue FlywayMigrationPlugin: Optionally disable echo for CLI #363
2. Allow username/password to be configured as a CLI command (after allowing for echo to be disabled) - See Issue FlywayMigrationPlugin: Allow username/password to be configured as CLI parameters #365
3. If a pending migration is detected, prompt the user before applying, "There are pending migrations - do you want to apply them?" <-- this is to prevent accidental changes - See FlywayPlugin: Prompt the user *again* before applying migration #368
4. Optionally create a snapshot before applying migration - See FlywayMigrationPlugin: optionally take a snapshot before applying a migration #369
5. If a migration fails, prompt the user to restore the backup. (This might be really trick, and unfeasible)
6. If a previously failed migration is detected, prompt the user to run flyway repair
7. If a previously failed migration is detected, optionally fail the pipeline on plan.
8. Figure out a workflow for flyway baseline