[whatwg] Asynchronous database API feedback

[Aaron Boodman]

I like the new Database API design a lot, but I wish there was an
option for synchronous DB access.

I did some quick tests and I can insert 1000 rows, totaling 3KB+ of
data into SQLite in less than a tenth of a second on Windows (I don't
have a mac here, but I recall Windows was slower when we last tested).
Reading takes a similar amount of time. It is definitely possible to
construct queries that will take a long time to execute, but I feel
that isn't the majority use case. Simple applications deal with a
relatively small amount of data at a time, and disk latency isn't an
issue for them. As an example, I use Gearpad
(http://aaronboodman.com/gearpad) on a daily basis and it interacts
with the database synchronously on the UI thread. The app feels very
responsive to me. An asynchronous database API would just making
writing that application harder with no real benefit.

To be clear, I understand that the asynchronous database API was
motivated by a desire to avoid another mistake like XMLHttpRequest's
sync mode. But I think this situation is different because:

a) Disk access is typically going to be a lot faster than network access
b) Logical SQL transactions are typically made up of many calls to
executeSql, whereas logical network transactions are (at least with
REST) typically made up of one or at most only a few XHR calls

I think there should at least be the option of a synchronous API for
the simple use cases.

Here is a proposal that minimizes increased API size and developer
brainprint: Add a synchronousTransaction() method to go alongside the
existing transaction() method. The effect of this API would
effectively be to modify all the calls inside the transaction to be
synchronous. Otherwise, the spec'd APIs do not change.

Here's the IDL of the additional API I'm suggesting:

interface Database {
  void synchronousTransaction(SQLSynchronousTransactionCallback
callback) throws SQLError;
}

interface SQLSynchronousTransactionCallback {
  void handleEvent(in SQLSynchronousTransaction transaction);
}

interface SQLSynchronousTransaction {
  SQLResultSet executeSql(in DOMString sqlStatement, in ObjectArray
arguments) throws SQLError;
}

Here's an example of how one might use it:

var db = openDatabase("foo");

try {
 db.synchronousTransaction(function(tx) {
   try {
     var bob = db.executeSql("select * from person where name = ?
limit 1", ["bob"]);
     var bobData = db.executeSql("select * from person where name = ?
limit 1", ["mary"]);
     db.executeSql("insert into friends values (?, ?)",
[bob.rows[0].id, mary.rows[0].id]);
   } catch (e) {
     console.log("statement failed: " + e);
   }
 });
} catch (e) {
 console.log("transaction failed: " + e);
}


Thoughts?

- a