If you are abstracting the database provider, you can move up caching to the handler level. This will mean that we can use Google CacheService to cache queries before we even worry about which flavor of database we are using. In fact, I usually find that I add caching later as an afterthought, but implementing it up front makes everything a lot more straightforward to test.

You can get this library at M3reA5eBxtwxSqCEgPywb9ai_d-phDA33 Note that if you use this library, you will be, by default, sharing the cache associated with this library. If you want to keep your cached data private you can pass a cache service to use in the constructor call. Here’s an example of using a cache specific to this user/script combination. 

var cacheHandler = new CacheHandler.CacheHandler(expiry,cacheSilo,false,false,CacheService.getUserCache(),"somecachecommunitykey");

Creating a key

The cache service is really straightforward – you write a key/value pair, and later you check to see if that key/value pair is in cache. If it is use it, if not, go back to the source of the data and refresh the cache copy again. Creating a key that defines the data you are caching can be little more tricky though. The key should be based on the criteria you use to retrieve the data – whether that be a url, or a database query or some other criteria. Building caching into the database abstraction layer, means that you can automate key creation based on the query being performed.


Let’s say you do a noSQL query, {siloId:’myTable’,region:’europe’} – then we need to make a key out of that to be able to store it in cache, so that when the same query comes through, we check cache first. Delegating the key creation to some repeatable process inside the query method means that we can be sure the cache matches the query. So first of all, let’s look at how we’d use this from within a data Handler. Since this is an abstracted handler, the actual database access is via the selected ‘driver’ object, which has common methods such as getObjectsByQuery(), and which it translates into the native equivalent for the database being used. Here, we would take two objects queryOb, and queryParams – example values might be {siloId:’myTable’,region:’europe’} and {sort:’country’}. If it finds it in cache it will use it, otherwise it will go and do a database query.  /**   * DataHandler.getObjectsByQuery()   * @param {object} queryOb some query object    * @param {object} queryParams additional query parameters (if available)   * @param {boolean}  noCache whether to use cache   * @return {object} results from selected handler   **/  self.getObjectsByQuery = function (queryOb,queryParams,noCache) {    var cached;    if (!noCache) {      cached = cacheHandler.getCache(queryOb,queryParams);    }    if (cached) {      return self.makeResults(enums.CODE.CACHE,undefined,cached);    }    else {      var result = driver.getObjectsByQuery(queryOb,queryParams,noCache);      cacheHandler.putCache (result.dataResult,queryOb,queryParams);      return result;    }  }; On initialization of this class, we would have set up a cacheHandler object with our preferred timeout, and some key to limit it to this database. var cacheHandler = new cCacheHandler.CacheHandler(enums.SETTINGS.CACHEEXPIRY,siloId+type.toString()+driverSpecific.toString(),false);

Here is the full code of the cache Handler


Since the number of arguments can be variable, we have to use a couple of lesser used javaScript tricks. Let’s start with generating a key from an unrestricted number of mixed objects and string arguments.    /**   * cCacheHandler.generateCacheKey()   * @param {object,..} queryOb some objects.   * @return {string} some one way encrypted version of text   **/  self.generateCacheKey = function () {    var s = siloId;    for (var i = 0; i < arguments.length; i++) {      if(arguments[i]) {         s+= ((typeof arguments[i]  === "object" ) ? JSON.stringify(arguments[i]) : arguments[i].toString()) + i.toString();      }    }    return Utilities.base64Encode( Utilities.computeDigest( Utilities.DigestAlgorithm.MD2, s));  }  This was passed on class intialization, and is some arbitrary string that uniquely identifies this database (to distinguish it from other identical queries on different databases)     var s = siloId; arguments is a special array-like object that is available in all javascript functions, and it can be used to access each of the arguments that were passed to it (since we don’t know how many there would be). So here we have to  build up a string that is a concatenation of the string representation of all the objects and strings that are in arguments, along with their position.     for (var i = 0; i < arguments.length; i++) {      if(arguments[i]) {         s+= ((typeof arguments[i]  === "object" ) ? JSON.stringify(arguments[i]) : arguments[i].toString()) + i.toString();      }    } By the time we get here, it could be that the string would be too long for a key (there is a limit). However, we can do a little hack using an encryption algorithm which will limit the key length and come up with something unique, then convert it to base64 encoding.     return Utilities.base64Encode( Utilities.computeDigest( Utilities.DigestAlgorithm.MD2, s)); The key will look something like this RBBa0/VJau76ycjUoQMB+w==, but you will never need to know it, since it is generated systematically as a by product of the query.


This will take an object containing data to be retrieved, along with some number of arguments. The objective here is convert the arguments object, from an array-like object, to an actual array – so we can capture it – then back again to a list of arguments of the generateCacheKey() method.   /**   * cCacheHandler.getCache()   * @param {object,..} queryOb some objects/text from which to generate a key.   * @return {object} the object   **/  self.getCache = function (ob) {    // this is how i can pass on the variable number of args    var s = self.generateCacheKey.apply (null, Array.prototype.slice.call(arguments));    var o = cache.get (s);    return o ? JSON.parse(o) : null;  } To understand how .slice.call can do this, you first need to think about how slice operates. If you take a look at this shim for .slice you’ll see that it iterates through an array, and the key point is that ‘this’, refers to the array it is iterating through. using .call on the Array.prototype allows you to use the slice function, but to specify a different ‘this’. var s = self.generateCacheKey.apply (null, Array.prototype.slice.call(arguments)); In other words someArray.slice() would return a copy of someArray because ‘this’ used within slice would apply to someArray. We can’t say arguments.slice(), because arguments isn’t exactly and array. But it’s close enough to being an array for the slice function to think it is, so we can coerce .slice() to operate on arguments by calling it, and passing arguments to it as  Array.prototype.slice.call(arguments). The effect will be that .slice() happily copies arguments to a real array. By copying it to an array, we now can use it to pass on to another function. However, that function is expecting an argument list, not an array. So we have to reverse what we just did by using .apply(). Like .call() we can change what ‘this’ means with .apply(), but we don’t need to do that, so we pass null as the first argument. The array that used to be arguments is now converted back to an argument list and passed on. To read more about apply, see Advanced Array functions 


The tricks with .apply() & .call()  are also used here. In addition, there is one mandatory argument – namely the object that needs to be written. That means we need to get it out of the way when passing the key objects over to generateCacheKey(). We can use .shift() to get rid of the first (index 0) element in an array – so after having converted arguments to an array we just .shift() it to ignore the data object argument    * cCacheHandler.putCache()   * @param {object} ob data to put to cache   * @param {object,..} queryOb some objects/text from which to generate a key.   * @return {string} some one way encrypted version of text   **/  self.putCache = function (ob) {    // this is how i can pass on the variable number of args, minus the first one    var a= Array.prototype.slice.call(arguments);    a.shift();    var s = self.generateCacheKey.apply (null, a);    cache.put ( s,JSON.stringify(ob),expiry);    return s;  }


You will want to be able to invalidate a cache (for example if you delete or insert something). As usual we are using .call() and .apply() to play around with the arguments so we can pass them on to get a cache key. 

   /**   * cCacheHandler.removeCache()   * @param {object,..} ob data to put to cache   * @param {object,..} queryOb some objects/text from which to generate a key.   * @return {void}    **/  self.removeCache = function () {    // this is how i can pass on the variable number of args    var s = self.generateCacheKey.apply (null, Array.prototype.slice.call(arguments));    cache.remove(s);  } Sometime you will want to deal with cache sizes bigger than that allowed by the cacheservice. These are handled automatically by the cache handler. For how, see Working with big caches For more on this topic see Database abstraction with google apps script You can get this library at MXhfe1Z1GKU-_Qnd94LqcsKi_d-phDA33