Mobile
Article

Storing Local Data in a Cordova App

By Narayan Prusty

Storing Data Locally in a Cordova App

Most mobile apps will need to store data locally and HTML5 introduced new options for storing data locally. To store data locally in an Cordova app we can use HTML5 storage combined with other mechanisms provided by plugins.

In this tutorial, I will introduce different ways of storing data locally and we will see how to use HTML5 Web Storage with the Cordova SQLite Plugin by creating a simple note taking app.

Different Ways to Store Data Locally

Let’s get straight into the different ways to store data locally:

Web Storage

The Web Storage API let’s us store key/value pairs as local and/or session storage. Local storage is persistent whereas session storage gets deleted when the app quits. Web storage can store 2-10 MB of data, the exact quota limit depends on the platform.

You cannot store a lot of data in web storage but it’s easier to read and write data to it.

For example: If your app consists of multiple HTML files, you can use web storage to pass small amounts of data between them.

Web SQL Database

The Web SQL API stores and queries data using SQL. You can store 50-200 MB of data in Web SQL Database, the exact quota limit depends on the platform. Once the limit is reached the WebView asks the user to grant permission to use more local space. This API is not supported by all platforms but you can use WebSQL Cordova Plugin to polyfill it.

IndexedDB

IndexedDB provides APIs to store and retrieve data in the form of objects, again the exact quota limit depends on the platform. Once the limit is reached, the WebView asks the user to grant permission to use more local space. This API is not supported by all platforms but you can use the IndexedDB Cordova Plugin to polyfill it.

Cordova File Plugin

This Cordova plugin implements the HTML5 Filesystem APIs for reading and writing data to files, so use this plugin if you want to store binary objects.

Cordova SQLite Plugin

This Cordova plugin lets the Cordova app access the underlying native SQLite database by providing an API identical to the Web SQL API. It has no quota limit and can sync the data to iCloud on iOS.

For most cases you will only need the Web Storage API and SQLite plugin.

Note: To store images locally you don’t have to use a file plugin. Some developers use the Base64 encoding schema to store images in SQLite. But for video, audio and other large files, encoding and decoding consumes a lot of time and memory therefore it’s recommended to store them in files and use the file URL to display them.

Overview of Web Storage

Although you might be familiar with web storage APIs, they’re worth revisiting.

How is how to add, delete, update and clear local storage:

if(localStorage != undefined)
{
  console.log("Local Storage is supported");

  //add
  localStorage.setItem("Website", "SitePoint");

  //update or overwrite
  localStorage.setItem("Website", "SitePoint.com");

  //remove
  localStorage.removeItem("Website");

  //remove all
  localStorage.clear();
}
else
{
  console.log("No support");
}

How is how to add, delete, update and clear session storage:

if(sessionStorage != undefined)
{
  console.log("session Storage is supported");

  //add
  sessionStorage.setItem("Website", "SitePoint");

  //update or overwrite
  sessionStorage.setItem("Website", "SitePoint.com");

  //remove
  sessionStorage.removeItem("Website");

  //remove all
  sessionStorage.clear();
}
else
{
  console.log("No support");
}

The Cordova SQLite Plugin

SQLite is an embedded DBMS based on the SQL language. A SQLite database with full API is provided by iOS, Android and Windows Phone.

The SQLite Cordova plugin provides a simple API to create databases and run queries on SQLite. This plugin exposes an API similar to the Web SQL API. You need to be familiar with SQL (Such as MySQL) to write queries.

Here is how to create a database and run SQL queries on the database.

// Wait for plugin to load
document.addEventListener("deviceready", onDeviceReady, false);

// Cordova is ready
function onDeviceReady()
{
    var db = window.sqlitePlugin.openDatabase({name: "demo.db"});

    db.transaction(function(tx) {

        //create table
        tx.executeSql("CREATE TABLE IF NOT EXISTS demo (id integer primary key, data text, data_num integer)", [], function(tx, res){

            //insert data
            tx.executeSql("INSERT INTO demo (id, data, data_num) VALUES (?,?,?)", [1, "test", 100], function(tx,res){

                //retrieve data
                tx.executeSql("SELECT * FROM demo WHERE id = ?", [1], function(tx, res){
                    for(var iii = 0; iii < res.rows.length; iii++)
                    {
                        alert(res.rows.item(iii).id);
                        alert(res.rows.item(iii).data);
                        alert(res.rows.item(iii).data_num);
                    }
                })

            });

        });

    }, function(err){

        //errors for all transactions are reported here
        alert("Error: " + err.message)

    });
}

Here we first create the database, then call the transaction method of the database object with a callback. Inside the callback we run the SQL queries. The queries are executed using the executeSql function which returns the response asynchronously.

If any of the queries fail, then the second callback passed to the transaction method is invoked. Callback of the executeSql will not be fired if the query fails.

To delete a database, use this code:

//delete database
window.sqlitePlugin.deleteDatabase({name: "demo.db"}, function(){
    alert("Successfully deleted database");
}, function(){
    alert("Error while delete database");
});

Creating a Note Taking App

Let’s get started with building a note taking app. This app will let users add and view notes.

You can find the final project on GitHub.

Starting

I won’t cover installing and creating a Cordova Application, if you haven’t done this before, read the getting started guide. Instructions to run and build the application are available on the same page.

Give the application an appropriate name and add the platforms you want to support. I am using Cordova’s Device plugin and SQLite third-party plugin in this tutorial. Run this command inside the Cordova project directory to install them:

cordova plugin add cordova-plugin-device
cordova plugin add cordova-plugin-sqlite

Make sure that the index.html file in the www directory looks like this, changing as relevant:

<!DOCTYPE html>
<html>
    <head>
        <meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width">
        <title>Note</title>

        <link rel="stylesheet" href="http://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.min.css">

    </head>
    <body>

        <!-- put jQuery mobile pages here -->

        <script type="text/javascript" src="cordova.js"></script>
        <script src="https://code.jquery.com/jquery-2.1.4.min.js"></script>
        <script src="http://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.min.js"></script>
        <script type="text/javascript">
            //put JS code here
        </script>
    </body>
</html>

I added jQuery and jQuery Mobile from CDNs. You can embed these files locally so that the app works without an Internet connection.

Initialize App

When the app loads we need to create the database object and check if the table to store data exists or not. If the table doesn’t exist then we need to create it.

Here is the code to initialize the app. Place this code in the script tag of the index.html page:

var db = null;

document.addEventListener("deviceready", function(){
    db = window.sqlitePlugin.openDatabase({name: "note.db"});
    db.transaction(function(tx) {
        tx.executeSql("CREATE TABLE IF NOT EXISTS note (name text primary key, data text)");
    }, function(err){
        alert("An error occurred while initializing the app");
    });
}, false);

Here we create a table with two columns, name and data. Every note will have a unique name to identify it.

Creating the Home Screen

Let’s create a home screen which will display when the app loads. On the home screen will be two buttons for adding and displaying notes.

Place this code in the body tag of the index.html page:

<div data-role="page" id="home">
    <div data-role="header">
        <h1>Home</h1>
    </div>

    <div data-role="main" class="ui-content">
        <p>
            <a target="_blank" href="#add" style="text-decoration: none"><button>Add Note</button></a>
            <a target="_blank" href="#display" style="text-decoration: none"><button>Display Notes</button></a>
        </p>
    </div>
</div>

Here is how the page now looks:

App Screenshot

Adding a Note

When the user clicks on the Add Note button we need to display another page with two fields, a field to enter the note name and another to enter the note data.

Place this code in the body tag of the index.html page:

<div data-role="page" id="add">
    <div data-role="header">
        <a target="_blank" href="#home" class="ui-btn ui-icon-home ui-btn-icon-left">Home</a>
        <h1>ADD</h1>
      </div>

      <div data-role="main" class="ui-content">
        <input type="text" id="name" placeholder="Enter Name" />
        <textarea id="note-text" placeholder="Place text here"></textarea>
        <a target="_blank" href="javascript:add()" style="text-decoration: none"><button>Add Note</button></a>
      </div>
</div>

When the user clicks the Add Note button, the add() function is invoked which stores the data in the table.

Place this code in the script tag of index.html page:

function add()
{
    var name = document.getElementById("name").value;
    var text = document.getElementById("note-text").value;

    if(name == "")
    {
        alert("Please enter name");
        return;
    }

    if(text == "")
    {
        alert("Please enter text");
        return;
    }

    db.transaction(function(tx) {
        tx.executeSql("INSERT INTO note (name, data) VALUES (?,?)", [name, text], function(tx,res){
            alert("Note Added");    
        });
    }, function(err){
        alert("An error occured while saving the note");
    });
}

Here we make sure that the input fields are not empty and then add a row to the table.

Here is how the page looks now:

Adding a Note

Displaying the Notes List

On the home page we have a button called Display Notes. When the user clicks the button we need to change to a page which displays all the notes.

Place this code in the body tag of the index.html page:

<div data-role="page" id="display">
    <div data-role="header">
        <a target="_blank" href="#home" class="ui-btn ui-icon-home ui-btn-icon-left">Home</a>
        <h1>NOTES</h1>
      </div>

      <div data-role="main" class="ui-content">
        <ul id="data-list">
        </ul>
      </div>
</div>

Next we need to retrieve all the notes from the database and populate this page. Place this code in the script tag of index.html page:

$(document).on("pagebeforeshow", "#display", function(){
    db.transaction(function(tx) {
        tx.executeSql("SELECT (name) FROM note", [], function(tx,res){
            for(var iii = 0; iii < res.rows.length; iii++)
            {
                document.getElementById("data-list").innerHTML = document.getElementById("data-list").innerHTML + "<li><a href='javascript:displayNote(\"" + res.rows.item(iii).name + "\")'>" + res.rows.item(iii).name + "</a></li>";
            }
        });
    }, function(err){
        alert("An error occured while displaying saved notes");
    });
});

We only return the name column as that is all we are displaying. Then we populate the content area with a list of names.

Here is how the page looks now:

List of Notes

Displaying a Single Note

When a user clicks on a note in the notes list we need to display the complete data of the note.

To do this, we need another page. Place this code in the body tag of the index.html page:

<div data-role="page" id="single-note">
    <div data-role="header">
        <a target="_blank" href="#display" class="ui-btn ui-icon-home ui-btn-icon-left">All</a>
        <h1 id="note-title"></h1>
      </div>

      <div data-role="main" class="ui-content">
        <p id="note-data"></p>
      </div>
</div>

To populate the name and data fields we invoke a function which retrieves all the data about the note from the database when a user clicks on a note.

Place this code in the script tag of index.html page:

function displayNote(name)
{
    db.transaction(function(tx) {
        tx.executeSql("SELECT * FROM note WHERE name = ?", [name], function(tx,res){
            var name = res.rows.item(0).name;
            var data = res.rows.item(0).data;

               document.getElementById("note-title").innerHTML = name;
               document.getElementById("note-data").innerHTML = data;

               $.mobile.changePage("#single-note");
        });
    }, function(err){
        alert(err.message);
        alert("An error occured while displaying the note");
    });
}

Here is how a single note looks:

Singe Note

Conclusion

The app is functional and can be deployed to iOS, Android and Windows Phone, but is basic. The next steps to create a full app would be letting users edit and delete notes.

This tutorial demonstrated the SQLite plugin and Web Storage, but selecting a storage mechanism for an app is a complex decision to make. What other options have you tried and how did they work for you?

  • Chris Ward

    How did it compare to the options mentioned above?

  • Veeraj Shenoy

    Error occurs on app initialization

    • Chris Ward

      OK, do you have any more detail than ‘error’?

  • AAA LLL

    I found data-list continues to grow if you go back and forth between home and display. I added a line to clear data-list before the for loop where it gets built.

    Question from a non-SQL pro… does each record automatically store creation time/date? I’d like the display page to also show the date/time the note was made in a human friendly format.

    • Crl

      Can you write the solution? Thanks!

  • Ibrahim Samad

    Nice tutorial, but I can’t see where you called the displayNote(name) for displaying single note.

    My other issue is a request: If you can make a tutorial on how to sync data from mysql server with sqlite.

    Thanks.

  • Olivia Anne

    Great tutorial! The most straightforward one I’ve been able to find for handling local data in Cordova.

    I downloaded your project from Github to deploy on my device and it’s having a breaking issue. Changing the alert() within the first database transaction function to display the error message (err.message instead of “An error occurred while initializing the application”) I get the error “Invalid database handle”. Do you know how to fix this? I’m running Android 5.1.1 on a Samsung S6.

  • sumit desai

    Good example but I’m getting this error JavaScript ERROR: Plugin SQLitePlugin.open is not supported.
    Please suggest.

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Get the latest in Mobile, once a week, for free.