Cannot change read-only flag after establishing a connection

Question

My application having Micronaut 4 + Micronaut Data + Micronaut Hikari + Sqlite is giving error

when hitting

curl --location 'http://localhost:8080/books'

the error is

{
    "_links": {
        "self": [
            {
                "href": "/books",
                "templated": false
            }
        ]
    },
    "_embedded": {
        "errors": [
            {
                "message": "Internal Server Error: Could not set JDBC Connection [HikariProxyConnection@820093718 wrapping org.sqlite.jdbc4.JDBC4Connection@7d9e8ef7] read-only: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection()."
            }
        ]
    },
    "message": "Internal Server Error"
}

My Code is

for SqliteSampleRepository.java

package com.sqlite.example;

import io.micronaut.core.annotation.NonNull;
import io.micronaut.data.jdbc.annotation.JdbcRepository;
import io.micronaut.data.model.query.builder.sql.Dialect;
import io.micronaut.data.repository.CrudRepository;

import java.util.Optional;

@JdbcRepository(dialect = Dialect.ANSI)
public interface SqliteSampleRepository extends CrudRepository<Book,Long> {
    public @NonNull Optional<Book> findById(Long id);
}

for SqliteSampleController.java

package com.sqlite.example;

import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.MediaType;
import io.micronaut.http.annotation.Body;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.Post;
//import io.micronaut.security.annotation.Secured;
//import io.micronaut.security.rules.SecurityRule;

import java.util.List;
import java.util.Optional;
//@Secured(SecurityRule.IS_ANONYMOUS)
@Controller("/books")
public class SqliteSampleController {
    private final SqliteSampleRepository repository;

    public SqliteSampleController(SqliteSampleRepository repository) {
        this.repository = repository;
    }

    @Post(produces = MediaType.APPLICATION_JSON)
    HttpResponse<Book> create(@Body Book book) { // (1)
        return HttpResponse.status(HttpStatus.CREATED).body(repository.save(book));
    }

    @Get(produces = MediaType.APPLICATION_JSON)
    HttpResponse<List<Book>> get() { // (2)
        return HttpResponse.status(HttpStatus.OK).body(repository.findAll());
    }
}

for Book.java is

package com.sqlite.example;

import io.micronaut.data.annotation.GeneratedValue;
import io.micronaut.data.annotation.Id;
import io.micronaut.data.annotation.MappedEntity;
import io.micronaut.serde.annotation.Serdeable;

@Serdeable
@MappedEntity
public class Book {
    @Id
    @GeneratedValue
    Long id;

    String title;

    String author;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }
}

my application.yaml is

micronaut:
  application:
    name: sqliteApplication
datasources:
  default:
    url: jdbc:sqlite:./mydatabase.db
    username: sa
    password: ''
    driver-class-name: org.sqlite.JDBC
    schema-generate: NONE
    dialect: ANSI
    auto-commit: true
    maximum-pool-size: 1

flyway:
  datasources:
    default:
      enabled: true

my build.gradle is

plugins {
    id("com.github.johnrengelman.shadow") version "8.1.1"
    id("io.micronaut.application") version "4.4.2"
    id("io.micronaut.aot") version "4.4.2"
}

version = "0.1"
group = "com.sqlite.example"

repositories {
    mavenCentral()
}

dependencies {
    annotationProcessor("io.micronaut.data:micronaut-data-processor")
    annotationProcessor("io.micronaut:micronaut-http-validation")
    annotationProcessor("io.micronaut.serde:micronaut-serde-processor")
    implementation("io.micronaut.data:micronaut-data-jdbc")
    implementation("org.xerial:sqlite-jdbc:3.46.1.0")
    implementation("io.micronaut.serde:micronaut-serde-jackson")
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    compileOnly("io.micronaut:micronaut-http-client")
    runtimeOnly("ch.qos.logback:logback-classic")
    implementation("io.micronaut.flyway:micronaut-flyway")
    testImplementation("io.micronaut:micronaut-http-client")
    aotPlugins platform("io.micronaut.platform:micronaut-platform:4.6.2")
    runtimeOnly("org.yaml:snakeyaml")
}


application {
    mainClass = "com.sqlite.example.Application"
}
java {
    sourceCompatibility = JavaVersion.toVersion("21")
    targetCompatibility = JavaVersion.toVersion("21")
}


graalvmNative.toolchainDetection = false

micronaut {
    runtime("netty")
    testRuntime("junit5")
    processing {
        incremental(true)
        annotations("com.sqlite.example.*")
    }
    aot {
        // Please review carefully the optimizations enabled below
        // Check https://micronaut-projects.github.io/micronaut-aot/latest/guide/ for more details
        optimizeServiceLoading = false
        convertYamlToJava = false
        precomputeOperations = true
        cacheEnvironment = true
        optimizeClassLoading = true
        deduceEnvironment = true
        optimizeNetty = true
        replaceLogbackXml = true
        configurationProperties.put("micronaut.security.jwks.enabled","false")
    }
}


tasks.named("dockerfileNative") {
    jdkVersion = "21"
}

Upon bit of research I came to know now in Micronaut 4 when encountering read operations micronaut tries to change the connection to readonly https://github.com/micronaut-projects/micronaut-data/blob/792e8a0d68ee33062cbf2d6d6009aa0a9cc10c59/data-connection/src/main/java/io/micronaut/data/connection/ConnectionOperations.java#L71

I tried setting explict read only also as mentioned here but it lead to another error on application Startup

 io.micronaut.context.exceptions.BeanInstantiationException: Bean definition \[javax.sql.DataSource\] could not be loaded: Unable to check whether table "main"."flyway_schema_history" exists

-------------------------------------------------------------------

SQL State  : null
Error Code : 0
Message    : A statement has already been executed on this connection; cannot upgrade to write transaction

    at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1996)
    at io.micronaut.context.DefaultApplicationContext.initializeContext(DefaultApplicationContext.java:307)
    at io.micronaut.context.DefaultBeanContext.configureAndStartContext(DefaultBeanContext.java:3314)
    at io.micronaut.context.DefaultBeanContext.start(DefaultBeanContext.java:345)
    at io.micronaut.context.DefaultApplicationContext.start(DefaultApplicationContext.java:215)
    at io.micronaut.runtime.Micronaut.start(Micronaut.java:75)
    at io.micronaut.runtime.Micronaut.run(Micronaut.java:334)
    at io.micronaut.runtime.Micronaut.run(Micronaut.java:320)
    at com.sqlite.example.Application.main(Application.java:9)

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

SQL State  : null
Error Code : 0
Message    : A statement has already been executed on this connection; cannot upgrade to write transaction

    at org.flywaydb.core.internal.database.base.Table.exists(Table.java:49)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.exists(JdbcTableSchemaHistory.java:104)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.allAppliedMigrations(JdbcTableSchemaHistory.java:193)
    at org.flywaydb.core.internal.info.MigrationInfoServiceImpl.refresh(MigrationInfoServiceImpl.java:87)
    at org.flywaydb.core.internal.command.DbValidate.lambda$validate$0(DbValidate.java:112)
    at org.flywaydb.core.internal.jdbc.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:59)
    at org.flywaydb.core.internal.command.DbValidate.validate(DbValidate.java:105)
    at org.flywaydb.core.Flyway.doValidate(Flyway.java:451)
    at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:175)
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:210)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:164)
    at io.micronaut.flyway.AbstractFlywayMigration.runFlyway(AbstractFlywayMigration.java:110)
    at io.micronaut.flyway.AbstractFlywayMigration.forceRun(AbstractFlywayMigration.java:93)
    at io.micronaut.flyway.AbstractFlywayMigration.run(AbstractFlywayMigration.java:66)
    at io.micronaut.flyway.DataSourceMigrationRunner.lambda$onCreated$1(DataSourceMigrationRunner.java:65)
    at java.base/java.util.Optional.ifPresent(Optional.java:178)
    at io.micronaut.flyway.DataSourceMigrationRunner.onCreated(DataSourceMigrationRunner.java:63)
    at io.micronaut.flyway.DataSourceMigrationRunner.onCreated(DataSourceMigrationRunner.java:39)
    at io.micronaut.context.DefaultBeanContext.triggerBeanCreatedEventListener(DefaultBeanContext.java:2383)
    at io.micronaut.context.DefaultBeanContext.postBeanCreated(DefaultBeanContext.java:2360)
    at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2304)
    at io.micronaut.context.DefaultBeanContext.doCreateBean(DefaultBeanContext.java:2312)
    at io.micronaut.context.DefaultBeanContext.createRegistration(DefaultBeanContext.java:3123)
    at io.micronaut.context.SingletonScope.getOrCreate(SingletonScope.java:80)
    at io.micronaut.context.DefaultBeanContext.findOrCreateSingletonBeanRegistration(DefaultBeanContext.java:3025)
    at io.micronaut.context.DefaultBeanContext.initializeEagerBean(DefaultBeanContext.java:2689)
    at io.micronaut.context.DefaultBeanContext.initializeContext(DefaultBeanContext.java:1990)
    ... 8 common frames omitted

Caused by: java.sql.SQLException: A statement has already been executed on this connection; cannot upgrade to write transaction
at org.sqlite.jdbc3.JDBC3Connection.tryEnforceTransactionMode(JDBC3Connection.java:64)
at org.sqlite.jdbc3.JDBC3PreparedStatement.executeQuery(JDBC3PreparedStatement.java:84)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.queryForInt(JdbcTemplate.java:111)
at org.flywaydb.core.internal.database.sqlite.SQLiteTable.doExists(SQLiteTable.java:68)
at org.flywaydb.core.internal.database.base.Table.exists(Table.java:47)
... 34 common frames omitted
Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to check whether table "main"."flyway_schema_history" exists

Caused by: java.sql.SQLException: A statement has already been executed on this connection; cannot upgrade to write transaction

PS: Im a bit new to micronaut f/w. I can't change the database to anything other than sqlite . Let me know if any more info is required

I already referred ScalikeJDBC + SQlite: Cannot change read-only flag after establishing a connection . But since application requires both read/write operations I can't open the connection as read-only

Thanks in advance :)