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. Again this posed problems as I had no idea how to accomplish this. 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.

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.

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.

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.

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. If you love JSON check out Lawnchair. Here It will create a table per “document store” and will give simple methods such as get , save, remove, etc. However, being someone who is used to working with oracle I feel that getting down and dirty is kinda fun.

Leave a Reply

Your email address will not be published. Required fields are marked *