HTML 5 Web SQL Database

Introduction
 
Web SQL is very interesting feature, even though it isn't part of the HTML 5 specification. But it is a separate specification and it can still help for developing web applications. Web SQL is used to manipulate a client-side database. Since I am saying that it is good to use, there is a disclaimer for its use; it is risky because it stores data at the client side, not on the server side. So always remember, don't store information sensitive to the server inside it.
 
Note
 
A Web SQL database only works in the latest versions of Safari, Google Chrome and Opera browsers.
 
Core Methods of Web SQL
 
The following are the 3 core methods of Web SQL that I will cover in this article:
  1. openDatabase 
  2. transaction
  3. executeSql
Creating and Opening Databases
 
Using the openDatabase method you can create an object for the database. If the database doesn't exist then it will be created and then an object for that database will be created. You also don't need to worry about closing the connection with the database.
 
To create and open the database you need to use the following syntax.

var dbObj = OpenDatabase('[Database_Name]', '[Version_Number]', '[Text_Description]', '[size]', '[Creation_Callback]') 

Example
 
The following example describes how to create a database or its object.
  1. First, create a button in your HTML 5 page as in the following:

     

    1. <!DOCTYPE html>  
    2. <html>  
    3.    <head>  
    4.       <title>Open Database</title>  
    5.    </head>  
    6.    <body>  
    7.       <button id="btnCreateDB">Create Database</button>  
    8.    </body>  
    9. </html>  

  2. Now create a JavaScript function to create the database as in the following:

    1. function CreateDB() {  
    2.             var Database_Name = 'MyDatabase';  
    3.             var Version = 1.0;  
    4.             var Text_Description = 'My First Web-SQL Example';  
    5.             var Database_Size = 2 * 1024 * 1024;  
    6.             var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size, OnSuccessCreate());  
    7.         }  
    8.         function OnSuccessCreate() {  
    9.             alert('Database Created Sucessfully');  
    10.         }  

  3. Now bind this JavaScript function to the onclick event of the btnCreateDB  button. The complete code is given below:

    1. <!DOCTYPE html>  
    2. <html>  
    3. <head>  
    4.     <title>Open DataBase</title>  
    5.     <script>  
    6.         function CreateDB() {  
    7.             var Database_Name = 'MyDatabase';  
    8.             var Version = 1.0;  
    9.             var Text_Description = 'My First Web-SQL Example';  
    10.             var Database_Size = 2 * 1024 * 1024;  
    11.             var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size, OnSuccessCreate());  
    12.         }  
    13.         function OnSuccessCreate() {  
    14.             alert('Database Created Sucessfully');  
    15.         }  
    16.     </script>  
    17. </head>  
    18. <body>  
    19.     <button id="btn1" onclick="CreateDB()">Create Database</button>  
    20. </body>  
    21. </html>  

  4. Now open this file, I am opening it in Google Chrome. By default the output will be:

    Create Database

  5. Now press the F12 function key and open the Google Chrome developer tool and open the Resource tab where you will get a Web SQL database.

    Web SQL database

  6. Now click on the Create Database button and you will get the following output.

    Database button

    And inside the developer tool you will get the database.

    developer tool

Since you saw how to create and open the database in Web SQL, so by using the openDatabase function we can create a database in Web SQL and open the database. There are 5 parameters that are accepted by this openDatabase function that are:
  1. Database name: This argument provides the name of the database that is mandatory to be provided, otherwise you will get an exception.
  2. Version number: Version number is also required; some database may be in version 2.0 and may be in 1.0 so if you know the version number of the database then only you can open it.
  3. Text description: This argument describes the database and provides information about the database.
  4. Size of database: This argument decides the size of the database.
  5. Creation callback: This argument is optional, if you do not provide any value then the database will also be created but if you want to perform some action after creation of the database then you can use this, so if the database is created successfully then this work will be done.
Transactions
 
After opening our database we can create transactions. This provides the rollback and commit facility. This means inside the transaction we can fire more than one query. If a transaction fails at any point of time or a query has an error then it will be rolled back including all the queries and if all the queries successfully executed then the transaction will be committed.
A transaction is the same as a function that contains more than one query statement.
Example
  1. function CreateDB() {  
  2.             var Database_Name = 'MyDatabase';  
  3.             var Version = 1.0;  
  4.             var Text_Description = 'My First Web-SQL Example';  
  5.             var Database_Size = 2 * 1024 * 1024;  
  6.             var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
  7.             dbObj.transaction(function (tx) {  
  8.                 //Code of the transaction  
  9.                 //will goes here  
  10.             });  
  11.         }  
executeSql
 
This method performs a very important role for the Web SQL database. This method is used to execute read and write statements which include SQL injection projection and provides a call back method to process the result of any queries. Once if we have a transaction object then we can call the executeSql method.
Example
 
The following example also explains how to create a table in Web SQL.
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <title>Open DataBase</title>  
  5.     <script>  
  6.         function CreateDB() {  
  7.             var Database_Name = 'MyDatabase';  
  8.             var Version = 1.0;  
  9.             var Text_Description = 'My First Web-SQL Example';  
  10.             var Database_Size = 2 * 1024 * 1024;  
  11.             var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
  12.             dbObj.transaction(function (tx) {  
  13.                 tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location)');  
  14.             });  
  15.         }  
  16.     </script>  
  17. </head>  
  18. <body>  
  19.     <button id="Create_DB_n_Table" onclick="CreateDB()">Create Database & Table</button>  
  20. </body>  
  21. </html>  
Output of the preceding Example
  1. When the page is loaded:

     page
  2. If you then open the developer tool of Google Chrome then you will get the following output:

    tool

  3. After clicking on the button:

    button
 How to inset the data into Web SQL table
 
The following example will explain how to insert the data into the database.
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <title>Open DataBase</title>  
  5.     <script>  
  6.         var Database_Name = 'MyDatabase';  
  7.         var Version = 1.0;  
  8.         var Text_Description = 'My First Web-SQL Example';  
  9.         var Database_Size = 2 * 1024 * 1024;  
  10.         var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
  11.         dbObj.transaction(function (tx) {  
  12.             tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location)');  
  13.         });  
  14.   
  15.         function Insert() {  
  16.             var id = document.getElementById("tbID").value;  
  17.             var name = document.getElementById("tbName").value;  
  18.             var location = document.getElementById("tbLocation").value;  
  19.             dbObj.transaction(function (tx) {  
  20.                 tx.executeSql('insert into Employee_Table(id, Name, Location) values(' + id + ',"' + name + '","' + location + '")');  
  21.             });  
  22.         }  
  23.     </script>  
  24. </head>  
  25. <body>  
  26.     <form id="frm1">  
  27.         <table>  
  28.             <tr>  
  29.                 <td>ID:</td>  
  30.                 <td><input type="text" id="tbID" /></td>  
  31.             </tr>  
  32.             <tr>  
  33.                 <td>Name:</td>  
  34.                 <td><input type="text" id="tbName" /></td>  
  35.             </tr>  
  36.             <tr>  
  37.                 <td>Location:</td>  
  38.                 <td><input type="text" id="tbLocation" /></td>  
  39.             </tr>  
  40.             <tr>  
  41.                 <td><button id="btnInsert" onclick="Insert()">Insert</button></td>  
  42.             </tr>  
  43.         </table>  
  44.     </form>  
  45. </body>  
  46. </html>  
In the preceding example I have created one form and created 3 Textboxes and one button to get the value and submit the value.
  1. When the page is loaded then:

    Pageload
  2. If you look at the developers tool then you will get output like this:

    developers tool
  3. When you fill in some data into the TextBoxes and submit it then:

    submit Form
How to read the data from the web SQL
 
The following example will explain how to read the data from the Web SQL.
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <title>Open DataBase</title>  
  5.     <script>  
  6.         var Database_Name = 'MyDatabase';  
  7.         var Version = 1.0;  
  8.         var Text_Description = 'My First Web-SQL Example';  
  9.         var Database_Size = 2 * 1024 * 1024;  
  10.         var dbObj = openDatabase(Database_Name, Version, Text_Description, Database_Size);  
  11.         dbObj.transaction(function (tx) {  
  12.             tx.executeSql('CREATE TABLE IF NOT EXISTS Employee_Table (id unique, Name, Location)');  
  13.         });  
  14.   
  15.         function Insert() {  
  16.             var id = document.getElementById("tbID").value;  
  17.             var name = document.getElementById("tbName").value;  
  18.             var location = document.getElementById("tbLocation").value;  
  19.             dbObj.transaction(function (tx) {  
  20.                 tx.executeSql('insert into Employee_Table(id, Name, Location) values(' + id + ',"' + name + '","' + location + '")');  
  21.             });  
  22.         }  
  23.         dbObj.transaction(function (tx) {  
  24.             tx.executeSql('SELECT * FROM Employee_Table', [], function (tx, results) {  
  25.                 var len = results.rows.length, i;  
  26.                 var str = '';  
  27.                 for (i = 0; i < len; i++) {  
  28.                 str += "<tr>";  
  29.                 str += "<td>" + results.rows.item(i).id + "</td>";  
  30.                 str += "<td>" + results.rows.item(i).Name + "</td>";  
  31.                 str += "<td>" + results.rows.item(i).Location + "</td>";  
  32.                 str += "</tr>";  
  33.                 document.getElementById("tblGrid").innerHTML += str;  
  34.                 str = '';  
  35.                 }  
  36.             }, null);  
  37.         });  
  38.     </script>  
  39. </head>  
  40. <body>  
  41.     <p id="hh"></p>  
  42.     <form id="frm1">  
  43.         <table>  
  44.             <tr>  
  45.                 <td>ID:</td>  
  46.                 <td><input type="text" id="tbID" /></td>  
  47.             </tr>  
  48.             <tr>  
  49.                 <td>Name:</td>  
  50.                 <td><input type="text" id="tbName" /></td>  
  51.             </tr>  
  52.             <tr>  
  53.                 <td>Location:</td>  
  54.                 <td><input type="text" id="tbLocation" /></td>  
  55.             </tr>  
  56.             <tr>  
  57.                 <td><button id="btnInsert" onclick="Insert()">Insert</button></td>  
  58.             </tr>  
  59.         </table>  
  60.     </form>  
  61.     <table id="tblGrid" cellpadding="10px" cellspacing="0" border="1">  
  62.         <tr style="background-color:black;color:white;font-size:18px;">  
  63.             <td>  
  64.                 ID  
  65.             </td>  
  66.             <td>  
  67.                 Name  
  68.             </td>  
  69.             <td>  
  70.                 Location  
  71.             </td>  
  72.         </tr>  
  73.     </table>  
  74. </body>  
  75. </html>  
As with SQL Server, by using a select query you can read the data from the Web SQL.

The following is the output of the code above:
Output 

Note that Web SQL Databases were on the W3C Recommendation track but specification work has stopped because all interested implementors use Sqlite and multiple independent implementations are necessary for a standard.

Author
Sourabh Somani
Sourabh Somani
0 56.1k 4.3m