Ionic 2/3 and SQLite


#1

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.


#2

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

#3

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?


#4

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.


#8

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


#9

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





      }
    });

#10

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

#11

Just a general note, but it may wind up being easier to utilize Ionic Storage rather than SQLite directly.


#12

Thanks but I need real SQL support rather than just a key-value-store.


#13

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'))
        })
    })
  }
}