Hello team,
I’m facing issue while working with ionic-native SQLite database.
I already posted question at below link, but sadly no solution there.
Hope you can help me out.
1 Like
You cannot call any SQLite methods until the platform is ready.
I tried it already, still it is not working. 
Hello guys,
finally i found solution myself. Would like to share with you all.
Please find updated code as below.
-
DBProvider.ts
import { Injectable } from “@angular/core”;
import { Platform } from “ionic-angular”;const DB_NAME: string = ‘DailySheet’;
const win: any = window;@Injectable()
export class DBProvider {
private _dbPromise: Promise;constructor(public platform: Platform) { this._dbPromise = new Promise((resolve, reject) => { try { let _db: any; this.platform.ready().then(() => { if (this.platform.is('cordova') && win.sqlitePlugin) { //FOR MOBILE DEVICE _db = win.sqlitePlugin.openDatabase({ name: DB_NAME, location: 'default' }); } else { //FOR WEBSQL console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!'); _db = win.openDatabase(DB_NAME, '1.0', 'database', 5 * 1024 * 1024); } resolve(_db); }); } catch (err) { reject({ err: err }); } }); this._tryInit(); } // Initialize the DB with our required tables _tryInit() { this.query(`CREATE TABLE IF NOT EXISTS AppUser ( UserId INTEGER NOT NULL, MobileNo TEXT NOT NULL UNIQUE, Email TEXT, PRIMARY KEY(UserId) )`).catch(err => { console.error('Storage: Unable to create initial storage tables', err.tx, err.err); }); } getAppUsers(): Promise<any> { return this.query('SELECT * FROM AppUser').then(data => { if (data.res.rows.length > 0) { console.log('Rows found.'); if (this.platform.is('cordova') && win.sqlitePlugin) { let result = []; for (let i = 0; i < data.res.rows.length; i++) { var row = data.res.rows.item(i); result.push(row); } return result; } else { return data.res.rows; } } }); } insertAppUser(): Promise<any> { let id = 1; let mobileno = '8905606191'; let email = 'niravparsana94@gmail.com'; this.query('INSERT INTO AppUser (UserId, MobileNo, Email) VALUES (' + id + ' ,\"' + mobileno + '\" ,\"' + email + '\")', []); return this.query('INSERT INTO AppUser (UserId, MobileNo, Email) VALUES (2, "9876543210","abc@gmail.com")', []); } updateAppUser(UserId): Promise<any> { let query = "UPDATE AppUser SET Email=? WHERE UserId=?"; return this.query(query, ['niravparsana@outlook.com', UserId]); } deleteAppUser(UserId): Promise<any> { let query = "DELETE FROM AppUser WHERE UserId=?"; return this.query(query, [UserId]); } query(query: string, params: any[] = []): Promise<any> { return new Promise((resolve, reject) => { try { this._dbPromise.then(db => { db.transaction((tx: any) => { tx.executeSql(query, params, (tx: any, res: any) => resolve({ tx: tx, res: res }), (tx: any, err: any) => reject({ tx: tx, err: err })); }, (err: any) => reject({ err: err })); }); } catch (err) { reject({ err: err }); } }); }}
1 Like
I would urge anybody coming across this thread not to emulate anything in the previous post. If you have a similar use case, use ionic-storage and store your user objects keyed by their user id. Much simpler, more portable, and avoids antipattern soup.
2 Likes
Why to use ionic-storage when you can use native SQLite database?
Here is an article which describes why to choose SQLite over localstorage.
That article was written before ionic-storage existed. You are attacking a strawman, because I never said anything about localstorage. Ionic storage seamlessly uses SQLite under the hood when available, and degrades gracefully when it isn’t.
3 Likes
Ionic Storage != localstorage
Ionic Storage can be SQLite under the hood, only if you don’t want that (or SQLite is not available as an desktop testing) it can fall back to localstorage.
2 Likes
So, Can you elaborate how ionic-storage works with SQLite?
You can share example for better understanding.
It says it uses key value pair then how to get records from tables or perform CRUD operation?
One more question, is it going to be helpful with WebSQL too? If yes then it would be very easy to test application during ionic serve. 
90% of mobile apps, including the snippet you showed of yours, can get by just fine with key/value pairs. Your key is your user id, and the value is a JSON object with the rest of the user information.
Yes, it gracefully falls back to using IndexedDB in a browser environment. You can even configure the order in which backend engines are preferred, if you want. The defaults are generally what most people would expect.
1 Like
Wonderful then, please share demo code if you have any. It will very helpful.
I came to know this, but why it asks us to install the plugin while it uses SQLite under the hood?
It can’t use SQLite if there is not plugin installed that offers SQLite to be used. SQLite is only available via the native plugin.
2 Likes
Good morning niravparsana94,
My name is Adriano and I’m from Brazil, I’m creating a mobile project with IONIC + CORDOVA, your post saved me dear, I’m now able to devolve with WEBSQL in development and SQLITE in PROD quietly.
The least I can do is thank you and congratulations on the post.
Thank you
1 Like
I’m glad that you find solution working. Thanks for writing.
1 Like
Hello @niravparsana94
Thank you for saving me time !
Because I’ll use many tables, so I refactor your code to use with many tables with CRUD queries generation.
Share it with you
db.ts
import {Injectable} from "@angular/core";
import "rxjs/add/operator/map";
import {Platform} from "ionic-angular";
/*
Generated class for the DbProvider provider.
See https://angular.io/docs/ts/latest/guide/dependency-injection.html
for more info on providers and Angular 2 DI.
*/
const DB_NAME: string = 'FeroUser';
const win: any = window;
export enum TABLES{Product, User}
;
@Injectable()
export class DbProvider {
private _dbPromise: Promise<any>;
constructor(public platform: Platform) {
this._dbPromise = new Promise((resolve, reject) => {
try {
let _db: any;
this.platform.ready().then(() => {
if (this.platform.is('cordova') && win.sqlitePlugin) {
//FOR MOBILE DEVICE
_db = win.sqlitePlugin.openDatabase({
name: DB_NAME,
location: 'default'
});
} else {
//FOR WEBSQL
console.warn('Storage: SQLite plugin not installed, falling back to WebSQL. Make sure to install cordova-sqlite-storage in production!');
_db = win.openDatabase(DB_NAME, '1.0', 'database', 5 * 1024 * 1024);
}
resolve(_db);
});
} catch (err) {
reject({err: err});
}
});
this._tryInit();
}
// Initialize the DB with our required tables
_tryInit(drop = false) {
if (drop) {
this.dropTable(TABLES.User);
this.dropTable(TABLES.Product);
}
this.createProductTable();
this.createUserTable();
}
private dropTable(table: TABLES) {
this.query("DROP TABLE " + TABLES[table]
).catch(err => {
console.error('Storage: Unable to create initial storage User table', err.tx, err.err);
});
}
private createProductTable() {
this.query(`
CREATE TABLE IF NOT EXISTS ` + TABLES[TABLES.Product] + ` (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
)
`).catch(err => {
console.error('Storage: Unable to create initial storage PRODUCT tables', err.tx, err.err);
});
}
private createUserTable() {
this.query(`
CREATE TABLE IF NOT EXISTS ` + TABLES[TABLES.User] + ` (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name DATE NOT NULL UNIQUE
birthday DATE NOT NULL
)
`).catch(err => {
console.error('Storage: Unable to create initial storage User table', err.tx, err.err);
});
}
list(table: TABLES): Promise<any> {
return this.query('SELECT * FROM ' + TABLES[table]).then(data => {
if (data.res.rows.length > 0) {
console.log('Rows found.');
if (this.platform.is('cordova') && win.sqlitePlugin) {
let result = [];
for (let i = 0; i < data.res.rows.length; i++) {
var row = data.res.rows.item(i);
result.push(row);
}
return result;
}
else {
return data.res.rows;
}
}
});
}
insert(newObject, table: TABLES): Promise<any> {
return this.query('INSERT INTO ' + TABLES[table] + ' (' + this.getFieldNamesStr(newObject)
+ ') VALUES (' + this.getFieldValuesStr(newObject) + ")", []);
}
private getFieldNamesStr(newObject) {
let fields = '';
for (let f in newObject) {
if (f !== "id") fields += f + ',';
}
fields = fields.substr(0, fields.length - 1);
return fields;
}
private getFieldValuesStr(object) {
let fields = '';
for (let f in object) {
if (f !== "id") fields += '\"' + object[f] + '\",';
}
fields = fields.substr(0, fields.length - 1);
return fields;
}
update(object, table: TABLES): Promise<any> {
return this.query('UPDATE ' + TABLES[table] + ' SET ' + this.getFieldSetNamesStr(object) + ' WHERE id=?',
this.getFieldValuesArray(object));
}
private getFieldSetNamesStr(object) {
let fields = '';
for (let f in object) {
if (f !== "id") fields += f + "=? ,";
}
fields = fields.substr(0, fields.length - 1);
return fields;
}
private getFieldValuesArray(object) {
let fields = [];
for (let f in object) {
if (f !== "id") fields.push(object[f]);
}
fields.push(object.id);
return fields;
}
delete(table: TABLES, object): Promise<any> {
let query = "DELETE FROM " + TABLES[table] + " WHERE id=?";
return this.query(query, object.id);
}
query(query: string, params: any[] = []): Promise<any> {
return new Promise((resolve, reject) => {
try {
this._dbPromise.then(db => {
db.transaction((tx: any) => {
tx.executeSql(query, params,
(tx: any, res: any) => resolve({tx: tx, res: res}),
(tx: any, err: any) => reject({tx: tx, err: err}));
},
(err: any) => reject({err: err}));
});
} catch (err) {
reject({err: err});
}
});
}
}
To use any of CRUD method (list, insert, update , delete), just pass the object when needed, and the name of table from the enum TABLES{product, user, …}.
In this way, when add a new table, just add it to the enum TABLES 
Thank you again.
1 Like
Hey, good to hear from you.
Also thanks for sharing the code. It will help alot.
Thanks again.
Hi Nirav,
I am too am building a helper class like the one you created. This post helped a lot as I did not find solution any where else. I have not coded yet but was looking for similar sample code. Thank you a lot.
1 Like