Database abstraction and Google Apps Script

Parse.com
parse.com as now been closed and moved to parseplatform.org. I will update the content of this page an move the back-end to this platform

All code here is unprotected and free to re-use. For more details see Reusing code from this site. If you find this site useful and wish to support it, you can do so by buying my book or video course.

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.

Page Content hide

1 DataHandler versus DbAbstraction

2 Here’s what it looks like.

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

16 Drivers available

17 Authentication

18 A note on Parse.com, orchestrate.io and mongolab.

19 Libraries

20 Reference

21 Share with your network

DataHandler versus DbAbstraction

Using the cDbAbstraction library is the recommended way to use DbAbstraction. The DataHandler library will be deprecated in the near future, as it contains references to all known backends and needs authorization for them all. DbAbstraction ony needs authorization for the backend(s) you are actually using. 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

Get Library Info

Here’s what it looks like.

Quickstart

Getting started with dbabstraction

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;
}
Here is the same thing using the DbAbstraction service as opposed to the DataHandler service. The only difference is the way you get the handler. In the case of DbAbstraction, you include cDbAbstraction and cDriverSheet libraries in your project. With DataHandler you only need to include the cDataHandler library, but it includes all known drivers. This is why the cDbAbstraction approach is better, as it only includes the code needed for your selected back ends. In all the examples that follow, you can substitute this approach with the given DataHandler approach as required.
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 cDbAbstraction.DbAbstraction ( cDriverSheet , {
siloid:siloId,
dbid: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);

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.

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.

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 cDatathandler.Datahandler(siloID, DataHandler.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 and 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