Sqlite queries as a service/provider

I’ve been following the tutorial here:

What I want to do is, instead of having the sqlite queries in a particular page, I want them as a service or provider.

Here’s the question from above:

I have a provider in ionic two, and I have this:

getCategory() {
    this.storage.query('SELECT * FROM category')
      .then((data) => {
        var category = [];
        if (data.res.rows.length > 0) {
          for (var i = 0; i < data.res.rows.length; i++) {
            category.push({
              name: data.res.rows.item(i).name,
              type: data.res.rows.item(i).type,
              note: data.res.rows.item(i).note
            });
          }
        }
        // console.log(JSON.stringify(category)); 
        return category; // is this correct?
      }, (error) => {
        console.log('Error -> ' + JSON.stringify(error.err));
      });
  }

Then I wish to do something like this in my pages, after injecting the service:

  constructor(nav, theservice) {
    this.nav = nav;
    this.service = theservice
    this.category = service.getCategory()
  }

How do I return something the results in order to be able to use? Trying the above returns nothing when I console log this.category

The tutorial on how to use sqlite in ionic 2 has been helpful, but couldn’t figure out how to convert them into services/providers.

I think you might find something like this introduction to Promises help wrap your head around the fundamental concepts. You are going to have to completely trashcan your mental model of “yo service, give me a category”. Asynchronous programming doesn’t work that way. You have to say “once you’ve got that category, here’s what I want to do”.

So you want something like this:

getCategory(): Promise<Category[]> {
  // note: I recommend never ever using 'SELECT *' 
  // because it has a tendency to silently start failing 
  // in mysterious ways when you add columns later
  return this.storage.query('SELECT name, type, note from category')
    .then((data) => {
      let ncategories = data.res.rows.length;
      let rv:Category[] = new Array(ncategories);
      for (let i = 0; i < ncategories; i++) {
        let item:any = data.res.rows(i);
        rv[i] = {name: item.name, type: item.type, note: item.note};
      }
      return rv;
    });
}
constructor(service:CategoryService) {
  service.getCategory().then((category) => {
    this.category = category;
  });
}
2 Likes

Thank you for the clarification. And yes, I’ve been trying to wrap my head around promises for a while, started from the article you pointed. Really useful.

This query example helps makes things clearer.

What I settled on finally. Posting here, might help someone:

in the service/provider

getCategory() {
   var storage = new Storage(SqlStorage);
   return new Promise(function(resolve, reject) {
       return storage.query('SELECT name, type, note FROM category')
          .then((data) => {
             // kinda lazy workaround
             resolve(data.res.rows);
           });
        });
   }

in constructor:

static get parameters() {
    return [ [Myservice] ];
}
constructor(myservice) {
  myservice.getCategory()
  .then((category) => {
    // recreate new array from old category array, or else:
    // EXCEPTION: Cannot find a differ supporting object
    // https://github.com/angular/angular/issues/6392#issuecomment-171428006
    this.categories = Array.from(category);
    console.log(this.categories);
  })
  .catch((error) => {
    console.log(error);
  });

}

in template:

<ion-list>
    <button ion-item *ngFor="#category of categories">
        {{ category.name }}
        <br>
        <ion-icon name="arrow-forward" item-right></ion-icon>
    </button>
</ion-list>

I stand behind my previous suggestions. IMHO, your getCategory is an example of the explicit Promise constructor antipattern, and you are foisting unnecessary work onto the page constructor. You are fighting against the Promise concept, and I think anybody else struggling with a similar issue should ignore your “solution”.

1 Like

Still learning this promise thing, so forgive me for bad patterns.

You say [quote=“rapropos, post:5, topic:52000”]
and you are foisting unnecessary work onto the page constructor.
[/quote]

The only work I know to be doing in the constructor is recreating the array with this.

this.categories = Array.from(category);

The rest of the work is in templates, the ngFor.

Instead of that, I could do this in the service promise:

resolve(Array.from(data.res.rows))

which would send the ‘unnecessary’ workload into the promise.

This is way too simplified for an example to fully express the powers of Promise, so yeah, in an elaborate example, pushing the workload to constructor might be a bad practice. In this case, I just need the objects returned from the db. It is just a select. Had to use promise because the query on storage returns a promise. Otherwise, a ‘hit db and run’ was what I was thinking.

Right. That’s not the constructor’s job. When your synchronous brain wants to declare a function returning a Foo, and it is coming from an asynchronous source, be it storage or a network request or whatever, make that function return a Promise<Foo> (or Observable<Foo>). Don’t just return whatever the upstream source gives you and make callers unpack it. Every single caller will have to have that same unpack code, which is unnecessary repetition.

myservice.getCategory()
  .then((category) =>

Anybody reading this code would expect category to be a Category. It isn’t. It’s a Promise<any>. It should actually be named categories, and it should be a Category[]. I also think Array.from is analogous to SELECT *. If Category objects are known to have certain properties, they should all be explicitly assigned in getCategories(). Otherwise, you set yourself up for a situation where you add a field to Category, forget to put it in the SQL schema, and can’t see anywhere in your code where it failed to be assigned.

Your comment recreate new array from old category array doesn’t describe what is happening. There is no ‘old category array’ being used. There is a Promise resolving to another Promise, which should not ever happen.

I’m sorry if any of this sounds overly harsh, but if you’re marking things as solutions and suggesting that future readers might benefit from them, I think it’s in all of our best interests to make them as good as we can.

2 Likes

Quick question. In plain javascript, how would you rewrite this part of your snippet?

getCategory(): Promise < Category[] > {
return this.storage.query('SELECT name, type, note from category')
  .then((data) => {
    let ncategories = data.res.rows.length;
    let rv: Category[] = new Array(ncategories);
    for (let i = 0; i < ncategories; i++) {
      let item: any = data.res.rows(i);
      rv[i] = { name: item.name, type: item.type, note: item.note };
    }
    return rv;
  });
}

Sorry, I don’t know. I absolutely can’t stand JavaScript, so I use any possible way to avoid ever writing it. Used to be CoffeeScript, now TypeScript.

Okay. I hope others used to plain Javascript get to see and help. thanks

Is there any necessity of using platform.ready in the code you demonstrated or would that be unnecessary as that promise already accounts for when the data is ready?

Yes there is a necessity, unless you don’t really bother some of your code running when the platform isn’t ready (which will likely throw errors)

the platform.ready is similar to the $(document).ready( function() {} ) in JQuery, however for platforms on which your code will run on.

Yeah, but I’m not entirely sure how exactly to wrap the promise in there? All my SQL queries are handled by a data model I’m using and I’ve made it so that all of them - but this one, now - are wrapped in platform.ready.

This is the adapted code:

constructor(nav,itemsSQL) {
...
    itemsSQL.refreshFromSQL().then((itemStorage) => {
       this.itemStorage = itemStorage;
    });
}

provider itself:

refreshFromSQL() {
    return this.itemsSQL.query("SELECT * FROM data").then((data) => {
        let len = data.res.rows.length;
        let itemStorage = new Array(len);
        for (let i = 0; i < len; i++) {
            itemStorage[i] = {name: data.res.rows.item(i).name, systemid: data.res.rows.item(i).systemid};
        }
        return itemStorage;
    });
}

This worked.

Yes on platform.ready. There are a number of ways you could enforce it. Probably the simplest would be to wrap the construction of the database object in the service constructor like so:

constructor() {
  platform.ready().then(() => {
    this.initializeStorage();   
  });
}

And then make getCategory either reject or resolve with an empty array if it is called before the platform is ready and the storage object is usable:

getCategory() {
  if (!this.storage) {
    return Promise.resolve([]);
  }

  // storage is usable, do normal operation
}