Introducing Web SQL Databases

by .

Please beware that as of 18th November the W3C is no longer actively working on the Web SQL Database specification.

The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications, so it’s about time we dig in and check it out.

What’s in the box?

If you haven’t guessed from the overly verbose specification title, Web SQL Databases is a spec that brings SQL to the client side. If you have a back-end developer’s background, then you’ll probably be familiar with SQL and happy as a pig in muck. If not, you might want to learn some SQL before you start hacking around, Google’s your friend here.

The specification is based around SQLite (3.1.19), but having come from MySQL myself, it’s all pretty much the same (sorry for the sweeping statement!).

For an example of Web SQL Databases working, have a look at the Twitter HTML5 chatter demo I put together.

order levitra
It uses SQL and the WHERE clause to narrow down the recent chat about HTML5 on Twitter (it will work in Safari, Chrome and Opera 10.50).

There are three core methods in the spec that I’m going to cover in this article:

  1. openDatabase
  2. transaction
  3. executeSql

Support is a little patchy at the moment. Only Webkit (Safari, SafariMobile and Chrome) and Opera 10.50 (ATOW alpha on Mac) support web databases. Fellow Doctor Bruce Lawson has told me that Firefox are holding off as they feel there’s a better implementation than SQLite (though I hope it’s similar, whatever they pick). Either way, I’d definitely recommend checking out the SQLite documentation for the functions that are available.

Because of this patchy support and the simple fact that Webkit had implemented the database spec some time ago, the spec on the W3C is now slightly ahead of the implementations in Safari, while Webkit is still catching up. On the other hand, since Opera has only just added support, it’s closer to the spec (I’ll mention the differences as we go along).

Nonetheless, it’s fun to play with, so let’s get playing!

Creating and Opening Databases

If you try to open a database that doesn’t exist, the API will create it on the fly for you. You also don’t have to worry about closing databases.

To create and open a database, use the following code:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

I’ve passed four arguments to the openDatabase method. These are:

  1. Database name
  2. Version number
  3. Text description
  4. Estimated size of database

The missing feature of openDatabase (I’m not sure when it was added) is the fifth argument:

  1. Creation callback

The creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly versioned.

The return value from openDatabase contains the transaction methods, so we’ll need to capture this to be able to perform SQL queries.

Estimated database size

From the tests I’ve run, only Safari prompts the user if you try to create a database over the size of the default database size, 5MB. The prompt is shown the image below, asking whether you want to grant the database permission to scale up to the next size of database — 5, 10, 50, 100 and 500MB. Opera, on the other hand, builds the database without complaining, which I expect might change later as it’s still in alpha.

Webkit database size prompt

Versions

I could be wrong, but everything I’ve tested so far says that versioning in SQL databases is borked. The problem is this:

If you upgrade your database to version 2.0 (e.g., there are some important schema changes since version 1.0), how do you know which visitors are on version 1.0 and which are on version 2.0?

The version number is a required argument to openDatabase, so you must know the version number before you try to open it. Otherwise, an exception is thrown.

Also, changeVersion, the method to change the database version, is not fully supported in Webkit. It works in Chrome and Opera, but not in Safari or Webkit. Regardless, if I can’t determine which version of database the user is on, then I can’t upgrade the user.

A possible workaround is to maintain a state database, something like the ‘mysql’ database in MySQL. This way, you would only have one version of this state database, and within this you would record the current version of any databases that control your application. It’s a hack, but it works.

Transactions

Now that we’ve opened our database, we can create transactions. Why bother with transactions instead of just running our SQL? Transactions give us the ability to rollback. This means that if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.

There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.

The transaction is simply a function that contains some code:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  // here be the transaction
  // do SQL magic here using the tx object
});

I recently uploaded a demo to html5demos.com that demonstrates a transaction rollback in action: Web SQL database rollback demo

In the nightly builds of the browsers, we also have db.readTransaction, which allows only read statements to run on the database. I assume there are performance benefits to using a read-only readTransaction instead of a read/write transaction, most probably to do with table locking.

Now that we’ve got our transaction object (named tx in my example) we’re ready to run some SQL!

executeSql

This is the funnel of love for all your SQL goodness. executeSql is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.

Once we have a transaction object, we can call executeSql:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE foo (id unique, text)');
});

This will now create a simple table called “foo” in our database called “mydb”. Note that if the database already exists the transaction will fail, so any successive SQL wouldn’t run. So we can either use another transaction, or we can only create the table if it doesn’t exist, which I’ll do now so I can insert a new row in the same transaction:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
  tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');
});

Now our table has a single row inside it. What if we want to capture the text from the user or some external source? We’d want to ensure it can’t compromise the security of our database (using something nasty like SQL injection). The second argument to executeSql maps field data to the query, like so:

tx.executeSql('INSERT INTO foo (id, text) VALUES (?, ?)', [id, userValue]);

id and userValue are external variables, and executeSql maps each item in the array argument to the “?”s.

Finally, if we want to select values from the table, we use a callback to capture the results:

tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
  var len = results.rows.length, i;
  for (i = 0; i < len; i++) {
    alert(results.rows.item(i).text);
  }
});

(Notice that in this query, there are no fields being mapped, but in order to use the third argument, I need to pass in an empty array for the second argument.)

The callback receives the transaction object (again) and the results object. The results object contains a rows object, which is array-like but isn’t an array. It has a length, but to get to the individual rows, you need to use results.rows.item(i), where i is the index of the row. This will return an object representation of the row. For example, if your database has a name and an age field, the row will contain a name and an age property. The value of the age field could be accessed using results.rows.item(i).age.

That’s all you should need to get started with Web SQL Databases. I’m certain that mini JavaScript libraries are going to emerge to help support working with databases. If you want to find out more about SQL databases (shameless self promotion begins) I just finished the storage chapter for Introducing HTML5, which I’m writing with fellow Doc Bruce, so check that bad boy out too!

Demos

94 Responses on the article “Introducing Web SQL Databases”

  • […] posted here: Introducing Web SQL Databases | HTML5 Doctor Share and […]

  • BWRic says:

    “Firefox are holding off as they feel there’s a better implementation than SQLite”

    Any idea what they’re looking to implement? Aren’t they using SQLite for bookmarks, history etc in the browser now so I’m surprised they don’t like it here.

  • Shelley says:

    I wouldn’t expect this spec to go anywhere because of the external dependencies. It’s important that people reading this post be aware that there is a strong possibility of this not going anywhere.

    From the spec:

    “This specification has reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path. Until another implementor is interested in implementing this spec, the description of the SQL dialect has been left as simply a reference to Sqlite, which isn’t acceptable for a standard. Should you be an implementor interested in implementing an independent SQL backend, please contact the editor so that he can write a specification for the dialect, thus allowing this specification to move forward.”

    Think about what this is asking for. Now, how realistic do you believe this to be?

    I didn’t cover it in my book.

  • Remy Sharp says:

    @Shelly – I read the spec and had that exact same thought: I can’t imagine that there’s going to be a spec’ed version of SQL any time soon (personally I half expect that this doesn’t ever happen)!

    That doesn’t mean it shouldn’t be discussed or played with. There are platforms where the browser is known to developers (i.e. not a great example, but people writing iPhone web apps), so this is one technology that can be exploited.

  • Shelley says:

    Remy, true: if you’re working in an environment outside of the normal web, such as with iPhone development, then all you need worry about is the implementation supported by Safari. Same, with other smart phones and using Opera mini, or whatever.

    But I don’t think there will ever be a W3C “standard” to provide a level of stability that a lot of companies are going to way. I think this one will always be a maverick implementation, because it will always be dependent on SQLite.

    I suppose Google could create its own relational database backend for this — but you know, the company hasn’t been making the best choices lately, so I would hesitate to use a SQLite alternative it comes up with.

    People have to think about this, and ask themselves: what does it provide that localStorage won’t provide? And do they really want to embed SQL into their JavaScript applications?

    Maybe they do, and they can afford the limited implementation. That’s cool, as long as people’s expectations about where all of this is going are realistic. And folks know this is not a component of HTML5.

  • Shelley says:

    Sorry, “going to want”, not “going to way”.

  • Mark says:

    Slightly off topic – ‘relative_time’ doesn’t seem to be working correctly. All tweets are listed as ‘[posted less than 5 seconds ago]’.

  • edvakf says:

    @BWRic

    “Firefox are holding off as they feel there’s a better implementation than SQLite”

    This is not exactly true. Mozilla and Microsoft’s thoughs are that requiring a specific version of SQLite (as a library and as an SQL dialect) is too harmful to the web since they can’t assure SQLite’s future versions will be compatible with the current version.

    You can read more about it in the links below.

    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0326.html
    http://groups.google.com/group/mozilla.community.web-standards/browse_thread/thread/da7000dcc486c0fb/c4aa832133ff907a#c4aa832133ff907a
    http://groups.google.com/group/mozilla.community.web-standards/browse_thread/thread/6f7af6cc0dd46070#
    http://lists.w3.org/Archives/Public/public-webapps/2009AprJun/1242.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0426.html
    http://o-micron.blogspot.com/2009/09/now-published-alternative-to-sql-for.html
    http://lists.w3.org/Archives/Public/public-webapps/2009AprJun/0106.html
    http://lists.w3.org/Archives/Public/public-webapps/2009JulSep/1136.html
    http://www.w3.org/2009/11/02-webapps-minutes.html#item14
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0674.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1263.html

    Finally Hixie decided that he doesn’t want to push the spec further unless there is any vendor who wants to implement the Web SQL Database without use of the SQLite.
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/0942.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1052.html
    http://lists.w3.org/Archives/Public/public-webapps/2009OctDec/1078.html

  • Stuart Jones says:

    Out of interest – what is the advantage of this over, say, a well-implemented Ajax/Server system?

  • mors says:

    Hi.
    I’m an Opera developer, and participated in Opera’s Web DB implementation.

    About the article: simple and sweet, but you could have covered error handling better. Note that there is window.SQLError. You can also use resultSet.rows[index], per the specification.

    Just a few remarks about Opera’s implementation:
    – Opera provides a default quota of 5MB per domain (origin in html5). Only after trying to overflow that quota will Opera request anything from the user. Current betas of 10.50 don’t implement the request, but the final will.
    – the quota is shared among all databases for a single origin
    – Opera