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