Flyway spring boot + java, new local database created by hibernate but now migrate tries to apply migrations that already happened

user_461631 0
Viewed 1439

I initially created my project using hibernate to create tables like most people do, but then following recommendations I started using flyway to do db migrations.

Problem is I erased my entire local system including db and trying to spin it u again but I get conflicts of hibernate and flyway.

I'm using the java api by the way. So when I went to rebuild the database locally I turned on spring.jpa.hibernate.ddl-auto=${HIBERNATE_DDL:create} just for the first run, then turned it to validate

So it built all the tables, but now when I try to launch the application it will try to run the first migration which is

ALTER TABLE public.auth ADD COLUMN resent boolean

which will cause an error on boot because that new column was added by hibernate

Error Code : 0
Message    : ERROR: column "resent" of relation "auth" already exists
Location   : db/migration/V1__Add_Resent_To_Auth.sql (/Users/brian/code/slap/build/resources/main/db/migration/V1__Add_Resent_To_Auth.sql)
Line       : 1
Statement  : ALTER TABLE public.auth
    ADD COLUMN resent boolean

So how do I tell flyway that the current version is V9 and only run migrations after that. Shouldn't it just go look at the flyway_schema_history and see version 9 is the last entry then run migrations after that? I must be missing something

I tried doing this in my config to set the baseline version first

@Configuration
class FlyWay() {

    @Value("\${spring.datasource.url}")
    lateinit var url: String

    @Value("\${spring.datasource.username}")
    lateinit var username: String

    @Value("\${spring.datasource.password}")
    lateinit var password: String

    @Bean
    fun migrate() {
        val flyway = Flyway.configure().baselineVersion("9.0").dataSource(url, username, password).load()
        flyway.migrate()
    }
}

no such luck it still tries to run V1

I tried adding it to application.properties too spring.flyway.baselineVersion=9.0 same error

flyway spring-boot hibernate spring
3 Answers

Scenario as I understood it:

  • Tables already exist
  • State of the tables corresponds to version "9.0"
  • Flyway baseline version should be set once for the local test DB

It might be useful to set the version via command line, since it is to be applied to the test database only once and then the normal migration strategies are to be applied.

Documentation see: https://flywaydb.org/documentation/usage/commandline/baseline

On macOS the Flyway command line client can be installed with brew install flyway.

Instructions

  • make sure the table flyway_schema_history is deleted. Use your preferred SQL client:
   drop table flyway_schema_history;
  • then set the baseline version using the Flyway command line client (this example uses a Postgres database):
   flyway -user=stephan -password= -url=jdbc:postgresql://localhost:5432/stephan -baselineVersion="9.0" -locations="src/main/resources/db/migration" baseline
  • check in SQL client:
select version from flyway_schema_history ;

This should show now "9.0". After that, the Spring Boot application should behave as usual.

Test

demo

Alternative

For those people who prefer to do this with a Maven command:

  • drop the table flyway_schema_history like shown above
  • use the command mvn flyway:baseline -Dflyway.baselineVersion="9.0" to set the baseline version

This requires a bit of configuration in the pom.xml file, e.g. if using a Postgres database:

<build>
    ...
    <plugins>
        ...
        <plugin>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <version>7.1.1</version>
            <configuration>
                <url>jdbc:postgresql://localhost:5432/stephan</url>
                <user>stephan</user>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>org.postgresql</groupId>
                    <artifactId>postgresql</artifactId>
                    <version>42.2.18</version>
                </dependency>
            </dependencies>
        </plugin>
    ...

Test with Maven

A quick test shows the same result.

demo with maven

user_23314450

Why not export the SQL-script from your database (created by Hibernate) and add it as the first Flyway script into your application? It's the cleanest solution as Hibernate doesn't need to be started manually again when the application will run on other systems.

user_145948000

Just try once after adding the following line in your application.yml spring.flyway.baseline-on-migrate: true

user_89905870
Related