html5sql.js Home Page

Overview

html5sql is a light JavaScript module that makes working with the HTML5 Web Database a whole lot easier. Its primary function is to provides a structure for the SEQUENTIAL processing of SQL statements within a single transaction. This alone greatly simplifies the interaction with the database however it doesn't stop there. Many other smaller features have been included to make things easier, more natural and more convenient for the programmer.

Core Features

Provide the capability to sequentially process SQL in many different forms:
- As a single SQL statement string.
- As an array of SQL statement strings.
- As an array of SQL statement objects (if you want to inject data into the SQL or get a callback after each SQL statement is processed)
- As a string containing multiple SQL statements, each of which ends in a semicolon.
- From a completely separate file containing SQL statements.
Provide a framework for controlling the version of a database.

Example

If you have tried using an HTML5 web database you know how complex it can be. Especially when you are setting it up your tables in the beginning. Well you will be happy to know this is no more.

To show you what I mean and illustrate the power of this module, take a look at this example:

Say you want to set up a table and insert a bunch of rows into this table. If you are using html5sql you would just put all the statements necessary to create your table into a separate file, which for this example we will name Setup-Tables.SQL. This file would probably look something like this:

CREATE TABLE example (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO example (data) VALUES ('First');
INSERT INTO example (data) VALUES ('Second');
INSERT INTO example (data) VALUES ('Third');

CREATE TABLE example2 (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO example2 (data) VALUES ('First');
INSERT INTO example2 (data) VALUES ('Second');
INSERT INTO example2 (data) VALUES ('Third');

With html5sql, to sequentially call these SQL statements and create your table(s) all you would need to do is open your database and then add a snippet of code like this:

$.get('Setup-Tables.SQL',function(sqlStatements){
    html5sql.process(
        //This is the text data from the SQL file you retrieved
        sqlStatements,
        function(){
            // After all statements are processed this function
            //   will be called.
        },
        function(error){
            // Handle any errors here
        }
    );
});

With the the jQuery get function your list of SQL statements is retrieved from your separate file, split into individual statements and processed sequentially in the order they appear.

While this is just a simple example it illustrates how much easier this module can make using an html5sql database.

Performance

While all this sounds great, you may be wondering if performance suffers when SQL statements are processed sequentially. The answer, as far as I can tell, is not that much.

For example, html5sql was able to create a table and sequentially insert 10,000 records into that table varying amounts of time but averaging somewhere between 2 and 6 seconds using the Google Chrome browser on my desktop. This test leads me to believe large data sets should be handled by html5sql quite well.

Rational

At its very core, SQL is designed to be a sequentially processed language. Certain statements must be processed before other statements. For example, a table must be created before data is inserted into it. Conversely, JavaScript is a very asynchronous, event driven language. This asynchronous nature is very present in the HTML5 client side database spec and introduces a high degree of complexity for the programmer. It is for this reason that this module was written.

This library was written with the understanding that the W3C has ceased to maintain the spec for the Web SQL Database. Even though they have withdrawn from the spec, because webkit has incorporated it the number if internet users with a compatible browser is still significant, expecially on mobile devices.

While this module decreases the complexity of using an HTML5 SQL database, it does not attempt to simplify the SQL itself. This is intentional. SQL is a powerful language and attempts to simplify it seem to only decrease it power and utility. In my experience, a better option is to just learn SQL better so it becomes more natural. An excellent resource for learning SQL is the SQLite website.

Goals for the future

I would like to continue to expand this library and incorporate more ideas to help programmers use HTML5 web databases. Eventually I would love to see this library used by everyone who wishes to utilize the HTML5 Web Database.

HTML5SQL.JS