Ionic / AngularJS service wrapper for Web SQL API / SQLite-Cordova-Plugin

app.js

angular.module('myApp', ['ionic', 'myApp.services', 'myApp.controllers'])
.run(function(DB) {
    DB.init();
});

config.js

angular.module('myApp.config', [])
.constant('DB_CONFIG', {
    name: 'DB',
    tables: [
      {
            name: 'documents',
            columns: [
                {name: 'id', type: 'integer primary key'},
                {name: 'title', type: 'text'},
                {name: 'keywords', type: 'text'},
                {name: 'version', type: 'integer'},
                {name: 'release_date', type: 'text'},
                {name: 'filename', type: 'text'},
                {name: 'context', type: 'text'}
            ]
        }
    ]
});

controllers.js

angular.module('myApp.controllers', ['myApp.services'])
.controller('DocumentCtrl', function($scope, Document) {
    $scope.documents = [];
    $scope.document = null;
    // Get all the documents
    Document.all().then(function(documents){
        $scope.documents = documents;
    });
    // Get one document, example with id = 2
    Document.getById(2).then(function(document) {
        $scope.document = document;
    });
});

services.js

angular.module('myApp.services', ['myApp.config'])
// DB wrapper
.factory('DB', function($q, DB_CONFIG) {
    var self = this;
    self.db = null;

    self.init = function() {
        // Use self.db = window.sqlitePlugin.openDatabase({name: DB_CONFIG.name}); in production
        self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1);

        angular.forEach(DB_CONFIG.tables, function(table) {
            var columns = [];

            angular.forEach(table.columns, function(column) {
                columns.push(column.name + ' ' + column.type);
            });

            var query = 'CREATE TABLE IF NOT EXISTS ' + table.name + ' (' + columns.join(',') + ')';
            self.query(query);
            console.log('Table ' + table.name + ' initialized');
        });
    };

    self.query = function(query, bindings) {
        bindings = typeof bindings !== 'undefined' ? bindings : [];
        var deferred = $q.defer();

        self.db.transaction(function(transaction) {
            transaction.executeSql(query, bindings, function(transaction, result) {
                deferred.resolve(result);
            }, function(transaction, error) {
                deferred.reject(error);
            });
        });

        return deferred.promise;
    };

    self.fetchAll = function(result) {
        var output = [];

        for (var i = 0; i < result.rows.length; i++) {
            output.push(result.rows.item(i));
        }
        
        return output;
    };

    self.fetch = function(result) {
        return result.rows.item(0);
    };

    return self;
})
// Resource service example
.factory('Document', function(DB) {
    var self = this;
    
    self.all = function() {
        return DB.query('SELECT * FROM documents')
        .then(function(result){
            return DB.fetchAll(result);
        });
    };
    
    self.getById = function(id) {
        return DB.query('SELECT * FROM documents WHERE id = ?', [id])
        .then(function(result){
            return DB.fetch(result);
        });
    };
    
    return self;
});

Hi jgoux,
First: Very nice work. It's exactly what I was looking for and you saved me lot of time I think :)
By using self.db = window.sqlitePlugin.openDatabase({name: DB_CONFIG.name}); there seems to be an error (maybe because of my android 4.4 testing-device (have read there is some kind of bug with 4.4 but can't find out what it is exactly)) so I used self.db = window.sqlitePlugin.openDatabase(DB_CONFIG.name, '1.0', 'database', -1); and it seems to work very well.
Now for my actual question: Is it possible to provide an exisiting sqlite-database instat of creating it in the app? If so, do you know where to place it (I'm developing an multi-platform app)? Can't find some helpfull tips for this.
Thanks for all help
Best regards

Hello,

This has been very helpfull, but i cant get it to run as part of my project. Any chance you have a working example of this in a mini solution available for download?

Many Thanks
Erika

How do you control the timing using this solution? If the DocumentCtrl calls Document.all() before the deviceReady event is fired then self.db will be null in DB service.

Hello wujitouch,
With ionicframework I solved this by inject $ionicPlatform to DB factory and then used $ionicPlatform.ready in query method :

.....
self.query = (query, bindings) ->
  bindings = (if typeof bindings isnt "undefined" then bindings else [])
  deferred = $q.defer()
  $ionicPlatform.ready ->
    self.db.transaction (transaction) ->
.....

I call DB.init() in app's run section also wrapped by $ionicPlatform.ready.

Hope this helps you.

My app is not running on Android tab, i changed self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1); to self.db = window.sqlitePlugin.openDatabase({name: DB_CONFIG.name}); but it does not display the result and the content of the template is also on completely appear on the screen.

Please help.

After some testing i found that if i comment out the below code in my controller everything is appearing on my screen, except the menu items i want to populate. My DB is also created and table are also populated.

Document.all().then(function(documents){
$scope.documents = documents;
});

do you have any idea why this is happening?

Hi, I have just implemented this into my project, from what i can see the return gets call twice... meaning its returning the value(s) twice. Depending on the data could this cause my application to slow down and if so how do i make sure that the function only returns the data once?

Thanks a lot, this document really help me.

Very nice gist. Thanks to you my app is a lot better organized.
Thanks

How would be the service, for an INSERT statement?

where can i find the sqlitedb file, what if i use pre-populated sqlite database?
Thanks

I am getting error "Cannot call method 'transaction' of null" on self.db.transaction(function (transaction) {...
And i can't figure why, DB.init() is inside $ionicPlatform.Ready. I don't understand why/how @zdenal fixed problem by wrapping both function and call on platform.ready?

self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1);
I replaced above line to following and it worked....
self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', 200000);

Here is how I solved the issue where self.db was not yet ready.

    self.query = function(query, bindings) {
        var wrapper = function() {

          bindings = typeof bindings !== 'undefined' ? bindings : [];
          var deferred = $q.defer();

          self.db.transaction(function(transaction) {
              transaction.executeSql(query, bindings, function(transaction, result) {
                  deferred.resolve(result);
              }, function(transaction, error) {
                  deferred.reject(error);
              });
          });

          return deferred.promise;
        };

        if (!self.db) {
          var q = $q.defer();
          $interval(function() {
            if (self.db) {
              q.resolve();
            }
          }, 100, 50);
          return q.promise.then(function() {
            return wrapper();
          });
        }

        return wrapper();
    };

I am not sure if this is a correct approach, but it worked for me.

Any Insert Statement Sample?

Thanks jgoux. Nice gist!

I've created a variant of this with insert and edit statements, and using ngCordova:
https://gist.github.com/borissondagh/29d1ed19d0df6051c56f

👍 This is awesome, thanks for sharing!

If anyone's having issues with Ionic specifically, @zdenal's comment about using IonicReady worked for me! (and if not, presumably waiting for deviceready should also do the trick)

I can't use them with sqlite. Can you upload your example to github. Thank you very much.

checkout my WebSQLite plugin for angularJS (angular-wsql). It wroks like a magic when u need to deal with WebSQLite in angnular. I'm really happy with it.

https://github.com/igorizr1/wSQL

it is also available via bower
bower install angular-wsql

When using DB.query for select it returns set of records. Now I want to implement DB.query(.....).then(); for insert query.
then() is not called in case of insert query. I want to check if my Insert suceeded or failed in then()

how do i store remote json data in sqlite

See my example using ngCordova and Service Pattern :)
https://gist.github.com/jdnichollsc/9ac79aaa3407e92677ba/

To download the code: http://1drv.ms/1Ono0Ys

Regards, Nicholls

"TypeError: window.sqlitePlugin" is undefined i am getting this error in browser and in phone i am getting this error Uncaught "TypeError: Cannot call method 'openDatabase' of undefined " please help me to get rid of this error .Thanks !!

hello jdnichollsc, okay !! How would the insert, update and delete. thank you.

very helpful tutorial. just one question, how to define foreign key relation between two tables in the config.js file?

Any sample for adding foreign key. How to define it?

Thank you jgoux ...

Hi @Djamilson, see my starter template using SQLite https://github.com/jdnichollsc/Ionic-Starter-Template

Thank you @jgoux
But, could you help me, how if I wanna initiate multiple tables?