Cordova SQLite Tutorial

Hello friend,

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

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 we’ll learn

• Create a new SQLite Database
• Create a 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: Let’s add platform and plugin to your project

cordova platform add android
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 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.');
        }
    );
});

Download