Introduction to Web SQL

In this post, we will see some information about Web SQL. I know all of you are familiar with SQL. If not, I strongly recommend you to read some basic information here. As the name implies, Web SQL has so many similarities with SQL. So if you are good in SQL, you will love Web SQL too. Web SQL is an API which helps developers to do some database operations on the client side, like creating database, opening the transaction, creating tables, inserting values to tables, deleting values, reading the data. If you need any other way to save some data in client side, you can use storage mechanisms introduced in HTML5.

Now, we will look at some of the operations a developer can do with Web SQL. I hope you will like this.

Using the Code

As you all know, to work with SQL queries, you must create a database. So the first step we are going to do is create the database.

Create/Open Web SQL Database

To create a Web SQL database, we can use a function called openDatabase which has four parameters as follows:

• Database name
• Version Number
• Description
• Size
• Creation callback.

The creation callback gets fired while the database is being created.

Now, shall we open a Web SQL database with the above mentioned parameters? We can do that by running a query as follows:

var myDBInstance = openDatabase('dbSibeeshPassion', '1.0',
       'This is a client side database', 2 * 1024 * 1024);

Here, I have given the size of my database as 2*1024*1024. In most browsers, the size is flexible, but few maintain a limit of 5 MB. As from the above query, we have created a Web SQL database. Now, we will check whether the DB is created successfully or not.

//check whether the database is created or not.
        if (!myDBInstance) {
            alert('Oops, your database was not created');
        }
        else {
            var version = myDBInstance.version;
        }

Here, you will get an alert if the database is not created. Or you will be able to fetch the version details from the database instance.

Once the database is created, we can start using the transaction as we use in SQL.

Creating Transaction

To create a transaction, we can use the following syntax. We can use transaction method from our database instance.

myDBInstance.transaction(function (tran) {
            });

Here, myDBInstance is our database instance. And tran is our transaction object which we are going to use for our upcoming operations. Why we uses transaction is, as you all know, transaction can be rolled backed. For example, if any of the operations throws any error, the transaction will be rolled backed so there won’t be any kind of mismatching data happening. And of course, we can easily manage error logs with the help of transaction. Shall we write queries needed for our operations?

First of all, we will create a table in our database. To execute any queries in Web SQL, you must use the method executesql.

tran.executeSql('CREATE TABLE IF NOT EXISTS Users (id unique, Name, MailID)');

As you can see, we are creating the table Users if it does not exist in the database. As in SQL, we are assigning id as a unique key.

Next thing is we need to insert some rows to our table.

tran.executeSql('insert into Users (id, Name, MailID) _
values (1, "Sibi","sibikv4u@gmail.com")');
   tran.executeSql('insert into Users (id, Name, MailID) _
   values (2, "Aji","ajaybhasy@gmail.com")');
   tran.executeSql('insert into Users (id, Name, MailID) _
   values (3, "Ansu","ansary.ans21@gmail.com")');

If you want to assign name, mailid, id values to insert query, you are welcome to create those variables and assign to the query as shown below:

var name = "Sibi";
            var id = "1";
            var MailID = "sibikv4u@gmail.com";

 tran.executeSql('insert into Users (id, Name, MailID) values (?,?,?)',[id,name,MailID]);

So we have inserted some values too. Now we need to read the data we have inserted to our table right? To do that, we need to create a new transaction and another executeSql command.

tran.executeSql('SELECT * FROM Users', [], function (tran, data) {
               });

Here, we will get the output in data. As you can see, I have given a call back function along with the command. This can be used to loop through our data and shows the same in our page. So we can modify our reading transaction block as follows:

myDBInstance.transaction(function (tran) {
               var html = '<table><thead><th>Mail ID
               </th><th>ID</th><th>Name
               </th></thead><tbody>';
               tran.executeSql('SELECT * FROM Users', [], function (tran, data) {
                   for (i = 0; i < data.rows.length; i++) {
                       html += '<tr><td>'
                           + '<a ' +
                           'href="mailto:' +
                           data.rows[i].MailID + '">' +
               data.rows[0].MailID + '</a>' +
                       '</td><td>' +
                       data.rows[i].id + '</td><td>' +
               data.rows[i].Name + '</td></tr>';
                   };
                   html += '</tbody></table>';
                   $('#myTab').html(html);
               });
           });

Before that:

• Please don’t forget to include jQuery reference
• Do not forget to create a div with id myTab

You can add a CSS for the table we are creating dynamically as follows:

<style>
        table,tr,td,th {
            border:1px solid #ccc;
            border-radius:5px;
            padding:10px;
            margin:10px;
        }
 
    </style>

Complete Code

Complete code for the implementation is given below:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Introduction to Web SQL</title>
    <script src="Scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript">
        var myDBInstance = openDatabase('dbSibeeshPassion', '1.0', 
		'This is a client side database', 3 * 1024 * 1024);
        //check whether the database is created or not.
        if (!myDBInstance) {
            alert('Oops, your database was not created');
        }
        else {
            var version = myDBInstance.version;
            //var name = "Sibi";
            //var id = "1";
            //var MailID = "sibikv4u@gmail.com";
            myDBInstance.transaction(function (tran) {
                tran.executeSql('CREATE TABLE IF NOT EXISTS Users (id unique, Name, MailID)');
                //tran.executeSql('insert into Users (id, Name, MailID) 
                //values (?,?,?)', [id, name, MailID]);
                tran.executeSql('insert into Users (id, Name, MailID) 
				values (1, "Sibi","sibikv4u@gmail.com")');
                tran.executeSql('insert into Users (id, Name, MailID) 
				values (2, "Aji","ajaybhasy@gmail.com")');
                tran.executeSql('insert into Users (id, Name, MailID) 
				values (3, "Ansu","ansary.ans21@gmail.com")');
            });
            myDBInstance.transaction(function (tran) {
                var html = '<table><thead><th>Mail ID 
                </th><th>ID</th><th>Name 
                </th></thead><tbody>';
                tran.executeSql('SELECT * FROM Users', [], function (tran, data) {
                    for (i = 0; i < data.rows.length; i++) {
                        html += '<tr><td>'
                            + '<a ' + 'href="mailto:' + 
                            data.rows[i].MailID + '">' + 
				data.rows[0].MailID + '</a>' +
                        '</td><td>' + data.rows[i].id + 
                        '</td><td>' + data.rows[i].Name + 
                        '</td></tr>';
                    };
                    html += '</tbody></table>';
                    $('#myTab').html(html);
                });
            });
        }

    </script>
    <style>
        table,tr,td,th {
            border:1px solid #ccc;
            border-radius:5px;
            padding:10px;
            margin:10px;
        }
 
    </style>
</head>
<body>
    <div id="myTab"></div>
</body>
</html>

Output

That is all. We did it. Happy coding!

Conclusion

Did I miss anything that you may think is needed? Did you try Web SQL yet? Have you ever wanted to do this? Could you find this post useful? I hope you liked this article. Please share your valuable suggestions and feedback.

Your Turn. What Do You Think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, ASP.NET Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.