@Deprecating connectionInitSql and connectionCustomizerClassName

[Brett Wooldridge]

Unfortunately, the "reach" of this group is not very large (currently 175) members, while the userbase of HikariCP is at least 10k users.  Still, we have to announce deprecations somewhere.  In the HikariCP 2.3.0 release, the connectionInitSql and connectionCustomizerClassName properties will be marked as deprecated, and HikariCP will log a WARN-level message if used.

There is no guarantee that they will be removed in the subsequent release of HikariCP, but we are signaling our intention to remove them in the future.  Hopefully, a logged warning will get the attention of anyone who happens to be using them.  A Google search as turned up no uses, but it cannot of course search private code-bases.

[Nick Palmer]

Private codebase person here, who's using connectionInitSql. (also someone who didn't see this initially and only just spotted the WARN).

If it does get removed, what is the alternative for those of us who might want to be run a statement when a connection is fresh to the pool? We currently use it to set a package global (Oracle users here) so that queries that run from that connection can get a reference to something. We also use it to set information on the connection to help get information about our connection pool from the database end, as we don't always have access to all of our app servers in production from one location.

Is there any reason not to use this property? Or is it just being deprecated and possibly removed because you can't see any current code using it and want to keep the codebase clean?

[Brett Wooldridge]

Hi Nick,

Thanks for weighing in on this, it's exactly the kind of feedback we wanted to receive.  TL;DR we will un-deprecate connectionInitSql.

We have several overlapping motivations, some leading to clearer decisions than others, which is why we made that original post.  Let's go through some of them here...  

The first motivation is, as you stated, keeping the codebase clean.  And a corollary to that of vigilance in minimizing the configuration "surface area".  But the counter-balance is that, for users of the library, "simple things should be simple".

The second motivation, related to the first, is the axiom that "features beget feature requests".  An example of that, related to connectionInitSql, was request #152 wherein the user wanted to run multiple statements instead of one.

Finally, and this applies more to connectionCustomizerClassName than connectionInitSql, strange as it may seem for the creators of a connection pool library, we want to avoid features that result in "pool lock-in".  It should be easy for users to switch pools -- not just to HikariCP but away from HikariCP.  In the case of the IConnectionCustomer, as soon as a user implements this interface and their application cannot run without its actions, they have locked their application to HikariCP.  Therefore, we can say with a high degree of confidence that IConnectionCustomer and its related configuration (connectionCustomizerClassName) will be removed.

Before circling back to connectionInitSql, let's consider the case of IConnectionCustomer.  If a user needs to execute some logic, or multiple statements, when a Connection is created then it is clear they will need to implement a class.  Once you've taken that step -- implementing a class -- is there a solution that does not lock the user to HikariCP?  The answer is "yes".  There are two interfaces, either of which the user can implement instead of IConnectionCustomer, that are non-proprietary and portable across every pool -- java.sql.Driver or javax.sql.DataSource.

Through either delegation, proxying, or direct subclassing a user can easily create their own implementation of connect() or getConnection() which, after obtaining a Connection from the real driver, executes whatever logic (or SQL) they wish before handing it off to HikariCP.  Something along the lines of:

public class CustomizedDataSource implements DataSource {

   private DataSource delegate;

   ...

   @Override

   public Connection getConnection() throws SQLException {

      Connection conn = delegate.getConnection();

      // customization code here

      return conn;

   }

}

This is just one solution among several that once written is portable across pools.  It has already been established that the user needs to implement some interface, it is just a question of which.

Now, circling back around to connectionInitSql.  We're going to retract @Deprecated from connectionInitSql for two primary reasons.  First, following the "simple things should be simple" maxim, the burden of implementing a custom class when all that is needed is a single statement execution is too high.  Second, from what we can see every major pool supports a similar construct, it is pure configuration, and therefore movement between pool implementations is not impeded.

[Nick Palmer]

Thanks for the insightful response Brett, and I'm glad to hear connectionInitSql will be sticking around.

I'll pay closer attention to this group in future and keep an eye on our logs from now on too!

[Niels van Klaveren]

We're currently using the Tomcat pool, but are keeping Hikari in mind as a potential up/sidegrade. Keeping connectionInitSql actually makes changing pools easier, since we're heavily using its counterpart initSQL in Tomcat on multiple database platforms at the moment.

Regarding request #152 I've updated the issue with quite an easy workaround to execute multiple statements within connectionInitSql which works on Postgres, MySQL, SQL server and Oracle, and probably other platforms as well.

[Brett Wooldridge]

Niels,

Thanks for the annotations on #152, it will help other users.

-Brett

[Frode Carlsen]

Another private repo user here.

It's unfortunate that the connectionCustomizerClassName is being deprecated and planned to be removed.  This will make it difficult to migrate a number of applications away from other pools onto HikariCP.

For larger applications there are a number of use cases for being able to selectively decide what statements o rcode to run when a connection is checked out of the pool or acquired from the  underlying datasource. Especially in multi-user and multi-tenant systems.

For example, for oracle it's things like altering the current_schema ( which may need to be set dynamically in multi-tenant systems - e.g. to access different workspaces in Oracle, or to separate the database owner from the user accessing it).  If it's static I agree it may be done with connectionInitSql, but that's now always the case.  

Also, a very common use case in larger systems is to set the client identifier to the end-user id so that any call to the database can be traced back to the end-user also on the database side or in database logs (instead of the connection user name).  This helps to match for example long-running queries to the end-users who initiated them.  Yet another is to propagate language and locale details to the database for I18N consideration in search, match and sort.

On Postgresql it could be altering the application name to achieve a similar effect, or set up a search_path to match the logged in user (when running with multiple postgres schemas).

Extending the driver / datasource I think only works when a connection is acquired from the underlying datasource, not when it's checked out from the pool. Also, it would mean implementing an interface on which only 1 or 2 methods needs to be overridden while the other 8-12 methods must be  delegated.

On the other hand, changing connection connection pools I believe (outside of perhaps frameworks) is pretty uncommon, so allowing one to implement one interface to delegate code to run on checkout would certainly be manageable.  At least as long as one has to specify the connection pool in the first place.

My 2pc, I hope it gets retained and it's purpose better understood.

- Frode

[Frode Carlsen]

Forget the above comment.  Not thinking straight on a Friday.

I can of course wrap the HikariDataSource to handle the case where the connection is checked out from the pool.  

So the only hassle is that you need to create a class with a number of delegate methods.  ButI can live with that.

[Brett Wooldridge]

Hi Frode,

(EDIT: formatting is nicer in a gist ... https://gist.github.com/brettwooldridge/dfd21934416fc2a3c865)

I took it as a challenge to see how minimal I could create the artiface around delegation for the purpose of getConnection() interception.  This is what I came up with...

public abstract class InterceptorDataSource {

   private final InvocationHandler handler;

   protected InterceptorDataSource(final DataSource delegate) {

      this.handler = new InvocationHandler() {

         public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {

            return (method.getName().equals("getConnection")) ? getConnection(delegate) : method.invoke(delegate, args);

         }

      };

   }

   protected Connection getConnection(final DataSource delegate) throws SQLException {

      return delegate.getConnection();

   }

   public static DataSource wrapInterceptor(InterceptorDataSource instance) {

      return (DataSource) Proxy.newProxyInstance(instance.getClass().getClassLoader(), new Class[] { DataSource.class }, instance.handler);

   }

}

Given this abstract base class, for example, you could then implement something like this...

HikariDataSource hikariDS = new HikariDataSource();

...

DataSource wrappedDS = InterceptorDataSource.wrapInterceptor(new InterceptorDataSource(hikariDS) {

   @Override

   protected Connection getConnection(DataSource delegate) throws SQLException {

      final Connection c = super.getConnection(delegate);

      System.out.println("Look mom, I'm wrapping HikariDataSource.getConnection() before it is given to the user");

      return c;

   }

});

...

and there after use the wrappedDS.  Off course you could eliminate the abstract class, but the benefit of it is that it has no knowledge of HikariCP, so you can use this interceptor with any pool.  Even cooler, is that the same wrapper could be used to wrap a DataSource passed into HikariCP to intercept connection creation before it is added to the pool!  Like this:

PGSimpleDataSource realDS = new PGSimpleDataSource(); // real DataSource to intercept

DataSource wrappedDS = InterceptorDataSource.wrapInterceptor(new InterceptorDataSource(realDS) {

   @Override

   protected Connection getConnection(DataSource delegate) throws SQLException {

      final Connection c = super.getConnection(delegate);

      System.out.println("Look mom, I'm intercepting PGSimpleDataSource.getConnection() before it reaches the pool");

      return c;

   }

});

HikariDataSource hikariDS = new HikariDataSource();

hikariDS.setDataSource(wrappedDS);

...

-Brett

[Brett Wooldridge]

The formatting of the gist looks nicer...

https://gist.github.com/brettwooldridge/dfd21934416fc2a3c865

-Brett