How to use database method ( Sqlite plugin) as a Provider in Ionic 3?

Question

I am using SqLite native plugin in Ionic 3 App. As per documentation , it works as expected. In app.commponent.ts , I created the table like this:

this.sqlite.create({
        name: 'sdt.db',
        location: 'default'
      })
        .then((db) => {
          //create table if not exists!!!

          this.db = db;

          console.log(" within app components");


         var createTableAccount = "CREATE TABLE IF NOT EXISTS 'accounts' (  'accountid' INTEGER,   'accountName'    TEXT NOT NULL,  'remarks'   TEXT, 'initBalance' INTEGER NOT NULL,   PRIMARY KEY('accountid')   );"            

          this.db.transaction(function(tx) {     

            tx.executeSql(createTableAccount);     
            //todo: create a transaction table ......... 
            //todo: insert data to table             

          }).then(() => {
            console.log("basic structure sql executed")
            //this.presentToast();

          }).catch(e => console.log(e));;


        });

In Home.ts pages constructor, I have used like this

this.sqlite.create({
      name: 'sdt.db',
      location: 'default'
    })
      .then((db) => {
        this.db = db;


      });

in pages:

  ionViewDidLoad() {

    this.loader = this.loading.create({
      content: 'Loading ...',
      cssClass: "loadingControllerCustomCss"
    });

    this.loader.present().then(() => {
     this.getBalance();
    });

  }

detail method is

 getBalance() {
    var balanceQuery = "select sum(trxamount) sumofamount from transactiontable";
    this.db.executeSql(balanceQuery, [])
      .then((data) => {
       this.balance = data.rows.item(0).sumofamount;
      }

      )
      .catch(e => console.log(e));

  }

But I want to create table once and reuse the getBalance() method so that I don't have to repeat segment of code.Hence, I want to use a provider(example BackendService) as a service method which can be reusable to all pages.

What will be the best practice ?

Can any body help with a complete example of sqlite native plugin as a provider in Ionic 3 where open database,create schema and get data will be shown?

Thanks in advanace!

Please share actual code in here otherwise its too many assumptions;( need app.module.ts (this is where you will init provider), app.component.ts, the page where you want provider to be used. — Sergey Rudenko, Feb 11 '18 at 01:49
I have shared all codes already.I want to use the provider in any pages. — mnhmilu, Feb 11 '18 at 01:56

Sergey Rudenko · Accepted Answer · 2018-02-11T18:00:10.810

OK first you need to install native plugins, see instructions here: https://ionicframework.com/docs/native/sqlite/

once done create your sqlite provider. Normally inside src you do folder "providers" and add sqlite.ts.

Its contents:

import { Injectable } from '@angular/core';
import { SQLite, SQLiteObject } from '@ionic-native/sqlite';

@Injectable()
export class SqLiteProvider {
    
    // we need to declare a var that will point to the initialized db:
    public db: SQLiteObject;

    constructor(
        private sqlite: SQLite
    ) 
    {
        this.sqlite.create({
            name: 'data.db',
            location: 'default'
          }).then((db: SQLiteObject) => {
              // here we will assign created db to our var db (see above)
              this.db = db;
              // we can now create the table this way:
              this.db.executeSql('create table danceMoves(name VARCHAR(32))', {})
                .then(() => console.log('Executed SQL'))
                .catch(e => console.log(e));
            }).catch(e => console.log(e));
    }
    
    // here in this provider we create getBalance method, that should be accessible by other pages:
    getBalance() {
    // we will do Promise here:
    return new Promise((resolve, reject) => {
        let balanceQuery = "select sum(trxamount) sumofamount from transactiontable";
        this.db.executeSql(balanceQuery, []).then((data) => {
            let balance = data.rows.item(0).sumofamount;
            // if we successfully obtain data - we resolve it, means it can be available via callback
            resolve(balance)
        }).catch((err)=>{ console.log(err); reject(err)}) // we deal with errors etc
    })
  }
}

Then you need to make this "global scope" provider if you want to use it anywhere in your app. For that you go to app.module.ts and import:

import { SqLiteProvider } from '../../providers/sqlite'; // ensure this is the folder where you created your sqlite.ts

Now for any page/component if you need to use this provider you just: - import it, - then use constructor to initialize it.

// some page or component:

import { SqLiteProvider } from '../../providers/sqlite' ... constructor( private sqlProvider: SqLiteProvider ) {}

Now in this page you can access this provider's methods by doing

this.sqlProvider.getBalance().then((data)=>{ console.log(data)}).

Thanks for your answer.Can you add a method (get data) with sample uses in the page? Will it be a promise or a simple method call ? — mnhmilu, Feb 11 '18 at 05:56
since you are dealing with DB it is async call and yes I would probably do Promise based one. If you confirm you need getBalance as example I can add it — Sergey Rudenko, Feb 11 '18 at 07:12
It will be very helpful, if you add the getBalance() method as promise and uses of that method in a page. Thanks. — mnhmilu, Feb 11 '18 at 08:25
OK I added the logic, BUT since I don't have your code as a working example I can verify that it works as is. But you should get the logic from this. Let me know after you try it in your project if it resolves your issue. — Sergey Rudenko, Feb 11 '18 at 18:01
Dit it help? Try implementing snd let me know if you need help — Sergey Rudenko, Feb 12 '18 at 06:28
Working like a charm ! I just received in page like this.dataService.getBalance().then((data:number) => { this.balance =data } ) .catch(...... — mnhmilu, Feb 12 '18 at 16:28

How to use database method ( Sqlite plugin) as a Provider in Ionic 3?

1 Answers1