HTML5 and Browser Caching: Part 2
Part 2: Web SQL Database
Web SQL Database is essentially a local implementation of SQLLite inside your browser. It can be queried using SQL, and offers a lot more functionality than the simple key-value store that Web Storage provides. There also seems to be a difference in the way browsers handle it.
- Transactions & Queries: Being a database you can setup a schema, and query the data easily. Web SQL also supports transactions, meaning that you can setup tranactions that contain one or more SQL statements. You are then able to rollback the transaction if any of the statements fails (either the SQL or the code in the transaction), so any changes are never committed.
- Variable Size: The default size for the database in most browsers is 5MB. However when you setup the database (and also it seems when extra items are stored) you can request extra space. This usually prompts the user with a dialogue box asking if they want to allow this. This is an advantage over for example using Web Storage as it means that it is often possible to store more than 5MB of data.
- Browser Support: The API for Web SQL Database is not supported by most desktop browsers (including FireFox and IE) See a useful list. Also they do not plan to introduce support. This is because the specification is no longer being maintained and it looks likely that the IndexedDB standard will take it’s place in future versions, and will be far more widely supported. IndexedDB isn’t currently any supported for this generation of browsers so is not practical to develop for at the moment. For this reason we have chosen to use Web SQL in our mobile application, with a view to replacing this code with the IndexedDB API once this is widely supported.
- Versioning: The implementation of versioning seems a bit odd. You need to know the database version number before you are able to connect to an already existing database, however without being able to connect to a users database you can’t find out what version they are using. This obviously makes things a little difficult when you are trying to upgrade to a newer version, with a different schema.
Using Web SQL Database:
If you are interested in getting your feet wet there’s a great article on how to use Web SQL Database at HTML5 Doctor . It covers creating and connecting to a database, querying and dealing with the results, and also has some nice demos.