Introduction To Web SQL

I know you all are familiar with SQL. If not I strongly recommend you 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 the developers to do some database operations in the client side, like creating a database, opening the transaction, creating tables, inserting values to tables, deleting values, and reading the data. If you need any other way to save some data in the client side, you can use storage mechanisms introduced in HTML5.

Now we will look 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.

                                       Getting_version_details_from_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 use transaction is, as you all know transaction can be roll backed. For example, if any of the operation throws any error, the transaction will be roll 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?

Firstly, 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","[email protected]")');
tran.executeSql('insert into Users (id, Name, MailID) values (2, "Aji","[email protected]")');
tran.executeSql('insert into Users (id, Name, MailID) values (3, "Ansu","[email protected]")');

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

var name = "Sibi";
var id = "1";
var MailID = "[email protected]";
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 can use and 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:' & lt;
            /code> <code style=" inherit; inherit; 0px !important; 0px !important; font-size: 1em !important; 1.1em !important; font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace !important; vertical-align: baseline !important; border-radius: 0px !important; bottom: auto !important; float: none !important; auto !important; left: auto !important; outline: 0px !important; overflow: visible !important; static !important; right: auto !important; top: auto !important; auto !important; box-sizing: content-box !important; direction: ltr !important; box-shadow: none !important; display: inline !important; background: none !important;">+ 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 = "[email protected]";
            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","[email protected]")');
                tran.executeSql('insert into Users (id, Name, MailID) values (2, "Aji","[email protected]")');
                tran.executeSql('insert into Users (id, Name, MailID) values (3, "Ansu","[email protected]")');
            });
            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

                               Web_SQL_Output

That is all. We did it. Have a 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 requirement? Did you find this post useful? I hope you liked this article. Please share with me 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, or 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.

Please read this article in my blog here.