Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ionic / AngularJS service wrapper for Web SQL API / SQLite-Cordova-Plugin
angular.module('myApp', ['ionic', 'myApp.services', 'myApp.controllers'])
.run(function(DB) {
DB.init();
});
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'}
]
}
]
});
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;
});
});
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;
});
@kJamesy

This comment has been minimized.

Copy link

@kJamesy kJamesy commented May 21, 2014

Thanks for this.
Could you give me a pointer as to what I'm doing wrong?
https://gist.github.com/kJamesy/a963714f6aaf1cf9cea4

SQLitePlugin was able to install the database and query it. But when I tried your wrapper, it just doesn't display - I guess I have a fatal error somewhere. (Won't know since I can't really test in the browser)

Cheers

@jgoux

This comment has been minimized.

Copy link
Owner Author

@jgoux jgoux commented Jun 4, 2014

Hello kJamesy,
I can't really debug your code without an error or something. Could you try to initialize the WebSQL database instead of the Sqlite Database ? (self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1);)
By doing it you can use the plugin in the browser and see in your JS console what's wrong.

@kJamesy

This comment has been minimized.

Copy link

@kJamesy kJamesy commented Jun 4, 2014

I actually got it to work, somehow! It just dies in the browser presumably because of the missing cordova.js but it's querying the database alright, I just found out! Was trying to see how I can use it to insert into the database.

@renato04

This comment has been minimized.

Copy link

@renato04 renato04 commented Jul 3, 2014

i'm don't know how is the right way to add those js files inside of my html index file. Could anyone help me?
Thanks.

@jkummer

This comment has been minimized.

Copy link

@jkummer jkummer commented Jul 9, 2014

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

@erikadanis81

This comment has been minimized.

Copy link

@erikadanis81 erikadanis81 commented Jul 26, 2014

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

@vicpon

This comment has been minimized.

Copy link

@vicpon vicpon commented Jul 28, 2014

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.

@zdenal

This comment has been minimized.

Copy link

@zdenal zdenal commented Aug 21, 2014

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.

@sarimghani

This comment has been minimized.

Copy link

@sarimghani sarimghani commented Aug 24, 2014

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.

@sarimghani

This comment has been minimized.

Copy link

@sarimghani sarimghani commented Aug 24, 2014

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?

@knightfox1337

This comment has been minimized.

Copy link

@knightfox1337 knightfox1337 commented Sep 1, 2014

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?

@yijian166

This comment has been minimized.

Copy link

@yijian166 yijian166 commented Sep 27, 2014

Thanks a lot, this document really help me.

@BondaCB

This comment has been minimized.

Copy link

@BondaCB BondaCB commented Sep 30, 2014

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

@quicoli

This comment has been minimized.

Copy link

@quicoli quicoli commented Oct 9, 2014

How would be the service, for an INSERT statement?

@bakulaw

This comment has been minimized.

Copy link

@bakulaw bakulaw commented Oct 20, 2014

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

@mladenp

This comment has been minimized.

Copy link

@mladenp mladenp commented Nov 10, 2014

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?

@atulrungta

This comment has been minimized.

Copy link

@atulrungta atulrungta commented Nov 14, 2014

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);

@FMCorz

This comment has been minimized.

Copy link

@FMCorz FMCorz commented Dec 8, 2014

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.

@jbagaresgaray

This comment has been minimized.

Copy link

@jbagaresgaray jbagaresgaray commented Jan 21, 2015

Any Insert Statement Sample?

@ramoncarreras

This comment has been minimized.

Copy link

@ramoncarreras ramoncarreras commented Jan 21, 2015

Thanks jgoux. Nice gist!

@botris

This comment has been minimized.

Copy link

@botris botris commented Jan 28, 2015

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

@alexweber

This comment has been minimized.

Copy link

@alexweber alexweber commented Jan 31, 2015

👍 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)

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Feb 12, 2015

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

@igorizr1

This comment has been minimized.

Copy link

@igorizr1 igorizr1 commented Mar 31, 2015

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

@ritvick

This comment has been minimized.

Copy link

@ritvick ritvick commented Jun 25, 2015

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()

@hridayakandel

This comment has been minimized.

Copy link

@hridayakandel hridayakandel commented Jul 8, 2015

how do i store remote json data in sqlite

@jdnichollsc

This comment has been minimized.

Copy link

@jdnichollsc jdnichollsc commented Oct 23, 2015

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

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

Regards, Nicholls

@hamza-d52

This comment has been minimized.

Copy link

@hamza-d52 hamza-d52 commented Nov 4, 2015

"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 !!

@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jan 3, 2016

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

@attiqJumani

This comment has been minimized.

Copy link

@attiqJumani attiqJumani commented Mar 3, 2016

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

@shraddha1112

This comment has been minimized.

Copy link

@shraddha1112 shraddha1112 commented Apr 28, 2016

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

@skanth91

This comment has been minimized.

Copy link

@skanth91 skanth91 commented May 25, 2016

Thank you jgoux ...

@jdnichollsc

This comment has been minimized.

Copy link

@jdnichollsc jdnichollsc commented May 29, 2016

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.