How to run flyway migration for reactive r2dbc driver on sprintboot stratup

Question

I am working on springboot webflux project with non-blocking database driver r2dbc,

But when Springboot application start Flyway does not run migrations. Below are my spring-boot pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>r2dbmigration</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>r2dbmigration</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot.experimental</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>

        <dependency>
            <groupId>io.r2dbc</groupId>
            <artifactId>r2dbc-postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot.experimental</groupId>
            <artifactId>spring-boot-test-autoconfigure-r2dbc</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot.experimental</groupId>
                <artifactId>spring-boot-bom-r2dbc</artifactId>
                <version>0.1.0.M3</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>

</project>

My migration file is : V1.1__create_file_import_table.sql with content of

DROP TABLE IF EXISTS file_import;
CREATE TABLE file_import
(
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    file_key    CHARACTER VARYING(255) NOT NULL,
    created_at  TIMESTAMP without time zone NOT NULL,
    created_by  BIGINT NOT NULL,
    PRIMARY KEY (id)
);

application.properties

spring.r2dbc.url= r2dbc:postgresql://localhost:5432/import
spring.r2dbc.username=postgres
spring.r2dbc.password=password

My application starts smoothly but there is no migration run.

Can someone please help me ?

Github URL

Thanks

Flyway requires a JDBC driver, did you also add a JDBC driver to your project? Also, provide a [mre], so we know your config, available libraries, etc. — Mark Rotteveel, Jan 01 '20 at 15:23
@MarkRotteveel I have added the pom.xml and migration related properties and .sql file — Alpesh Jikadra, Jan 01 '20 at 16:36
You haven't configured any connection information for JDBC, you only have R2DBC. Flyway cannot use R2DBC. — Mark Rotteveel, Jan 01 '20 at 17:16
You might want to leave your vote on https://github.com/flyway/flyway/issues/2502 to let Flyway maintainers you'd like to use it with R2DBC. — mp911de, Jan 01 '20 at 18:09
@AlpeshJikadra is using the _'Maven Flyway plugin'_ an option? see Answer. — Dirk Deyne, Jan 04 '20 at 16:42

score 21 · Accepted Answer · answered May 05 '20 at 04:30

The following Java implementation is based on @Sim's Kotlin example.

Example pom.xml

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RC1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>com.example</groupId>
    <artifactId>flyway</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>flyway</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>11</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-webflux</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-r2dbc</artifactId>
        </dependency>
        <dependency>
            <groupId>dev.miku</groupId>
            <artifactId>r2dbc-mysql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>io.projectreactor</groupId>
            <artifactId>reactor-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
    </pluginRepositories>

</project>

Example application.yml

---
spring:
  r2dbc:
    url: r2dbc:pool:mysql://localhost:3306/defaultdb
    username: <user>
    password: <pass>
  flyway:
    url: jdbc:mysql://localhost:3306/defaultdb
    user: ${spring.r2dbc.username}
    password: ${spring.r2dbc.password}
    baseline-on-migrate: true

Add the following Spring Boot configuration - FlywayConfig.java

package com.example.flyway;

import org.flywaydb.core.Flyway;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;

// https://stackoverflow.com/a/61412233
@Configuration
public class FlywayConfig {

    private final Environment env;

    public FlywayConfig(final Environment env) {
        this.env = env;
    }

    @Bean(initMethod = "migrate")
    public Flyway flyway() {
        return new Flyway(Flyway.configure()
                .baselineOnMigrate(true)
                .dataSource(
                        env.getRequiredProperty("spring.flyway.url"),
                        env.getRequiredProperty("spring.flyway.user"),
                        env.getRequiredProperty("spring.flyway.password"))
        );
    }
}

First execution:

: Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
: Finished Spring Data repository scanning in 7ms. Found 0 R2DBC repository interfaces.
: Flyway Community Edition 6.4.1 by Redgate
: Database: jdbc:mysql://localhost:3306/defaultdb (MySQL 5.5)
: Successfully validated 1 migration (execution time 00:00.006s)
: Creating Schema History table `defaultdb`.`flyway_schema_history` ...
: DB: Name 'flyway_schema_history_pk' ignored for PRIMARY key. (SQL State: 42000 - Error Code: 1280)
: Current version of schema `defaultdb`: << Empty Schema >>
: Migrating schema `defaultdb` to version 1.0.001 - Initialise database tables
: Successfully applied 1 migration to schema `default` (execution time 00:00.036s)
: Netty started on port(s): 8080
:Started Application in 1.829 seconds (JVM running for 2.343)

Second execution:

: No active profile set, falling back to default profiles: default
: Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
: Finished Spring Data repository scanning in 11ms. Found 0 R2DBC repository interfaces.
: Flyway Community Edition 6.4.1 by Redgate
: Database: jdbc:mysql://localhost:3306/defaultdb (MySQL 5.5)
: Successfully validated 1 migration (execution time 00:00.009s)
: Current version of schema `defaultdb`: 1.0.001
: Schema `defaultdb` is up to date. No migration necessary.
: Netty started on port(s): 8080
: Started Application in 1.273 seconds (JVM running for 1.695)

score 10 · Answer 2 · answered Apr 24 '20 at 15:44

A bit late answer, but you can also set up a Spring bean manually to handle the migration.

Add flyway config to application.yml (or .properties):

spring:
  flyway:
    url: jdbc:postgresql://localhost:5432/<db-name>
    user: <user>
    password: <password>

This code is Kotlin, but it can easily be translated to Java:

import org.flywaydb.core.Flyway
import org.springframework.boot.autoconfigure.flyway.FlywayProperties
import org.springframework.context.annotation.Bean
import org.springframework.context.annotation.Configuration

@Configuration
class FlywayConfig(private val flywayProperties: FlywayProperties) {
    @Bean(initMethod = "migrate")
    fun flyway(): Flyway? {
        return Flyway(Flyway.configure()
                .baselineOnMigrate(true)
                .dataSource(flywayProperties.url, flywayProperties.user, flywayProperties.password)
        )
    }
}

FlywayProperties was not created by Spring Boot auto config. Probably because of missing some dependency. I had to inject values directly — Andrey, Mar 22 '21 at 08:01
You might need to add an `@EnableConfigurationProperties(FlywayProperties.class)` to this configuration object, which can convince Spring to still create that properties bean. — Bwvolleyball, Mar 02 '23 at 03:12

score 5 · Answer 3 · answered Sep 23 '21 at 20:01

Solved this by using the combination of Flyway + JDBC, and R2DBC for the rest of DB interactions.

application.properties

my.database.url=postgresql://localhost:5432}/my_database
spring.r2dbc.url=r2dbc:${my.database.url}
spring.r2dbc.username=user
spring.r2dbc.password=pass
spring.flyway.locations=classpath:db/migration
spring.flyway.enabled=true
spring.flyway.validate-on-migrate=true
spring.flyway.user=${spring.r2dbc.username}
spring.flyway.password=${spring.r2dbc.password}
spring.flyway.url=jdbc:${my.database.url}

build.gradle

dependencies {
 ...
 implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'
 runtimeOnly 'org.flywaydb:flyway-core:7.9.1'
 runtimeOnly 'org.postgresql:postgresql:42.2.20'
 runtimeOnly 'io.r2dbc:r2dbc-postgresql'
 runtimeOnly 'org.springframework.boot:spring-boot-starter-jdbc'
 ...
}

Location of flyway migration SQL scripts:

src/main/resources/db/migration/V1__create_my_table.sql

score 4 · Answer 4 · answered Jul 12 '20 at 18:14

It seems like there is no "official" solution for this problem yet, but there is a temporary solution to solve that: "R2DBC migration tool".

@Nikita Konev provide us a good temporary solution for this problem. I have been using that and works well.

Please check:

R2DBC support - Flyway: https://github.com/flyway/flyway/issues/2502
R2DBC migration tool: https://github.com/nkonev/r2dbc-migrate
Example project (you can play around): https://github.com/nkonev/r2dbc-migrate-example

Thanks to Nikita Konev

score 3 · Answer 5 · answered Jun 01 '21 at 11:28

This might be delayed response, but personally, I prefer to just add the spring starter. This worked for me:

// build.gradle.kts
implementation("org.flywaydb:flyway-core:7.9.1")
runtimeOnly("org.springframework.boot:spring-boot-starter-jdbc")
runtimeOnly("org.postgresql:postgresql:42.2.20")

Then your application.yml file can look like:

# application.yml
spring:
  r2dbc:
    url: r2dbc:postgresql://user:pass@host/db
  flyway:
    enabled: true
    validate-on-migrate: true
    user: user
    password: pass
    url: jdbc:postgresql://host:5432/db
    schemas: ["schema"]

Dirk Deyne · Answer 6 · 2020-01-01T19:26:20.857

0

As mentioned in the comments: R2DBC and Flyway are not compatible yet.

A workaround is to execute Flyway migration using the Maven Flyway plugin, note that you need to provide a JDBC-URL.

In your case, you can trigger the migration via

mvn flyway:migrate -Dflyway.url=jdbc:postgresql://localhost:5432/import -Dflyway.user=postgres -Dflyway.password=password

or via

mvn flyway:migrate

if you add the plugin+configuration in your pom.xml

  <plugin>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-maven-plugin</artifactId>
    <version>6.1.3</version>
    <configuration>
        <url>jdbc:postgresql://localhost:5432/import</url>
        <user>postgres</user>
        <password>password</password>
    </configuration>
  </plugin>

execute the migration via

edited Jan 01 '20 at 19:26

answered Jan 01 '20 at 18:44

Dirk Deyne

6,048
1
15
32

well, it is not quite workaround, especially when the build tool does not have access to database or when migration is running during the startup phase. – Peter Jurkovic Jan 06 '20 at 15:22
@PeterJurkovic not talking about a plugin for a build tool here... If your build tool can perform any _mvn build_, it can execute _mvn flyway:migrate_ – Dirk Deyne Jan 06 '20 at 19:30

How to run flyway migration for reactive r2dbc driver on sprintboot stratup

6 Answers6

Linked