AbstractRoutingDataSource + JPA won't create table except defaultTargetDataSource

Question

I use JPA annotations (Hibernate implementation) to initialize my DB schema. And i follow the article DYNAMIC DATASOURCE ROUTING to implement the dynamic datasource routing class.

However, i have two databases (mapped 2 data sources). I set the first data source as defaultTargetDataSource. then start my application. When my application try to access 2nd data source, it tell me the table doesn't exist. It seems AbstractRoutingDataSource only create the table for the default data source but other data sources.

Is there any idea to create schema in all databases ?

PS.I'm using AbstractRoutingDataSource to implement my own DB shards.

Answer 1

I guess that you are using the hibenate configuration:

spring:
  jpa:
    hibernate:
      ddl-auto: update

to reflect the entity changes to the database schema. This works fine as long as we use a single data source that is configured to be connected at startup.

However, if you have multiple data sources it is not possible to use this feature. The general approach with AbstractRoutingDataSource is to not have a data source at startup but select it at runtime.

If you select a primary data source, then it will be only applied to the primary one as hibernates applies this feature at startup, but the remaining databases will not be migrated.

To reflect the changes to all of your databases you can use a database migration tool such as Flyway or Liquibase.

Flyway is using SQL and pretty easy to configure and use to use.