HTML5 Web SQL Database – Intro to Versioning and Migrations
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:
- Webmaster creates Javascript that requires database with schema A
- User goes to website and a local database with schema A is created
- Webmaster updates Javascript to use schema B
- User goes to another page -- but what now? User's database is using schema A but the javascript expects schema B
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.
April 26th, 2010 - 02:56
Thank you, this is definitely useful! I was wondering how web developers expected to update their applications for users with different versions, and you answered it
Now, the annoying question: won’t the code become too big overtime, if you have to embed all the migration scripts?
April 26th, 2010 - 09:33
Uh….gzip your javascript?
That is a valid point. One solution I can think of is to break up your migrations into like groups of 10 or something, then detect which migrations a user needs to download, i.e. if I’m going from schema v15 to v25, I’ll need to download migrations1.js and migrations2.js, but if I’m going from v24 to v25 I only need migrations2.js. Annoying, but I’m sure a system could be worked out for that fairly easily.
June 1st, 2010 - 00:45
Elegant and very useful!
Sadly enough, Migrator won’t work on iPhone because the new database version number becomes visible in Safari only in future sessions. See last paragraph in: http://developer.apple.com/safari/library/documentation/iphone/conceptual/safarijsdatabaseguide/usingthejavascriptdatabase/usingthejavascriptdatabase.html
Any attentions on publishing a version that works around that problem? Is it possible to have Migrator check and update the version number only once while executing the entire migrations array?
June 1st, 2010 - 10:56
Hmm, that’s pesky. I’ll see if there’s anything I can do about it. I won’t be able to test it on the iPhone, but of course I can test it on Safari for Windows. I’ll send you an email when I update the post.
June 5th, 2010 - 18:01
schory, not only does it not work on iPhone, it also doesn’t work on certain Safari versions because of a bug (id 27836, see https://bugs.webkit.org/show_bug.cgi?id=27836 )
I’ve found a workaround like this :
_db.changeVersion(_db.version, version,
function(tx){ // transaction
tx.executeSql(migrations[version]);
},
function(err){ // error
if (err.code == 2) {
// Safari bug 27836 probably occured
// changeVersion() will never work on a Safari version with this bug, so we need to do the transaction again, and manually maintain the version number
_doSafariBugMigration(versions, migration);
}
JuSy.log(err);
},
function(){ // success
JuSy.log(“[Html5Db] Database migrated to version : ” + version);
_doMigration(versions, migrations);
}
); // end changeVersion()
I know it’s fighting a symptom instead of solving the problem, but I can’t think of any other way…
June 5th, 2010 - 18:48
Robin, it’s worth noting that the Webkit bug you mention was supposedly fixed in October 2009. Naturally, this doesn’t mean that the fix has been picked up by all browsers using the Webkit engine, but the fix will propagate eventually.
January 16th, 2011 - 06:38
Tried to do the same, but catch an “current version of the database and `oldVersion` argument do not match” error. Can’t changeVersion on new database
January 16th, 2011 - 08:51
Not sure I exactly follow, but I did have problems with changeVersion not working on some Safari/Webkit browsers, so I resorted to maintaining an actual table for schema tracking instead (as mentioned in my note at the bottom).
If I’m interpreting what you’re saying though, you’d try to open a database with a particular version string, catch the exception if it wasn’t the expected version, and subsequently performing some sort of migration to get it up to the expected version using changeVersion..? If so, seems like that should work, in theory…
October 10th, 2011 - 08:04
Hi, Thanks for information
But,
Why NOT is this javascript library the rollback if I failed ?
June 6th, 2012 - 07:59
Thank you! I couldn’t find this at first and posted a question on StackOverflow about it. I’ll put your post in as an answer.
June 12th, 2012 - 03:57
Excelent post. I couldnt explain it better :p. Really usefull.
Thanks for this.
July 12th, 2012 - 04:34
Nice one, just after your article i found one more article which shows how to join all these pieces and setup a todo maker using websql . It is also nice and informative
http://www.thetutlage.com/post=TUT221
July 12th, 2012 - 05:22
Thanks a lot for the Migrator. Help me a lot and working very well. I did not find many discussions and exemples on the subject. So very useful!
January 14th, 2013 - 23:53
That is very userful, I decided to use it for my web app, appreciate again
April 5th, 2013 - 10:17
I’ve been through the same issues and here what I ended up doing, it could be easily improved but it’s pretty straight-forward as it is.
https://gist.github.com/YannickGagnon/5320593
April 22nd, 2013 - 08:01
Thanks! This saved me from doing it myself (although I already went halfway before searching, oops). Still useful after a few years.
http://38apps.net/html5-websql-database-joy/
April 22nd, 2013 - 22:02
In my library https://bitbucket.org/ytkyaw/ydn-db I reflect the existing schema and
migrate as necessary in one go. It is very effective, although the logic is very complicated.
July 23rd, 2013 - 09:30
Awesome.
You inspired my GWT based Web SQL migration mechanisms.
Thanks!