Recently I was working on a project where we wanted to reuse our code for multiple customers. The only difference: they all have their own database where their data was stored. We can do this in two different ways

  • deploy a new application for every customer. For a smal amount of customers that might be acceptable.
  • Based on the url we can detect which datasource we need for a customer. If we would be using the same url for all customers, we can have some central database which keeps all kind of information including datasource name for each individual customer. We could store the datasource name in a private request collection variable called mainDatasource (or put it in some user specific cache or session variable), so each customer has his own datasource.

Since our project was existing code, I have a few hundred references to <cfquery> and queryExecute and many queryBuilder (qb) calls for newer code.

When working with a default datasource we did not have to specify it this code, but now we want to be more specific, so we had to do <cfquery datasource="#prc.mainDatasource#"> in many places. QueryExecute has a signature of queryExecute( sql, params, options ) where params and options are completely optional. So we had to search for every single queryExecute call and change it to queryExecute(sql, params, {datasource: prc.mainDatasource} ) in many places.

For queryBuilder this wasn’t such a boring and repetitive job. Of course I could find every qb instance and add a setDatasource call like this

qb.from("sometable" )
  .get( options = { datasource : prc.mainDatasource } )

but thats still a lot of work. But qb has interceptors, and until now I thought this was only useful for debugging. But no. It is quite powerful and the preQBExecute interception opens up the possibility to change query options before it is executed. So we created this interceptor:

component {
  /**
   * Configure the interceptor 
   /*QbDynamicDatasourceInterceptor
   */
  void function configure(){
  }
  /**
   * preQBExecute
   */
  function preQBExecute( event, data, buffer, rc, prc )
  {
    var thisDataSource = 
      event.getPrivateValue( "mainDatasource","" );
	if ( len( thisDataSource) && 
       !data.options.keyExists("dataSource") ){
	  data.options["dataSource"] = thisDataSource;
	}
  }
}

Since the data in this interception exposes the query options it is almost trivial to change the datasource. we just added an extra check so the datasource was not changed it it already was explicitly named. We only had to register our interceptor in the coldbox config like this

interceptors = [
  {
   class: "interceptors.QbDynamicDatasourceInterceptor"
  },
// ..and more

So I already liked interceptors, but now even more.

There was one more job to be done in our application. We also had many ORM objects which were coupled to our default datasource. We decided to replace them by quick ORM objects, and although it required some more work, in the end we found an easy way to connect our quick objects to a dynamic datasource. I will explain how in part 2 of my dynamic datasources project.