Chrome Extensions and Web SQL - practical experience

I think Web SQL is not one of the coolest new features in modern browsers like Canvas or WebSockets because it's probably useful just for really specific sort of web apps and I've honestly never seen any real usage of it. Well, until I made PHP Ninja manual of course :-).

Just briefly, PHP Ninja manual is a Chrome Extension that contains pre-parsed whole official PHP manual (that's around 6000 methods) and allows you to search immediately in it.

I was first trying to keep everything very simple and use just simple JSON files to store the "database". It worked fine and it was really fast (Chrome parses for instance 300 kB JSON in just a few miliseconds) but there was one significant disadvantage.

I couldn't "search" among the "database".

If I typed for example replace it didn't find anything because there's no method that starts with the word 'replace'. But what if I was still interested in methods like str_replace or array_replace? The only way was to traverse through all 6000 methods and find those that contains word replace. At this point JSON turned out to be useless so my only chance was to switch to Web SQL database.

Web SQL

There are some nice tutorials on Web SQL:

But what I couldn't find anywhere was some practical experience or summary what are pros and cons of using Web SQL (eventualy in combination with Chrome Extensions).

So, now I think that there are some points I should be aware of before I started rewriting my code for Web SQL:

  1. everything in Web SQL is always asynchronous (you need callbacks for everything)
  2. there's nothing like phpMyAdmin for Web SQL (but it's SQLite).
  3. you can't distribute your Chrome extension (or website) with pre-indexed database.
  4. maximum database size is limited.
  5. there are some weird things...

Here in more detail:

1. It's all asynchronous

For me, and I believe for most developers, is easier to develop synchronous applications than asynchronous (maybe that's why many developers write such horrible JavaScript code). Rewriting most of the application logic into asynchronous calls meant a lot of refactoring for me.

The good thing is that it forced me to rethink all the logic and in the end it's way better than the original version.

2. Forget phpMyAdmin

It's very uncomfortable to maintain Web SQL databases because there's no sofisticated tool like phpMyAdmin. Once you create database tables you have to rely that you created them correctly. Chrome Developer Toolbar gives you some basic view on data stored in your database but you can't execute your own SQL queries so it isn't really helpful.

On the other hand Web SQL is just an ordinary SQLite3 database which can be opened with tools like Lita.

Google Chrome stores all Web SQL databases in:

Windows Vista or 7: \Users\_username_\AppData\Local\Google\Chrome\User Data\Default\databases
Windows XP: \Documents and Settings\_username_\Local Settings\Application Data\Google\Chrome\User Data\Default\databases
Mac OS X: ~/Library/Application Support/Google/Chrome/Default/databases
Linux: ~/.config/google-chrome/Default/databases

Actually, to make sure that database tables are always created I try to "recreate" them every time I connected to the database with openDatabase:

 1 
 2 
 3 
 4 
 5 
db = window.openDatabase('test', '1.0', 'My test database', 1024 * 1024, function(db) {});
db.transaction(function (tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY ASC AUTOINCREMENT, title, description)');
    tx.executeSql('CREATE INDEX IF NOT EXISTS title_idx ON my_table (title)');
});

The `IF NOT EXISTS` is essential phrase here. Without it, these queries would throw an error.

3. You can't distribute your application with pre-indexed database

This doesn't matter for apps that want to store in database just user created data. For me (and for all apps based on some "dictionary") this is a big problem. Probably the only solution is to distribute your app with all data you need to index in some eg. JSON (like I did) and when the app is first lunched index the database.

Here comes another important question. Are users willing to wait? I measured number of users who started installation, users who finished the installation and users who closed and reopened the popup window during the installaton. Results look like this (I should note that cca 6000 inserts took in average 13.1 seconds. On my laptop it's just 6 seconds):

I was surprised that 83.4% of users were willing to wait for 13.1 second until the installation is finished without closing the window.

4. Database size is limited

For extensions and I guess also for websites Google Chrome limits the maximum size for databases to 5 MB.

Fortunatelly, for Chrome Extensions you can bypass this limitation by setting unlimitedStorage permission in your manifest.json.

5. The weird things

There are a few things that were quiet weird for me. For instance:

This function in called right after the database is opened and according to W3C Web SQL draft you can use it to migrate the database (eg. update table structure) as it's explained in the draft:

 1 
 2 
 3 
 4 
 5 
openDatabase('documents', '1.0', 'my storage', 5*1024*1024, function (db) {
    db.changeVersion('', '1.0', function (t) {
        t.executeSql('CREATE TABLE docids (id, name)');
    }, error);
});

Maybe I just misunderstood something but it didn't work as I expected. For me it's not such a big problem because I include in the extension simple JSON with timestamp when was the database generated and after installation I save it to localStorage. Every time the popup is opened I compare these two timestamps and if they don't match I dump all data and tables and creat them again from scratch.

Conclusion

I think in many cases it's not really necessary to use Web SQL because you can achive the same results with localStorage in combination with JSON.parse and JSON.stringify which is way easier than learning and implementing Web SQL.

Another important thing is that WebSQL is "is no longer in active maintenance" by W3C Working Group, so maybe this technology will be deprecated in the future.

Anyway, it has great potential but its implementation isn't painless and you should think twice whether it's really the best option for you.

blog comments powered by Disqus