How to auto create database and table when use TenantConnectionResolver to dynamic datasource?

Question

In quarkus application.properties

quarkus.datasource.jdbc.url=jdbc:postgresql://192.168.100.110:5432/test
quarkus.datasource.username=root
quarkus.datasource.password=root
quarkus.hibernate-orm.database.generation=update
quarkus.hibernate-orm.multitenant=DATABASE

And use TenantResolver to resolve tenantId

@PersistenceUnitExtension
@RequestScoped
public class CustomTenantResolver implements TenantResolver {

  @Override
  public String getDefaultTenantId() {
    return TenantUtil.NONE.toString();
  }

  @Override
  public String resolveTenantId() {
    return TenantUtil.getTenantId().toString();
  }
}

Then use TenantConnectionResolver to resolve dataSource

@ApplicationScoped
@PersistenceUnitExtension
public class DataSourceTenantConnectionResolver implements TenantConnectionResolver {

  @ConfigProperty(name = "quarkus.datasource.jdbc.url")
  String url;

  @ConfigProperty(name = "quarkus.datasource.username")
  String username;

  @ConfigProperty(name = "quarkus.datasource.password")
  String password;

  private final Map<String, ConnectionProvider> map = new HashMap<>();

  @Override
  public ConnectionProvider resolve(String tenantId) {
    System.out.println("resolve ConnectionProvider " + tenantId);
    if (map.containsKey(tenantId)) {
      return map.get(tenantId);
    }
    ConnectionProvider provider;
    if (tenantId.equals(TenantUtil.NONE)) {
      provider = new QuarkusConnectionProvider(createDataSource(url, "test", username, password));
    } else {
      provider =
          new QuarkusConnectionProvider(
              createDataSource(
                  "jdbc:postgresql://192.168.100.110:5432/tenant"
                      + tenantId
                      + "?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&createDatabaseIfNotExist=true",
                  "tenant" + tenantId,
                  "root",
                  "root"));
    }
    map.put(tenantId, provider);
    return provider;
  }

  private AgroalDataSource createDataSource(
      String url, String database, String username, String password) {
    try {
      AgroalDataSourceConfigurationSupplier configurationSupplier =
          new AgroalDataSourceConfigurationSupplier();
      AgroalConnectionPoolConfigurationSupplier connectionPoolConfig =
          configurationSupplier.connectionPoolConfiguration();
      connectionPoolConfig.maxSize(1000);

      AgroalConnectionFactoryConfigurationSupplier connectionFactoryConfig =
          connectionPoolConfig.connectionFactoryConfiguration();
      connectionFactoryConfig.jdbcUrl(url);
      connectionFactoryConfig.principal(new NamePrincipal(username));
      connectionFactoryConfig.credential(new SimplePassword(password));
      connectionFactoryConfig.initialSql("CREATE DATABASE IF NOT EXISTS " + database);

      return AgroalDataSource.from(configurationSupplier.get());
    } catch (SQLException | RuntimeException e) {
      throw new IllegalStateException("Exception while creating datasource for " + url, e);
    }
  }
}

The question 1

Unable to automatically create a database through jdbcUrl of createDatabaseIfNotExist=true. Is this the expected behavior? So i have use to connectionFactoryConfig.initialSql("CREATE DATABASE IF NOT EXISTS " + database)

The question 2

How to automatically create tables like quarkus.hibernate-orm.database.generation=update ?

Answer 1

Unable to automatically create a database through jdbcUrl of createDatabaseIfNotExist=true. Is this the expected behavior? So i have use to connectionFactoryConfig.initialSql("CREATE DATABASE IF NOT EXISTS " + database)

See https://stackoverflow.com/a/73669197/6692043

How to automatically create tables like quarkus.hibernate-orm.database.generation=update ?

I'm not sure you can directly call Hibernate ORM to do that, at least not without a significant effort, since most of those tools are designed with single-tenancy in mind.

I'd recommend just dumping Hibernate ORM's schema initialization scripts, preferably while you develop the application, and manually applying those whenever you connect to a new datasource (and you detect it's empty).

Ideally, you'd use Flyway for such things. But given you're using dynamic datasources, you probably can't rely on Quarkus' Flyway object injection and will have to build the Flyway object manually.