SiteKickr Web Development

Taming Web SQL

Phil LaNasa

If you’re coming from server-side development, Web SQL might appear to be both limiting and difficult to grasp. You’re used to sending a request to a database server and waiting for the result set before proceeding to the next line of code. The asynchronous nature of Web SQL is no doubt one of it’s trickiest aspects to master.

I’ve said it in a few other posts, and it also applies here; to really understand and take full advantage of Web SQL, you need to have a solid understanding of JavaScript. I typically recommend the book Object-Oriented JavaScript as a great introduction to JavaScript, as it also dives into advanced topics such as how functions and callbacks really work – a necessity to fully understand Web SQL.

When executing queries, you don’t wait around for the result before proceeding to the next line of code. Instead, you pass a callback function, which is executed once the database has retrieved the result set.

Example of Asynchronous Web SQL call

db.transaction(function(transaction) {
transaction.executeSql(sql, args, function(transaction, resultset) {
chosenWord = resultset.rows.item(0)['w_word'];
alert('Is this your word ' + chosenWord);
});
});

In this case, I’m using an “inline callback”. The entire callback function is sent as an argument to the executeSql function. This function will be executed when the database returns the results. In the meantime, the rest of your script will continue to execute.

How that you have a very basic understanding of how Web SQL works with asynchronous callbacks, I’d like to introduce my method for managing the database connections, initializing database data and hiding the details from the rest of the code.

Encapsulating the database methods

As with any type of application, adding database storage to a JavaScript app adds another level of complexity. Where we were previously accustomed to storing data on the server side (or in cookies with smaller storage requirements), we now have the power of SQL directly available to our scripts. But, as we know from our server-side development endeavors, any interaction with a storage mechanism should not be tightly coupled with the code that depends on it. For example, on the server side, if you are using MySQL today, you might be using Oracle tomorrow, and you don’t want to have to update thousands of lines of code, each assuming that their using a MySQL database.

With Web SQL, we have an understanding that it is a deprecated technology. It dominates mobile devices now, but that might not be the case in the next year or two. We want to be sure to encapsulate all of our database related methods into a separate object. I like to create an entirely separate dbase.js file. A typical dbase.js file might look like the following. Note that I did not use a database “class”. You might find that the database integration internals are a little more “hidden” if you made the dbase object private and created public accessor methods.

(function() {
var database = { 'name' : 'MyAppName', 'version' : '1.0',
'displayName' : 'MyAppName', 'maxSize' : 5000000 }
dbase.db = window.openDatabase(database.name, database.version, database.displayName, database.maxSize);

    dbase.db.transaction(function(transaction) {
sql = 'DROP TABLE IF EXISTS  phrase';
transaction.executeSql(sql, [], function(transaction) {
sql =  'CREATE TABLE IF NOT EXISTS phrase (';
sql += 'p_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ';
sql += 'p_text VARCHAR NOT NULL, p_rating INTEGER DEFAULT 0)';
transaction.executeSql(sql, [], function() {
// callback that table is created
}
});
});

    dbase.InsertPhrase = function (phrase) {
var sql, values = [phrase];
sql = 'INSERT INTO phrase (p_text) ';
sql += 'VALUES(?)';
dbase.db.transaction(function(transaction) {
transaction.executeSql(sql, values,
function(transaction, data) { }
);
});
}

dbase.SelectRandomPhrase = function (callback) {
var seed = (Math.random() + 1) * 1111111;
var sql = ‘SELECT * FROM phrase ORDER BY (p_id * ? % 10000) LIMIT 1’;
var chosenPhrase; dbase.db.transaction(function(transaction) {
transaction.executeSql(sql, [seed], function(transaction, results) {
chosenPhrase = results.rows.item(0)[‘p_text’];
callback(chosenPhrase);
},
function(transaction, error) { }
);
});
})();

 

Initializing the table data

Depending on the nature of your application, it may rely on a certain amount of data already available in the database for query purposes. In a synchronous, server-side world, this is never an issue, to initialize the database we simply perform a series of insert statements once.

When dealing with client-side applications, this needs to be done the first time the app is loaded, on each client. Considering that we’re limited to 5MB of storage space, you would expect any such initialization to take only a matter of seconds. While this may hold true in a server-side scenario, we’re dealing with asynchronous methods in JavaScript. If you take a second a look at the insert function above, you’ll recall that it involves a callback when the transaction object is ready, which then allows you to execute the insert statement. If you had 10,000 records to insert, it very possible that you’d have thousands of transaction objects in memory at any given time, increasing execution time.

It isn’t necessary, however to create a separate transaction object for each statement. A single transaction object will do the job. In the example below, I’m pulling the initialization data from a plain text file where each line in the file corresponds to a record in the database. Once the data is available, a single transaction object is used to insert the data into the database.

var phraseInsert = 'INSERT INTO phrase (p_text) VALUES (?)';
$.ajax({
url: 'phrase.txt',
dataType: 'text',
success: function(data) {
var rows = data.split('n');
dbase.db.transaction(function(transaction) {
for (row in rows) {
transaction.executeSql(phraseInsert, [rows[row]]);
}
}),
}
});

Querying the database

Running queries on the Web SQL database is similar to server-side database querying. But there are a few key differences. The asynchronous nature of Web SQL is most burdensome when doing queries. Where with many INSERT, UPDATE, DELETE statements, we don’t necessarily need to wait for a result, with a SELECT statement, everything we do depends on that result. The result will only be available in the form of a callback.

Initially, it’s difficult to wrap your head around this. The best thing to do is make sure that any code you write that interacts with the database, even indirectly, is setup to work with asynchronous calls and callbacks.

For example, if you have a private function to retrieve something from a Web SQL database table, and a public function which calls the private function and pretties up the results, that public function should, instead of returning a value, call a callback function.

I’ll demonstrate a Web SQL query in the code below. I like this snippet because it deals with many of the differences between Web SQL and server-side databases.

dbase.SelectRandomWord = function (pos, intensity, callback) {
var seed = (Math.random() + 1) * 1111111;
var sql, args;

args = [pos, intensity];
sql = ‘SELECT w.w_word FROM word w ‘;
sql += ‘INNER JOIN word_pos_xref wpx ON wpx.w_id = w.w_id ‘;
sql += ‘INNER JOIN pos p ON wpx.pos_id = p.pos_id ‘;
sql+= ‘WHERE p.pos_name = ? AND w.w_intensity <= ?’;

  sql += ' ORDER BY (w.w_id * ? % 10000) LIMIT 1';

args.push(seed);

var chosenWord;
dbase.db.transaction(function(transaction) {
transaction.executeSql(sql, args,
function(transaction, resultset) {
chosenWord = resultset.rows.item(0)[‘w_word’];
callback(chosenWord);
},
function(transaction, error) {
console.log(error);
}
);
});

The first thing you might notice is that I’m attempting to get a random record from the database. The Web browser version of SQLite does not support the RANDOM() function that we are used to, so we need to create our own seed and work it into an ORDER BY clause.

The next thing you’ll notice is the ? placeholders. This allows Web SQL to safely prepare a query, which you can pass parameters into, in the form of an array. You’ll notice that the executeSql function takes 4 arguments:

  1. The prepared SQL statement/query.
  2. The arguments array.
  3. A success callback.
  4. An error callback.

The success callback allows us to grab the result set returned from the query. It is pretty straightforward. However, there is one pitfall that I’ve run into before. The result set rows are not returned as an array. Instead, you call an item() function on the rows object, passing in the row number that you wish to retrieve. So, syntactically, we are using parenthesis, not brackets to wrap the row number. Once you are aware of that, each row is simply a JavaScript object where the keys are the field names of the table, as requested in the SELECT statement.

Notes