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;
});
@jkummer
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

yijian166 commented Sep 27, 2014

Thanks a lot, this document really help me.

@BondaCB
Copy link

BondaCB commented Sep 30, 2014

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

@quicoli
Copy link

quicoli commented Oct 9, 2014

How would be the service, for an INSERT statement?

@bakulaw
Copy link

bakulaw commented Oct 20, 2014

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

@mladenp
Copy link

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
Copy link

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
Copy link

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
Copy link

jbagaresgaray commented Jan 21, 2015

Any Insert Statement Sample?

@ramoncarreras
Copy link

ramoncarreras commented Jan 21, 2015

Thanks jgoux. Nice gist!

@botris
Copy link

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
Copy link

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)

Copy link

ghost commented Feb 12, 2015

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

@igorizr1
Copy link

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
Copy link

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
Copy link

hridayakandel commented Jul 8, 2015

how do i store remote json data in sqlite

@jdnichollsc
Copy link

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
Copy link

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

Copy link

ghost commented Jan 3, 2016

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

@attiqJumani
Copy link

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
Copy link

shraddha1112 commented Apr 28, 2016

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

@skanth91
Copy link

skanth91 commented May 25, 2016

Thank you jgoux ...

@jdnichollsc
Copy link

jdnichollsc commented May 29, 2016

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

@naufal18-bot
Copy link

naufal18-bot commented Feb 1, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment