Using Flyway with SQLite database in spring boot

Question

In my Spring Boot project, I need to use SQLite database. I have setup the SQLite database successfully like below:

The dependencies:

implementation("org.xerial:sqlite-jdbc:3.42.0.0")
implementation("org.hibernate.orm:hibernate-community-dialects")
implementation("org.springframework.boot:spring-boot-starter-data-jpa")

application.yml

spring:
  datasource:
    url: jdbc:sqlite:file:myDb.db?cache=shared
    username: root
    password: root

As you can see above, I am using on disk SQLite database, the database file is myDb.db.

I also created the datasource bean:

    @Bean
    public DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(props.getDriverClassName());
        dataSource.setUrl(props.getUrl());
        dataSource.setUsername(props.getUsername());
        dataSource.setPassword(props.getPassword());
        return dataSource;
    }

I verified this SQLite database setup works, because when I start running my project the entity classes' associated db tables are created successfully in database.

Next, I try to setup Flyway as below:

I included dependency:

implementation("org.flywaydb:flyway-core:8.5.11")

In application.yml, I added:

spring:
  ...
  flyway:
    baseline-on-migrate: true
    schemas: myDb
    enabled: true
    url: jdbc:sqlite:file:myDb.db?cache=shared
    user: root
    password: root
    validate-on-migrate: true
    locations: "classpath:db/migration"

I also created directory /db/migration directory under src/main/resources/

But when start running the project, I end up with error:

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to check whether table "myDb"."flyway_schema_history" exists`

I do understand in SQLite there is no concept of "database schema", so I also tried manually create the table flyway_schema_history after which then start running the project, but I still get the same error.

How can I make Flyway + SQLite work in spring boot? What am I missing?

(I doubt it is a database connection issue since before I setup Flyway the project can successfully make a connection & create tables based on JPA Entities. Or Could it be I miss some SQLite specific configurations of Flyway to establish DB connection for Flyway??? I'm not sure...)

Answer 1

You are giving too much parameter, below is my application yaml file

spring:
  jpa:
    hibernate.hbm2ddl.auto: create-drop
    hibernate.show_sql: true
  datasource:
    url: jdbc:sqlite:file:myDb.db?cache=shared
    username: root
    password: root
  h2:
    console.enabled: true
  flyway:
    baseline-on-migrate: true
#    schemas: myDb
    enabled: true
    url: jdbc:sqlite:file:myDb.db?cache=shared
    user: root
    password: root
    validate-on-migrate: true
    locations: "classpath:db/migration"

basically I just commented schema and things started working. I also added h2 console and start checking mydb and this is how it looks below are the parameters for h2 console login here you can find the full working example.