Repositories do not work with SQLite

[APXEOLOG]

Expected Behavior

Both read and write methods should be executable from the Repository (i.e., findAll() and deleteAll()) while using SQLite

Actual Behaviour

An error occurs during execution

io.micronaut.data.connection.exceptions.ConnectionException: Could not set JDBC Connection [org.sqlite.jdbc4.JDBC4Connection@6c63e2b6] read-only: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection().
	at io.micronaut.data.connection.support.JdbcConnectionUtils.setConnectionReadOnly(JdbcConnectionUtils.java:170)
	at io.micronaut.data.connection.support.JdbcConnectionUtils.applyReadOnly(JdbcConnectionUtils.java:75)
	at io.micronaut.data.connection.jdbc.operations.DefaultDataSourceConnectionOperations.lambda$setupConnection$0(DefaultDataSourceConnectionOperations.java:67)
	at java.base/java.util.Optional.ifPresent(Optional.java:178)
	at io.micronaut.data.connection.jdbc.operations.DefaultDataSourceConnectionOperations.setupConnection(DefaultDataSourceConnectionOperations.java:65)
	at io.micronaut.data.connection.support.AbstractConnectionOperations.executeWithNewConnection(AbstractConnectionOperations.java:166)
	at io.micronaut.data.connection.support.AbstractConnectionOperations.execute(AbstractConnectionOperations.java:114)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.executeRead(DefaultJdbcRepositoryOperations.java:816)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findAll(DefaultJdbcRepositoryOperations.java:557)
	at io.micronaut.data.jdbc.operations.DefaultJdbcRepositoryOperations.findAll(DefaultJdbcRepositoryOperations.java:145)
	at io.micronaut.data.runtime.intercept.DefaultFindAllInterceptor.intercept(DefaultFindAllInterceptor.java:49)
	at io.micronaut.data.runtime.intercept.DataIntroductionAdvice.intercept(DataIntroductionAdvice.java:84)
	at io.micronaut.aop.chain.MethodInterceptorChain.proceed(MethodInterceptorChain.java:143)
	at com.apxeolog.olympus.repository.GridReadRepository$Intercepted.findByGridIdIn(Unknown Source)
	at com.apxeolog.olympus.service.GridRelationService.updateGridRelationsBatch(GridRelationService.java:95)
	at com.apxeolog.olympus.util.TaskProcessor.processLoop(TaskProcessor.java:91)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:545)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:328)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1090)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:614)
	at java.base/java.lang.Thread.run(Thread.java:1474)
Caused by: java.sql.SQLException: Cannot change read-only flag after establishing a connection. Use SQLiteConfig#setReadOnly and SQLiteConfig.createConnection().
	at org.sqlite.jdbc3.JDBC3Connection.setReadOnly(JDBC3Connection.java:146)
	at io.micronaut.data.connection.support.JdbcConnectionUtils.setConnectionReadOnly(JdbcConnectionUtils.java:167)

Steps To Reproduce

1. Simple SQLite setup

... micronaut 4.9.4
annotationProcessor("io.micronaut.data:micronaut-data-processor")
implementation("io.micronaut.data:micronaut-data-jdbc")
implementation("io.micronaut.sql:micronaut-jdbc-hikari")

implementation("org.xerial:sqlite-jdbc:3.50.3.0")

datasources:
  default:
    url: jdbc:sqlite:./data/test.db?journal_mode=WAL&synchronous=NORMAL&cache_size=-32000&temp_store=MEMORY&busy_timeout=5000
    driverClassName: org.sqlite.JDBC
    dialect: H2
    hikari:
      maximum-pool-size: 1
      minimum-idle: 0

@JdbcRepository(dialect = Dialect.H2)
public interface TestRepository extends CrudRepository<Entity, Long> {

}

2. Call different (read/write) repository methods

testRepository.deleteAll();
testRepository.findAll();

---

The problem here is that Repository forces a specific connection state, i.e., (call chain):

@NonNull
    @Override
    public <T, R> List<R> findAll(@NonNull PreparedQuery<T, R> preparedQuery) {
        SqlPreparedQuery<T, R> sqlPreparedQuery = getSqlPreparedQuery(preparedQuery);
        return executeRead(connection -> findAll(connection, sqlPreparedQuery, true), sqlPreparedQuery.getInvocationContext());
    }


...

private <I> I executeRead(Function<Connection, I> fn, AnnotationMetadata annotationMetadata) {
        if (!jdbcConfiguration.isAllowConnectionPerOperation() && connectionOperations.findConnectionStatus().isEmpty()) {
            throw connectionNotFoundAndNewNotAllowed();
        }
        return connectionOperations.execute(ConnectionDefinition.READ_ONLY.withAnnotationMetadata(annotationMetadata), status -> {
            Connection connection = status.getConnection();
            applySchema(connection);
            return fn.apply(connection);
        });
    }

...


@Override
    protected void setupConnection(ConnectionStatus<Connection> connectionStatus) {
        connectionStatus.getDefinition().isReadOnly().ifPresent(readOnly -> {
            List<Runnable> onCompleteCallbacks = new ArrayList<>(1);
            JdbcConnectionUtils.applyReadOnly(LOG, connectionStatus.getConnection(), readOnly, onCompleteCallbacks);
            if (!onCompleteCallbacks.isEmpty()) {
                connectionStatus.registerSynchronization(new ConnectionSynchronization() {
                    @Override
                    public void executionComplete() {
                        for (Runnable onCompleteCallback : onCompleteCallbacks) {
                            onCompleteCallback.run();
                        }
                    }
                });
            }
        });
    }

...


public static void applyReadOnly(Logger logger,
                                     Connection connection,
                                     boolean isReadOnly,
                                     List<Runnable> onCompleteCallbacks) {
        boolean connectionReadOnly = isReadOnly(connection);
        if (connectionReadOnly != isReadOnly) {
            setConnectionReadOnly(logger, connection, isReadOnly);
            onCompleteCallbacks.add(() -> setConnectionReadOnly(logger, connection, connectionReadOnly));
        }
    }

If the current connection state does not match the one enforced by the Repository operation, it would call connection.setReadOnly which is not supported by the SQLite driver.
Naturally, if you plan to write anything, you would not create a read-only connection from the beginning, so any READ method from the repository would result in the error

Environment Information

• Windows 10
• OpenJDK 25

Example Application

No response

Version

4.9.4

[APXEOLOG]

I was able to workaround it by disabling the setReadOnly method (through providing a custom connection Proxy).
Below is the code:

import com.zaxxer.hikari.HikariDataSource;
import io.micronaut.configuration.jdbc.hikari.DatasourceConfiguration;
import io.micronaut.context.event.BeanCreatedEvent;
import io.micronaut.context.event.BeanCreatedEventListener;
import io.micronaut.core.annotation.NonNull;
import io.micronaut.core.annotation.Order;
import io.micronaut.core.order.Ordered;
import jakarta.inject.Named;
import jakarta.inject.Singleton;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

import javax.sql.DataSource;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.SQLException;

@Order(Ordered.HIGHEST_PRECEDENCE)
@Singleton
public class SQLiteDataSourceListener implements BeanCreatedEventListener<DataSource> {
    private static final Logger log = LoggerFactory.getLogger(SQLiteDataSourceListener.class);

    private final DatasourceConfiguration datasourceConfig;

    public SQLiteDataSourceListener(@Named("default") DatasourceConfiguration datasourceConfig) {
        this.datasourceConfig = datasourceConfig;
    }

    @Override
    public DataSource onCreated(@NonNull BeanCreatedEvent<DataSource> event) {
        // Configure SQLite-specific settings
        SQLiteConfig config = new SQLiteConfig();
        config.setJournalMode(SQLiteConfig.JournalMode.WAL);
        config.setSynchronous(SQLiteConfig.SynchronousMode.NORMAL);
        config.setTempStore(SQLiteConfig.TempStore.MEMORY);
        config.setCacheSize(-32000);
        config.setBusyTimeout(5000); // 5 seconds for highly concurrent scenarios
        config.setTransactionMode(SQLiteConfig.TransactionMode.DEFERRED);

        // Additional optimizations for concurrent access
        config.setLockingMode(SQLiteConfig.LockingMode.NORMAL); // Allow multiple connections
        config.setSharedCache(false); // Disable shared cache for better concurrency

        // Create custom SQLite DataSource
        SQLiteDataSource dataSource = new CustomSQLiteDataSource(config);
        dataSource.setUrl(datasourceConfig.getUrl());

        // Close HikariDataSource that has already been created (i.e., shutdown connection pool)
        if (event.getBean() instanceof HikariDataSource hds) {
            hds.close();
        }

        log.debug("Replaced DataSource {} with a custom SQLiteDataSource", event.getBean());

        return dataSource;
    }

    private static class CustomSQLiteDataSource extends SQLiteDataSource {
        public CustomSQLiteDataSource(SQLiteConfig config) {
            super(config);
        }

        @Override
        public Connection getConnection() throws SQLException {
            return wrapConnection(super.getConnection());
        }

        private Connection wrapConnection(Connection realConnection) {
            return (Connection) Proxy.newProxyInstance(Connection.class.getClassLoader(), new Class<?>[]{ Connection.class }, new ConnectionProxy(realConnection));
        }
    }

    private record ConnectionProxy(Connection realConnection) implements InvocationHandler {

        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            String methodName = method.getName();

            // Log important lifecycle methods
            log.trace("Invoking {}({}) on {}", methodName, args, realConnection);

            // Silently ignore setReadOnly() calls - SQLite doesn't support it
            if ("setReadOnly".equals(methodName)) {
                return null;
            }

            // Delegate all other calls to real connection
            try {
                return method.invoke(realConnection, args);
            } catch (InvocationTargetException e) {
                // Unwrap exceptions from the proxy
                Throwable cause = e.getCause();
                log.trace("Invocation of {}({}) on {} resulted in exception: {}", methodName, args, realConnection, cause.getMessage());
                throw cause;
            }
        }
    }
}