Programming in HTML5 with JavaScript & CSS3 Training Guide
<– back to Chapter 16 - Offline Web Applications
Web SQL is arguably one of the most powerful options available to you. It provides a full relational database that includes many of the features you’ve come to enjoy from server-side database offerings.
Most implementations are built on SQLite, which is one of the most widely used lightweight database engines. It’s an open-source solution with a vibrant community backing it.
Before starting with Web SQL, be aware that the W3C has stated that Web SQL is no longer on its recommendation track and that it does not intend to maintain the specification.
What this means depends on the browser you’re targeting. While the spec is no longer being maintained, some browsers (Safari and Chrome) have continued their support.
Therefore, Web SQL might be a viable option if you’re building specifically for a platform such as iOS for iPad or iPhone. Another common use is in building Google Chrome extensions.
If you need a browser-agnostic solution then you might be better off with IndexedDB instead.
In the following sections, examine the syntax used to create or open a database, start a transaction, and execute a SQL command. Most of these command should look very familiar if you’ve used other relational databases.
To start communication with a database, use the openDatabase method, which returns a Database object. If you attempt to open a database that doesn’t exist, it will be automatically created for you, so you won’t need to execute any extra steps.
The following are the openDatabase parameters.
In the following example, a database named Library is created with an estimated size of 5 MB. It returns a Database object that supports transactional operations.
var db = openDatabase('Library', '1.0', 'My library', 5 * 1024 *1024);
If you’re familiar with traditional database connections, you might be expecting a need to close a connection. With Web SQL, however, that’s automatically handled, so you don’t have to close the connection manually.
As your application grows, your data requirements change. You might need to add new tables, drop existing ones, or even change particular columns. The Database object provides the following hooks for making those changes.
You can add an authors table to the Library created earlier. You need a callback method that accepts a transaction object, which executes the CREATE TABLE script.
The transaction object allows multiple actions within it, and it automatically rolls back all changes if any fail. For now, this example keeps the idea simple by adding just one table.
function migrateDB(transaction) {
transaction.executeSql("CREATE TABLE IF NOT EXISTS authors(" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"firstName TEXT, " +
"lastName TEXT, " +
"dateCreated TIMESTAMP DEFAULT(datetime('now', 'localtime')))"
);
}
function onError(error) {
console.log('Error Code:', error.code, 'Message: ', error.message );
}
function onSuccess() {
console.log('Migration complete!');
}
var db = openDatabase('Library', '1.0', 'My library', 5 * 1024 * 1024);
db.changeVersion('1.0', '2.0', migrateDB, onError, onSuccess);
Live Sample - Web SQL - Create database, add table, insert, update, delete
Later in the chapter, you can read the version property of the Database object to determine the schema version with which you are working.
Note that version updates are applied asynchronously, so if the following line was placed immediately after the db.changeVersion() call in the preceding code, it would still display 1.0 because the console.log() method would fire before the migrations had a chance to complete.
console.log("Current schema:", db.version);
Now that the migration has been applied, you have a new table in your database with the following columns.
id that is one greater than the id of the last record added.Now that you have a schema in place, you can use transactions to execute SQL statements. To do this, the Database object provides the following two methods.
Both methods accept the same three parameters.
The callback method will receive a transaction object that includes an executeSql method for performing data changes. It has the following parameters.
In the next section, you see how you can use the transactions to execute some of the most commonly used SQL commands.
Now that you have a database and table in place, add a new record. Like creating a new table, do this by using the executeSql method on the transaction instance.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
db.transaction(function(t) {
t.executeSql("INSERT INTO authors (firstName, lastName) " +
" VALUES ('James', 'Priest')");
});
However, in general, it’s a good idea to useSQL parameters when working with dynamic SQL. The preceding statement can be rewritten to take advantage of an optional second parameter on the executeSql method, which accepts an array of field values.
Note the use of the question marks to indicate that the value will be populated from the array being passed in.
var firstName = 'James';
var lastName = 'Priest';
db.transaction(function(t) {
t.executeSql("INSERT INTO authors (firstName, lastName) VALUES(?, ?)",
[firstName, lastName]);
});
You can go a step further by adding a callback to the executeSql method, which enables you to capture the Id of the newly created row.
function itemInserted(transaction, results) {
console.log("Id:", results.insertId);
}
var firstName = 'James';
var lastName = 'Priest';
db.transaction(function(t) {
t.executeSql("INSERT INTO authors (firstName, lastName) VALUES(?, ?)",
[firstName, lastName],
itemInserted);
});
Live Sample - Web SQL - Create database, add table, insert, update, delete
In the following example, the lastName of the author, which has an id of 1, is updated. Besides the SQL syntax differences, it’s very similar to the code used for adding a new record.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
var authorId = 1;
var lastName = 'Smith';
db.transaction(function(t) {
t.executeSql("UPDATE authors SET lastName = ? WHERE id = ?"
, [lastName, authorId]);
});
Live Sample - Web SQL - Create database, add table, insert, update, delete
Removing records is also fairly straightforward. The following example deletes the author record with an id of 1.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
var authorId = 1;
db.transaction(function(t) {
t.executeSql("DELETE FROM authors WHERE id = ?", [authorId]);
});
Live Sample - Web SQL - Create database, add table, insert, update, delete
Now that you know how to add data to the database, you can read and display those records back to the user. Create a simple SELECT statement to read all values from the authors table. When executeSql is called this time, a callback method is passed that accepts a transaction object and a results set containing the rows returned from the SQL statement.
As the displayResults method iterates through the rows, it formats the person’s name in a list item and adds it to an unordered list with an id of items. To access the individual column values within the row, use dot notation, which reads each as a property on the object.
function displayResults(transaction, results) {
for (var i = 0; i < results.rows.length; i++) {
var item = results.rows.items(i);
$('#items').append('<li>' + item.firstName + ' ' + item.lastName + '</li>');
}
}
var db = openDatabase('library', '2.0', 'My library', 5 * 1024 * 1024);
db.transaction(function(t) {
t.executeSql("SELECT * FROM authors", [], displayResults)
});
Because you are only retrieving data, you just as easily could have used the readTransaction method instead of the transaction method.
db.readTransaction(function(t) {
t.executeSql("SELECT * FROM authors", [], displayResults);
})
Quick check
- The following statement has a syntax error in the second step of the transaction in this migration script (misspelled CREATE as CRATE). What do you expect will happen because of this migration script?
function migrateDB(transaction) {
transaction.executeSql("CREATE TABLE authors(firstName TEXT)");
transaction.executeSql("CREATE TABLE books(title TEXT)");
}
var db = openDatabase('Library', '1.0', 'My library', 5 * 1024 * 1024);
db.changeVersion('1.0', '2.0', migrateDB);
Answer
- Neither table will be created.
You rarely want to read every row from a database table; most of the time, you need to limit those results to specific criteria. Because current implementations are based on SQLite, you have all the power of a mature database engine to help you.
For example, you can add a WHERE clause to return only records with a specific lastName value, as follows.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
var lastName = 'Priest';
db.transaction(function(t) {
t.executeSql("SELECT * FROM authors WHERE lastName = ?"
, [lastName], displayResults);
});
You might like to find all authors whose last name starts with the letter D. To do so, use the LIKE keyword along with the % wildcard.
var lastName = 'D%';
db.transaction(function(t) {
t.executeSql("SELECT * FROM authors WHERE lastName LIKE ?"
, [lastName], displayResults);
});
Web SQL includes support for traditional JOIN statements (such as INNER JOIN and LEFT JOIN), which can be used to include columns from multiple tables within a single SELECT statement.
Assume you added a books table to your library database and would now like to modify your earlier query to include the title of each book in the results.
var db = openDatabase('Library', '1.0', 'My library', 5 * 1024 * 1024);
var lastName = 'D%';
db.transaction(function(t) {
t.executeSql("SELECT a.firstName, a.lastName, b.title " +
"FROM authors a " +
"INNER JOIN books b on a.id = b.authorId " +
"WHERE lastName LIKE ?"
, [lastName], displayResults);
});
Another useful feature of Web SQL is the ability to group results, which enables the use of more advanced functions such as COUNT(x), MIN(x), MAX(x), and SUM(x) within your SELECT statements. For example, the following is a new query that finds the number of books written by each author.
db.transaction(function(t) {
t.executeSql("SELECT a.firstName, a.lastName, COUNT(b.id) as numOfBooks " +
"FROM authors a " +
"INNER JOIN books b on a.id = b.authorId " +
"GROUP BY a.id"
, [], displayResults);
});
openDatabase() command. If the database does not exist, it will be created automatically.changeVersion() method.var db = createDatabase('mydb', '1.0', 'My database', 'My database', 5 * 1024 * 1024);var db = new Database('mydb');var db = initDatabase('mydb', '1.0', 'My database', 'My database', 5 * 1024 * 1024);var db = openDatabase('mydb', '1.0', 'My database', 'My database', 5 * 1024 * 1024);t.executeSql("INSERT INTO books(title) VALUES(?)", ["Harry Potter"]);t.executeSql("INSERT INTO books(title) VALUES([0])", ["Harry Potter"]);t.executeSql("INSERT INTO books(title) VALUES([1])", ["Harry Potter"]);t.executeSql("INSERT INTO books(title) VALUES({0})", ["Harry Potter"]);