Google Apps Script (GAS) ScriptDB Replacement

No ScriptDB? No problem.

On May 15th Google announced that it was deprecating it's ScriptDB service. As a developer who leaned heavily on ScriptDB this meant adjusting and finding a new NoSQL database by November 20, 2014 when the service will go the way of the dodos.

Google was kind enough to provide us developers with a rough migration guide. But man is it rough. Fortunately, they give enough clues to figure out how to integrate some cloud-based NoSQL data store alternatives.

Clue #1: NoSQL Alternatives to ScriptDB

The first clue comes with some of the alternatives Google gives. The two cloud-hosted NoSQL databases Parse Core and MongoLab (uses MongoDB) both offer really great alternatives to ScriptDB.

Here's a quick comparison of the two services' free version

What do you get for free?
Name File Storage DB Storage Extras Limitations
Parse Core 20 GB 20 GB
  • REST API
  • Web Admin Console
  • Analytics
  • Push Notifications (for mobile apps)
  • Multiple SDKs for iOS, Android, OSX, JavaScript, and .NET
  • 3rd Party Libraries
  • 30 transactions/second
  • One Concurrent Background Job
  • Cannot be hosted anywhere else
  • Only forum-based help
MongoLab 0.5 GB (shared with DB) 0.5 GB (shared with File)
  • REST API
  • Web Admin Console
  • Can be hosted for a cost on one of several prominent cloud hosting services within a cluster or a single node
  • 24/7 Monitoring & Alerts
  • Daily System-level back-up
  • 2-factor Authentication
  • E-mail support
  • Limited storage space
  • No analytics
  • No push notifications

The main determinant, in my eyes, is how much space you anticipate you'll need. If it's a lot, then I'd recommend going with Parse. If it's minuscule, then MongoLab would be the way to go especially if you're newer to using web-based NoSQL DB as you can get some timely email support.

Clue #2: Using UrlFetch

So, the second clue is that you need to use Google's UrlFetch service. Below is how you get the standard result of fetching a URL is to receive the full HTML from the page you requested.

function getResponse() {
  var url = 'http://www.google.com/';
  var response = UrlFetchApp.fetch(url);
  Logger.log(response);
}

However, what you need the UrlFetch to really do is perform as a JSONP connection between your script and the NoSQL data stores using their respective REST APIs. You can do this by passing some of them in as options within the standard request.

Quick Security Warning

Now before I get into how to do this, you have to be extremely careful with JSONP as it is not the most secure way to pass data into a database. MongoLab does have 2-factor authentication along with basic authentication, while Parse allows you to create both class level and object level security measures such as Access Control Lists and creation limitations. Both pass data over https and SSL. That doesn't mean it's 100% secure. You have to be careful of Cross Site Request Forgery (CSRF) (see this StackOverflow Q&A) and people sniffing your data as it's passed between Apps Script & whichever service you choose (although Parse claims you don't have to worry about man-in-the-middle attacks). This article has some good pointers about avoiding CSRF regarding how to make your application more difficult to hack or attack.

More Security - Storing your Keys in Properties

One way to make your script more secure is by keeping the REST API keys stored in the Apps Script project properties. These are key/value pairs that only you, as the project owner, can see and edit. To access them go to File --> Project Properties and then select the Project Properties tab. There you can enter those pairs as you see fit. Here's how I did it for both MongoLab and Parse:

Using Properties to store sensitive REST API data
  Parse Core MongoLab
Properties Settings
Apps Script Code
function getKeys() {
  var props =PropertiesService.getScriptProperties(); 
  var apiId = props.getProperty('apiId');
  var restApi = props.getProperty('restApi');
  var returnObj = {apiId: apiId,
                   restApi: restApi};

  return returnObj;
}
function getKeys() {
  var props = PropertiesService.getScriptProperties();
  var api = props.getProperty('api');
  var db = props.getProperty('db');
  var collection = props.getProperty('collection');
  var returnObj = {api: api,
                    db: db,
                    collection: collection};

  return returnObj;
}

Now the fun: Querying the NoSQL Databases

Once you've got your properties, here's how to do a query with both MongoLab and Parse:

Querying the Data
  Parse Core MongoLab
Query All
function queryAll() {
  var properties = getKeys();
  var appId = properties.appId;
  var restApi = properties.restApi;
  var class = properties.class;
  var url = 'https://api.parse.com/1/classes/' + class;
  
  //options that are passed into the header along with the method
  var options = {
    "method" : "get",
    "headers" : {
      "X-Parse-Application-Id": appId,
      "X-Parse-REST-API-Key": restApi,
    }
  }
  
  var data = UrlFetchApp.fetch(url, options);
  var cleanData = JSON.parse(data).results;
  
  Logger.log(cleanData);
  return cleanData;
}
function queryAll() {
  var props = getKeys();
  var api = props.api;
  var db = props.db;
  var collection = props.collection;
  
  //Build the URL
  var fullUrl = 'https://api.mongolab.com/api/1/databases/' + db + '/collections/' + collection + '?apiKey=' + api;
  var fullResponse = UrlFetchApp.fetch(fullUrl);
  
  Logger.log('full response from db: ' + fullResponse);
  
  return fullResponse;
}
Query Specific Items
function query(key,value) {
  var properties = getKeys();
  var appId = properties.appId;
  var restApi = properties.restApi;
  var class = 'TestObject';
  var url = 'https://api.parse.com/1/classes/' + class;
  
  //query with key/value properties passed in
  var query = 'where={"' + key + '":"' + value + '"}'
  var encoded = encodeURIComponent(query);
  var queryUrl = url + '?' + encoded;
  
  var options = {
    "method" : "get",
    "headers" : {
      "X-Parse-Application-Id": appId,
      "X-Parse-REST-API-Key": restApi,
    }
  }
  
  var data = UrlFetchApp.fetch(queryUrl, options);
  var cleanData = JSON.parse(data).results;
  
  return cleanData;
}
function queryById(queryId) {
  var props = getKeys();
  var api = props.api;
  var db = props.db;
  var collection = props.collection;
  
  //query
  var query = { "_id": { "$oid": queryId} };
  var cleanQuery = encodeURIComponent(Utilities.jsonStringify(query));
  
  //URL
  var queryUrl = fullUrl + '&q=' + cleanQuery;
  var queryResponse = UrlFetchApp.fetch(queryUrl);
  
  Logger.log('query response from db: ' + queryResponse);
  return queryResponse;
}

Up Next...

In my next post I'll cover how to write and delete in both Parse Core and MongoLab.