Dynamic database pool configuration with Vert.x SQL Client

Vert.x SQL Client pro­vides since Vert.x 4.1.1 con­nec­tion cre­ation load bal­anc­ing.

You can cre­ate a pool with a list of con­nect op­tions that the pool uses with a round robin pol­icy when it needs to cre­ate a con­nec­tion.

List<PgConnectOptions> servers = getListOfServers();

PgPool pool = PgPool.create(vertx, servers);

Load bal­anc­ing is in fact an helper in front of a more pow­er­ful fea­ture:

interface Pool {
  ....
  Pool connectionProvider(Function<Context, Future<SqlConnection>> provider);
  ....
}

The connectionProvider is called by the pool when it needs to cre­ate a new con­nec­tion, here is a triv­ial usage:

ConnectionFactory factory = new PgDriver().connectionFactory(vertx, connectOptions);
pool.connectionProvider(ctx -> {
  return factory.connect(ctx);
});

Since the provider is asyn­chro­nous, it can ob­vi­ously be abused to pro­vide dy­namic pool con­fig­u­ra­tion.

pool.connectionProvider(ctx -> {
  Future<PgConnectOptions> fut = retrieveOptions();
  return fut.compose(connectOptions -> {
    // Do not forget to close later
    ConnectionFactory factory = new PgDriver().connectionFactory(vertx, connectOptions);
    return factory.connect(ctx);
  });
});

Im­por­tant caveat, when the con­nec­tion fac­tory be­comes use­less (e.g be­cause of a new con­fig­u­ra­tion) it must be closed to re­lease its re­sources.

The SQL client does not pro­vide out of the box such fea­ture be­cause of the above caveat: it is dif­fi­cult to de­ter­mine when the fac­to­ries needs to be dis­posed. In­stead we ex­pose the connectionProvider to allow ap­pli­ca­tion im­ple­ment it triv­ially since each ap­pli­ca­tion know best how and when re­lease the re­sources.