Can we use a second database for javers audit related tables

Question

I will mention my problem.

I need two databases. DB1 for my application tables DB2 for saving only the audited tables jv_...

To solve the problem I did the following

`@Bean
    public ConnectionProvider jpaConnectionProvider() {
        OtherConnectionProvider other = new OtherConnectionProvider();
        try {
            other.setConnection(dataSource().getConnection());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return scp;
    }`

OtherConnectionProvider is a implementation of org.javers.repository.sql.ConnectionProvider. dataSource() is the normal javax.sql.Datasource.

After using this, spring ignores the database properties mentioned in application.properties and creates the schema and javers related tables in this new schema since I have the following in my application.properties. spring.jpa.hibernate.ddl-auto=create

score 0 · Answer 1 · answered Jun 21 '19 at 20:33

Setting dedicated database for Javers audit data is easy for MongoDB (see https://javers.org/documentation/spring-boot-integration/#starter-repository-configuration), but there is no out-of-the box solution for SQL. The main problem is coordinating transactions in two independent SQL databases.

See How to configure transaction management for working with 2 different db in Spring?

score 0 · Accepted Answer · answered Jun 22 '19 at 06:22

Thanks for the reply. I fixed it the following way. See https://www.baeldung.com/spring-data-jpa-multiple-databases. But in the url, it has mentioned about two database configuration. One of the configuration which is Primary should be picked up from application.properties. Second database configuration can be picked up from spring configuration as mentioned in the URL https://javers.org/documentation/spring-integration/#jpa-entity-manager-integration. The solution is tricky enough since the standard properties of spring.datasource are not applicable here. Moreover, addition of commit properties using javers will help. This will act as tenant information. Following is the typical code where CustomJpaHibernateConnectionProvider is the implementation of org.javers.repository.sql.ConnectionProvider

   @Bean
public ConnectionProvider jpaConnectionProvider() {
    CustomJpaHibernateConnectionProvider scp = new 
      CustomJpaHibernateConnectionProvider();
    try {
        scp.setConnection(dataSource().getConnection());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return scp;
}

and the datasource would like this.

 @Bean
    @ConfigurationProperties(prefix="spring.javers-datasource")
    public DataSource dataSource(){
        return DataSourceBuilder.create().build();
    }

The data sources properties are not standard Spring boot properties.

spring.datasource.jdbcUrl = jdbc:postgresql://localhost/test
spring.datasource.username = postgres
spring.datasource.driverClassName=org.postgresql.Driver

Can we use a second database for javers audit related tables

2 Answers2