Skip to main content

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.

Comments

Popular posts from this blog

Python and Parquet Performance

In Pandas, PyArrow, fastparquet, AWS Data Wrangler, PySpark and Dask. This post outlines how to use all common Python libraries to read and write Parquet format while taking advantage of  columnar storage ,  columnar compression  and  data partitioning . Used together, these three optimizations can dramatically accelerate I/O for your Python applications compared to CSV, JSON, HDF or other row-based formats. Parquet makes applications possible that are simply impossible using a text format like JSON or CSV. Introduction I have recently gotten more familiar with how to work with  Parquet  datasets across the six major tools used to read and write from Parquet in the Python ecosystem:  Pandas ,  PyArrow ,  fastparquet ,  AWS Data Wrangler ,  PySpark  and  Dask . My work of late in algorithmic trading involves switching between these tools a lot and as I said I often mix up the APIs. I use Pandas and PyArrow for in-RAM comput...

Design of Large-Scale Services on Cloud Services PART 2

Decompose the Application by Workload Applications are typically composed of multiple workloads. Different workloads can, and often do, have different requirements, different levels of criticality to the business, and different levels of financial consideration associated with them. By decomposing an application into workloads, an organization provides itself with valuable flexibility. A workload-centric approach provides better controls over costs, more flexibility in choosing technologies best suited to the workload, workload specific approaches to availability and security, flexibility and agility in adding and deploying new capabilities, etc. Scenarios When thinking about resiliency, it’s sometimes helpful to do so in the context of scenarios. The following are examples of typical scenarios: Scenario 1 – Sports Data Service  A customer provides a data service that provides sports information. The service has two primary workloads. The first provides statistics for th...

Design of Large-Scale Services on Cloud Services PART 1

Cloud computing is distributed computing; distributing computing requires thoughtful planning and delivery – regardless of the platform choice. The purpose of this document is to provide thoughtful guidance based on real-world customer scenarios for building scalable applications Fail-safe   noun . Something designed to work or function automatically to prevent breakdown of a mechanism, system, or the like. Individuals - whether in the context of employee, citizen, or consumer – demand instant access to application, compute and data services. The number of people connected and the devices they use to connect to these services are ever growing. In this world of always-on services, the systems that support them must be designed to be both available and resilient. The Fail-Safe initiative  is intended to deliver general guidance for building resilient cloud architectures, guidance for implementing those architectures  and recipes for implementing these architectures...