Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Introduction to Web SQL

DZone's Guide to

Introduction to Web SQL

In this post, we will check out some information about Web SQL. Web SQL is an API which helps developers do some database operations in client side, like: creating databases, opening the transaction, creating tables, inserting values to tables, deleting values, and reading data.

· Web Dev Zone
Free Resource

Add user login and MFA to your next project in minutes. Create a free Okta developer account, drop in one of our SDKs to your application and get back to building.

In this post, we will check out some information about Web SQL. I know you all are familiar with SQL (If not, I strongly recommend you to read some basic informations here). As the name implies, Web SQL has many similarities to SQL. So, if you are good with SQL, you will love Web SQL too. Web SQL is an API which helps developers do some database operations in client side, like: creating databases, opening the transaction, creating tables, inserting values to tables, deleting values, and reading data. If you need another 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 like it!

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 follow, is creating 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 maintains a limit of 5 MB. As from the above query we have created a Web SQL database. Now we will check whether the DB was 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



    Getting_version_details_from_database_instance

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

    Creating Transaction

    To create a transaction, we can use the following syntax. We can use the 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. The reason we use transaction is, as you probably know, transaction can be roll backed. For example, if any of the operation throws an error, the transaction will be roll backed so there won’t be any kind of mismatched 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, 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, or 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

    Web_SQL_OutputWeb_SQL_Output

    That is all. We did it. Have a happy coding.

    Conclusion

    Did I miss anything that you think is necessary? Have you tried Web SQL, yet? Have you ever wanted to do this before? Did you find this post useful?

    I hope you liked the article. Please share your valuable suggestions and feedback with me.

    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 and I’ll definitely try to help if I can.

    Launch your application faster with Okta’s user management API. Register today for the free forever developer edition!

    Topics:
    databases ,sql ,javascript ,web ,web sql

    Published at DZone with permission of Sibeesh Venu, DZone MVB. See the original article here.

    Opinions expressed by DZone contributors are their own.

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}