Factory for Data Operations on SQLite using Ionic

One of the pain points in hybrid app development is data persistence & data storage. Though LocalStorage can be used for storing less critical data like cache, devs usually look at SQLite for consistent data storage backend. SQLite works fine for both the platforms (Android & iOS). 

In this post we discuss how to efficiently work with SQLite using a simple factory that can be used for doing simple operations on your SQLite Database. 

SQLite can be accessed natively only, so you need to install the cordova plugin for SQLite.

Download ngCordova dependancies

bower install ngCordova

Include ng-cordova.min.js in your index.html file before cordova.js and after your AngularJS / Ionic file (since ngCordova depends on AngularJS).

<script src="lib/ngCordova/dist/ng-cordova.js"></script>
<script src="cordova.js"></script>

Inject as an Angular dependency

angular.module('myApp', ['ngCordova'])

Install SQLite Cordova Plugin

cordova plugin add https://github.com/litehelpers/Cordova-sqlite-storage.git

Declare a global variable 

var db = null;

So that 'db' is accessible through our the scope the app.

In your app.js :-

if (window.cordova) {
    $rootScope.showHeader = false;
    db = $cordovaSQLite.openDB({ name: 'myapp.db', location: 'default' });
} else {
    db = window.openDatabase("myapp.db", "1.0", "My app", -1);
}

'myapp.db' is the name of your DB. The github page for cordova-sqlite-storage plugin have examples on how to do different operations on the database, below is a simple factory that can make these operations clean and readable:

.factory('DBA', function($cordovaSQLite, $q, $ionicPlatform) {
        var self = this;
        self.query = function(query, parameters) {
            parameters = parameters || [];
            var q = $q.defer();
            $ionicPlatform.ready(function() {
                $cordovaSQLite.execute(db, query, parameters)
                    .then(function(result) {
                        q.resolve(result);
                    }, function(error) {
                        q.reject(error);
                    });
            });
            return q.promise;
        }
        self.getAll = function(result) {
            var output = [];
            for (var i = 0; i < result.rows.length; i++) {
                output.push(result.rows.item(i));
            }
            return output;
        }
        self.getById = function(result) {
            var output = null;
            output = angular.copy(result.rows.item(0));
            return output;
        }
        return self;
    })
    .factory('Data', function($cordovaSQLite, DBA) {
        var self = this;
        self.all = function() {
            return DBA.query("SELECT key, value FROM your_table")
                .then(function(result) {
                    return DBA.getAll(result);
                });
        }
        self.get = function(key) {
            var parameters = [key];
            return DBA.query("SELECT key , value FROM your_table WHERE key = (?)", parameters)
                .then(function(result) {
                    return DBA.getById(result);
                });
        }
        self.add = function(obj) {
            var parameters = [obj.key, obj.name];
            return DBA.query("INSERT INTO your_table (key , value) VALUES (?,?)", parameters);
        }
        self.remove = function(obj) {
            var parameters = [obj.key];
            return DBA.query("DELETE FROM your_table WHERE key = (?)", parameters);
        }
        self.update = function(oldkey, newDataObj) {
            var parameters = [newDataObj.key, newDataObj.value, oldkey];
            return DBA.query("UPDATE your_table SET key = (?), value = (?) WHERE key = (?)", parameters);
        }
        return self;
    })

Disclaimer -- This factory code is for demonstration only, please sanitise and secure your user inputs. Additionally, you can make "your_table" dynamic depending on your use-case.

You may use this factory and do the CRUD operations

Add name in db  :-

var nameObj = {};
nameObj.key = "name ";
nameObj.name = "Abhay Kumar";
Data.add(nameObj);

Get name :-

Data.get("name").then(function(result) {
    userName = result.value;
});

Update name :-

var  nameObj = {};
nameObj.key = "name";
nameObj.value = "QED42";
Data.update("name", nameObj).then(function(result) {
    console.log("Result :", result);
})

Delete name :-

var nameObj = {};
nameObj.key = "name";
Data.remove(nameObj).then(function(result) {
    console.log("Result :", result);
});

Hope you find it useful, Let us know in Comments if you extend this factory or have a better one!