Storing Data Locally in a PhoneGap App with SQLite

Data storing is a basic requirement while creating an application.

It is possible to store data online but the app needs to be online whenever data processing is required.

For local data storage use SQLite database which is already embedded on the mobile platforms – Android, IOS, Windows, Blackberry, etc.

The Cordova plugin provides support to access SQLite database in the app.

In this tutorial, I am creating an Android app where use SQLite database to save and retrieve records. Deploy the application with PhoneGap Build.

Demo App Download

Create App
Create Database
Create Table
Insert Records
Select Records
Completed Code
Deploy
Conclusion

1. Create App

Download and install Nodejs. Open Nodejs console.
Navigate to the directory where you want to create an application.

F:\phonegap>

Execute following command in Nodejs console.

F:\phonegap>cordova create myapp

Here, myapp is the name of the project.

Add Platform

Navigate to project directory myapp and execute the command cordova platform add android --save to add Android platform.

F:\phonegap>cd myapp
F:\phonegap\myapp>cordova platform add android --save

Add files

Copy config.xml from project root to www/ directory and copy cordova.js from platforms\android\platform_www\cordova.js to www/ directory.

Learn more about application setup from here.

2. Create Database

With openDatabase() method create the database and open it.

// window.openDatabase("database-name","version","database description","database size in bytes")
var db = window.openDatabase("tutorialdb", "1.0", "tutorial database", 1000000); //will create database tutorialdb or open it

3. Create Table

With transaction() method prepare and execute SQL query with executeSQL().

The transaction() method takes 3 parameters –

A function from where set SQL query and execute.
A function for error handling.
A function to handle successful execution.

It is called from database initialized object.

db.transaction(populateDB, errorCB, successCB);

function populateDB(tx){
 tx.executeSql('CREATE TABLE IF NOT EXISTS notes (id INT,note TEXT NOT NULL)');
}

function errorCB(err) {
 alert("Error processing SQL: "+err.code);
}

function successCB() {
 // alert("success!");
}

4. Insert Records

In a similar way insert records to the created Table with transaction() and executeSql() method.

db.transaction(insertNote, errorCB, successCB);

function insertNote(tx){
 var note = document.getElementById('note').value;
 tx.executeSql("INSERT INTO notes(note) VALUES (?)",[note]);
}

function errorCB(err) {
 alert("Error processing SQL: "+err.code);
}

function successCB() {
 // alert("success!");
}

5. Select Records

Execute SQL query and loop on the result to get data.

db.transaction(function(tx){
 
 tx.executeSql("select * from notes",[],function(tx,result){
   var len = result.rows.length;
 
   for (var i=0; i<len; i++){
    var note = result.rows.item(i).note;
   }
 
 },errorCB);
}, errorCB, successCB);

6. Completed Code

First, create database and table if not exist. Fetch existing records from the table and on button submit insert record to the table.

<!DOCTYPE html>
<html lang="en">
 <head>
  <meta charset="UTF-8">
  <meta http-equiv="Content-Security-Policy" content="default-src *; style-src 'self' 'unsafe-inline'; script-src 'self' 'unsafe-inline' 'unsafe-eval'" />
  <title>PhoneGap App</title>

 </head>
 <body>
 
  <input type='text' id='note' >
  <button onclick='insertData()' id='submit'>Submit</button><br>
  <ul id='list'></ul>

  <!-- Script -->
  <script type="text/javascript" src="cordova.js"></script>
  <script type="text/javascript">

  // window.openDatabase("database-name","version","database description","database size in bytes")
  var db = window.openDatabase("tutorialdb", "1.0", "tutorial database", 1000000); //will create database Dummy_DB or open it
  document.addEventListener("deviceready", onDeviceReady, false);
 
  function onDeviceReady() {

   // Create Table
   db.transaction(populateDB, errorCB, successCB);

   // Select records
   fetchData();
  }

  function populateDB(tx){
   tx.executeSql('CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY AUTOINCREMENT,note TEXT NOT NULL)');
  }

  // Fetch all records
  function fetchData(){
   db.transaction(function(tx){
 
    tx.executeSql("select * from notes",[],function(tx1,result){
     var len = result.rows.length;
 
     for (var i=0; i<len; i++){
      var note = result.rows.item(i).note;

      // Add list item
      var ul = document.getElementById("list");
      var li = document.createElement("li");
      li.appendChild(document.createTextNode(note));
      ul.appendChild(li);
     }
 
    },errorCB);
   }, errorCB, successCB);
  }
 
  function insertData(){

   // Insert record
   db.transaction(insertNote, errorCB, successCB);
  }

  function insertNote(tx){
   var note = document.getElementById('note').value;
 
   // Insert query
   tx.executeSql("INSERT INTO notes(note) VALUES (?)",[note]);
 
   // Append new list item
   var ul = document.getElementById("list");
   var li = document.createElement("li");
   li.appendChild(document.createTextNode(note));
   ul.appendChild(li);
  }

  function errorCB(err) {
   alert("Error processing SQL: "+err.code);
  }

  function successCB() {
   // alert("success!");
  }
 
  </script>
 </body>
</html>

7. Deploy

Open PhoneGap build in your web browser and log in to your account.
Create a zip of www/ files and upload it.

Click on Ready to Build button.
Now download the Android app either by clicking Android icon or by QR code.

Demo App

8. Conclusion

To maintain your app data on the device use SQLite database which is already embedded in mobile devices and Cordova provide support for access it.

Contents