Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

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 very early version of 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 library reference is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j

Here's what it looks like.


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 handler = new cDataHandler.DataHandler(siloId,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,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,dhConstants.DB.SHEET,undefined,ssid);

using  scriptdb

var handler = new cDataHandler.DataHandler(siloId,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

We need a few more operations, but these are the basic ones to get started with. I'll add over time - you can request on the Forum gadget. In the pipeline already are update(id,object) and get() 

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.
  • Since the back end can be SQL, a CMS, a noSQL database, a file, a Fusion database or a spreadsheet and perhaps some others too, representing data more than 1 level deep is only supported on those that are not 2d. So for example {a:1,b:2,c:3} are okay on all back ends, but {a:1,b:{x:1,y:2},c:3} cannot easily and transparently be represented on table based (spreadsheets, sql, fusion) dbs. I'm working on a way to deal with that but I'm not there yet. 
  • 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.
  • Permissions and oAuth2. I have not done much here with permissions yet, but it is on the roadmap
  • 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,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,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,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

So far I've written drivers for Sheets, ScriptDB, Fusion, orchestrate.io, Drive and Parse.com. 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

For now, everything is public. I'll work in oAuth2 for permissions shortly. 

A note on Parse.com and orchestrate.io


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 for both parse.com and orchestrate.

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

Libraries

  • The library reference is Mj61W-201_t_zC9fJg1IzYiz3TLx7pV4j.  
  • 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

For help and more information join our forum,follow the blog or follow me on twitter .

Subpages (26): View All
Comments