Quarkus - Panache always use default data source and not the assigned DataSource

Question

I'm trying to do a CRUD to two different data source (PostgreSQL and MySQL) However, it seems even after assigning a @DataSource to the repository class, it still uses the default one.

This is the error I got:

2023-10-27 17:54:28,443 ERROR [io.qua.hib.orm.run.sch.SchemaManagementIntegrator] (Hibernate post-boot validation thread for <default>) Failed to validate Schema: Schema-validation: missing table [cash_postgres]

I assume Quarkus is using the default data source is because cash_postgres did not exist in the MySQL db and only in my PostgreSQL.

I've tried to follow this documentation, and other StackOverflow/Github questions/ticket but still have no clue.

This is my application.properties

quarkus.datasource.db-kind=mysql
quarkus.datasource.username=root
quarkus.datasource.jdbc.url=jdbc:mysql://localhost:3307/mysqldb

quarkus.datasource."mysqldatasource".db-kind=mysql
quarkus.datasource."mysqldatasource".username=root
quarkus.datasource."mysqldatasource".jdbc.url=jdbc:mysql://localhost:3307/mysqldb

quarkus.datasource."postgresqldatasource".db-kind=postgresql
quarkus.datasource."postgresqldatasource".username=user
quarkus.datasource."postgresqldatasource".password=password
quarkus.datasource."postgresqldatasource".jdbc.url=jdbc:postgresql://localhost:5431/postgresdb

quarkus.http.port=8080

This is my Entities:

@Entity
@Table(name = "cash_mysql")
public class CashMySQL {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "amount")
    private int amount;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated_by")
    private String updatedBy;
}

@Entity
@Table(name = "cash_postgres")
public class CashPostgres {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private int id;

    @Column(name = "amount")
    private int amount;

    @Column(name = "create_time")
    private Date createTime;

    @Column(name = "update_time")
    private Date updateTime;

    @Column(name = "created_by")
    private String createdBy;

    @Column(name = "updated_by")
    private String updatedBy;
}

This is my repo

@ApplicationScoped
@DataSource("mysqldatasource")
public class CashMySQLRepository implements PanacheRepository<CashMySQL> {}

@ApplicationScoped
@DataSource("postgresqldatasource")
public class CashPostgresRepository implements PanacheRepository<CashPostgres> {}

This is my resource

@Path("/benchmark")
public class MyResource {

    @Inject
    @DataSource("mysqldatasource")
    CashMySQLRepository cashMySQLRepository;
    @Inject
    @DataSource("postgresqldatasource")
    CashPostgresRepository cashPostgresRepository;
}

Really appreciate any help I can get

Answer 1

What we need to do is

• create two persistence units, one for mySql, one for Postgres,
• move the two Entity classes in separate packages,
• bind the mySql persistence unit to the mySql data source
• bind the package with the mySql Entity to the mySql persistence unit
• bind the postgres persistence unit to the mySql data source
• bind the package with the mySql Entity to the postgres persistence unit.

When we asusme that class CashMySQL lives in package de.turing85.persistence.entity.mysql and class CashPostgres lives in package de.turing85.persistence.entity.postgres, the application.properties could look like this:

quarkus.datasource."mysqldatasource".db-kind=mysql
quarkus.datasource."postgresqldatasource".db-kind=postgresql

quarkus.hibernate-orm."mysql".database.generation=drop-and-create
quarkus.hibernate-orm."mysql".datasource=mysqldatasource
quarkus.hibernate-orm."mysql".packages=de.turing85.persistence.entity.mysql

quarkus.hibernate-orm."postgres".database.generation=drop-and-create
quarkus.hibernate-orm."postgres".datasource=postgresqldatasource
quarkus.hibernate-orm."postgres".packages=de.turing85.persistence.entity.postgres

With a corresponding implementation for our endpoint:

@Path("cash")
@Produces(MediaType.APPLICATION_JSON)
public class CashEndpoint {
  private static final Random RANDOM = new Random();

  private final CashMySQLRepository mySqlRepository;
  private final CashPostgresRepository postgresRepository;

  public CashEndpoint(@DataSource("mysqldatasource") CashMySQLRepository mySqlRepository,
      @DataSource("postgresqldatasource") CashPostgresRepository postgresRepository) {
    this.mySqlRepository = mySqlRepository;
    this.postgresRepository = postgresRepository;
  }


  @POST
  @Transactional
  public int[] generateRandomEntry() {
    Date now = new Date();
    int amount = RANDOM.nextInt(1000);
    String createdBy = "foo" + RANDOM.nextInt();
    String updatedBy = "bar" + RANDOM.nextInt();
    // @formatter:off
    CashMySQL cashMySQL = CashMySQL.builder()
        .amount(amount)
        .createdBy(createdBy)
        .updatedBy(updatedBy)
        .createTime(now)
        .updateTime(now)
        .build();
    // @formatter:on
    mySqlRepository.persist(cashMySQL);

    // @formatter:off
    CashPostgres cashPostgres = CashPostgres.builder()
        .amount(amount)
        .createdBy(createdBy)
        .updatedBy(updatedBy)
        .createTime(now)
        .updateTime(now)
        .build();
    // @formatter:on
    postgresRepository.persist(cashPostgres);
    return new int[] {cashMySQL.getId(), cashPostgres.getId()};
  }

  @GET
  @Path("mysql/{id}")
  public CashMySQL getFromMySqlById(@PathParam("id") long id) {
    return mySqlRepository.findById(id);
  }

  @GET
  @Path("postgres/{id}")
  public CashPostgres getFromPostgresById(@PathParam("id") long id) {
    return postgresRepository.findById(id);
  }
}

and a happy path test:

@QuarkusTest
@TestHTTPEndpoint(CashEndpoint.class)
class CashEndpointTest {

  @Inject
  @DataSource("mysqldatasource")
  CashMySQLRepository mySqlRepository;

  @Inject
  @DataSource("postgresqldatasource")
  CashPostgresRepository postgresRepository;

  @Test
  void testPostAndGet() {
    // @formatter:off
    long[] ids = RestAssured
        .when().post()
        .then()
            .statusCode(Response.Status.OK.getStatusCode())
            .extract().body().as(long[].class);

    CashMySQL cashMySqlFromHttp = RestAssured
        .when().get("/mysql/%d".formatted(ids[0]))
        .then()
            .statusCode(Response.Status.OK.getStatusCode())
            .extract().body().as(CashMySQL.class);
    // @formatter:on
    CashMySQL cashMySqlFromDb = mySqlRepository.findById(ids[0]);
    assertThat(cashMySqlFromHttp).isEqualTo(cashMySqlFromDb);

    // @formatter:off
    CashPostgres cashPostgresFromHttp = RestAssured
        .when().get("/postgres/%d".formatted(ids[0]))
        .then()
            .statusCode(Response.Status.OK.getStatusCode())
            .extract().body().as(CashPostgres.class);
    // @formatter:on
    CashPostgres cashPostgresFromDb = postgresRepository.findById(ids[0]);
    assertThat(cashPostgresFromHttp).isEqualTo(cashPostgresFromDb);
  }
}

we can validate the behaviour.

A full example can be found in this github.com repository.