ScriptDB: Advanced Topics

This section covers more complex applications of ScriptDB.

Storing dates

ScriptDB cannot store Date objects directly; instead, you must store a representation of the date and reconstruct it later. If you don't intend to search based on dates, then you can store the numeric timestamp from the Date object like this:

var date = new Date('1/1/2014');
var item = {
  timestamp: date.getTime();
}
var record = db.save(item);

Then, to reconstitute the Date:

var date = new Date(record.timestamp);

If you want to be able to query individual fields of the Date object, you can store it like so:

var date = new Date('1/1/2014');
var item = {
  timestamp: date.getTime(); // To recreate the Date object later.
  day: date.getDay();        // 0 is Sunday, 1 is Monday, etc.
  date: date.getDate();      // Day of the month.
  month: date.getMonth();    // 0 is January, 1 is February, etc.
  year: date.getFullYear();  // Full 4-digit year.
  // And so on.
}
var record = db.save(item);

Storing them that way would allow you to find all records where the date was a Monday, like so:

var mondayResults = db.query({day: 1});

Calling a library's database

If your script uses a library, you can write your code to call the library's ScriptDB instance instead of the current project's own instance. The following code shows how this is typically set up:

function getDb() {
  return ScriptDb.getMyDb();
}

function useLibraryDb() {
  var db = Library.getDb();
  // Do stuff with the database.
}

Passing a database to a library

Alternately, you might want library code to use the current project's ScriptDB instance rather than the library own's instance. In this case, you can simply pass the current project's database to the library, like so:

var db; // Global variable updated by setDb().

function setDb(externalDb) {
  db = externalDb;
}

function useDb() {
  // Do stuff with the database.
}

function passDbToLibrary() {
  var db = ScriptDb.getMyDb();
  Library.setDb(db);
}

Simulating a table

Unlike some databases, ScriptDB does not provide multiple tables. Instead, you form logical subsets of your data.

To represent a distinct subset, use a unique key. For example, you could add the key-value pair table: "employee" to objects that represent employees. There's nothing magical about the key name table here; it's just a name to demarcate your subsets.

For overlapping subsets (say, "people," some of whom are employees and some of whom are customers), use a flag. For example, you might add the key-value pairs table: "people", is_employee: true to objects that represent employees.

Simulating transactions

ScriptDB doesn't have transactions in the normal relational database sense, but you can prevent concurrent access to the same ScriptDB instance using the Lock service, which was designed for this kind of use. For example, if you wanted to increment an object's count variable:

var id = 'ID_FOR_THE_OBJECT';
var lock = LockService.getPublicLock();
lock.waitLock(30000);
try {
  var obj = db.load(id);
  obj.count = obj.count + 1;
  db.save(obj);
} finally { // Make sure the lock is released even if we blow up.
  lock.releaseLock();
}

Syntax comparisons with SQL

INSERT INTO bar (a, b) VALUES (1, 'foo')

db.save({table: 'bar', a: 1, b: 'foo'})

UPDATE bar SET a=5, b='baz' WHERE id='id'

var item = db.load(id);
item.a = 5;
item.b = 'baz';
db.save(item);

DELETE FROM bar WHERE id='id'

var item = db.load(id);
db.remove(item);

SELECT * FROM bar WHERE id = 'id';

db.load(id)

SELECT * FROM bar WHERE a=1 AND foo='baz'

db.query({table: 'bar', a: 1, foo: 'baz'})

SELECT * FROM bar WHERE foo IN('baz', 'qux')

db.query({
    table:'bar',
    foo: db.anyOf(['baz', 'qux'])
})

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.