HTML5 WebSQL Database Joy

First, some history. I’ve been working on a Blackberry app for Mindr Mobile using WebWorks. Given how drastically BB is changing with BB10, I figured it was easier to use WebWorks in both legacy BB and BB10, rather than completely different development on each. So I got to dust off the HTML and Javascript skills.

Part of the app I’m building requires having a local database. OK, require might be the wrong word as it could be done with HTML local storage, but a database is easier, and more in line with the Android version of the app.  Conveniently, both Android and HTML5 WebSQL use SqlLite as the backend database as well, so I don’t even have to worry about differences in syntax (although I already use several db’s, so not a big deal), and should be able to port everything across easily.

Aside: I went with WebSQL over IndexedDB since WebSQL is supported in WebWorks.  At least it is documented in the WebWorks documentation.  And using a relational database makes it easier/quicker to port from Android.

It actually was pretty easy to port the functionality, almost a copy/paste (just translating java to javascript).  However, they way that Android and HTML5 handle the database versioning and migrations differ.

Android has a SqLiteOpenHelper class to help with database upgrades, specifically the  onUpgrade method that gets called when the version of the database is different than the requested version.

HTML5 does not have this, exactly.  When you open a database using window.openDatabase, you can optionally specify a version number.  If you do, the version has to match exactly or you get an error.  If you do not specify a version, then you get whatever is already there, or a new database if none has been created, after which you can verify the version you want with db.changeVersion, however, you have to specify both the old and new version, and you have to check the versions yourself for whether you want to call changeVersion, which is annoying and eventually can be a maintenance nightmare.

At this point, I had started looking into creating a versioning class for Javascript to handle it for me, but checked Google instead.  It knows stuff.  That’s when I came upon this guy’s post about creating a database migration class, which was where I was headed anyway.  So rather than repeat what he did in my words, I just leave the link to his page for further reading, if you are so inclined.  It still has some extra work that will always get executed whether an update is needed or not, but it is easier and less to maintain than the alternatives.

Moral of the story: Google first, think later.

2 thoughts on “HTML5 WebSQL Database Joy

  1. Glad to help! Somewhat annoyed that a migrator class is necessary in the first place (and not built into the spec), but at least that spec is going away. Unfortunately the exact same thing is needed for IndexedDB, too! It offers an “upgradeneeded” event with the old and new version numbers, at least, as opposed to simply failing if the version number is wrong (hurr). Still need a migrator class, but it’s almost as trivial as maintaining an array of migration callbacks (where index corresponds to DB version number). Something, anyway.

    • Yeah, spec is pretty basic. They tend to leave the fancy usability parts to others, see what works, and then maybe work that in later.

      I’m a little sad that WebSQL is going away (I like SQL!) but I can see the benefits of IndexedDB in a JS Object world, particularly when JS Objects are returned in either case.

      Will play more later…

Leave a Reply to rsimmons Cancel reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>