How do placeholders work in Flyway?

Question

I'm evaluating Flyway for use in my project. Our current SQL scripts contain placeholders for things like URLs which will have a different domain names depending on the environment (dev, qa, prod).

Specifically, we might have INSERT statements like

INSERT INTO FEED VALUES ('app.${env.token}.company.org/feed1', 'My Feed');

${env.token} needs to be replaced with 'dev', 'qa', or 'prod'.

We have about 50 different properties that could potentially need replacement in SQL scripts. The properties all reside in one or two properties files.

Is there a way to run the Flyway Ant migration task so that it pulls the replacement tokens and values from a properties file? Something along the lines of the Ant filter task?

Axel Fontaine · Accepted Answer · 2013-11-19T13:05:50.003

17

Currently when supplying placeholders as properties, the property name should be prefixed with flyway.placeholders.

For example the ${env.token} placeholder can be specified directly as this Ant property: flyway.placeholders.env.token

There is currently no support for passing a property file directly, without using prefixes for the property names. Feel free to raise an issue in the Issue Tracker. :-)

edited Nov 19 '13 at 13:05

answered Feb 23 '12 at 20:24

Axel Fontaine

34,542
16
106
137

2

Axel, can you add this explanation to documentation at here: https://flywaydb.org/documentation/migration/sql I couldn't understand the correct use of placeholders before reading from here. – csonuryilmaz Apr 07 '17 at 12:23

Dennis Hoer · Answer 2 · 2017-03-03T18:09:20.277

16

If the token was subdomain:

INSERT INTO FEED VALUES ('app.${subdomain}.company.org/feed1', 'My Feed');

The values in flyway.conf:

flyway.url=jdbc:mydb://db
flyway.user=root
flyway.schemas=schema1
flyway.placeholders.subdomain=example

Or command line:

flyway -url=jdbc:mydb://db -user=root -schemas=schema1 -placeholders.subdomain=example migrate

Would run the script as:

INSERT INTO FEED VALUES ('app.example.company.org/feed1', 'My Feed');

edited Mar 03 '17 at 18:09

answered Mar 03 '17 at 18:03

Dennis Hoer

3,039
2
23
34

1

In the event that the person finding this page is satisfied with environment variable replacement I'll round out your answer with this clip from the doc page: "to replace a placeholder named key1 with the value value1, you can set the environment variable FLYWAY_PLACEHOLDERS_KEY1 to value1. Flyway will then convert the KEY1 part to lowercase (key1) and look for it, in conjunction with the placeholder prefix and suffix, in your SQL migrations. Flyway will then replace any occurrences it finds with value1." – bielawski Nov 14 '18 at 21:42

score 3 · Answer 3 · answered Jun 03 '19 at 11:13

From my experience, it's much easier to use environment variables instead of CLI or config file (especialy when using docker and k8s).

You can use environment variables in the following format -

export FLYWAY_PLACEHOLDERS_USER=${USER}

Then in you sql statement, use this variable like this -

INSERT INTO tmptable (user)
VALUES ('${user}')

read more about environment variables here

score 0 · Answer 4 · answered May 06 '19 at 00:57

Maven version:

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
        <plugin>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <configuration>
                <url>jdbc:mysql://localhost/cloud</url>
                <user>root</user>
                <password>root</password>
                <placeholderReplacement>false</placeholderReplacement>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>${mysql.version}</version>
                    <scope>runtime</scope>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

How do placeholders work in Flyway?

4 Answers4

Linked