Support for read-only connection datasource

[seetharamani]

I have 2 datasources defined in my application. One as a primary datasource and another as a read-only datasource.

@Configuration
@EnableJpaRepositories(
    basePackages = [
        "com.test.db"
    ],
    excludeFilters = [ComponentScan.Filter(ReadOnlyRepository::class)],
    entityManagerFactoryRef = "entityManagerFactory"
)
class PrimaryDatabaseConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource")
    fun dataSourceProperties(): DataSourceProperties {
        return DataSourceProperties()
    }

    // Useful references - https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-configure-a-datasource
    @Bean(name = ["primaryDataSource"])
    @Primary
    @ConfigurationProperties("spring.datasource.configuration")
    fun primaryDataSource(dataSourceProperties: DataSourceProperties): HikariDataSource {
        return dataSourceProperties.initializeDataSourceBuilder()
            .type(HikariDataSource::class.java)
            .build()
    }

    @Bean
    @Primary
    fun entityManagerFactory(
        builder: EntityManagerFactoryBuilder,
        @Qualifier("primaryDataSource") primaryDataSource: DataSource
    ): LocalContainerEntityManagerFactoryBean {
        return builder
            .dataSource(primaryDataSource)
            .packages(
                "com.test.db",
                "com.test.graphql.resolver"
            )
            .persistenceUnit("primary")
            .build()
    }
}

@Configuration
@EnableJpaRepositories(
    basePackages = [
        "com.test.db",
        "com.test.graphql.resolver"
    ],
    includeFilters = [ComponentScan.Filter(ReadOnlyRepository::class)],
    entityManagerFactoryRef = "readOnlyEntityManagerFactory"
)
class ReadOnlyDatabaseConfiguration {

    @Bean(name = ["readOnlyDataSource"])
    fun readOnlyDataSource(dataSourceProperties: DataSourceProperties): HikariDataSource {
        val readOnlyDataSource = dataSourceProperties
            .initializeDataSourceBuilder()
            .type(HikariDataSource::class.java)
            .build()

        readOnlyDataSource.isReadOnly = true
        readOnlyDataSource.connection.isReadOnly = true
        return readOnlyDataSource
    }

    @Bean
    fun readOnlyEntityManagerFactory(
        builder: EntityManagerFactoryBuilder,
        @Qualifier("readOnlyDataSource") readOnlyDataSource: DataSource
    ): LocalContainerEntityManagerFactoryBean {
        return builder
            .dataSource(readOnlyDataSource)
            .packages(
                "com.test.db",
                "com.test.graphql.resolver"
            )
            .persistenceUnit("readOnly")
            .build()
    }
}

In my application test yaml file I have configured the following properties

spring:
  datasource:
    url: jdbc:postgresql://127.0.0.1:5432/app
    username: postgres
    password:
  jpa:
    show-sql: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL95Dialect
        jdbc:
          lob:
            non_contextual_creation: true

Few JPA repositories are configured to use read-only datasource using the @ReadOnlyRepository annotation.

Annotations on my IT case as follows

@ExtendWith(SpringExtension::class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureEmbeddedDatabases(
    AutoConfigureEmbeddedDatabase(beanName = "primaryDataSource"),
    AutoConfigureEmbeddedDatabase(beanName = "readOnlyDataSource")
)

The issue here is I am getting the following error while instantiating the readOnlyDataSource

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.zaxxer.hikari.HikariDataSource]: Factory method 'readOnlyDataSource' threw exception; nested exception is org.postgresql.util.PSQLException: FATAL: database "app" does not exist

I have verified that this issue occurs only when the datasource is set as read-only

readOnlyDataSource.isReadOnly = true
readOnlyDataSource.connection.isReadOnly = true

I do have flyway migrations and I can see the logs saying my migrations are completed using the primaryDataSource since I have it marked as @Primary

"message":"Successfully applied 15 migrations to schema \"public\" (execution time 00:00.163s)","logger_name":"org.flywaydb.core.internal.command.DbMigrate","thread_name":"prefetching-3","level":"INFO","level_value":20000}

When the second DB gets created, I can also see that migrations are checked but wasn't run

"message":"Schema \"public\" is up to date. No migration necessary.","logger_name":"org.flywaydb.core.internal.command.DbMigrate","thread_name":"main","level":"INFO","level_value":20000}

If I remove the readonly properties, then it works fine. If I remove the @ReadOnlyRepository annotation, which means all my JPA repos uses primary read write connection then IT case works fine.

This made me think that, read only datasource connection is not working well with embedded postgres. Looking for some help here!

[seetharamani]

I just noticed and also as per the documentation as well, the actual datasources created from the application context gets replaced with embedded datasource.

{"@timestamp":"2020-07-29T18:05:01.178-04:00","@version":"1","message":"Replacing 'primaryDataSource' DataSource bean with embedded version","logger_name":"io.zonky.test.db.postgres.EmbeddedPostgresContextCustomizerFactory","thread_name":"main","level":"INFO","level_value":20000}
{"@timestamp":"2020-07-29T18:05:01.180-04:00","@version":"1","message":"Replacing 'readOnlyDataSource' DataSource bean with embedded version","logger_name":"io.zonky.test.db.postgres.EmbeddedPostgresContextCustomizerFactory","thread_name":"main","level":"INFO","level_value":20000}

So in this case, since I have marked the particular Repository with @ReadOnlyRepository which is being recognized only by the readOnlyDataSource using the following filter configuration,

@EnableJpaRepositories(
    basePackages = [
        "com.test.db",
        "com.test.graphql.resolver"
    ],
    includeFilters = [ComponentScan.Filter(ReadOnlyRepository::class)],
    entityManagerFactoryRef = "readOnlyEntityManagerFactory"
)

the embedded datasource would have no knowledge of that repository. And so I am getting error that, the particular relation accessed by that repository doesn't exists.

Caused by: org.postgresql.util.PSQLException: 
ERROR: relation "commands_and_responses" does not exist

[tomix26]

I tried to reproduce the reported issue but everything works as expected.

First of all, setting read-only flags in the ReadOnlyDatabaseConfiguration#readOnlyDataSource method should have no effect. Because if an embedded database is used, this factory method should not be called at all. So, could you please check again if this change has any effect or not?

The only problem I came across was missing data in the database. That happens because using two @AutoConfigureEmbeddedDatabase annotations creates two independent databases. Whereas Flyway initializes only a primary database by default, so the read-only database is empty. It corresponds to the last error you reported: ERROR: relation "commands_and_responses" does not exist.

If I understood your configuration correctly, both data sources connect to the same target database. The first data source is a standard one and the second one is for read-only access. So I would suggest doing integration tests in the same way. Just use a single @AutoConfigureEmbeddedDatabase annotation to create only a single database, and the read-only data source can be a wrapper of the standard one. There is an example: https://github.com/tomix26/embedded-database-demo/blob/readonly-datasource/src/test/java/com/github/tomix26/embedded/database/demo/SingleDataSourceWithReadOnlyWrapperTest.java

In that case the ReadOnlyDataSourceWrapper only wraps the primary data source and sets the read-only flag before a connection is returned.

[tomix26]

No response, I'm closing the issue.