Using scriptDB

What is scriptDB

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.

scriptDB is a javaScript object database for Google Apps Script project. Here are a few key facts
  • Every project can have its own scriptDB
  • There are no Tables (this is a noSQL database). Instead it is up to you to use data attributes to separate your data into 'silos'.
  • Querying is done 'by example'
  • There are various limits and quotas
  • The data is persistent and accessible 'across projects'
  • Using shared libraries can be tricky, since the library would use it's own project's scriptDB rather than the one of the running project.
Here's a summary of few of its uses from this site

Example of use

The Rest to Excel library is a good candidate to use the scriptDB to store its library data for both Excel and GAS. However, since I will be using the scriptDB for many things, I wanted to first formalize how I was going to approach data siloing, since that could get tiresome in the future.

I'm using a couple of new classes - cScriptDbSilo and cScriptDbSiloItem to automate that. Here's a test example that uses 2 differentt scriptDb - a local one, and the one associated with the mcpher shared library. ScriptDB also makes a useful lockBox.

function testThings(){
  // delete everything from library and also from local
  mcpher.scriptDbSilo().remove();
  mcpher.scriptDbSilo().remove(undefined,ScriptDb.getMyDb());
  // add some stuff
  mcpher.scriptDbSilo("some library stuff").save({stuff:"library stuff"});
  mcpher.scriptDbSilo("some local stuff",ScriptDb.getMyDb()).save({stuff:"local stuff"});
  mcpher.scriptDbSilo("some local stuff").save({stuff2:"more local stuff"});
  // show it
  mcpher.DebugPrint(mcpher.scriptDbSilo("some library stuff").query().next());
  var results =  mcpher.scriptDbSilo("some local stuff").query();
  while (results.hasNext() ) {
    mcpher.DebugPrint(results.next());
  }
  // delete it
  mcpher.scriptDbSilo("some library stuff").remove(); 
  mcpher.scriptDbSilo("some local stuff").remove(); 
}

That gives this result

{"siloId":"some library stuff","stuff":"library stuff"}
{"siloId":"some local stuff","stuff":"local stuff"} 
{"siloId":"some local stuff","stuff2":"more local stuff"}

Walkthrough

  • Delete everything in the mcpher  scriptDb
mcpher.scriptDbSilo().remove();
  • Delete everything in the local scriptDb. Note that since the DbSilo classes exist in the mcpher library, they will be by default operating on the mcpher scriptDb - so we need to pass the DB to use - in this case the local one (ScriptDb.getMyDb());

mcpher.scriptDbSilo().remove(undefined,ScriptDb.getMyDb());
  • Add some stuff to the mcpher scriptDb. I'll use the key I've given below now to reference any data associated with this particular silo. The underlying classes take care of tweaking the stored objects to silo them.
mcpher.scriptDbSilo("some library stuff").save({stuff:"library stuff"});
  • Add some stuff to the local scriptDb. Just like the delete example, we need to specify that this is going on in the local DB this time. However now that we have created the silo named "some local stuff", the associated DB will now be stored and will not need to be mentioned again as per the 2nd example.
mcpher.scriptDbSilo("some local stuff",ScriptDb.getMyDb()).save({stuff:"local stuff"});
mcpher.scriptDbSilo("some local stuff").save({stuff2:"more local stuff"});
  • Do a query of the results in the library scriptDb
mcpher.DebugPrint(mcpher.scriptDbSilo("some library stuff").query().next());
  • Do a query of the local stuff, in this case we need to loop to show the multiple results
  var results =  mcpher.scriptDbSilo("some local stuff").query();
  while (results.hasNext() ) {
    mcpher.DebugPrint(results.next());
  }
  • delete the data just added
  mcpher.scriptDbSilo("some library stuff").remove(); 
  mcpher.scriptDbSilo("some local stuff").remove(); 


Silos

Using a Silo manager like this enforces some standardization on how you partition data in this 'tableless' database. You can have as many silos as you want, each identified by some unique key. 

Changing the default scriptDb

If the default scriptDb you want to use is not the one associated with the mcpher library, then you can change it by an initial call like this
mcpher.createDbSilos( ScriptDb.getMyDb());

or later by changing the xDb property
mcpher.scriptDbSilo().xDb = ScriptDb.getMyDb();

Deleting data in Silos

You are only able to delete data that you created. Items are written with a time stamp and the user who wrote it. Deleting checks that the user ID matches before allowing deletion. I'll also be updating this shortly to restrict shared library to read only. 


The cScriptDbSilo code

You can use the mcpher library mentioned in  Creating a Google Apps Script Library to access this and to see the code.  A snapshot of the code is in this gist and reproduced below.

Code


Summary


Take a look at how the From VBA to Google Apps Script for more like this. Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.