Working With HTML5 SQL – Storing and Retrieving Records

HTML5 Sql will help us to storage structured data on the web browser. Following example will show you how to store and retrieve records using web SQL.

Localstorage is good to store key value pairs to some extent. But when you need to store some large amount of data, it is probably good idea to work with SQL.

Creating Database

Creating SQL database using javascript is very simple. Below one line of code will create SQL database.

var database = openDatabase("records", "", "Offline_WebApp", 1048576);

openDatabase Arguments

  • Database Name
  • Version
  • Database Display Name
  • Estimated size in Bytes

Displaying Records from SQL

In the above section, we have created database, now we are writing a function which does the following.

1. Create table “Records” if not exist.
2. Select all columns from table “Records”.
3. Loop through the sql rows fetched from the database.

var displayRecords = function () {
  			database.transaction(function(transaction){
  				transaction.executeSql('CREATE TABLE IF NOT EXISTS records(task TEXT)', 
     			 []);
    			transaction.executeSql('SELECT * FROM records', [], function(tx, rs) {
      				for(var i = 0; i < rs.rows.length; i++) {
        				console.log(rs.rows[i]);
      				}
    			});
  			})
  		};

Insert Records in the SQL table

Lets write another function to insert record into the database table “Records”.

var insertRecord = function(taskValue) {
  			database.transaction(function(transaction){
  				transaction.executeSql('INSERT INTO records VALUES(?)',
                                [ taskValue],
      				function(tx, rs) {
        				console.log("Inserted record");
      				},
      				function(tx, error) {
       					console.log("error", error)
      				});
  			});
  		}

Complete App To Insert and Retrieve Records Using HTML Form

Try the below code which accepts form input task name and adds to the SQL database table “records”.


<!DOCTYPE html>
<html>
<head>
 <title>Offline WebApp</title>
 <script
 src="https://code.jquery.com/jquery-3.2.1.min.js"
 integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
 crossorigin="anonymous"></script>
 <script type="text/javascript">
 
 $(function(){

 var database = openDatabase("records", "", "Offline_WebApp", 1048576);
 var displayRecords = function () {
 database.transaction(function(transaction){
 transaction.executeSql('CREATE TABLE IF NOT EXISTS records(task TEXT)', 
 []);
 transaction.executeSql('SELECT * FROM records', [], function(tx, rs) {
 
 $("#tasks").empty(); 
 for(var i = 0; i < rs.rows.length; i++) {

 $("#tasks").append("<li>" + rs.rows[i].task + "</li>");
 //console.log(rs.rows[i]);
 }
 });
 })
 };

 var insertRecord = function(taskValue) {
 database.transaction(function(transaction){
 transaction.executeSql('INSERT INTO records VALUES(?)', [ taskValue],
 function(tx, rs) {
 console.log("Inserted record");
 },
 function(tx, error) {
 console.log("error", error)
 });
 });
 }

 $("#offline").on("submit", function(e){
 e.preventDefault();
 var taskValue = $("#task").val();
 insertRecord(taskValue);
 displayRecords();
 })

 displayRecords();
 })
 </script>
</head>
<body>

<form id="offline">
 <input type="text" id="task" name="task">
 <button type="submit">Add Task</button>
</form>
<ul id="tasks">
 

</ul>

</body>
</html>

Hope you find this useful..!