Cordova SQLite Storage Tutorial (A complete CRUD)

Hello friend,

Tired of searching cordova sqlite storage example? No worries, I have written a step by step tutorial for integrating sqlite with your cordova (PhoneGap) apps.

Please login to download source code Login Here

What is SQLite ?

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private.

What I’m going to do now ?

Create a new SQLite Database
Create New Table
Insert Data
Read Data
Update Data
Delete Data
Drop table
Drop Database

Step by Step Guide for PhoneGap SQLite tutorial

Step 1 : Create a new PhoneGap / Apache Cordova Project

cordova create SQLiteExample com.codesundar.sqlite SQLiteExample
cd SQLiteExample

Step 2 : Add required platform such as android, ios

cordova platform add android

Step 3 : Add Plugin for SQLite

cordova plugin add cordova-sqlite-storage

Dependancies : I used Ionic CSS Components (ionic.css) & Jquery (jquery.js) with this example

Now we’re going to perform CRUD operation with apache Cordova / PhoneGap SQLite.

Database Name : mySQLite.db

Table Name : codesundar

Table Fields : id, title, desc

Functions / Methods for database Operations

openDatabase – Create database connection

transaction – create transactions

executeSql – executes Query with database

SYNTAX

myDB.transaction(function(transaction) {
 var executeQuery = "QUERY";
 transaction.executeSql(executeQuery, [ ],
 function(tx, result) {
//Success
},
function(error){
// Error
 });

Creating New Database using PhoneGap with SQLite Plugin

First, we need to create / open a database.

var myDB = window.sqlitePlugin.openDatabase({name: "mySQLite.db", location: 'default'});

Creating New Table using PhoneGap with SQLite Plugin

Using the myDB variable we need to execute the command. use CREATE a query for creating a table

myDB.transaction(function(transaction) {
transaction.executeSql('CREATE TABLE IF NOT EXISTS codesundar (id integer primary key, title text, desc text)', [],
function(tx, result) {
alert("Table created successfully");
},
function(error) {
alert("Error occurred while creating the table.");
});
});

Insert data into `codesundar` table

Inserting a data to the table. Here I used static data, instead of that you can pass the data in dynamic. Use INSERT query for inserting data to SQLite database

var title="sundaravel";
var desc="phonegap freelancer";
myDB.transaction(function(transaction) {
var executeQuery = "INSERT INTO codesundar (title, desc) VALUES (?,?)";
transaction.executeSql(executeQuery, [title,desc]
, function(tx, result) {
alert('Inserted');
},
function(error){
alert('Error occurred');
});
});

View All Data

for reading all data from table execute SELECT the query

myDB.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM codesundar', [], function (tx, results) {
var len = results.rows.length, i;
$("#rowCount").append(len);
for (i = 0; i < len; i++){
$("#TableData").append("<tr><td>"+results.rows.item(i).id+"</td><td>"+results.rows.item(i).title+"</td><td>"+results.rows.item(i).desc+"</td></tr>");
}
}, null);
});

Update Data to SQLite Database Table:

use UPDATE Query for updating the database with id

$("#update").click(function(){
 var id=$("#id").text();
 var title=$("#title").val();
 var desc=$("#desc").val()
 myDB.transaction(function(transaction) {
 var executeQuery = "UPDATE codesundar SET title=?, desc=? WHERE id=?";
 transaction.executeSql(executeQuery, [title,desc,id],
 //On Success
 function(tx, result) {alert('Updated successfully');},
 //On Error
 function(error){alert('Something went Wrong');});
 });
});

Delete Data from table

use DELETE Query for updating the database with id

myDB.transaction(function(transaction) {
 var executeQuery = "DELETE FROM codesundar where id=?";
 transaction.executeSql(executeQuery, [id],
 //On Success
 function(tx, result) {alert('Delete successfully');},
 //On Error
 function(error){alert('Something went Wrong');});
 });

Delete entire table

use DROP Query for updating the database with id

myDB.transaction(function(transaction) {
 var executeQuery = "DROP TABLE IF EXISTS codesundar";
 transaction.executeSql(executeQuery, [],
 function(tx, result) {alert('Table deleted successfully.');},
 function(error){alert('Error occurred while droping the table.');}
 );
 });