Why use db.each() instead db.all() or db.get() in node-sqlite?


#1

Question

Why use db.each() instead of db.all() or db.get() in node-sqlite?

Answer

db.each(), as we may remember from this lesson, runs the query passed as an argument and for each result from the database, it will run the callback.

In the given example:

db.each("SELECT * FROM Dog WHERE breed = 'Labrador'", 
  (error, row) => {
  /*gets called for every row our query returns*/
    console.log(`${row.name} is a good dog`);
  });

For each returned Labrador dog, we will console.log their name and that they are a good dog, as a difference to that, using db.all() will return all the items from the database to memory, then, run the query, and call the callback on all the results of the query, so it will be a great method if we intend to use all the data, like for example, arranging our dogs by breed:

db.all("SELECT * FROM Dog ORDER BY breed",
  (error, rows) => {
  // receives all the results as an array
    rows.forEach( row => console.log(`${ row.name} is a ${row.breed}`);)
  });

Therefore is better to use db.each() when you intend to retrieve some items from the database.

We use db.get() only when we want one result, since it will only return the first one found that matches the query:

db.get("SELECT * FROM Dog WHERE dogname = 'Poochy'", (err, row)=>{
  return row;
});

Most of the time, we will find ourselves using db.each() more often than not, is the most flexible, since it does not return all the items in the database, but it also does not return only one, which makes it work with only the memory necessary. There will be times that you want to get all the items in the database, or you only need one, but for most of the other options, db.each() is a great option.