HTML5 Web SQL Databases w/ JavaScript

For fun I started building a small application using PhoneGap. More than anything I want to build a simple mobile app and see how easy it would be to deploy to multiple platforms. Problem was the app needed to store some data locally. I immediately thought of HTML 5 and web sql databases as an option. Turns out it is pretty simple if you have done any database work in the past. There are a few gotchas when working with SQLite and for me it was particularly frustrating working with the date/time features.

Create a database

For simplicity I made an object called Database and added methods to the proto chain for each database action. One thing to note is that a reference to mydb has been placed in the function for the Database object. This way once the database is open we can set this.mydb to that open database connection for further calls.

var Database = function(){
 var mydb=false;
}

Database.prototype.initDB = function() {
     try { 
        if (!window.openDatabase) { 
          alert('not supported'); 
        } else { 
          var shortName = 'sampledatabase'; 
          var version = '1.0'; 
          var displayName = 'Sample Database'; 
          var maxSize = 65536; // in bytes 
          this.mydb = openDatabase(shortName, version, displayName, maxSize); 
         }
      } catch(e) {}
}

// Then we can easily make a new Database object and call the initDB method.
var database = new Database();
database.initDB();

Create a table

Next, we are going to want to create a database table to store our data. We only want to create this table IF it does not exist already. Once again this is pretty simple. We created a table with an auto id, name_field, interval, and date_added. One thing to be aware of when using dates is that SQLite will use GMT as the standard for the SQL variable “now”. To change this you can do as we did below. Just set now to ‘localtime’. Also, we have to set a callback handler for success and error. In this example we are setting success to a nullDataHandler which does nothing and an error handler which alerts the error.

Database.prototype.createTable = function() {
 try {
  this.mydb.transaction(
    function(transaction) {
   //transaction.executeSql('DROP TABLE sample_db', [], this.nullDataHandler, this.errorHandler);
            transaction.executeSql("CREATE TABLE IF NOT EXISTS sample_db(id INTEGER PRIMARY KEY AUTOINCREMENT, name_field TEXT NOT NULL DEFAULT "", interval INTEGER NOT NULL DEFAULT 0, date_added TIMESTAMP DEFAULT (datetime('now','localtime')));", [], this.nullDataHandler, this.errorHandler); 
          });
      } catch(e) {}
}

Database.prototype.errorHandler = function (transaction, error) { 
  // returns true to rollback the transaction
  alert("Error processing SQL: "+ error);
  return true;  
}

// null db data handler
Database.prototype.nullDataHandler = function (transaction, results) {
}

Insert a row

If you have ever written a SQL insert statement this is as easy as Paul Walker winning a race for pinks. (Side note: Can’t believe they have made 5 of those movies…) Anyway, once again we need to specify success and error handlers.

Database.prototype.saveData = function(name_field, interval) {
 try{
  this.mydb.transaction(
    function(transaction) {
   transaction.executeSql("INSERT INTO sample_db (name_field, interval) VALUES (" + name_field + ", " + interval + ");", [], this.nullDataHandler, this.errorHandler);
  });
 } catch(e) {
  alert("Error processing SQL: "+ e.message);
  return;
 }
}

Crud everywhere

Reading and updating from the database are just as easy as inserting. We will illustrate a select below. Using the strftime function provided by SQLite we can manipulate the format of our date_added timestamp for output. This will help since I personally find dates in JavaScript annoying to deal with. One more thing to note is we are using a success handler named resultSetHandler this time to handle the sql query response.

// load the currently selected icons
Database.prototype.loadData = function() {
 try {
  this.mydb.transaction(
   function(transaction) {
               transaction.executeSql("SELECT id,name_field,interval,date_added, strftime('%d', date_added) as day, strftime('%Y', date_added) as year, strftime('%w-%m-%Y', date_added) as formatted_date, strftime('%H:%M:%S', date_added) as time_added FROM sample_db ORDER BY date_added desc",[], this.resultSetHandler, this.errorHandler);
         });
    } catch(e) {
        alert(e.message);
    }
}

Database.prototype.resultSetHandler = function(transaction, results) {
// For now just outputting the results.
 console.log(results);
}

As you can see it is pretty simple and straightforward to start using WebSQL.With support in iOS 3.2+ and Android 3+ it is ideal for mobile projects. As a side note I l know there is a definite movement server side towards ORM and writing less SQL. That being said there are some options to abstract the SQL away.