Before we say anything at all about Web SQL, we regret to inform you that this spec is dead. Or dying. Or at least sentenced to death. Web SQL as a feature was a rather interesting one. It gave you access to miniature databases within the browser. For web developers who did server-side work, this was especially nice because they may have had some familiarity with SQL already. However, for reasons that are not important to this book, the specification has been EOLed (End of Life) and will (possibly) not be available in the future. That means—in theory—you shouldn’t even be reading this chapter.
However...
Web SQL has very good support on mobile browsers, and was available before IndexedDB and much better supported than IndexedDB. It is entirely possible that as a developer, you will run into web apps making use of Web SQL. While we don’t recommend starting new projects with Web SQL, we hope this chapter will give you enough knowledge about the ins and outs of Web SQL so that if you have to help support an existing implementation you’ll know what to do.
As before, this particular client-side data storage technique will be tied to a particular domain. Storage limits are pretty varied and can range from 5 MB to 50 to more. Before we dive in, let’s cover some basic terms.
For those of you with experience working with traditional relational database servers, you can just go ahead and skip this section.
Much like IndexedDB, databases in Web SQL have a name and a version. Unlike in IndexedDB, though, the version number will not provide an event for you to perform changes; instead, it acts as a validation. If the user had an earlier version of the database, you can perform an update manually to handle changes. (We’ll show an easier way around that, however.) Next you provide a “friendly name” for the database, which as far as I can tell is never referenced again. You also are required to provide an initial size for the database. This is an estimated size, and frankly, most examples I’ve seen use the same value (5 MB) and don’t bother actually trying to figure out what size they really think they need. Your code will begin by opening the database, which is a synchronous API.
db
=
window
.
openDatabase
(
"name"
,
"1"
,
"nice name"
,
5
*
1024
*
1024
);
Note that I’ve taken the result of the window.openDatabase
call and stored it. This lets you perform operations on the database later on. Let’s consider a simple example that just opens up a database and uses the console to display the object itself (Example 5-1).
<!doctype html>
<html>
<head>
<script
type=
"text/javascript"
src =
"http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"
></script>
</head>
<body>
<script>
function
websqlOK
()
{
return
"openDatabase"
in
window
;
}
var
db
;
$
(
document
).
ready
(
function
()
{
//No support? Go in the corner and pout.
if
(
!
websqlOK
())
return
;
db
=
window
.
openDatabase
(
"db1"
,
"1"
,
"Database 1"
,
5
*
1024
*
1024
);
console
.
dir
(
db
);
});
</script>
</body>
</html>
While there isn’t much going on here, this shows the basic “how to get started”-type code for working with Web SQL. Figure 5-1 shows what Chrome displays in the console for the database object.
Once you’ve gotten a database object, you can then begin doing, well, everything—and unlike in IndexedDB, working with data in Web SQL is rather simple (assuming you know SQL, of course). Once again, a transaction will be used, and once again, you get to specify either a read-only or read/write transaction, but after that, the code remains the same no matter what you do. All that changes is the SQL. As a basic example, here is how you open a read-only transaction (this assumes you’ve created a Web SQL variable called db
):
db
.
readTransaction
(
function
to
do
stuff
,
error
handler
,
success
handler
);
In real code, this could look like this:
db
.
readTransaction
(
function
(
tx
)
{
tx
.
executeSql
(
"select * from foo"
);
},
function
(
e
)
{
console
.
log
(
"Db error "
,
e
);
},
function
()
{
console
.
log
(
"Done"
);
});
The first argument to the readTransaction
call is a function that is provided a transaction object. On that object, you can run executeSql
, which, as you might guess, is where you perform SQL queries. The second argument is the error handler and the last is the success handler.
So far, so good. This is where things get a tiny bit confusing. First, here is how the API works in general:
tx
.
executeSql
(
"sql statement"
,
"array of values"
,
"success handler"
,
"error hander"
);
Disregard the second argument for now; we’ll come back to it. The thing you’ll want to note most is that the order of handlers (success and then error) is the opposite of the transaction call. This is very easy to mess up, so be careful when working with these handlers.
Let’s enhance the initial demo to do some setup work. Before you can store data in a database, you’ll need a table. Luckily, it isn’t difficult to create tables in SQL. Example 5-2 shows how.
<!doctype html>
<html>
<head>
<script
type=
"text/javascript"
src =
"http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"
></script>
</head>
<body>
<script>
function
websqlOK
()
{
return
"openDatabase"
in
window
;
}
var
db
;
$
(
document
).
ready
(
function
()
{
//No support? Go in the corner and pout.
if
(
!
websqlOK
())
return
;
db
=
window
.
openDatabase
(
"db1"
,
"1"
,
"Database 1"
,
5
*
1024
*
1024
);
db
.
transaction
(
function
(
tx
)
{
tx
.
executeSql
(
"create table if not exists notes(id INTEGER PRIMARY "
+
"KEY AUTOINCREMENT, title TEXT, body TEXT, updated DATE)"
);
},
dbError
,
function
(
tx
)
{
ready
();
});
});
function
dbError
(
e
)
{
console
.
log
(
"Error"
,
e
);
}
function
ready
()
{
console
.
log
(
"Ready to do stuff!"
);
}
</script>
</body>
</html>
In this version, after the database is opened we create a read/write transaction (using db.transaction
). We then use SQL to create a table. The cool thing about this SQL is that it gracefully handles not doing anything if the table already exists. As we stated earlier, Web SQL does include the concept of versioning and does offer a way to perform tasks when versions change, but this style of setup is far simpler and most likely sufficient for your needs. You could execute multiple different SQL statements there to set up as many tables as you need.
Obviously all of this is simple if you know SQL. If you don’t, there are various books and tutorials that can help you. The SQL used in Example 5-2 creates a table called notes
. It has an id
column that will be the primary key for data, a title
and body
column that contain text, and an updated
column storing a date value.
Now let’s kick it up a notch with a real, but simple, demo in Example 5-3.
<!doctype html>
<html>
<head>
<script
type=
"text/javascript"
src =
"http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"
></script>
</head>
<body>
<h2>
Add a Note</h2>
<form>
Title:<input
type=
"text"
id=
"title"
><br/>
Body:<br/>
<textarea
id=
"body"
></textarea><br/>
<button
id=
"addNote"
>
Add Note</button>
</form>
<p/>
<table
id=
"notes"
border=
"1"
><tbody></tbody></table>
<script>
function
websqlOK
()
{
return
"openDatabase"
in
window
;
}
var
db
;
$
(
document
).
ready
(
function
()
{
//No support? Go in the corner and pout.
if
(
!
websqlOK
())
return
;
db
=
window
.
openDatabase
(
"db1"
,
"1"
,
"Database 1"
,
5
*
1024
*
1024
);
db
.
transaction
(
function
(
tx
)
{
tx
.
executeSql
(
"create table if not exists notes(id INTEGER PRIMARY "
+
"KEY AUTOINCREMENT, title TEXT, body TEXT, updated DATE)"
);
},
dbError
,
function
(
tx
)
{
ready
();
});
});
function
dbError
(
e
)
{
console
.
log
(
"Error"
,
e
);
}
var
$title
,
$body
,
$notesTable
;
function
ready
()
{
$
(
"#addNote"
).
on
(
"click"
,
addNote
);
$title
=
$
(
"#title"
);
$body
=
$
(
"#body"
);
$notesTable
=
$
(
"#notes tbody"
);
renderNotes
();
}
function
addNote
(
e
)
{
e
.
preventDefault
();
//no validation
var
title
=
$title
.
val
();
var
body
=
$body
.
val
();
db
.
transaction
(
function
(
tx
)
{
tx
.
executeSql
(
"insert into notes(title,body,updated) values("
+
"'"
+
title
+
"','"
+
body
+
"',"
+
(
new
Date
().
getTime
())
+
")"
);
},
dbError
,
function
(
tx
)
{
$title
.
val
(
""
);
$body
.
val
(
""
);
renderNotes
();
});
}
function
renderNotes
()
{
db
.
readTransaction
(
function
(
tx
)
{
tx
.
executeSql
(
"select * from notes order by updated desc"
,[],
function
(
tx
,
results
)
{
var
rowStr
=
""
;
for
(
var
i
=
0
;
i
<
results
.
rows
.
length
;
i
++
)
{
var
row
=
results
.
rows
.
item
(
i
);
//use row.col
rowStr
+=
"<tr><td>"
+
row
.
title
+
"</td>"
;
rowStr
+=
"<td>"
+
row
.
body
+
"</td>"
;
var
d
=
new
Date
();
d
.
setTime
(
row
.
updated
);
rowStr
+=
"<td>"
+
d
.
toDateString
()
+
" "
+
d
.
toTimeString
();
rowStr
+=
"</td></tr>"
;
};
$notesTable
.
empty
();
$notesTable
.
append
(
rowStr
);
});
},
dbError
);
}
</script>
</body>
</html>
This new version includes a form and an empty table. The form will be used to let the user enter a note (a title and the body), while the table will be used to display existing data. That’s it for the user interface—now let’s dig into the code.
The ready
function will be run after the initial table creation SQL is executed. Remember, this SQL safely runs multiple times, as it won’t recreate the table after the first time. We add a simple click handler for the form, store some jQuery variables from the DOM, and immediately run the renderNotes
function.
The addNote
click handler simply fetches the form values and then creates a SQL statement to handle the insert. This SQL statement is rather brittle. We’ll fix that in the next update. Once the SQL statement is executed, renderNotes
is run again to update the display.
Within renderNotes
, we again have a transaction, but note the change to a read-only transaction. You can see that we select all rows and order by the updated
column so we always get the latest data first. Ignore that empty array argument for now. Once the SQL is executed, we can work with the results. The success handler for executeSql
is passed the transaction object itself and the results. This results
object is an instance of a SQLResultSet
. It has a rows
property, which has a length
allowing us to loop over it. To get an individual row, the item
method is called with the corresponding row number. That row object is just a set of key/value pairs representing the columns in the row. A string is used to construct a table (yes, yes, I know, tables are passé), which is then rendered out to the DOM. Figure 5-2 demonstrates how this looks. (And yes, it could be much better designed.)
So, now that you have a basic idea of how Web SQL works, let’s focus on the insert statement from Example 5-3:
tx
.
executeSql
(
"insert into notes(title,body,updated) values("
+
"'"
+
title
+
"','"
+
body
+
"',"
+
(
new
Date
().
getTime
())
+
")"
);
When executed, this generates a SQL statement that could look like so:
insert
into
notes
(
title
,
body
,
updated
)
values
(
'some title'
,
'some body'
,
1
)
The issue with the code is that if the form values themselves included a single quote character, the SQL would break. Typically, allowing user input to drive dynamic SQL leads to something called a SQL injection attack. It’s nasty, but luckily easily fixed. Remember that second argument that was an empty array? Instead of creating a dynamic SQL string with concatenation, you can use “tokens” within the SQL that represent variables. You can then use the array argument to supply those values. Example 5-4 demonstrates how simple this is to put into effect.
function
addNote
(
e
)
{
e
.
preventDefault
();
//no validation
var
title
=
$title
.
val
();
var
body
=
$body
.
val
();
db
.
transaction
(
function
(
tx
)
{
tx
.
executeSql
(
"insert into notes(title,body,updated) "
+
"values(?,?,?)"
,
[
title
,
body
,
new
Date
().
getTime
()]);
},
dbError
,
function
(
tx
)
{
$title
.
val
(
""
);
$body
.
val
(
""
);
renderNotes
();
});
}
Notice how the SQL now is a simple string—no embedded variables. Where the variables were, there are now question marks. They will be replaced in the same order as the values included in the array in the next argument.
You can find pretty good support for Web SQL in Chrome’s Dev Tools. Under the Resources tab, you’ll see a section just for Web SQL along with any defined databases. Selecting one and expanding it then lets you select a table to view all the data (see Figure 5-3).
The empty text field at the bottom of the data display lets you enter the name of a column. Doing so will filter the view to just the primary key and that column. What isn’t terribly obvious is that if you click on the database itself, you’ll see a console where you can enter arbitrary SQL statements (see Figure 5-4).
As we mentioned in the beginning of the chapter, you’ll probably not be starting new projects with Web SQL, but if you have to debug an existing one, Chrome’s Dev Tools support can be very useful.
Let’s look at the current state of Web SQL support in Figure 5-5.
As you can see, Chrome, Safari, and their respective mobile versions support this feature—not bad for a dead spec. But alas, it is truly dead (or on the way out), so the recommendation is to not use it if you can avoid it. If you are going to use it, then the same places where you would use IndexedDB would certainly apply here as well.
18.118.12.157