Use Flyway Placeholders with Substitution Properties

Use substitution properties to resolve Flyway placeholders in SQL migrations and apply the resolved SQL with Apply DB Schema

Configure Flyway placeholders as substitution properties on a DB Instance. Use the same SQL migration across environments, then apply the resolved SQL in a pipeline.

Before you begin

Complete the following setup before you add placeholders:

• Flyway schema: Create a Flyway-compatible DB Schema and store versioned SQL migrations in Git. Go to Work with Flyway migration files to structure those files.

• DB Instance: Create the DB Instance that the pipeline uses. Go to Provision Database DevOps to create a schema and instance.

• Pipeline: Create a Database DevOps pipeline. Go to Create a pipeline in Database DevOps to add Apply DB Schema and Rollback DB Schema steps.

What you will learn from this topic

• How Flyway placeholders map to substitution properties on a DB Instance

• How to add ${name} tokens to a versioned SQL migration

• How to apply and roll back Flyway migrations after placeholders are resolved

Add placeholders to a Flyway migration

Add placeholders to a versioned SQL migration in ${name} format. The name inside the token must match a substitution property key.

The following migration contains placeholders for a table name and column lengths:

V2__create_audit_table.sql

CREATE TABLE ${table_name} (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(${name_length}) NOT NULL,
    description VARCHAR(${description_length})
);

Default placeholder format

Flyway uses ${name} as the default placeholder format. Go to Migration placeholders to review native Flyway placeholder behavior.

Configure substitution properties

Create or update the DB Instance that the Flyway pipeline step uses. Add the key-value pairs under Substitute Properties. Do not include ${ and } in the property key. Harness passes each pair to Flyway as a placeholder with no extra mapping.

For the preceding migration, configure the following properties:

• table_name: audit_events

• name_length: 100

• description_length: 255

When a Flyway step uses this DB Instance, Harness passes the properties to Flyway. Flyway resolves the migration as follows:

Resolved SQL after substitution

CREATE TABLE audit_events (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255)
);

To use a different value in another environment, configure that environment's DB Instance. Leave the SQL migration in Git unchanged.

Harness ignores flyway.toml placeholders

If the repository includes a flyway.toml file, Harness does not read placeholder values from it. Configure substitution properties on the DB Instance instead. This matches how Harness ignores liquibase.properties for Liquibase migrations.

Apply and roll back the migration

Use Apply DB Schema and Rollback DB Schema for Flyway migrations. Do not add a native Flyway Command step unless you need a command those steps do not cover.

1

In the pipeline, add an Apply DB Schema step.

2

Set Migration Type to Flyway.

3

Select the Flyway DB Schema and the DB Instance that contains the substitution properties.

4

Run the pipeline. Harness applies the pending migrations with placeholders resolved.

5

To revert, add a Rollback DB Schema step that uses the same DB Schema and DB Instance.

The following pipeline applies the Flyway schema on the DB Instance that holds those properties:

pipeline.yaml

pipeline:
  name: Flyway with Placeholders
  identifier: flyway_with_placeholders
  projectIdentifier: default_project
  orgIdentifier: default
  description: Demonstrates Flyway placeholder support through substitution properties
  tags: {}
  stages:
    - stage:
        name: Deploy Flyway Migrations
        identifier: deploy_flyway_migrations
        description: Apply Flyway migrations with placeholder substitution
        type: Custom
        spec:
          execution:
            steps:
              - stepGroup:
                  name: Flyway Migration
                  identifier: flyway_migration
                  steps:
                    - step:
                        type: DBSchemaApply
                        name: Apply Flyway with Placeholders
                        identifier: apply_flyway_with_placeholders
                        spec:
                          connectorRef: dockerHarness
                          globalSettings:
                            baselineOnMigrate: "true"
                          migrationType: Flyway
                          dbSchema: Flyway
                          dbInstance: sql01
                        timeout: 10m
                  stepGroupInfra:
                    type: KubernetesDirect
                    spec:
                      connectorRef: db
                  failureStrategies:
                    - onFailure:
                        errors:
                          - AllErrors
                        action:
                          type: MarkAsFailure
            rollbackSteps: []
          serviceDependencies: []
        tags: {}
        failureStrategies:
          - onFailure:
              errors:
                - AllErrors
              action:
                type: MarkAsFailure

Next steps

Use these pages after you configure placeholders:

• Work with Flyway migration files: Structure and name Flyway migrations.

• Apply DB Schema step: Apply Flyway migrations.

• Rollback DB Schema step: Roll back Flyway migrations.

• Substitute properties in changelogs: Liquibase equivalent on the same DB Instance UI.