How to Build a ToDo Application with Web SQL and jQuery

By
on
JavaScript

This tutorial will take you through the step by step development of building a ToDo App entirely powered by Web SQL. You will learn about getting started with Web SQL, understanding transactions, following best practices and how to write re-usable functions.

Please Note: The Web SQL database API is not a part of the HTML5 specification, although it is in the suite of specifications that can help you build sophisticated web applications.

This is what we will be building:

ToDo App preview screenshot

Live DemoDownload Source Code

Table of Contents

  1. Introduction To Web SQL – Understanding key features of Web SQL, including browser support.
  2. Creating Static HTML/CSS Template – It is always a good practice to divide application development into different modules. For example: Designing, Client-Side Logics and Backend Logics. Further on in this tutorial we will be creating a static template to understand the design work-flow of our application (Todo App).
  3. Adding Functionality – Lastly, we will use jQuery to create functions and run transactions to add required functionality to our application (Todo Maker).

Requirements

  1. Web SQL supported browser. Currently Web SQL is supported by the following browsers.
    • Chrome 4+
    • Safari 4+
    • Opera 11+
    • iOS Safari 5+
    • Android 3+
    • Opera Mobile 11+
  2. Copy of the jQuery Library
  3. Basic understanding of jQuery and SQL

Choosing The Right Application

Choosing the right applications to build is very important while working with Web SQL. For instance, if you are planning to create a blog or a website using Web SQL, it will probably not be the best choice. As Web SQL database is hosted by the user’s browser, the user has the power to delete tables or remove entire database. A ToDo App or sticky notes can be perfect examples of Web SQL in action because the users will be entering data related to their day-to-day activities and it will hardly make a difference if they choose to delete the entered data.

Web SQL Core Methods

We will be working with 3 main methods provided by Web SQL API in order to maintain our Todo App.

  1. openDatabase – This method will create an instance of database object using the database name. Also, it will create a new database if it does not already exist.
  2. transaction – This method will let you perform and commit transactions on a specific database.
  3. executeSql – executeSql will let you run SQL queries like read, insert and delete.

Examples Of Above Methods

openDatabase

		openDatabase("speckyboy","1.0","My First database",5 * 1024 * 1024);

openDatabase will take 4 parameters separated by comma (,) as described below

  1. speckyboy : Database Name.
  2. 1.0: Database Version.
  3. My First Database: Database Description.
  4. 5*1024*1024: 5mb of storage space.

transaction

		db.transaction(function (tx) {
			// here goes the SQL query
		});

transaction is required to execute different SQL queries and it will take one parameter that will be appended to the actual query.

executeSql

		tx.executeSql('CREATE TABLE IF IT DOES NOT EXIST FOO (id INTEGER, foo TEXT)');

the above examples were just an introduction to Web SQL. There is still a lot more to understand while creating a functional application. Now, we will setup the design template with HTML and CSS and create a simple design for our ToDo app:

File Structure

The above image shows the required files in the process of creating the Todo Maker application.

  1. style.css – This file will contain our applications design.
  2. jquery.js – This will be the copy of jQuery library which will help us write jQuery functions.
  3. manageTodo.js – In this file, we will write our own code from scratch.
  4. index.html – index.html will be the actual file containing the HTML code.

Create a new file index.html and paste the below code inside it.

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta name="generator" content=
  "HTML Tidy for Linux/x86 (vers 11 February 2007), see www.w3.org" />

  <title>Html5 WebSql Todo Maker</title>
  <meta charset="utf-8" />
  <link rel="stylesheet" href="css/style.css" type="text/css" />
  <script type="text/javascript" src="js/jquery.js">
</script>
  <script type="text/javascript" src="js/manageTodo.js">
</script>
</head>

<body>
  <div id="wrapper">
    <div id="header">
      <h2><a href="#">TodoMaker</a></h2>
    </div><!-- end header -->

    <div id="bodyWrapper">
      <div id="todo_wrapper" class="white_bg">
        <h2>List of created todo</h2>

        <ul class="list"></ul>
      </div><!-- end todo_wrapper -->

      <div id="todo_form" class="white_bg">
        <div id="form_wrapper">
          <textarea id="todo_item_text" placeholder="Enter todo here">
</textarea> <input type="text" id="todo_due_date" placeholder="Due Date" />
        </div><!-- end form_wrapper --><input type="submit" id="create_todo" value=
        "Add Todo" class="button" />
      </div><!-- end todo_form -->
    </div><!-- end bodyWrapper -->
  </div><!-- end wrapper -->
</body>
</html>

The above code will create a simple HTML document with an unordered list of todo and form elements, along with a textarea, text input and a submit button.

Understanding the HTML Structure

  1. In the head portion we have included the required CSS and jQuery files.
  2. ul.list will contain all of the created ToDo items inside list-items (li) with a hidden input field containing a unique ID of every ToDo item.
  3. Finally, we will setup the form elements with a textarea (to write ToDo item text), input box (for due date) and a submit button.

Let’s add some styling to these dead HTML elements using CSS. Open the file style.css inside the CSS folder and paste the below markup inside it:

/*
     /////////  General Styling Rules On Body and Other Page Elements ///////////////
*/
body{
	background: #f7f7f7;
	font-family: Arial, "MS Trebuchet", sans-serif;
	font-size: 12px;
	color: #3a3a3a;
}
*{
	margin: 0;
	padding: 0;
}
a{
	text-decoration: none;
}
ul, ol{
	list-style: none outside none;
}
.clear{
	clear: both;
}
/*
	///////// Body Header Rules with header sticked on top with absolute positioning ///////
*/
#header{
	background: none repeat scroll 0 0 #33363B;
	color: #999999;
	padding: 10px;
	position: absolute;
	left: 0;
	right: 0;
}
#header h2{
	padding-left: 10px;
}
#header h2 a{
	color: #999999;
	text-transform: uppercase;
	font-size: 12px;
}
/*
	//////////////   Main Body Div Styles Including List Of Created ToDo ///////////////

*/

/*  Body wrapper holding all page elements except header */
#bodyWrapper{
	width: 500px;
	margin: auto;
	padding-top: 50px;
}

/* todo div and all todo inside a list */
.white_bg{
	background: #fff;
	padding: 20px;
	border-radius: 5px;
	-moz-border-radius: 5px;
	-webkit-border-radius: 5px;
	margin-bottom: 20px;
}
#todo_wrapper h2{
	color: #555;
	border-bottom: 1px dotted #999;
	margin-bottom: 30px;
	padding-bottom: 10px;
}
#todo_wrapper ul li{
	border-bottom: 1px solid 
	#E8E8E8;
	font-size: 11px;
	margin: 0;
	padding: 4px;
	padding-left: 0px;
	padding-bottom: 3px;
	padding-top: 5px;
}
#todo_wrapper ul li .todo_text{
	float: left;
	padding: 3px;
}
#todo_wrapper ul li .due_date{
	background: none repeat scroll 0 0 #FFD5E3;
	border: 1px solid #FCB7CE;
	float: right;
	padding: 1px 8px 1px 6px;
	border-radius: 20px;
	-moz-border-radius: 20px;
	-webkit-border-radius: 20px;
}
#form_wrapper{
	border: 1px solid #CCCCCC;
	border-radius: 4px;
	-moz-border-radius: 4px;
	-webkit-border-radius: 4px;
	box-shadow: 1px 1px 13px #EEEEEE;
	-webkit-box-shadow: 1px 1px 13px #EEEEEE;
	-moz-box-shadow: 1px 1px 13px #EEEEEE;
}
#todo_form textarea{
	border: 0px solid #ccc;
	border-bottom: 1px solid #ccc;
	width: 98%;
	font-family: Arial, Helvetica, sans-serif;
	font-size: 13px;
	line-height: 15px;
	height: 15px;
	display: block;
	min-height: 50px;
	background-color:transparent;
	resize: none;
	overflow-y: scroll;
	padding: 5px;
}
#todo_form input[type="text"]{
	border: 0px solid #ccc;
	width: 95%;
	font-family: Arial, Helvetica, sans-serif;
	font-size: 13px;
	display: block;
	background-color:transparent;
	padding: 5px;
}
.button {
	-moz-box-shadow:inset 0px 1px 0px 0px #bbdaf7;
	-webkit-box-shadow:inset 0px 1px 0px 0px #bbdaf7;
	box-shadow:inset 0px 1px 0px 0px #bbdaf7;
	background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #79bbff), color-stop(1, #378de5) );
	background:-moz-linear-gradient( center top, #79bbff 5%, #378de5 100% );
	filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#79bbff', endColorstr='#378de5');
	background-color:#79bbff;
	-moz-border-radius:26px;
	-webkit-border-radius:26px;
	border-radius:26px;
	border:1px solid #84bbf3;
	display:inline-block;
	color:#ffffff;
	font-family:arial;
	font-size:13px;
	font-weight:bold;
	padding:4px 12px;
	text-decoration:none;
	text-shadow:1px 1px 0px #528ecc;
	margin-top: 20px;
	cursor: pointer;
}.button:hover {
	background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #378de5), color-stop(1, #79bbff) );
	background:-moz-linear-gradient( center top, #378de5 5%, #79bbff 100% );
	filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#378de5', endColorstr='#79bbff');
	background-color:#378de5;
}.button:active {
	position:relative;
	top:1px;
}
.highlight{
	background: yellow;
}
#delete{
	float: right;
	margin-left: 10px;
	margin-top: 3px;
}
.error_message{
	padding-top: 50px;
	color: #888;
	font-size: 80px;
	text-align: center;	
}

Since there is nothing special about CSS here, we will not spend time understanding that.

Next, we will get into writing JS code to add functionality to our ToDo maker.

As you know, HTML5 is still in development and is very inconsistent among browsers. As a result, we need to write a function to find whether the end user’s browser supports Web SQL or not. If the browser does not support Web SQL, we will show an error with some static HTML text.

Open the file manageTodo.js and paste the below code inside it 

$(function(){
	function init(){
		if(typeof(openDatabase) !== 'undefined')
		{
			// We will write something here later
		}
		else
		{
			$('#bodyWrapper').html('<h2 class="error_message"> Your browser does not support webSql</h2>');
		}
	}
	init();
});

We will create a function init with if-and-else statements to find out whether the browser supports Web SQL database or not. If typeof(openDatabase) == 'undefined' we will show a message that the user’s browser does not support webSql.

You can test the above code in Mozilla Firefox and you will get the same error message.

As we are creating an entire application, it is important to use best practices like creating functions and using variables with namespaces. I will explain each and every part of the code as we proceed along.

Note: Delete everything from manageTodo.js and start from scratch; do not worry about the above code. Plus, all the code would go inside the domReady function.

$(function() {
	var speckyboy = {}
	speckyboy.init = {}
	speckyboy.init.db = {}
});

We will wrap everything inside the domReady function to make sure the page is loaded before we perform any actions. Also, we have created 3 variables using namespaces which are NULL by default.

Why Should We Use Namespaces?

  1. Namespace is used to prevent name collisions.
  2. Let’s say, instead of using speckyboy.init, you created a variable called init and later you wish to expand this ToDo app using third party plugins. There will be obvious name collision issues if that plugin also used a variable called init.

Creating The Database

		// Holding database instance inside a global variable
		speckyboy.init.open = function(){
			speckyboy.init.db = openDatabase("speckyboy","1.0","My First database",5 * 1024 * 1024);
			// dbname, verison, desc, size
		}

Creating the Table

	speckyboy.init.createTable = function(){
		var database = speckyboy.init.db;
		database.transaction(function(tx){
			tx.executeSql("CREATE TABLE IF IT DOES NOT EXIST todo (ID INTEGER PRIMARY KEY ASC,todo_item TEXT,due_date VARCHAR)", []);
		});
	}

We will execute the Create Table command to create a table with the name todo and the following fields:

  1. ID : It will be a primary key and will auto increment everytime there’s a new entry.
  2. todo_item : It will be the actual field containing our ToDo text.
  3. due_date : due_date will be a VARCHAR field (character field) saving our ToDo item due date.

Where to Look Now?

If you refresh your page, you will notice no change on your screen. Following are the reasons for that:

  1. First, we have not yet performed any actions that will make changes on the screen.
  2. Secondly, we have only created the functions, but we haven’t called them. Let’s get into some action now!
	function init(){
		if(typeof(openDatabase) !== 'undefined')
		{
			speckyboy.init.open();
			speckyboy.init.createTable();
		}
		else
		{
			$('#bodyWrapper').html('<h2 class="error_message"> Your browser does not support webSql</h2>');
		}
	}
		init();

We will create the init function with a conditional statement looking for browser support. If the browser supports Web SQL, then it will execute the above two functions to create database and table. Else, it will throw an error telling the user that the browser is unsupported.

Click F12 (assuming you are on Chrome and Windows) to open Chrome Inspector and go to Resources. If you are able to find a database name and a table similar to the screenshot given below, you are ready to move ahead. Else, you need to look for errors inside Inspector Console.

Adding ToDo

	// passing values as parameters
		speckyboy.init.addTodo = function(todoItem,dueDate){
			var database = speckyboy.init.db;
			database.transaction(function(tx){
				 tx.executeSql("INSERT INTO todo (todo_item,due_date) VALUES (?,?)", [todoItem,dueDate]);
			});
		}

<p>We will run prepared statement to add a new item to our existing SQL table. In this function, we will pass <code>todo_item</code> and <code>due_date</code> values as parameters.</p>

<p>As this is just a function, we need to find a way to grab textarea and input field values and pass them as parameters while calling this function.</p>

<h2>Adding OnClick Event Handler To Add ToDo</h2>


		$('#create_todo').click(function(){
			var todo_item_text = $('#todo_item_text').val();
			var todo_due_date = $('#todo_due_date').val();
			
			if(todo_item_text.length == '' || todo_due_date.length == '')
			{
				alert('Both fields are required');
			}
			else
			{
				speckyboy.init.addTodo(todo_item_text,todo_due_date);
				$('#todo_item_text').val('');
				$('#todo_due_date').val('');
			}
		});



If you have been working with jQuery selectors, you will understand the code at first glance. Nevertheless, we will discuss the above code line by line.


    

  1. Everytime we click the submit button, it will run the above block of code.
    2. 

  2. Next, we have created a couple of variables containing the value of the textarea and the input box.
    3. 

  3. Conditional if-and-else statements will make sure that the fields are not empty before inserting them into the database.
    4. 

  4. Inside the else block we will call the function speckyboy.init.addTodo and will pass 2 required parameters.
    5. 

  5. Last two lines will make the textarea and input box empty.
    6. 



Testing Time


Copy the above code and add it to your manageTodo.js file. Open the index.html file inside a browser and let's create a new ToDo:




Once again open the Inspector (F12), go to Resources and click on the table name (todo). If you are able to see your created ToDo, you are good to go! Otherwise, simply jump to console and do some error handling.


Getting the Created ToDo


	speckyboy.init.getTodo = function(){
		var database = speckyboy.init.db;
		var output = '';
		database.transaction(function(tx){
			tx.executeSql("SELECT * FROM todo", [], function(tx,result){
				for (var i=0; i < result.rows.length; i++) {
					todo_item = result.rows.item(i).todo_item;
					todo_due_date = result.rows.item(i).due_date;
					todo_id = result.rows.item(i).ID;
					showAllTodo(todo_item,todo_due_date,todo_id);
				}
			});
		});
	}



Now, we shall learn how to list the created ToDo. Let's understand the above code because it is slightly complicated.


    

  1. Select statement will get everything from the table todo and will add the values to an array [] 
    2. 

  2. Next, we will run a callback function with a parameter called result.
    3. 

  3. result.rows.length will contain the number of rows inside the table. As per our example, it will be 2.
    4. 

  4. We will run a for loop to grab items inside the table and pass them as a parameter to a new function called showAllTodo();
    5. 



Creating the Function showAllTodo


		function showAllTodo(todo_item,todo_due_date,todo_id){
		$('ul.list').append(
			'<li><div class="todo_item"><span class="todo_text">' + todo_item + '</span>' + 
			'<a href="#" id="delete"> Delete </a><span class="due_date">' + todo_due_date + '</span>' + 
			'<input type="hidden" id="this_id" value="' + todo_id + '"><div class="clear"></div></div></li>');	
			$('li:last').addClass('highlight').delay(1000).queue(function(next){ $(this).removeClass('highlight'); next(); });
		}



In the above code, all we are doing is appending list items (li) to our unordered list. Let's break the code down.


    

  1. We have created a delete link next to every ToDo, so that we can add delete functionality later on.
    2. 

  2. Since we are not sure which delete link a user will click and which ToDo we should delete, we need to find a way to keep a connection with the delete link and the ToDo ID.
    3. 

  3. And in order to keep such a connection, we have created a hidden input field containing the ToDo item ID which is unique to all ToDo items.
    4. 

  4. Finally, to add highlighting animations, we are adding the class highlight to the created ToDo and will remove the class after 1 sec.
    5. 



Let's go ahead and call the function speckyboy.init.getTodo(); inside the init function we created. On refreshing the page, you will be able to see all the created ToDo items from the Web SQL table.


	
	function init(){
		if(typeof(openDatabase) !== 'undefined')
		{
			speckyboy.init.open();
			speckyboy.init.createTable();
			speckyboy.init.getTodo();
		}
		else
		{
			$('#bodyWrapper').html('<h2 class="error_message"> Your browser does not support webSql</h2>');
		}
	}



Delete the Todo


	speckyboy.init.deleteTodo = function(id){
		var database = speckyboy.init.db;
		database.transaction(function(tx){
			tx.executeSql("DELETE FROM todo WHERE ID=?",[id]);
		});
	}



Deleting the ToDo is quite simple. All we need to do is to pass the ID of the ToDo we want to delete. Now, we need to write a click handler that will run the above funtion everytime we click the delete link.


Writing OnClick Delete Event Handler


		$('#delete').live("click",function(){
			var id = $(this).closest('li').find('#this_id').val();
			$(this).closest('li').addClass('highlight').delay(1000).queue(function(next){ $(this).remove(); next(); });
			speckyboy.init.deleteTodo(id);
		});



    

  1. First, we will get the hidden input value from the list item.
    2. 

  2. Again, we will add some highlighting animation to change the background of the item we are going to delete.
    3. 

  3. Finally, we will call the funtion speckyboy.init.deleteTodo to delete the selected item.
    4. 



We are now just a step away from creating a stable application. Let's check the following points that are still missing:


    

  1. Everytime we create a new ToDo, we need to refresh the page in order to get the active list.
    2. 

  2. As due date input is an open field, there are chances of misformatting.
    3. 



Solving the Above Two Problems


As far as page refresh is concerned, we can fix that out by making a small change to our existing function speckyboy.init.addTodo.


	speckyboy.init.addTodo = function(todoItem,dueDate){
		var database = speckyboy.init.db;
		database.transaction(function(tx){
			tx.executeSql("INSERT INTO todo (todo_item,due_date) VALUES (?,?)", [todoItem,dueDate],
			showAllTodo(todoItem,dueDate));
		});
	}



What's the Change?


After running the insert query and passing the values, we have added showAllTodo as a callback function. It will grab all the ToDo items and will list them without a page refresh.


Also, for the due date we can use jQuery UI calendar that will make sure that the date is in the same format everywhere. Open the index.html file once again and add the following code inside the head portion of the file.


<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.18/jquery-ui.min.js"></script>
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.17/themes/redmond/jquery-ui.css" type="text/css">



The above two lines are the reference to the jQuery UI library. We can now call the datepicker method on the input field.


		$('#todo_due_date').datepicker();	





Finished!


ToDo App preview screenshot


Live DemoDownload Source Code

                


Related Posts



                

                    
Related Topics

                                    

				
This page may contain affiliate links. At no extra cost to you, we may earn a commission from any purchase via the links on our site. You can read our Disclosure Policy at any time.