Learn Everything about Web SQL (A-Z)

Web SQL is a client-side database management system used by web developers to store temporary data for tracking user activity and for some other several usage.

Learn more: What is Web SQL Database

In this article, We will learn the basic procedure of Web SQL database implementation in the browser using Javascript and SQL commands. It works as a Web API and connected with HTML and introduced in HTML5.

Prerequisite

In Web development, prior knowledge of HTML, CSS, Javascript is must for basic interactive yet static website development. And for dynamic website, SQL is very common language for database.

To understand and work with Web SQL you should have basic and prior knowledge of HTML, CSS, Javascript and SQL. If you know them already then it will be easy to understand. If you don’t know them already then you can read the following tutorials:

HTML Tutorial CSS Tutorial Javascript Tutorial SQL Tutorial

In this tutorial, we will discuss how to implement Web SQL to store, delete, modify data in client-side by a live working example. Foe better understanding, you may read the Web SQL tutorial from the following link:

Example: Storing Exam Results using Web SQL

We will create a database for an Examination result with name and passed division. Let’s begin:

The following steps we have to take to complete the fully working Web SQL database.

  • Checking browser compatibility
  • Creating Database using necessary parameter
  • Creating student name and result table
  • Getting all the result list from the database to display
  • Adding new result to database
  • Removing an existing result from database etc.

Result interface using HTML

The following HTML codes create the interface for adding, deleting and viewing data from Web SQL:

<!DOCTYPE html>
<html>
<head>
    <title>Web SQL - Storing Exam Result</title>
</head>
<body>
  <h1>Examination Result</h1>

  <div id="controls">
    <p>Add a result to the Web SQL database</p>
    <label>Student Name:</label>
    <input type="text" id="s_name" />
    <br />
    <label>Archived Result:</label>
	<select name="s_result" id="s_result">
	  <option value="First Division">First Division</option>
	  <option value="Second Division">Second Division</option>
	  <option value="Third Division">Third Division</option>
	</select>
    <br />
    <button type="button" id="addcar" onclick="addResult();">Add Result</button>
  </div>
<div>
     <h3>Result Board</h3>
    <ul id="resultlist"></ul>
</div>

 

Checking browser compatibility, Cretaing Database and Table

Test the browser supports Web SQL or not. If supports then create a database as resultdb and tables for students result using the following codes otherwise through a not supported message to the user.

if (window.openDatabase) {  
    var mydb = openDatabase("resultdb", "0.1",
	"A Database of Cars I Like", 2048 * 2048);
    mydb.transaction(function (t) {
        t.executeSql("CREATE TABLE IF NOT EXISTS results_table
		(id INTEGER PRIMARY KEY ASC, make TEXT, model TEXT)");
    });

} else {
    alert("WebSQL is not supported by your browser!");
}

 

Getting the result list from database and show it in the browser

Now we will create a function called updateResultList with parameters transaction and results to get the result data from database and then we will create another function outputResult for showing the result in browser window. See the codes below:

function updateResultList(transaction, results) {
    var listitems = "";
    var listholder = document.getElementById("resultlist");

    listholder.innerHTML = "";

    var i;
    for (i = 0; i < results.rows.length; i++) {
        var row = results.rows.item(i);
		
        listholder.innerHTML += "<li>" + row.make + " - " + row.model +
		" (<a href='javascript:void(0);' onclick='deleteResult(" + row.id + ");'>Delete Result</a>)";
    }       
}

function outputResults() {
    if (mydb) {
        mydb.transaction(function (t) {
            t.executeSql("SELECT * FROM results_table", [], updateResultList);
        });
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}

 

Add results to Database

Now we will make a connection with our HTML button to interact with the database for adding data from the database. See the code example below:

function addResult() {
    if (mydb) {
        var make = document.getElementById("s_name").value;
        var model = document.getElementById("s_result").value;

        if (make !== "" && model !== "") {
            mydb.transaction(function (t) {
                t.executeSql("INSERT INTO results_table 
				(make, model) VALUES (?, ?)", [make, model]);
                outputResults();
            });
        } else {
            alert("You must enter a make and model!");
        }
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}

 

Delete results from Database

We can also delete a data row from a table in Web SQL from the browser with Javascript. Implement the below code to delete a row from the Database.

function deleteResult(id) {
    if (mydb) {
        mydb.transaction(function (t) {
      t.executeSql("DELETE FROM results_table WHERE id=?", [id], outputResults);
        });
    } else {
        alert("db not found, your browser does not support web sql! Try using google chrome");
    }
}

 

Call the main Function to display on page load

If we want to see the Database output in browser window on page load. Then we have to call output function for extracting tables from the databse. Here the calling method of the outputResult functon:

outputResults();

 

Complete Codes:

Here is the complete code example of Web SQL. This will help you to demonstrate the Web SQL and its functionality.

<!DOCTYPE html>
<html>
<head>
    <title>Web SQL - Storing Exam Result</title>
</head>
<body>
  <h1>Examination Result</h1>

  <div id="controls">
    <p>Add a result to the Web SQL database</p>
    <label>Student Name:</label>
    <input type="text" id="s_name" />
    <br />
    <label>Archived Result:</label>
	<select name="s_result" id="s_result">
	  <option value="First Division">First Division</option>
	  <option value="Second Division">Second Division</option>
	  <option value="Third Division">Third Division</option>
	</select>
    <br />
    <button type="button" id="addcar" onclick="addResult();">Add Result</button>
  </div>
<div>
     <h3>Result Board</h3>
    <ul id="resultlist"></ul>
</div>
<script>
if (window.openDatabase) {  
    var mydb = openDatabase("resultdb", "0.1",
	"A Database of Cars I Like", 2048 * 2048);
    mydb.transaction(function (t) {
        t.executeSql("CREATE TABLE IF NOT EXISTS results_table
		(id INTEGER PRIMARY KEY ASC, make TEXT, model TEXT)");
    });

} else {
    alert("WebSQL is not supported by your browser!");
}

function updateResultList(transaction, results) {
    var listitems = "";
    var listholder = document.getElementById("resultlist");

    listholder.innerHTML = "";

    var i;
    for (i = 0; i < results.rows.length; i++) {
        var row = results.rows.item(i);
		
        listholder.innerHTML += "<li>" + row.make + " - " + row.model +
		" (<a href='javascript:void(0);' onclick='deleteResult(" + row.id + ");'>Delete Result</a>)";
    }       
}

function outputResults() {
    if (mydb) {
        mydb.transaction(function (t) {
            t.executeSql("SELECT * FROM results_table", [], updateResultList);
        });
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}

function addResult() {
    if (mydb) {
        var make = document.getElementById("s_name").value;
        var model = document.getElementById("s_result").value;

        if (make !== "" && model !== "") {
            mydb.transaction(function (t) {
                t.executeSql("INSERT INTO results_table 
				(make, model) VALUES (?, ?)", [make, model]);
                outputResults();
            });
        } else {
            alert("You must enter a make and model!");
        }
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}

function deleteResult(id) {
    if (mydb) {
        mydb.transaction(function (t) {
            t.executeSql("DELETE FROM results_table WHERE id=?", [id], outputResults);
        });
    } else {
        alert("db not found, your browser does not support web sql!");
    }
}

outputResults();
</script>
</body>
</html>

 

Output:

Examination Result

Add a result to the Web SQL database





 


Result Board

     

    Now try adding result and name using the form and dropdown. Note that, after adding results it will be saved into your browsers Web SQL database. Means If you reload the page, you can still see the data that you have entered earlier unil you delete the site data and clear browser cache and data.

    Note: Not all the browsers support Web SQL. For better experience try in Google Chrome.