How should I set up SQL queries in a service?

#1

I have a local SQL table called Idioms and a table called Favorites. Favorites only has one column, an idiom_id column. I can check and see if a user has favorited an idiom by running a query like so.

    this.sqlite.create({
        name: "ionicdb.db",
        location: "default"
      }).then((db: SQLiteObject) => {
    database.executeSql("SELECT * FROM Favorites WHERE idiom_id=?", [idiom_id])
      .then(res => {
        return res.rows.length > 0 ? true : false
      })
})

I’ll be using this a lot so I want to move it into a service, but I’m not quite sure what I should return because the sql query returns a promise.

Doing something like this

export class FavoritesService {

    isFavorited(idiom_id, database) {
        database.executeSql("SELECT * FROM Favorites WHERE idiom_id=?", [idiom_id])
          .then(res => {
             return res.rows.length > 0 ? true : false
         })
    }
}

I’m getting an error when I assign something to the return value like so (in my page.ts file)


this.alertCtrl.create({
    title: "SQL",
    subTitle:  isFavorited(idiom_id, database) .toString()
}).present()

The error I get is

Property 'toString' does not exist on type 'void'

Is this because the SQL query is running asynchronously? If so, do I need to return a promise in my function, instead of the current way?

#2

Add “return” just before data.execureSql(…)
This will return the promise of writing to the SQLdb.

#3

Do I have to return the promise and then do my logic outside of the function, or is it possible to return a value from my .then() block?

For example:

export class FavoritesService {

    isFavorited(idiom_id, database) {
        let check = false;
        database.executeSql("SELECT * FROM Favorites WHERE idiom_id=?", [idiom_id])
          .then(res => {
             check =  res.rows.length > 0 ? true : false
         })

      return check
    }
}

That would be a way to return a bool instead of a promise, right?

Also, does this “block” all other code from running until the query is complete?

#4

You want to return a promise, because you need to tell whatever is calling this service to wait until you’ve finished. You could certainly change what your promise resolves with using a similar method to above:

isFavorited(idiomId, database): Promise<boolean> {
  return database.executeSql(`SELECT * FROM Favorites WHERE idiom_id=${idiomId}`)
    .then(results => results.rows.length > 0);
}

So now you return a promise, but a promise that resolves with a boolean. This is what you want, because you need to then in your controller be able to wait for this call to complete, so you can then chain the promise, or use async await.

Edit: In the code you posted above, you would always return false, because you return check before the query completes.

#5

Also, probably don’t pass the database into each function, keep the database in the service itself. Open the connection on app start and set it to a private property of the database service. Otherwise things are going to get really ugly.

The error message in your original post Property 'toString' does not exist on type 'void' makes perfect sense, your isFavorited function does not have a return statement. The callback you pass to your promise .then has a return statement, but isFavorited does not return anything, so typescript returns void and you get your error message.

If you use the sample I poseted before, your code will work by simply using async/await or regular promises. I’ll show async/await since it’s simpler:

async showMyAlert(idiomId) {
  this.alertCtrl.create({
    title: 'SQL',
    subTitle: await this.mySqlService.isFavorited(idiomId)
  })
  .present()
}

You should probably wrap that in a try/catch block though so you can catch errors. But, barring sql errors, that will work.

#6

Thanks. I’m new to using promises (I’m just thinking of them in the same as AJAX requests. Is that wrong?). I’m not quite clear on the return value though. Is it a boolean or a promise? I want to be able to write something like this

addFavorite(idiom_id, database) {
 if(!isFavorited(idiom_id, database)) {
   return favoriteIdiom(idiom_id, database)
 } else {
   return false;
 } 
}
#7

Promises are used with ajax, anything async uses them. I’d recommend reading up on it. The return value, Promise<boolean>, is a promise that resolves with a boolean value. I’m not really going to explain the concept via a post here, but do some googling and you’ll get some better ideas. The short version is anything that takes a long time (in computer time), like getting data over the internet or writing to a hard drive, is done out of sync with the rest of your code. Javascript says “this is going to take a while, I’m going to keep running the rest of your code, when this slow task finishes, I’ll do whatever is in the .then callback.”

So, you have to do all your async stuff with either promises or callbacks, and promises are far easier to work with provided you avoid the major promise anti-patterns. Async/await makes it even easier. But ultimately it’s something you’re going to want to learn if you want to continue to develop in JS.