Phonegap SQLite Tutorial with Example – Apache Cordova

phonegap-sqlite-tutorial
phonegap-sqlite-tutorial
PhoneGap-SQLite-tutorial

In this tutorial, we’re going to see how to use the sqlite plugin with PhoneGap / apache Cordova with the example.This Apache Cordova / Phonegap SQLite Tutorial with example explains to you how to perform CRUD operation with SQLite Database such as Create Database, Table, Insert, Update, Delete and Read data.

[download src=”https://phonegappro.com/wp-content/uploads/2016/05/phonegap-sqlite-example.zip” text=”DOWNLOAD SOURCE CODE”]

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.

Supported Platform : Android, iOS, Windows Universal(8.1), Amazon Fire-OS, WP(7/8)

What I’m going to do now ?

  1. Create a new SQLite Database
  2. Create New Table
  3. Insert Data
  4. Read Data
  5. Update Data
  6. Delete Data
  7. Drop table
  8. Drop Database

Step by Step Guide for PhoneGap SQLite tutorial

Step 1 : Create a new PhoneGap / Apache Cordova Project

cordova create SQLiteExample com.phonegappro.sqlite 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 : phonegap_pro

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 phonegap_pro (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 `phonegap_pro` 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 phonegap_pro (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 phonegap_pro', [], 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 phonegap_pro 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 phonegap_pro 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 phonegap_pro";
 transaction.executeSql(executeQuery, [],
 function(tx, result) {alert('Table deleted successfully.');},
 function(error){alert('Error occurred while droping the table.');}
 );
 });

[download src=”https://phonegappro.com/wp-content/uploads/2016/05/phonegap-sqlite-example.zip” text=”DOWNLOAD SOURCE CODE”]

  • Was this Helpful ?
  • Yes   No
  • sakshi tripathi

    create table and other buttons are not working..how to run the code..help

  • Theo Affail

    aren’t these supposed to be in controllers or something?
    I’m using the phone gap desktop app and also using the Ionic which is based on Angularjs

    It comes with files such as app.js, controllers.js, etc

    most tutorials about this look something like this (this is actually from the cordova site)

    module.controller(‘MyCtrl’, function($scope, $cordovaSQLite) {

    var db = $cordovaSQLite.openDB({ name: “my.db” });

    // for opening a background db:

    var db = $cordovaSQLite.openDB({ name: “my.db”, bgType: 1 });

    $scope.execute = function() {

    var query = “INSERT INTO test_table (data, data_num) VALUES (?,?)”;

    $cordovaSQLite.execute(db, query, [“test”, 100]).then(function(res) {

    console.log(“insertId: ” + res.insertId);

    }, function (err) {

    console.error(err);

    });

    };

    });

    but yours doesn’t show anything like that and not sure where to use it. any help would be great

    • Hi @theoaffail:disqus , This example is purely based on javascript. but you can refer Ionic + ngCordova

  • Jun Wei Ng

    may I know where is location for the sqlite database? and also, your tutorial is not explain well, i have installed sqlite plugin in the sample app u given, but the buttons and table are not working as well.

    • 1) you’ve to include sqlite plugin
      2) we cannot find the location

      • Jun Wei Ng

        which is how to include the sqlite plugin?

        • cordova plugin add cordova-sqlite-storage

          • Jun Wei Ng

            yes, i have done this, but it’s still not working.

          • It should work man!!, It’s a working code only

          • Jun Wei Ng

            yeah, it should, but i still cant figure out what happen actually, do you mind to send a working sample to me? it is very important to me, because i’m learning mobile apps in this semester in my university.

            my email: junweiisthebos@gmail.com

          • Lanaya

            yes, same goes to me. Mid send me a working sample too? Email : shockx96@gmail.com . Thanks

  • @sundaravel am new to phonegap. One thing i seems not to understand is how to install the sqlite plugin “cordova plugin add cordova-sqlite-storage” doesnt make much meaning to me. can you paste here an example of a working index.html showing something like

    ——————————————————————————————————-
    ——————————————————————————————————–

    Hello World

    Creating tables

    myDB.transaction(function(transaction) {

    transaction.executeSql(‘CREATE TABLE IF NOT EXISTS phonegap_pro (id integer primary key, title text, desc text)’, [],

    function(tx, result) {

    alert(“Table created successfully”);

    },

    function(error) {

    alert(“Error occurred while creating the table.”);

    });

    });

    ————————————————————————————————–
    ————————————————————————————————–

    Please help me out, because am not making meaning out of whats is going on here.

    • My Dear Friend@Bruno, In Phonegap “Plugin is Everything”, If you want to work with SQLite, you need to add respective plugin

      Tips: you can download my example & try it by yourself

  • Snehil

    Hi , Sundaravel
    Thanks for this great tutorial ,
    I am new to phonegap , I have downloaded your code and build it online , now when i installed apk in my android phone , it does nothing .
    How to install sqlite plugin where to install it . Please help

    • use CLI mode, please read my installation guide to do that

  • deepak reddy

    hii could you please help me with a login page having username & password validation using sqlite

    • Yes, It’s possible but how you validate user without webservice ?

      • deepak reddy

        using sqlite db based on the key value concept as of mysql.
        but am not sure whether it works??

      • deepak reddy

        I mean to say just like gmail login.
        user authentication before entering into app

  • ramesh

    Hi Velu,

    I have new one for phonegap. I have tried following your setps wat ever u told.Finally i tried to run the application in andriod mble .But i got this error ” uncaught type error cannot call method ‘transaction of undefined ” . How cani rectify please tell me where i mistaken .

    Regards,
    Ramesh

  • ramesh

    Please check the image

    • I’ve updated that code. Download & Try again

  • ramesh

    i made test2 image

  • ramesh

    i have done test 3 image

  • ramesh

    hi Velu,

    I have downloaded in your updated code and checked i get attached errors. Please check it

  • Daniel Sifontes

    Hi Bro,
    I follow your instruccions but create a table it’s wrong! Just show the alert error! ….. This my Piece of code :
    (all this on $ionicPlatform.ready function)

    var QDCatalogo = window.sqlitePlugin.openDatabase({name: “QDCatalogo.db”, location: ‘default’});

    QDCatalogo.transaction(function(transaction){

    transaction.executeSql(‘CREATE TABLE IF NOT EXISTS TbParCab (ClienteWeb text , Empresa integer primary key, Parametro integer primary key, Codigo integer primary key, Nombre text, Campo text, Relacion text, Status text, NombreCabEn text, NombreCabCh text)’, [],

    function(tx, result){

    alert(‘tabla TbParCab creada’);

    },

    function(error){

    alert(‘Ocurrio un error creando tabla TbParCab’);

    });

    });

    • Daniel Sifontes

      My error was on many primary key, so I fixed!

  • irwan Dwiyanto

    Hi , Sundaravel thanks for tutorial but I tried to make a query with ” select ( … ) where = ? ” but no way is there any wrong with my script ?

    function detailDB(tx) {
    tx.executeSql(‘SELECT judul FROM BUKU where id = ?’, [id], function (tx, results) {
    var name = results.rows.item(0).noqr;
    var data = results.rows.item(0).judul;

    document.getElementById(“noqr”).innerHTML = noqr;
    document.getElementById(“judul”).innerHTML = judul;

    }, errorCB );
    }

  • Rohan

    how to run sqlite on windows?

    • You don’t want to install any special software for that, you just add plugin & start coding

  • Anisha

    where the sqlite database stored?

    • Every Mobile has an internal database, you data’ll store @ internal storage only (Phone Memory)

      • Anisha

        var db = window.openDatabase(name, version, displayName, estimatedSize);
        db.transaction(function (tx) {
        tx.executeSql(sqlStatement, valueArray, function (tx, result) {
        console.log(result);
        }, function (error) {
        console.log(error);
        });
        });

        —— found this in https://cordova.apache.org………………. where this database stored

  • Jooo

    I create project, add plugin, build on Android and throw your file into folder platforms/android/platform_www and when I open index.html in browser showing log after click button “new transaction waiting for a operation”… please help me! It’s very important for me 🙁

    • 1) You need to paste your file in FolderName/www
      2) This will work only on device

      • Jooo

        Ok, from the begin :)… I create project, add Android platform and add plugin. I have 4 folders(platforms,…., www). I paste your file in www folder. When I open project throw log “cordova.js” missing and after click Button “transaction of undefined”… when I open app on device, click button do nothing

        • Supritha Rao

          Hey I am facing the same problem. Did u get the solution? Can You please help me..

          • Jooo

            I opened it in Android Studio only… When I tried open it in phonegap (emulator or device) all time throw log “transaction of undefined”… Maybe this problem is related with wrong version phonegap and sqlite…

          • Supritha Rao

            Thank You. I am a beginner to this. Any changes to be made in the code? In the browser, in console i am getting error : “Uncaught Error: Module cordova-sqlite-storage.SQLitePlugin does not exist.” and
            “Uncaught TypeError: Cannot read property ‘transaction’ of undefined”.
            And Directly i want to run it on my mobile for now without any emulator, not possible?

          • Jooo

            I changed nothing in the code. Database will not work in browser, only in device or emulator. You can two solution:
            1) generate android (or iOS etc.) file using phonegap (many tutorials is on yt) and next open it in Android Studio(or other..) and run on device or emulator – this solution only work on my case.
            2) try run app on device using only phonegap, there is tut how you can do this: https://www.formget.com/phonegap-build/ – when I tried run every app with database using this solution I had “transaction” log still… probably it’s fault new version phonegap.
            I

  • sumit desai

    I’m using cordova SQLite plugin in my cordova mobile app project .

    Android 5.0 , cordova 6.3.1 cordova-sqlite-storage 1.4.7

    DB is getting opened properly but after execution of any transaction like select ,update delete etc I’m getting below error
    !JavaScript ERROR: Plugin SQLitePlugin.open is not supported
    Please suggest and help .
    Thanks in advance

    • Declare your connection object as global variable.

  • sumit desai

    Can you share complete cordova app source code?

  • Yasar

    I can’t see where you are dropping the database.
    You are only dropping the tables….

    • you can try this
      this.deleteDatabase(“databasename.db”);

  • ravi mca

    Downloading is not available.
    Ii is asking to login. I register with this site but i did not get any password to the email.
    How to download this source code

    • You can download now

      • ravi mca

        Thanks Sundara Vel, Now it is downloading.

      • Anil pareek

        is asking to login,plz help me sir.send me username and password ??

    • Anil pareek

      is asking to login,plz help me.send me username and password ??

  • Trishna Pampari

    Hi, can we upload csv file to sqlite, Please give an example

  • Brodybits (Chris Brody)

    Please add the “–save” flag to the cordova plugin add command so that people do not have to commit the plugins tree in git or other source control systems. I already documented this in https://github.com/litehelpers/Cordova-sqlite-storage#quick-installation and raised this in: https://github.com/cordova/cordova-discuss/issues/56

  • David Boyd

    hello, i registered but the download’s not available

  • Arnold Parge

    I am new to PhoneGap.
    where to write the following:
    cordova create SQLiteExample com.phonegappro.sqlite SQLiteExample