I've been dabbling a bit with Chrome extensions, which have at their disposal HTML5 localStorage and Web SQL Database. And...the web sql database especially is pretty cool. But one thing was bothering me -- if I have a database on the client, how do I maintain the schema? The (simplest) answer is in changeVersion.
First a little background -- each database has an "version" property stored in the metadata of the database. This basically safeguards you from the following scenario:
- User goes to website and a local database with schema A is created
How does the webmaster update from A to B? Or create the initial schema, for that matter? changeVersion.
Here's how you migrate the original schema, A in our case:
That works...but already there's a problem. If a user revisits a page, that changeVersion line will attempt to execute again. Easy enough to fix:
(Note: the second argument to openDatabase is the "expected version" of the database. If you specify it, the openDatabase method will fail unless the database matches the version you provided, without chance to recover. I haven't found a use for this yet, so you'll always see the expected version as empty string from me. Also note: this particular problem could be solved by setting the initial schema in the openDatabase callback, but you'd still hit it later when you add your first schema alteration)
Now users can view and refresh a page without the database connection erroring out. Gooood. But wait, I need to modify the schema again. What now? Let's try this:
This...actually doesn't work at all (except within the scope of web workers, perhaps), because within the "regular" web sql database (window.openDatabase), everything is asynchronous. Which is awesome for performance, but a pain if you're trying to do what we're trying to do right now. Also, changeVersion only changes the version after the callback has run, successfully. In other words, for a user new to the site with no schema, the "if db version == 1" line will get executed before the database has been updated to version 1! If you refresh the page a few times this might work, but...that's obviously not acceptable. But we're getting closer:
Alright, this actually works, but it's cumbersome and still has a fair bit of code duplication (less in the second version...).
And now for the punch line -- what if we take that "v3" migration, stash all the migrations in an array, and...say, put it in a class, maybe called Migrator? And that's what I've done: And usage example:
Granted, it is small and fairly simple (and uh, disclaimer, not tested super thoroughly), but if nothing else it should be a good starter for your own migration scripts.
Thanks for reading!
UPDATE on June 5, 2010:
I've created a github project with a more recent version (one that works with Safari/Webkit), available here. It does this by using a schema table to track state instead of relying on changeVersion. While sort of a hack, the changeVersion design is rather poor anyway (pretty much the only useful openDatabase version string is "", since everything else could throw an exception). Additionally, I added some logging, an example, and what I discovered to be a much-needed feature: callbacks, which let you execute further SQL with confidence that the migrations have already occurred.