> For the complete documentation index, see [llms.txt](https://developer.harness.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://developer.harness.io/database-devops/use-db-devops/changelogs-and-schema-changes/flyway/subs-properties-in-flyway.md).

# 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 [Working with Flyway Migration Files](/database-devops/use-db-devops/changelogs-and-schema-changes/flyway/flyway-migrations-file-structure.md) to structure those files.
* **DB Instance:** Create the DB Instance that the pipeline uses. Go to [Provision Database DevOps](/database-devops/setup-db-devops/provision-database-devops.md) to create a schema and instance.
* **Pipeline:** Create a Database DevOps pipeline. Go to [Create a pipeline in Database DevOps](/database-devops/use-db-devops/deployment-pipeline-configuration/create-a-pipeline.md) 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:

{% code title="V2\_\_create\_audit\_table.sql" overflow="wrap" %}

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

{% endcode %}

{% hint style="info" %}
**Default placeholder format**

Flyway uses `${name}` as the default placeholder format. Go to [Migration placeholders](https://documentation.red-gate.com/flyway/flyway-concepts/migrations/migration-placeholders) to review native Flyway placeholder behavior.
{% endhint %}

### 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:

{% code title="Resolved SQL after substitution" overflow="wrap" %}

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

{% endcode %}

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

{% hint style="info" %}
**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.
{% endhint %}

### 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.

{% stepper %}
{% step %}
In the pipeline, add an **Apply DB Schema** step.
{% endstep %}

{% step %}
Set **Migration Type** to **Flyway**.
{% endstep %}

{% step %}
Select the Flyway DB Schema and the DB Instance that contains the substitution properties.
{% endstep %}

{% step %}
Run the pipeline. Harness applies the pending migrations with placeholders resolved.
{% endstep %}

{% step %}
To revert, add a **Rollback DB Schema** step that uses the same DB Schema and DB Instance.
{% endstep %}
{% endstepper %}

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

{% code title="pipeline.yaml" overflow="wrap" %}

```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
```

{% endcode %}

## Next steps

Use these pages after you configure placeholders:

* [Working with Flyway Migration Files](/database-devops/use-db-devops/changelogs-and-schema-changes/flyway/flyway-migrations-file-structure.md): Structure and name Flyway migrations.
* [Apply DB Schema step](/database-devops/use-db-devops/deployment-pipeline-configuration/step-types/apply-dbschema-step.md): Apply Flyway migrations.
* [Rollback DB Schema step](/database-devops/use-db-devops/deployment-pipeline-configuration/step-types/rollback-dbschema-step.md): Roll back Flyway migrations.
* [Substituting Properties in Changelogs](/database-devops/use-db-devops/changelogs-and-schema-changes/liquibase/subs-properties-in-changelogs.md): Liquibase equivalent on the same DB Instance UI.

{% @harness-feedback/feedback %}