Hi all. I am searching now for quite some time and I’m totally confused by the changes in using SQLite in Ionic.
I don’t want to use SQLite as Localstorage. I think key value store get’s complicated if the data model grows. So I have to use SQLite and SQLiteObject now I think? I want to add it as a provider in my app.
import { Injectable } from '@angular/core';
import { Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
@Injectable()
export class DBProvider {
storage: SQLite;
constructor(public platform: Platform, public sqlite: SQLite) {
this.platform.ready().then(() => {
this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
.then((db: SQLiteObject) => {
db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {})
.then(() => console.log('Executed SQL'))
.catch(e => console.log(e));
})
.catch(e => console.log(e));
})
}
}
This is asynchroneous. Now, on the homepage I want to start the DB and load certain data from the DB and show it on the homepage. How can I do this? I mean how can I guarantuee that the promise “create” is finished when I start loading my data from the DB?
Also, is there a possibility to view the DB with a browser (like pgAdmin in postgres? Do you know good example pages? I found so many but all with older versions of sqlite for ionic.
I normally do something like this:
@Injectable()
export class DBProvider {
storage: SQLite;
ready: Promise<void;
constructor(public platform: Platform, public sqlite: SQLite) {
this.ready = this.platform.ready().then(() => {
return this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
})
.then((db: SQLiteObject) => {
this.storage = db;
return db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {});
})
.then(() => console.log('Executed SQL'))
.catch(e => console.log(e));
}
public getAll() {
return this.ready.then(_ => {
//...return ALL the lists!
});
}
}
Thanks! Great! I just don’t get why I have to create a sqliteobject too. Do I have to do this again in getAll()? Or can I set it as attribute in the class and use it again and again?
Ah, whoops. I misread what storage
was. So I don’t think you need to save a reference to SQLite
, instead you can save a reference to the SQLiteObject
that sqlite.create
returns.
So an updated example:
@Injectable()
export class DBProvider {
db: SQLiteObject;
ready: Promise<void;
constructor(public platform: Platform, public sqlite: SQLite) {
this.ready = this.platform.ready().then(() => {
return this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
})
.then((db: SQLiteObject) => {
this.db = db;
return db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {});
})
.then(() => console.log('Executed SQL'))
.catch(e => console.log(e));
}
public getAll() {
return this.ready.then(_ => {
return this.db.executeSql('SELECT * FROM lists');
});
}
}
So I replaced storage: SQLite
with db: SQLiteObject
and then set it with this.db = db;
.
Then as you can see later on you can just execute your queries with this.db
.
My new code is:
import { Injectable } from '@angular/core';
import { Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
@Injectable()
export class DBProvider {
// indicates if Promise is resolved (https://forum.ionicframework.com/t/ionic-2-3-and-sqlite/91326/2)
databaseCreated: Promise<void>;
db: SQLiteObject;
constructor(public platform: Platform, public sqlite: SQLite) {
this.databaseCreated = this.create().then(() => {this.db.executeSql('SELECT * FROM lists', {})})
}
public create() : Promise<void> {
return new Promise<void>((resolve) => {
this.platform.ready().then(() => {
this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
.then((db: SQLiteObject) => {
this.db = db;
db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {})
.then(() => console.log('Created SQL'))
.catch(e => console.log(e));
})
.catch(e => console.log(e));
})
resolve();
})
}
public getAll() {
this.db.executeSql('SELECT * FROM lists', {})
.then(() => console.log('Executed SQL'))
}
}
but gives me:
What is wrong??
see this one…
this.platform.ready().then(() => {
this.myHttp = http;
if (!this.isOpen) {
// this.storage = new SQLiteObject();
// this.storage.openDatabase({
this.sqlite.create({
name: "school.db",
location: "default"
}).then((db: SQLiteObject) => {
this.storage=db;
this.isOpen = true;
this.dbstatus++;
this.createTables();
}, (error) => {
console.error("Unable to open database", error);
});
}
});
I believe the issue is that you don’t utilize the databaseCreated
promise in getAll
. I’ve updated the code below to show what I mean.
I also changed a couple other items, namely there’s not a reason to manually create a Promise
(as showcased below) and normally it’s an anti-pattern to nest promises.
export class DBProvider {
// indicates if Promise is resolved (https://forum.ionicframework.com/t/ionic-2-3-and-sqlite/91326/2)
databaseCreated: Promise<void>;
db: SQLiteObject;
constructor(public platform: Platform, public sqlite: SQLite) {
this.databaseCreated = this.create().then(() => {this.db.executeSql('SELECT * FROM lists', {})})
}
public create() : Promise<void> {
return this.platform.ready().then(() => {
return this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
});
})
.then((db: SQLiteObject) => {
this.db = db;
return db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {});
})
.then(() => console.log('Created SQL'))
.catch(e => console.log(e));
}
public getAll() {
this.databaseCreated.then(_ => {
return this.db.executeSql('SELECT * FROM lists', {});
})
.then(() => console.log('Executed SQL'));
}
}
Just a general note, but it may wind up being easier to utilize Ionic Storage rather than SQLite directly.
1 Like
Thanks but I need real SQL support rather than just a key-value-store.
Thanks! That did it. I also found out that it’s needed to call sqlite.create in each call.
import { Injectable } from '@angular/core';
import { Platform } from 'ionic-angular';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';
@Injectable()
export class DBProvider {
// indicates if Promise is resolved (https://forum.ionicframework.com/t/ionic-2-3-and-sqlite/91326/2)
databaseCreated: Promise<void>;
constructor(public platform: Platform, public sqlite: SQLite) {
this.databaseCreated = this.create()
}
public create(): Promise<void> {
return this.platform.ready().then(() => {
this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
.then((db: SQLiteObject) => {
db.executeSql('create table if not exists lists(id INTEGER PRIMARY KEY AUTOINCREMENT, listname TEXT)', {})
.then(() => console.log('Created / Opened SQL'))
.catch(e => console.log(e));
})
.catch(e => console.log(e));
})
}
public getAll() {
this.databaseCreated.then(() => {
this.sqlite.create({
name: 'teacherApp.db',
location: 'default'
})
.then((db: SQLiteObject) => {
db.executeSql('SELECT * FROM lists', {})
.then(() => console.log('Executed SQL'))
})
})
}
}