Services‎ > ‎Desktop Liberation‎ > ‎

Database abstraction with google apps script

Sometimes you start with a particular database, or a spreadsheet, then you decide to use something else, and you have to refactor all your code. This is a Google Apps Script handler for database abstraction (with of course a heavy focus on Google options), to try to avoid some of that pain. 

The cDataHandler library includes all known back end drivers. If you just want to limit to a specific driver (avoids unnecessary permission authorizations) - you can use the cDbAbstraction library instead - in fact I recommend you use DbAbstraction instead of DataHandler where possible.

cDataHandler uses cDbAbstraction so the mechanics are the same.  There are some examples of getting started with this topic in the do something useful with GAS in 5 minutes series if you want to get right down to playing.

To get an up to date list of all library dependencies, click below.

https://script.google.com/macros/s/AKfycbwZ2Hht93wTNzvRmYINYF7obaOHciBXWcP_wAiEtyGq70_x3cI/exec




Here's what it looks like.


Quickstart

getting started with dbabstraction



Here's the first app built with this. The client (which has no idea what database it is using) is javascript, the data is stored on parse.com and served up from a Google Apps Script webapp. Take a moment to register what you mainly use from Apps Script. You can find a write up on this and other apps at Apps built with data abstraction


Primer


Example

The best way is to look at an example. 

For this you'll need to create a spreadsheet, along with an empty sheet. You pass the ssid and sheet name  (known as the siloId)  in the following example. You'll also need the library
  • Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j

Let's say you want to use a spreadsheet as a database to start with, but may later graduate to something else. Here's how you would write some data to a spreadsheet using this framework.

function generateFreshTestData () {
  
  var data = [{country:"England",region:"Europe",dc:44},
              {country:"Scotland",region:"Europe",dc:44},
              {country:"China",region:"Asia",dc:86},
              {country:"Canada",region:"North America",dc:1},
              {country:"United States",region:"North America",dc:1},
              {country:"Japan",region:"Asia",dc:81}
             ]
  var siloId= 'abstractdb';
  var ssid = '12pTwh5Wzg0W4ZnGBiUI3yZY8QFoNI8NNx_oCPynjGYY';
  var handler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SHEET,undefined,ssid);
  
  if (!handler.isHappy()) { 
    Logger.log ('couldnt open sheet:' + siloId + ' or spreadhseet:' + ssid);
  }
  
  var result;

  // delete everything on the sheet
  result = handler.remove();
  if (result.handleError < 0) {
    Logger.log(JSON.stringify(result));
  }
  
  // make some new data
  result = handler.save(data);
  if (result.handleError < 0) {
    Logger.log(JSON.stringify(result));
  }

  return result;

}

That would give us a sheet that looks like this.

country region dc
England Europe 44
Scotland Europe 44
China Asia 86
Canada North America 1
United States North America 1
Japan Asia 81

Nothing fancy there, but let's say we have a change of mind, and want to use a database instead of a worksheet, lets say ScriptDb.

function generateFreshTestData () {
  
    var data = [{country:"England",region:"Europe",dc:44},
              {country:"Scotland",region:"Europe",dc:44},
              {country:"China",region:"Asia",dc:86},
              {country:"Canada",region:"North America",dc:1},
              {country:"United States",region:"North America",dc:1},
              {country:"Japan",region:"Asia",dc:81}
             ]
  
  var handler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SCRIPTDB,undefined,'mydb',ScriptDb.getMyDb());
  if (!handler.isHappy()) { 
    Logger.log ('couldnt open sheet:' + siloId + ' or spreadhseet:' + ssid);
  }
  
  var result;
  // delete everything
  result = handler.remove();
  if (result.handleError < 0) {
    Logger.log(JSON.stringify(result));
  }
  
  // make some new data
  result = handler.save(data);
  if (result.handleError < 0) {
    Logger.log(JSON.stringify(result));
  }
  
  // refresh cache and make sure it worked
  result = handler.query();
  if (result.handleError < 0 || result.data.length !== data.length) {
    Logger.log(JSON.stringify(result));
  }
  
  return result;

}

Here's what gets stored

{siloId=play, data={dc=44.0, region=Europe, country=England}}
{siloId=play, data={dc=44.0, region=Europe, country=Scotland}}
{siloId=play, data={dc=86.0, region=Asia, country=China}}
{siloId=play, data={dc=1.0, region=North America, country=Canada}}
{siloId=play, data={dc=1.0, region=North America, country=United States}}
{siloId=play, data={dc=81.0, region=Asia, country=Japan}}

The key thing though, is that the only difference between the first code and the second is the handler constructor

using a sheet

var handler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SHEET,undefined,ssid);

using  scriptdb

var handler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SCRIPTDB,undefined,'mydb',ScriptDb.getMyDb());

This is pretty interesting approach, because it means that the handler can now take care of caching, error handling, and initialization, and the specific drivers can translate the handler requests into native database API requests. And more importantly, the app code can be exactly the same - regardless of the choice of database, or if you decide to migrate from one to the other. 

It does mean that the handler syntax will be be fairly basic, but perfectly adequate for trivial lookup tables, or minor transactions. I also still have to handle dates and figure out what to do with objects that are more than 1 level deep when the backend is a spreadsheet.

Querying

Since the backend database is irrelevant, we can describe querying in a database independent way using the language of the DataHandler layer.
Here's some example queries - it should be fairly obvious what they are doing.

    var result = handler.query({dc:44},{sort:'country'});
    var result = handler.query({region:'Europe'},{sort:'country',limit:2});
    var result = handler.query();

Removing

Just like querying ...

    var result = handler.remove({region:'Europe'});

Saving
    
    var result = dbHandler.save([{country:"France",region:"Europe",dc:33},{country:"Spain",region:"Europe",dc:34}]);

Counting

   var result = handler.count({region:'Asia'});

Other operations

These are the basic ones to get started with, but there are also get and update operations. I'll add over time - you can request on the Forum gadget.  

Flattening

Even though you can't really store and query deep objects such as {a:{b:{c:'x',d:'y'}}} in two dimensional spaces such as a spreadsheet, this is supported through first flattening  the object. Flattening and unflattening happen automatically where required so there's no special action required. 

Limitations

This is a very early version of this so there are certainly a few. Hopefully this list will become out of date as I deal with them.
  • Some of these are not suitable for larger volume databases - for example it's not practical to rewrite a large data file every time you change it, but it is useful to be able to easily copy from any database to a JSON file, so including that option can be valuable.
  • Google Apps Script has a number of quotas and limits. Some of them are automatically handled by Backing off on rate limiting but others such as "exceeded execution time" are not.
  • Dates - it's always awkward to deal with date formats and local times. I am planning to use the same approach I used in more complex parse API topics, but it's not yet complete.
  • Other drivers - it is fairly easy to create a driver for other back ends. If anyone creates one (or improves an exiting one), I'd appreciate if you would allow it to be included in this library to make it generally available. You can get me on our forum or on G+

Copying

With this kind of framework, moving from one backend database to another is a piece of cake, and you wont have to change any of your code once you've done it (aside from the handle constructor) 

Let's say you want to move from using a spreadsheet to scriptDB. (For brevity I've omitted some error handling, but you can see the pattern from the first one)

  // take all the data from a sheet
  handler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SHEET,undefined,ssid);
  if (!handler.isHappy()) { 
    Logger.log ('couldnt open sheet:' + sheetName + ' or spreadhseet:' + ssid);
  }
  
  result = handler.query();
  if (result.handleError < 0) {
    Logger.log(JSON.stringify(result));
  }
  
  //get a handle for a scriptDB
  dbHandler = new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.SCRIPTDB,undefined,'mydb',ScriptDb.getMyDb());

  // delete anything currently in this silo
  dbResult = dbHandler.remove();

 // save that
  dbResult = dbHandler.save(result.data);

.. and the other way .. we'll sort it too before writing it

// now write all back to spreadsheet, sorted by reverse dial code

// delete anything in the sheet
  result = handler.remove();

// get everything from scriptDB and sort it reverse dialling code order
  dbResult = dbHandler.query(undefined,{sort:'-dc'},1);

 // save it to the sheet 
  result = handler.save(dbResult.data);


.. and then copy it over to parse.com , and see what we have

// get a parseHandler
  parseHandler =  new cDataHandler.DataHandler(siloId,cDataHandler.dhConstants.DB.PARSE,undefined,'myparse',findRegistryPackage());
  
// delete everything there
  parseResult = parseHandler.remove();

// retrieve whats in scriptDb and write it to parse
  parseResult = parseHandler.save(dbHandler.query().data);

 // see what happened and log the result
  result = parseHandler.query();
  Logger.log(JSON.stringify(result));

.. we get this

{

    "handleCode": 0,

    "handleError": "",

    "data": [

        {

            "region": "Asia",

            "dc": 86,

            "country": "China"

        },

        {

            "region": "Europe",

            "dc": 44,

            "country": "Scotland"

        },

        {

            "region": "North America",

            "dc": 1,

            "country": "United States"

        },

        {

            "region": "North America",

            "dc": 1,

            "country": "Canada"

        },

        {

            "region": "Europe",

            "dc": 44,

            "country": "England"

        },

        {

            "region": "Europe",

            "dc": 34,

            "country": "Spain"

        },

        {

            "region": "Europe",

            "dc": 33,

            "country": "France"

        },

        {

            "region": "Asia",

            "dc": 81,

            "country": "Japan"

        }

    ]

}

.. and heading over to the parse data browser we see this


Drivers available

For a list of supported drivers, see the list at the bottom of this page. Others are under construction. Writing new drivers is pretty straightforward - see here for how. If you'd like to help by writing a new one, please let me know through G+ or our forum 

Authentication

This supports oAuth2 authentication. You need to pass an access token when you take out a handle. With the DataStore driver you'll see some oAuth2 patterns for how to initially get that token using ezyOauth2, which you can read about at EzyOauth2 - taking some pain out of Apps Script API authentication

A note on Parse.com,  orchestrate.io amd mongolab.


Parse.com is protected by API keys which you can read about in the library I developed for parse.com - noSQL database for GAS. I store my credentials encrypted in my GAS user properties, and retrieve them with findRegistryPackage().  You can do it like that, or some other method. 

The credentials object passed to the DataHandler needs to look like this. I recommend you use UserProperties or ScriptProperties for parse.com and orchestrate and Driver MongoLab

{applicationID:"your Parse-applicationID", restAPIKey:"your Parse-REST-API-Key"}

Libraries

  • The library reference is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j.  
  • An alternative interface to limit the number of libraries to specific ones is available here - Driver DbAbstraction
  • The caching library is already available and can be used standalone. There is no need to specifically include the cache library if you are using the DataHandler
  • There is an additional abstraction layer and library used by DataHandler REST API, if you are interested in exposing your data through a web app. It includes both these libraries.

Reference

Supported drivers and handlers
Other stuff and advanced topics
For help and more information join our forum,follow the blog or follow me on twitter .

Subpages (30): View All
Comments